Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@xxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
