筛选未生效导致数据计算错误

💬 StarRocks 用户问答
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述

SQL执行计划生成错误，筛选未生效

【背景】做过哪些操作？

  1. DDL&DML语句
    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”
    );

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);

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;

  1. 查询语句
    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;
    【业务影响】
    数据计算错误
    【是否存算分离】
    是
    【StarRocks版本】例如：3.3.0
    【集群规模】例如：3fe（1 follower+2observer）+3be
    【机器信息】CPU虚拟核/内存/网卡，例如：16C/64G/万兆
    【联系方式】社区群9-行者无疆
    【附件】
    1502×670 81.8 KB

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

执行计划：
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

#2

在子查询中，你限制了DATE_TRUNC(‘YEAR’, t0.JZRQ) = DATE_ADD(DATE_TRUNC(‘YEAR’, CURRENT_DATE()), INTERVAL 0 YEAR)，但这可能不足以过滤掉所有不在2024年的数据。特别是，如果JZRQ字段包含非标准日期格式或者数据类型为字符串，而非日期类型，可能会导致错误的比较和过滤。

#3

数据都是标准的日期格式

#4

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 tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR AS tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR
WHERE
YEAR(tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.JZRQ) = YEAR(CURDATE())
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;

image
image1839×730 32.1 KB

#5

请问你使用的版本是哪个？

#6

方式一：
DATE_TRUNC(‘YEAR’,tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.JZRQ) = DATE_ADD(DATE_TRUNC(‘YEAR’, CURRENT_DATE ()), INTERVAL 0 YEAR)

方式二：
YEAR (tn_32_bbbbbbbbbbbbb_0828_2__default__DWD_YX_ZLSR.JZRQ) = YEAR (CURRENT_DATE ())

用方式一的执行结果不对

京ICP备2022026421号-1