SQLite

Artifact [7aedf2b6]
Login

Artifact 7aedf2b61aaf31a427c8b1dcc2929d9d80572333:

Ticket change [7aedf2b6] - New ticket [b7c8682c] Incorrect result from LEFT JOIN with OR in the WHERE clause. by drh 2012-03-09 16:58:06.
D 2012-03-09T16:58:06.045
J comment The\sfollowing\sSQL\sdemonstrates\sthe\sproblem:\r\n\r\n<blockquote><pre>\r\nCREATE\sTABLE\st1(a\sINTEGER\sPRIMARY\sKEY,\sb,\sc,\sd);\r\nCREATE\sTABLE\st2(x\sINTEGER\sPRIMARY\sKEY,\sy);\r\nCREATE\sTABLE\st3(p\sINTEGER\sPRIMARY\sKEY,\sq);\r\nINSERT\sINTO\st1\sVALUES(2,3,4,5);\r\nINSERT\sINTO\st1\sVALUES(3,4,5,6);\r\nINSERT\sINTO\st2\sVALUES(2,4);\r\nINSERT\sINTO\st3\sVALUES(5,55);<p>\r\nSELECT\s*\r\n\s\sFROM\st1\sLEFT\sJOIN\st2\sON\sy=b\sJOIN\st3\r\n\sWHERE\sc==p\sOR\sd==p;\r\n</pre></blockquote>\r\n\r\nPrior\sto\scheck-in\s[b23ae131874bc5c621f0]\sthe\squery\sreturns\stwo\srows.\s\sBut\r\nfollowing\sthat\scheck-in,\sonly\sone\srow\sis\sreturned.\s\sThe\stwo-row\sresult\sis\r\ncorrect.\r\n\r\nThis\sproblem\swas\sdiscovered\sby\sAlan\sChandler\sand\sreported\son\sthe\smailing\r\nlist\sas\s"Strange\sdifference\sbetween\ssqlite\s3.7.3\sand\s3.7.10".
J detected Application_Fault
J foundin 3.7.10
J severity Severe
J status Open
J subsystem Unknown
J title Incorrect\sresult\sfrom\sLEFT\sJOIN\swith\sOR\sin\sthe\sWHERE\sclause
J type Code_Defect
K b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4
U drh
Z 2870663a6dbb05885e0690772f220717