From: | "Mark, Terry" <tmark(at)amgen(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Weird (?) problem with order of conditions in SELECT |
Date: | 2001-03-20 16:06:02 |
Message-ID: | 548152BB0AD9D2119C400008C7CFE8C805AFCBD3@gold-exch.amgen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello all,
I have encountered a weird problem I can't seem to understand. It involves
a correlated subquery, where the rows returned seem to depend upon the order
I specify my conditions. I can't see why the order should be important
(except maybe for performance)
To demonstrate I created a toy data set and query (note, both are completely
contrived so neither really 'makes sense' beyond this context). I present
first two queries which return different results, but differ *only in the
order I specify conditions*, along with the results of the queries. (The
toy data set is at the very end of the email)
SELECT c.score FROM c
WHERE c.score >= (SELECT MAX(score) AS score FROM c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id AND c.b_id = b.b_id)
AND a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id;
score
-------
2500
(1 row)
* NOTE, this query returns only one row.
SELECT c.score FROM c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id
AND c.score >= (SELECT MAX(score) AS score FROM c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id);
score
-------
100
2500
(2 rows)
* NOTE, now TWO rows are returned, even though all that has happened is I've
changed the order of the conditions.
What's going on here ? Why should the order of conditions be important ??
Any insight would be greatly appreciated.
Thanks,
terry
CREATE TABLE people (name text not null PRIMARY KEY, age int);
insert into people values ('nugget', 33);
insert into people values ('lisa', 32);
insert into people values ('larry', 28);
CREATE TABLE a (a_id INT NOT NULL PRIMARY KEY, job TEXT NOT NULL, name TEXT
NOT NULL, FOREIGN KEY (name) REFERENCES people(name));
INSERT INTO a VALUES (1, 'programmer', 'nugget');
INSERT INTO a VALUES (2, 'programmer', 'lisa');
INSERT INTO a VALUES (2, 'secretary', 'lisa');
INSERT INTO a VALUES (3, 'secretary', 'lisa');
INSERT INTO a VALUES (4, 'student', 'larry');
INSERT INTO a VALUES (5, 'programmer', 'nugget');
CREATE TABLE b (b_id INT NOT NULL PRIMARY KEY, a_id INT NOT NULL, FOREIGN
KEY (a_id) REFERENCES a(a_id));
INSERT INTO b (a_id, b_id) VALUES (1,1);
INSERT INTO b (a_id, b_id) VALUES (2,2);
INSERT INTO b (a_id, b_id) VALUES (3,3);
INSERT INTO b (a_id, b_id) VALUES (4,4);
INSERT INTO b (a_id, b_id) VALUES (5,5);
CREATE TABLE c (c_id INT NOT NULL PRIMARY KEY, b_id INT NOT NULL, score INT
NOT NULL, FOREIGN KEY (b_id) REFERENCES b(b_id));
INSERT INTO c (b_id, c_id, score) VALUES (1,1,100);
INSERT INTO c (b_id, c_id, score) VALUES (2,2,400);
INSERT INTO c (b_id, c_id, score) VALUES (3,3,900);
INSERT INTO c (b_id, c_id, score) VALUES (4,4,1600);
INSERT INTO c (b_id, c_id,score) VALUES (5,5,2500);
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-03-20 16:41:45 | Re: Weird (?) problem with order of conditions in SELECT |
Previous Message | Jeff Williams | 2001-03-19 21:27:11 | PosgreSQL Windows install |