Re: Bug with index-usage?

Jaime Casanova wrote:
> On 11/14/05, Sebastian Böck <sebastianboeck@freenet.de> wrote:
>
>>Hello,
>>
>>I get unpredictibale results selecting from a view depending on
>>index-usage.
>>
>>Please see the attached script for details.
>>
>>Is it a bug or some "weird feature"?
>>
>>Any help appreciated to get predictibale results
>>
>>Sebastian
>>
>>
>>CREATE TABLE test1 (
>> id SERIAL PRIMARY KEY,
>> name TEXT NOT NULL
>>);
>>
>>INSERT INTO test1 (name) VALUES ('test1_1');
>>INSERT INTO test1 (name) VALUES ('test1_2');
>>INSERT INTO test1 (name) VALUES ('test1_3');
>>
>>CREATE TABLE test2 (
>> id SERIAL PRIMARY KEY,
>> type TEXT NOT NULL CHECK (type IN ('a','b','c')),
>> test1_id INTEGER REFERENCES test1
>>);
>>
>>INSERT INTO test2 (type,test1_id) VALUES ('a',1);
>>INSERT INTO test2 (type,test1_id) VALUES ('a',2);
>>INSERT INTO test2 (type,test1_id) VALUES ('a',3);
>>INSERT INTO test2 (type,test1_id) VALUES ('b',1);
>>INSERT INTO test2 (type,test1_id) VALUES ('b',2);
>>INSERT INTO test2 (type,test1_id) VALUES ('b',3);
>>INSERT INTO test2 (type,test1_id) VALUES ('c',1);
>>INSERT INTO test2 (type,test1_id) VALUES ('c',2);
>>INSERT INTO test2 (type,test1_id) VALUES ('c',3);
>>
>>CREATE OR REPLACE VIEW test AS
>> SELECT test2.*
>> FROM test2
>> LEFT JOIN test2 AS t2 ON
>>   test2.type IN ('c','b') AND
>>   t2.type = 'a';
>>
>>SELECT * from test WHERE type = 'a';
>>
>>CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
>>CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
>>CREATE INDEX index_c ON test2 (id) WHERE type = 'c';
>>
>>SET enable_seqscan TO OFF;
>>
>>SELECT * from test WHERE type = 'a';
>>
>
>
> i don't have my machine at hand but i don't think that even the select
> is right, you have a join but without joining clauses you will get a
> cartesian product...
>
> what do you believe is the right answer... just for my probe later...

I think it should be:

  id | type | test1_id
----+------+----------
   1 | a    |        1
   2 | a    |        2
   3 | a    |        3


because a

EXPLAIN SELECT * from test WHERE type = 'a';

shows some weird assumptions

Index Scan using index_a on test2  (cost=0.00..4.69 rows=1 width=40)
   Filter: (("type" = 'c'::text) OR ("type" = 'b'::text))

note that index_a is defined as:

CREATE INDEX index_a ON test2 (id) WHERE type = 'a';

Sebastian