2.0.12 升级为 2.1.4后同一条sql执行结果不同

Viewed 197
2.1 2.0

建表语句：

CREATE TABLE `recursive_fact_bai_01_number` (
  `id` VARCHAR(20) NULL,
 `j_interval_day_1` VARCHAR(1000) NULL,
  `j_interval_day_2` VARCHAR(1000) NULL,
  `k_number` DECIMAL(10, 0) NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT 'olap'
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

查询语句：

select  min(k_number) a1,cast(J_INTERVAL_DAY_2 as char(100))
      ,first_value(cast(J_INTERVAL_DAY_2 as char(100)) ) over(partition by J_INTERVAL_DAY_1 
              order by cast(J_INTERVAL_DAY_1 as char(100)) ,cast(J_INTERVAL_DAY_2 as char(100))
              ,min(k_number)
              rows between 2 preceding and unbounded following) a4
      ,grouping_id(cast(J_INTERVAL_DAY_1 as char(100)) ) a5
from recursive_fact_bai_01_number
where k_number=67
group by cube(J_INTERVAL_DAY_1 ,cast(J_INTERVAL_DAY_1 as char(100)) ,cast(J_INTERVAL_DAY_2 as char(100)) )
having grouping_id(cast(J_INTERVAL_DAY_1 as char(100)) ) =0

2.0.12 执行结果：
image.png

2.1.4执行结果：
image.png

去掉having过滤后，两个版本执行结果一致
image.png

aa10
2 Answers

这样验证下，先看看是不是优化器的问题：

确定下2.0.12 是有有开启新优化器，show variables like "%enable_nereids_planner%"; 同步下 2.0.12 和 2.1.4的优化器，保持一致。

阿渊@SelectDB5960

老师，这个问题方便的话加我下主页微信的，我们看下的

未和用户建联，长时间未更新，先close掉，遇到类似问题可以先看看是否是优化器的问题

阿渊@SelectDB5960