The following bug has been logged online:
Bug reference: 3494
Logged by: Sergey Burladyan
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.9
Operating system: CentOS release 5 (Final)
Description: may be Query Error: subplan does not executed
Details:
i have query with join of two table and 'where' filter it result by subplan
which have references to join result, but this subplan not executed and
result is incorrect. This subplan also not exist in explain analyze output.
test schema:
create table test_1 ( name char(10), ku numeric(4) , ku_1 numeric(4) );
insert into test_1 (name,ku,ku_1) values ('Petrov', 1, 0);
insert into test_1 (name,ku,ku_1) values ('Ivanov', 2, 0);
insert into test_1 (name,ku,ku_1) values ('Sidorov', 3, 0);
create table test_2 (kh numeric(13), ku numeric(4) , d_s timestamp );
insert into test_2 (kh,ku,d_s) values (1, 1, '2007-01-01');
insert into test_2 (kh,ku,d_s) values (1, 2, '2007-01-01');
insert into test_2 (kh,ku,d_s) values (1, 3, '2007-01-01');
problem query:
select *
from test_1 mt1,
test_2 mt2
where mt2.kh = 1 and
mt2.ku between 1 and 100 and
mt1.ku = mt2.ku and
mt1.ku = (select min(t1.ku)
from test_1 t1,test_2 t2
where t1.ku_1 = mt1.ku_1 and
t2.kh = mt2.kh and
t2.d_s = mt2.d_s and
t1.ku = t2.ku )
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------
Merge Join (cost=2.13..2.19 rows=3 width=60) (actual time=0.062..0.078
rows=3 loops=1)
Merge Cond: ("outer".ku = "inner".ku)
-> Sort (cost=1.05..1.06 rows=3 width=32) (actual time=0.026..0.029
rows=3 loops=1)
Sort Key: mt1.ku
-> Seq Scan on test_1 mt1 (cost=0.00..1.03 rows=3 width=32)
(actual time=0.007..0.011 rows=3 loops=1)
-> Sort (cost=1.08..1.08 rows=3 width=28) (actual time=0.028..0.030
rows=3 loops=1)
Sort Key: mt2.ku
-> Seq Scan on test_2 mt2 (cost=0.00..1.05 rows=3 width=28)
(actual time=0.011..0.018 rows=3 loops=1)
Filter: ((kh = 1::numeric) AND (ku >= 1::numeric) AND (ku <=
100::numeric))
But, when i comment out some 'where' condition in subplan because it always
true (i think) - subplan show up and query work ok:
select *
from test_1 mt1,
test_2 mt2
where mt2.kh = 1 and
mt2.ku between 1 and 100 and
mt1.ku = mt2.ku and
mt1.ku = (select min(t1.ku)
from test_1 t1,test_2 t2
where /* t1.ku_1 = mt1.ku_1 and */
t2.kh = mt2.kh and
t2.d_s = mt2.d_s and
t1.ku = t2.ku )
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------
-------
Nested Loop (cost=0.00..8.74 rows=1 width=60) (actual time=0.125..0.248
rows=1 loops=1)
Join Filter: ("inner".ku = "outer".ku)
-> Seq Scan on test_2 mt2 (cost=0.00..7.67 rows=1 width=28) (actual
time=0.114..0.228 rows=1 loops=1)
Filter: ((kh = 1::numeric) AND (ku >= 1::numeric) AND (ku <=
100::numeric) AND ((subplan) = ku))
SubPlan
-> Aggregate (cost=2.19..2.20 rows=1 width=10) (actual
time=0.063..0.064 rows=1 loops=3)
-> Merge Join (cost=2.12..2.18 rows=3 width=10) (actual
time=0.039..0.054 rows=3 loops=3)
Merge Cond: ("outer".ku = "inner".ku)
-> Sort (cost=1.05..1.06 rows=3 width=10) (actual
time=0.009..0.011 rows=3 loops=3)
Sort Key: t1.ku
-> Seq Scan on test_1 t1 (cost=0.00..1.03
rows=3 width=10) (actual time=0.004..0.009 rows=3 loops=1)
-> Sort (cost=1.07..1.08 rows=3 width=10) (actual
time=0.024..0.026 rows=3 loops=3)
Sort Key: t2.ku
-> Seq Scan on test_2 t2 (cost=0.00..1.04
rows=3 width=10) (actual time=0.006..0.013 rows=3 loops=3)
Filter: ((kh = $0) AND (d_s = $1))
-> Seq Scan on test_1 mt1 (cost=0.00..1.03 rows=3 width=32) (actual
time=0.003..0.006 rows=3 loops=1)
i am not sure, is this my incompetence or may be problem in planer ?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
