Re: Bug with index-usage? - Mailing list pgsql-general

Tom Lane wrote:
> =?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes:
>
>>I get unpredictibale results selecting from a view depending on
>>index-usage.
>
>
> It's not actually *using* the indexes, although presence of the indexes
> does seem to be needed to trigger the bug:
>
> regression=# explain SELECT * from test WHERE type = 'a';
>                                         QUERY PLAN
> -------------------------------------------------------------------------------------------
>  Nested Loop Left Join  (cost=0.00..2.29 rows=1 width=40)
>    Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
>    ->  Seq Scan on test2  (cost=0.00..1.16 rows=1 width=40)
>          Filter: (("type" = 'a'::text) AND (("type" = 'c'::text) OR ("type" = 'b'::text)))
>    ->  Seq Scan on test2 t2  (cost=0.00..1.11 rows=1 width=0)
>          Filter: ("type" = 'a'::text)
> (6 rows)
>
> regression=# drop index index_b;
> DROP INDEX
> regression=# explain SELECT * from test WHERE type = 'a';
>                                   QUERY PLAN
> -------------------------------------------------------------------------------
>  Nested Loop Left Join  (cost=0.00..2.24 rows=1 width=40)
>    Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
>    ->  Seq Scan on test2  (cost=0.00..1.11 rows=1 width=40)
>          Filter: ("type" = 'a'::text)
>    ->  Seq Scan on test2 t2  (cost=0.00..1.11 rows=1 width=0)
>          Filter: ("type" = 'a'::text)
> (6 rows)
>
> It looks like the problem is that the new 8.1 OR-index-qual code is
> confused about when it can apply outer-join conditions.  It shouldn't be
> propagating the outer-join condition into the scan condition on test2,
> but it is.  Will fix.
>
>             regards, tom lane

Hi,

thanks for lookin into it.

I patched my 8.1 installation with the following changes:

http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461

The error described above doesn't exist any more, but it's still
buggy. Just create a view which is left-joining to an other table. The
joined columns don't show up in the view.

CREATE OR REPLACE VIEW test_ AS
   SELECT test2.*, test1.name
   FROM test2
   LEFT JOIN test1 ON test1.id = test2.test1_id
   LEFT JOIN test2 AS t2 ON
     test2.type IN ('c','b') AND
     t2.type = 'a';

In 8.0 I get:

SELECT * from test WHERE type = 'a';
  id | type | test1_id |  name
----+------+----------+---------
   1 | a    |        1 | test1_1
   2 | a    |        2 | test1_2
   3 | a    |        3 | test1_3
(3 rows)

In 8.1 (with or without your patches) I get:

SELECT * from test_ WHERE type = 'a';
  id | type | test1_id | name
----+------+----------+------
   1 | a    |        1 |
   2 | a    |        2 |
   3 | a    |        3 |
(3 rows)

Hope you could repeat the problem. Otherwise, please contact me.

Sebastian