BUG #3494: may be Query Error: subplan does not executed - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3494
Logged by:          Sergey Burladyan
Email address:      eshkinkot@gmail.com
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 ?