Обсуждение: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 18123 Logged by: The execution plan for the left join is incorrect, returning incorrect rows of data. Email address: dafoer_x@163.com PostgreSQL version: 14.0 Operating system: centos_x86 Description: create table tb11(a int, b int); insert into tb11 values(1,1); select count(1) from tb11 a1 where a1.a =1; select count(1) from tb11 a1 left join tb11 a2 on a1.a=a2.a where a1.a=1 and a2.a=3; postgres=# select count(1) from tb11 a1 where a1.a =1; count ------- 1 (1 row) postgres=# select count(1) from tb11 a1 left join tb11 a2 on a1.a=a2.a where a1.a=1 and a2.a=3; count ------- 0 (1 row) postgres=# explain select count(1) from tb11 a1 left join tb11 a2 on a1.a=a2.a where a1.a=1 and a2.a=3; QUERY PLAN ------------------------------------------------ Aggregate (cost=0.00..0.01 rows=1 width=8) -> Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false (3 rows) In the left join scenario, the conclusion of 'result = false' cannot be obtained. Could you please help analyze this issue? Thank you. dafoer
Re: BUG #18123: The execution plan for the left join is incorrect, returning incorrect rows of data.
От
Sergei Kornilov
Дата:
Hello Usual query error, not a bug. "where a2.a=3" turns left join to inner join because a2.a can not be null here. This way the conditions contradict each other and it is possible to immediately get an empty set as a result without actuallyexecuting the query. regards, Sergei