Re: Bug with index-usage?

On 11/14/05, Sebastian Böck <sebastianboeck(at)freenet(dot)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...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)