An incorrect execution plan leads to incorrect SQL query results.

[zhangle1250]

Steps to reproduce the behavior (Required)

1. CREATE TABLE DWD_YX_ZLSR (
   HYLX varchar(65533) NULL COMMENT "",
   YWLX varchar(65533) NULL COMMENT "",
   YWLX2 varchar(65533) NULL COMMENT "",
   SR decimal(16, 4) NULL COMMENT "",
   SR_Q decimal(16, 4) NULL COMMENT "",
   KHMC varchar(65533) NULL COMMENT "",
   SRFB varchar(65533) NULL COMMENT "",
   KHYWLXSR decimal(16, 4) NULL COMMENT "",
   YWFWDM varchar(65533) NULL COMMENT "",
   JZRQ varchar(65533) NULL COMMENT "",
   YWDL varchar(65533) NULL COMMENT "",
   BH varchar(65533) NULL COMMENT "",
   HTH varchar(65533) NULL COMMENT "",
   XSDDM varchar(65533) NULL COMMENT "",
   FZR varchar(65533) NULL COMMENT "",
   KHDM varchar(65533) NULL COMMENT "",
   WLDM varchar(65533) NULL COMMENT "",
   HBYWFWDM varchar(65533) NULL COMMENT ""
   ) ENGINE=OLAP
   DUPLICATE KEY(HYLX, YWLX, YWLX2)
   DISTRIBUTED BY HASH(HYLX, YWLX, YWLX2) BUCKETS 1
   PROPERTIES (
   "replication_num" = "3",
   "in_memory" = "false",
   "enable_persistent_index" = "false",
   "replicated_storage" = "true",
   "compression" = "LZ4"
   );

CREATE VIEW tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR (HYLX, YWLX, YWLX2, SR, SR_Q, KHMC, SRFB, KHYWLXSR, YWFWDM, JZRQ, YWDL, BH, HTH, XSDDM, FZR, KHDM, WLDM, HBYWFWDM) AS SELECT aaaaaaa.DWD_YX_ZLSR.HYLX, aaaaaaa.DWD_YX_ZLSR.YWLX, aaaaaaa.DWD_YX_ZLSR.YWLX2, aaaaaaa.DWD_YX_ZLSR.SR, aaaaaaa.DWD_YX_ZLSR.SR_Q, aaaaaaa.DWD_YX_ZLSR.KHMC, aaaaaaa.DWD_YX_ZLSR.SRFB, aaaaaaa.DWD_YX_ZLSR.KHYWLXSR, aaaaaaa.DWD_YX_ZLSR.YWFWDM, CAST(aaaaaaa.DWD_YX_ZLSR.JZRQ AS DATE) AS JZRQ, aaaaaaa.DWD_YX_ZLSR.YWDL, aaaaaaa.DWD_YX_ZLSR.BH, aaaaaaa.DWD_YX_ZLSR.HTH, aaaaaaa.DWD_YX_ZLSR.XSDDM, aaaaaaa.DWD_YX_ZLSR.FZR, aaaaaaa.DWD_YX_ZLSR.KHDM, aaaaaaa.DWD_YX_ZLSR.WLDM, aaaaaaa.DWD_YX_ZLSR.HBYWFWDM
FROM aaaaaaa.DWD_YX_ZLSR AS DWD_YX_ZLSR;

2. INSERT INTO '....'

INSERT INTO aaaaaaa.DWD_YX_ZLSR (HYLX,YWLX,YWLX2,SR,SR_Q,KHMC,SRFB,KHYWLXSR,YWFWDM,JZRQ,YWDL,BH,HTH,XSDDM,FZR,KHDM,WLDM,HBYWFWDM) VALUES
(NULL,'AGG','AGG',22877.3600,NULL,'教育科技发展有限公司',NULL,NULL,'MS01','20240401','数云融合',NULL,NULL,'0014639837','ZHENGDL','0100321181','000000000080008667',NULL),
(NULL,'AGG','AGG',7675.8700,NULL,'数码信息系统有限公司',NULL,NULL,'MP01','20240301','数云融合',NULL,NULL,'0013561365',NULL,'0100090384','000000000080008667',NULL),
(NULL,'AGG','AGG',2725.6600,NULL,'超拓能科技有限公司',NULL,NULL,'PU01','20230401','数云融合',NULL,NULL,'0013977922','RUANXC','0100314329','000000000282342262',NULL),
(NULL,'AGG','AGG',149.4300,NULL,'数码科技有限公司',NULL,NULL,'RF01','20230301','数云融合',NULL,NULL,'0013904445','ZHANGXFAQ','0100198608','000000000282348870',NULL),
(NULL,'AGG','AGG',8952.4000,NULL,'LOANCLOUDTECHNOLOGYLIMITED',NULL,NULL,'QA01','20240101','数云融合',NULL,NULL,'0014511658','QIANXW','0100322768','000000000080008828',NULL),
(NULL,'AGG','AGG',4245.2800,NULL,'企业通信有限公司',NULL,NULL,'KD01','20230701','数云融合',NULL,NULL,'0014169416','WANGCYM','0100260405','000000000080008667',NULL),
(NULL,'AGG','AGG',6701.0400,NULL,'网络系统有限公司',NULL,NULL,'LB01','20240601','数云融合',NULL,NULL,'2145782039',NULL,'0100001925','000000000282228880',NULL),
(NULL,'AGG','AGG',37157.5500,NULL,'有限责任公司',NULL,NULL,'5V01','20230501','数云融合',NULL,NULL,'0014018317','MAQY','0100251752','000000000083001428',NULL),
(NULL,'AGG','AGG',28873.0000,NULL,'有限公司成都分公司',NULL,NULL,'5701','20230301','数云融合',NULL,NULL,'0013915776','HEXKB','0100314642','000000000690003781',NULL),
(NULL,'AGG','AGG',4937.1700,NULL,'信息科技有限公司',NULL,NULL,'F601','20230201','数云融合','P202301416595','663162','0013872288','WANGTTAE','0100275032','000000000238051090',NULL);

