Обсуждение: BUG #16501: Incorrect result. select multi_key_columns_range_partition_table
BUG #16501: Incorrect result. select multi_key_columns_range_partition_table
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 16501 Logged by: Kobayashi Hisanori Email address: hisanori.kobayashi.bp@nttdata.com PostgreSQL version: 12.2 Operating system: Windows10 Description: I found that Incorrect-result sql. Same for 12.2 and 12.3 I am not good at English, so I will send a reproduction script. ------------------------------ -- ***** execute from psql -- -- ***** Create Test Table drop table if exists pt_test02 ; create table pt_test02 ( kbn smallint not null , nen char(4) not null , mm char(2) not null , cd char(3) not null , val00 numeric(15, 3) , usr varchar(10) ) partition by list(kbn) with (oids=false) ; create table pt_test02_1 partition of pt_test02 for values in (1) partition by range(nen, mm) with (oids=false) ; create table pt_test02_1_2019_01 partition of pt_test02_1 for values from ('2019', '01') to ('2019', '07') ; create table pt_test02_1_2019_02 partition of pt_test02_1 for values from ('2019', '07') to ('2019', '13') ; create table pt_test02_1_2020_01 partition of pt_test02_1 for values from ('2020', '01') to ('2020', '07') ; create table pt_test02_1_2020_02 partition of pt_test02_1 for values from ('2020', '07') to ('2020', '13') ; create table pt_test02_2 partition of pt_test02 for values in (2) partition by range(nen, mm) with (oids=false) ; create table pt_test02_2_2019_01 partition of pt_test02_2 for values from ('2019', '01') to ('2019', '07') ; create table pt_test02_2_2019_02 partition of pt_test02_2 for values from ('2019', '07') to ('2019', '13') ; create table pt_test02_2_2020_01 partition of pt_test02_2 for values from ('2020', '01') to ('2020', '07') ; create table pt_test02_2_2020_02 partition of pt_test02_2 for values from ('2020', '07') to ('2020', '13') ; -- -- ***** Test Data Insert insert into pt_test02 values(1, '2020', '01', 1, null, null) ; -- -- ***** Test SQL and result -- select count(*) from pt_test02 where kbn = 1 and nen <= '2020' and mm = '01' ; -- ***** Result = 0 ... incorrect -- select count(*) from pt_test02_1 where kbn = 1 and nen <= '2020' and mm = '01' ; -- ***** Result = 0 ... incorrect -- select count(*) from pt_test02_1_2020_01 where kbn = 1 and nen <= '2020' and mm = '01' ; -- ***** Result = 1 ... correct -- select count(*) from pt_test02 where kbn = 1 and nen = '2020' and mm = '01' ; -- ***** Result = 1 ... correct ------------------------------
Re: BUG #16501: Incorrect result. select multi_key_columns_range_partition_table
От
David Rowley
Дата:
On Thu, 18 Jun 2020 at 21:49, PG Bug reporting form <noreply@postgresql.org> wrote: > I am not good at English, so I will send a reproduction script. Many thanks for the report. This is certainly a bug in the partition pruning code for range partitioned tables. A more simple case is: create table rp (a int, b int) partition by range (a,b); create table rp_2020 partition of rp for values from (2020, 1) to (2020, 7); insert into rp values(2020,1); explain select * from rp where a <= 2020 and b = 1; Which gives: QUERY PLAN ------------------------------------------ Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false (2 rows) # set enable_partition_pruning=off; SET # select * from rp where a <= 2020 and b = 1; a | b ------+--- 2020 | 1 (1 row) This seems to be caused by the following code, which assumes it's ok to use the prefix for <= / = / >= btree operators. Initially, I imagined that there's no reason to allow anything apart from = there, but I suppose we could consider sub-ranges of partitions that are <= 2020, but then I don't really understand why the same thing can't be done with < 2020. /* * We can't consider subsequent partition keys if the * clause for the current key contains a non-inclusive * operator. */ if (pc->op_strategy == BTLessStrategyNumber || pc->op_strategy == BTGreaterStrategyNumber) consider_next_key = false; break; Perhaps Amit can explain the reason for that? David
Re: BUG #16501: Incorrect result. select multi_key_columns_range_partition_table
От
Etsuro Fujita
Дата:
On Fri, Jun 19, 2020 at 9:00 AM David Rowley <dgrowleyml@gmail.com> wrote: > On Thu, 18 Jun 2020 at 21:49, PG Bug reporting form > <noreply@postgresql.org> wrote: > > I am not good at English, so I will send a reproduction script. > > Many thanks for the report. This is certainly a bug in the partition > pruning code for range partitioned tables. Thanks, Kobayashi-san! > A more simple case is: > > create table rp (a int, b int) partition by range (a,b); > create table rp_2020 partition of rp for values from (2020, 1) to (2020, 7); > insert into rp values(2020,1); > explain select * from rp where a <= 2020 and b = 1; > > Which gives: > QUERY PLAN > ------------------------------------------ > Result (cost=0.00..0.00 rows=0 width=0) > One-Time Filter: false > (2 rows) > > > # set enable_partition_pruning=off; > SET > # select * from rp where a <= 2020 and b = 1; > a | b > ------+--- > 2020 | 1 > (1 row) Thanks for the simple test case, David! > This seems to be caused by the following code, which assumes it's ok > to use the prefix for <= / = / >= btree operators. I think the root cause for this is the same as that for bug #16500. See the commit message in the patch in [1]. > Initially, I > imagined that there's no reason to allow anything apart from = there, > but I suppose we could consider sub-ranges of partitions that are <= > 2020, but then I don't really understand why the same thing can't be > done with < 2020. > > /* > * We can't consider subsequent partition keys if the > * clause for the current key contains a non-inclusive > * operator. > */ > if (pc->op_strategy == BTLessStrategyNumber || > pc->op_strategy == BTGreaterStrategyNumber) > consider_next_key = false; > break; Me either. Best regards, Etsuro Fujita [1] https://www.postgresql.org/message-id/CAPmGK16pXA_5-Sct%3DnWJh4SSPTVv7YAjXYjyz8iRt7WHBKdpjA%40mail.gmail.com