Description:
Hello out there,
I have a problem with 'WHERE' statements on outer joins which are always true.
I do the following:
<snip>
CREATE TABLE "AUTHOR"("NAME" VARCHAR(20) NOT NULL);
CREATE TABLE "BOOK"("AUTHOR" VARCHAR(20) NOT NULL,"TITLE" VARCHAR(20));
INSERT INTO "AUTHOR"("NAME") VALUES('PAUL');
INSERT INTO "AUTHOR"("NAME") VALUES('PETER');
INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK1');
INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK2');
</snip>
I have the classical Authors database with poor Mr Peter who claims to be an author but hasn't published anything yet. Or we were just to lazy to add it to the DB. If I want a list of all authors with corresponding book title and a NULL where there isn't any book I do.
<snip>
SELECT A."NAME",B."TITLE" from AUTHOR A
LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME"
</snip>
Now comes the twisted part. If I do
<snip>
SELECT A."NAME",B."TITLE" from AUTHOR A
LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME"
WHERE (0=0)
</snip>
It still gives me the same as in the first query since the expression 0=0 always is true for every row returned. But If I do.
<snip>
SELECT A."NAME",B."TITLE" from AUTHOR A
LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME"
WHERE (0=0 OR B."TITLE"='A')
</snip>
It only returned those rows with a defined B."TITLE" value although the contents does not count since 0=0 OR B."TITLE"='A' always is true. It starts to work again if I say '0=0 OR TITLE IS NULL' returning all rows with or without a defined "TITLE"
It also works when I explicitly add the poor PETER as an author of NULL books into the BOOKS table
with
<snip>
INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PETER',NULL);
SELECT * from BOOK A
WHERE
(0=0 OR "TITLE"='A')
</snip>
I've compared this with the Sybase Studio Anywhere RDBMS and it behaves as I expected.
Btw: The whole point behind the 'always true' WHERE statement is that I'm working
with a OUTER JOIN over three tables with many columns in which the user might want to search but doesn't have to. The Statement is something like
<snip>
...
WHERE (0=? OR "Col1"=?)
AND (0=? OR "Col2"=?)
AND (0=? OR "Col3"=?)
</snip>
In an PreparedStatement of JDBC. Setting the first parameter of each (0=? OR "ColX"=?) to 0 would disable this part of the WHERE Statement all together and setting it to something different would enable it.
Thank you for your time and any hints how to possibly solve this issue...
P.S: thats with MySQL 5.0.22 on fedora core 5 with InnoDb tables.
How to repeat:
CREATE TABLE "AUTHOR"("NAME" VARCHAR(20) NOT NULL);
CREATE TABLE "BOOK"("AUTHOR" VARCHAR(20) NOT NULL,"TITLE" VARCHAR(20));
INSERT INTO "AUTHOR"("NAME") VALUES('PAUL');
INSERT INTO "AUTHOR"("NAME") VALUES('PETER');
INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK1');
INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK2');
SELECT A."NAME",B."TITLE" from AUTHOR A
LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME"
WHERE (0=0 OR B."TITLE"='A')
Description: Hello out there, I have a problem with 'WHERE' statements on outer joins which are always true. I do the following: <snip> CREATE TABLE "AUTHOR"("NAME" VARCHAR(20) NOT NULL); CREATE TABLE "BOOK"("AUTHOR" VARCHAR(20) NOT NULL,"TITLE" VARCHAR(20)); INSERT INTO "AUTHOR"("NAME") VALUES('PAUL'); INSERT INTO "AUTHOR"("NAME") VALUES('PETER'); INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK1'); INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK2'); </snip> I have the classical Authors database with poor Mr Peter who claims to be an author but hasn't published anything yet. Or we were just to lazy to add it to the DB. If I want a list of all authors with corresponding book title and a NULL where there isn't any book I do. <snip> SELECT A."NAME",B."TITLE" from AUTHOR A LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME" </snip> Now comes the twisted part. If I do <snip> SELECT A."NAME",B."TITLE" from AUTHOR A LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME" WHERE (0=0) </snip> It still gives me the same as in the first query since the expression 0=0 always is true for every row returned. But If I do. <snip> SELECT A."NAME",B."TITLE" from AUTHOR A LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME" WHERE (0=0 OR B."TITLE"='A') </snip> It only returned those rows with a defined B."TITLE" value although the contents does not count since 0=0 OR B."TITLE"='A' always is true. It starts to work again if I say '0=0 OR TITLE IS NULL' returning all rows with or without a defined "TITLE" It also works when I explicitly add the poor PETER as an author of NULL books into the BOOKS table with <snip> INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PETER',NULL); SELECT * from BOOK A WHERE (0=0 OR "TITLE"='A') </snip> I've compared this with the Sybase Studio Anywhere RDBMS and it behaves as I expected. Btw: The whole point behind the 'always true' WHERE statement is that I'm working with a OUTER JOIN over three tables with many columns in which the user might want to search but doesn't have to. The Statement is something like <snip> ... WHERE (0=? OR "Col1"=?) AND (0=? OR "Col2"=?) AND (0=? OR "Col3"=?) </snip> In an PreparedStatement of JDBC. Setting the first parameter of each (0=? OR "ColX"=?) to 0 would disable this part of the WHERE Statement all together and setting it to something different would enable it. Thank you for your time and any hints how to possibly solve this issue... P.S: thats with MySQL 5.0.22 on fedora core 5 with InnoDb tables. How to repeat: CREATE TABLE "AUTHOR"("NAME" VARCHAR(20) NOT NULL); CREATE TABLE "BOOK"("AUTHOR" VARCHAR(20) NOT NULL,"TITLE" VARCHAR(20)); INSERT INTO "AUTHOR"("NAME") VALUES('PAUL'); INSERT INTO "AUTHOR"("NAME") VALUES('PETER'); INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK1'); INSERT INTO "BOOK"("AUTHOR","TITLE") VALUES('PAUL','BOOK2'); SELECT A."NAME",B."TITLE" from AUTHOR A LEFT OUTER JOIN BOOK B ON B."AUTHOR"=A."NAME" WHERE (0=0 OR B."TITLE"='A')