GreenPlum数据库版本
select version();
PostgreSQL 9.4.26 (Greenplum Database 6.21.2 build commit:e5309e047a1f0f00bfa0c1fccf7d141b5e78a41d Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 25 2022 02:16:31
创建测试表
drop table if exists demo.test_pt;
create table demo.test_pt(
datedday date,
i int
)
partition by range(datedday) (
partition pn_20221014 start ('2022-10-01'::date) end ('2022-10-02'::date) with (appendonly='true', orientation='row'),
partition pn_20221015 start ('2022-10-02'::date) end ('2022-10-03'::date) with (appendonly='true', orientation='row'),
partition pn_20221016 start ('2022-10-03'::date) end ('2022-10-04'::date) with (appendonly='true', orientation='row'),
partition pn_20221017 start ('2022-10-04'::date) end ('2022-10-05'::date) with (appendonly='true', orientation='row'),
partition pn_20221018 start ('2022-10-05'::date) end ('2022-10-06'::date) with (appendonly='true', orientation='row'),
partition pn_20221019 start ('2022-10-06'::date) end ('2022-10-07'::date) with (appendonly='true', orientation='row'),
partition pn_20221020 start ('2022-10-07'::date) end ('2022-10-08'::date) with (appendonly='true', orientation='row'),
partition pn_20221021 start ('2022-10-08'::date) end ('2022-10-09'::date) with (appendonly='true', orientation='row'),
partition pn_20221022 start ('2022-10-09'::date) end ('2022-10-10'::date) with (appendonly='true', orientation='row'),
partition pn_20221023 start ('2022-10-10'::date) end ('2022-10-11'::date) with (appendonly='true', orientation='row'),
default partition pdefault with (appendonly='true', orientation='row')
);
insert into demo.test_pt(datedday,i)values
('2022-10-01',1),
('2022-10-01',2),
('2022-10-02',1),
('2022-10-02',2),
('2022-10-03',1),
('2022-10-03',2),
('2022-10-04',1),
('2022-10-04',2),
('2022-10-05',1),
('2022-10-05',2),
('2022-10-06',1),
('2022-10-06',2),
('2022-10-07',1),
('2022-10-07',2),
('2022-10-08',1),
('2022-10-08',2),
('2022-10-09',1),
('2022-10-09',2),
('2022-10-10',1),
('2022-10-10',2),
('2022-10-11',1),
('2022-10-11',2);
问题暴露
-
由于or之间的条件类型不同, 触发bug, 导致查询结果中丢失了date类型的结果, 查询表为非分区表时不会暴露该问题
select datedday,sum(i) from demo.test_pt where datedday = ('2022-10-10')::date or datedday = ('2022-10-09')::timestamp or datedday = ('2022-10-08')::date or datedday = ('2022-10-07')::timestamp group by 1 order by 1;
-
or关键字之间的条件类型相同时, 不会暴露该问题
select datedday,sum(i) from demo.test_pt where datedday = ('2022-10-10')::date or datedday = ('2022-10-09')::date or datedday = ('2022-10-08')::date or datedday = ('2022-10-07')::date group by 1 order by 1; -- 或 select datedday,sum(i) from demo.test_pt where datedday = ('2022-10-10')::timestamp or datedday = ('2022-10-09')::timestamp or datedday = ('2022-10-08')::timestamp or datedday = ('2022-10-07')::timestamp group by 1 order by 1;
-
使用in关键字代替or关键字时, 不会暴露该问题
select datedday,sum(i) from demo.test_pt where datedday in ( ('2022-10-10')::date, ('2022-10-09')::timestamp, ('2022-10-08')::date, ('2022-10-07')::timestamp) group by 1 order by 1;
解决
暂时还没查到具体问题, 只能在开发中规避该问题