4. SELECT '....'

SELECT
t4.f1 AS METRIC_$$SELF$$_rGFqnridcINDfxIs,
t4.f0 AS DIMENSION_metric_time_DveQKAuzpESNgOyx
FROM
(
SELECT
t0.JZRQ AS f0,
COUNT(CASE WHEN DATE_TRUNC('YEAR', t0.JZRQ) = DATE_ADD(DATE_TRUNC('YEAR', CURRENT_DATE ()), INTERVAL 0 YEAR) AND t0.YWDL = '数云融合' THEN t0.HYLX ELSE NULL END) AS f1
FROM
(
SELECT
tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.JZRQ,
tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.YWDL,
tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.HYLX
FROM
default_catalog.bbbbbbbbbbbbbcan.tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR AS tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR
WHERE
DATE_TRUNC('YEAR',tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.JZRQ) = DATE_ADD(DATE_TRUNC('YEAR', CURRENT_DATE ()), INTERVAL 0 YEAR)
AND tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.YWDL = '数云融合') AS t0
WHERE
DATE_TRUNC('YEAR',t0.JZRQ) = DATE_ADD(DATE_TRUNC('YEAR', CURRENT_DATE ()), INTERVAL 0 YEAR)
AND t0.YWDL = '数云融合'
GROUP BY
t0.JZRQ) AS t4
WHERE
DATE_TRUNC('YEAR',t4.f0) = DATE_ADD(DATE_TRUNC('YEAR', CURRENT_DATE ()), INTERVAL 0 YEAR)
ORDER BY
(t4.f0) IS NULL,
t4.f0
LIMIT 4001;

Expected behavior (Required)

应该只有2024年的数据，不应该有2023年的数据

Real behavior (Required)

StarRocks version (Required)

• 3.2.8
  explain:
  PLAN FRAGMENT 0
  OUTPUT EXPRS:39: count | 19: cast
  PARTITION: UNPARTITIONED

RESULT SINK

6:Project
| <slot 19> : 19: cast
| <slot 39> : 39: count
| limit: 4001
|
5:MERGING-EXCHANGE
limit: 4001

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 05
UNPARTITIONED

4:TOP-N
| order by: <slot 40> 40: expr ASC, <slot 19> 19: cast ASC
| offset: 0
| limit: 4001
|
3:Project
| <slot 19> : 19: cast
| <slot 39> : 39: count
| <slot 40> : 19: cast IS NULL
|
2:AGGREGATE (update finalize)
| output: count(38: case)
| group by: 19: cast
|
1:Project
| <slot 19> : 41: cast
| <slot 38> : if((date_trunc('year', 41: cast) = '2024-01-01') AND (11: YWDL = '数云融合'), 1: HYLX, NULL)
| common expressions:
| <slot 41> : CAST(10: JZRQ AS DATE)
|
0:OlapScanNode
TABLE: DWD_YX_ZLSR
PREAGGREGATION: ON
PREDICATES: 11: YWDL = '数云融合'
partitions=1/1
rollup: DWD_YX_ZLSR
tabletRatio=1/1
tabletList=7073700
cardinality=200
avgRowSize=21.0

[satanson]

@zhangle1250 the plan is wrong, OlapScanNode misses its predicate "date_trunc('year', 41: cast) = '2024-01-01') AND (11: YWDL = '数云融合')", this expr occurs 4 times in the SQL, it seems that predicates pushdown or tedious predicates elimination works in the wrong way. I would fix it.

[satanson]

#50854