【GreenPlum Bug】查询分区表时对时间字段使用or关键字过滤时的Bug问题

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

问题暴露

1. 由于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;
   

   

2. 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;
   

   

3. 使用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;
   

   

解决

暂时还没查到具体问题, 只能在开发中规避该问题