Skip to content

[Bug] An analysis error will report when there are different types in case-when-then-else with group by clause #4645

@xy720

Description

@xy720

Describe the bug
If there are different scalar types in case-when-then-else with group by clause, an error will report.

For example, there is a 9 columns table named test_tbl:

+----------------------+-------------+------+-------+---------------------+---------+
| Field                | Type        | Null | Key   | Default             | Extra   |
+----------------------+-------------+------+-------+---------------------+---------+
| event_day            | INT         | No   | true  | NULL                |         |
| event_hour           | TINYINT     | No   | true  | 0                   |         |
| event_minute         | TINYINT     | No   | true  | 0                   |         |
| event_time           | DATETIME    | No   | true  | 1970-01-01 00:00:00 |         |
| search_page          | VARCHAR(30) | No   | true  |                     |         |
| search_source        | TINYINT     | No   | true  | 0                   |         |
| soft_version         | VARCHAR(30) | No   | true  |                     |         |
| not_arrived_category | VARCHAR(30) | No   | true  |                     |         |
| pv                   | FLOAT       | No   | false | 0                   | REPLACE |
+----------------------+-------------+------+-------+---------------------+---------+
CREATE TABLE `baiduapp_strategy_performance_search_ios_arrive` (
  `event_day` int(11) NOT NULL COMMENT "日期,如20191016",
  `event_hour` tinyint(4) NOT NULL DEFAULT "0" COMMENT "小时,如8",
  `event_minute` tinyint(4) NOT NULL DEFAULT "0" COMMENT "分钟,如59",
  `event_time` datetime NOT NULL DEFAULT "1970-01-01 00:00:00" COMMENT "隶属时间(区间查询用)",
  `search_page` varchar(30) NOT NULL DEFAULT "" COMMENT "page来源",
  `search_source` tinyint(4) NOT NULL DEFAULT "0" COMMENT "search_source来源",
  `soft_version` varchar(30) NOT NULL DEFAULT "" COMMENT "APP版本,如11.15.0.2",
  `not_arrived_category` varchar(30) NOT NULL DEFAULT "" COMMENT "未到达原因",
  `pv` float NOT NULL DEFAULT "0" COMMENT "聚合pv数"
) ENGINE=OLAP
UNIQUE KEY(`event_day`, `event_hour`, `event_minute`, `event_time`, `search_page`, `search_source`, `soft_version`, `not_arrived_category`)
COMMENT "OLAP"
PARTITION BY RANGE(`event_day`)
(PARTITION p202006 VALUES [("20200601"), ("20200701")),
PARTITION p202007 VALUES [("20200701"), ("20200801")),
PARTITION p202008 VALUES [("20200801"), ("20200901")),
PARTITION p202009 VALUES [("20200901"), ("20201001")),
PARTITION p202010 VALUES [("20201001"), ("20201101")),
PARTITION p202011 VALUES [("20201101"), ("20201201")),
PARTITION p202012 VALUES [("20201201"), ("20210101")))
DISTRIBUTED BY HASH(`event_day`) BUCKETS 16
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);

The following sql will report an analysis error.

SELECT
    a.event_day,
    a.search_page,
    GROUP_CONCAT(a.search_source) AS search_source,
    a.soft_version,
    sum(b.pv) AS daoda_pv,
    sum(b.new_pv) AS new_daoda_pv,
    sum(a.pv) AS total_pv,
    sum(b.pv) / sum(a.pv) * 100 AS arrived_rate,
    sum(b.new_pv) / sum(a.pv) * 100 AS new_arrived_rate
FROM
(
    SELECT
        event_day,
        case when '' = '' then 'all' else '' end as search_page,
        case when "('1','2','5')" = '' then 'all' else search_source end as search_source,
        case when '' = '' then 'all' else '' end as soft_version,
        SUM(pv) AS pv
    FROM baiduapp_strategy_performance_search_ios_arrive
    WHERE event_day BETWEEN 20200908 and 20200914
    AND 1=1
    AND search_source in ('1','2','5')
    AND 1=1
    AND soft_version >= '11.23.0.0'
    GROUP BY event_day,search_page,search_source,soft_version
    ORDER BY search_source
) a 
LEFT JOIN
(
    SELECT
        event_day,
        case when '' = '' then 'all' else '' end as search_page,
        case when "('1','2','5')" = '' then 'all' else search_source end as search_source,
        case when '' = '' then 'all' else '' end as soft_version,
        SUM(if(not_arrived_category = '', pv, 0)) AS pv,
        SUM(pv) AS new_pv
    FROM baiduapp_strategy_performance_search_ios_arrive
    WHERE event_day BETWEEN 20200908 and 20200914
    AND 1=1
    AND search_source in ('1','2','5')
    AND 1=1
    AND not_arrived_category in ('', 'stay_too_short')
    AND soft_version >= '11.23.0.0'
    GROUP BY event_day,search_page,search_source,soft_version
    ORDER BY search_source
) b ON a.event_day = b.event_day
   AND a.search_page = b.search_page
   AND a.search_source = b.search_source
   AND a.soft_version = b.soft_version
GROUP BY
    a.event_day,
    a.search_page,
    a.soft_version
ORDER BY event_day DESC
limit 0, 5000

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions