BUG #6662: Database do not push condition to subquery, test case for bug 6658

The following bug has been logged on the website:

Bug reference:      6662
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.1.3
Operating system:   Linux
Description:=20=20=20=20=20=20=20=20

I managed create simple self-contained test case for 6658.



create table test as select * from generate_series(1,100000) as g(val);
create index test_val_special on test((val || ''));
analyze test;
select count(*) from test;

--ok
--index scan
explain analyze
SELECT val
FROM test
WHERE (val || '')=3D'something';


                                                      QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------------------
 Index Scan using test_val_special on test  (cost=3D0.01..8.29 rows=3D1 wid=
th=3D4)
(actual time=3D0.011..0.011 rows=3D0 loops=3D1)
   Index Cond: (((val)::text || ''::text) =3D 'something'::text)
 Total runtime: 0.038 ms


--not ok
--seq scan on 9.1.3
explain analyze
SELECT val=20
FROM
(
SELECT
val,
(val || '') AS search_string
FROM test
) AS t1
WHERE search_string=3D'something';

                                                    QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------
 Subquery Scan on t1  (cost=3D0.00..3443.00 rows=3D500 width=3D4) (actual
time=3D47.076..47.076 rows=3D0 loops=3D1)
   Filter: (t1.search_string =3D 'something'::text)
   ->  Seq Scan on test  (cost=3D0.00..2193.00 rows=3D100000 width=3D4) (ac=
tual
time=3D0.012..34.949 rows=3D100000 loops=3D1)
 Total runtime: 47.091 ms


--the same second query on 8.3.13
--plan correct
                                                      QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------------------
 Index Scan using test_val_special on test  (cost=3D0.01..8.29 rows=3D1 wid=
th=3D4)
(actual time=3D0.004..0.004 rows=3D0 loops=3D1)
   Index Cond: (((val)::text || ''::text) =3D 'something'::text)
 Total runtime: 0.018 ms

Kind Regards,
Maksym