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';
