Artifact
5400ddb5c1397eb5047abda43b19db21bf962d38f4202a42b027ed99270a724a:
Ticket change
[5400ddb5c1]
- New ticket
[5948e09b8c] Incorrect result from LEFT JOIN.
by
drh
2019-02-05 13:55:55.
D 2019-02-05T13:55:55.395
J foundin 3.26.0
J icomment The\squery\sat\sthe\send\sof\sthe\sfollowing\sSQL\sreturns\sno\srows,\sbut\sit\sshould\sreturn\r\none\srow.\s\sThe\scorrect\sanswer\s(one\srow)\sis\sconfirmed\sby\srunning\sthe\ssame\r\nSQL\sagainst\sPostgreSQL.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(a\sINT);\r\nINSERT\sINTO\st1(a)\sVALUES(1);\r\nCREATE\sTABLE\st2(b\sINT);\r\nSELECT\sa,\sb\r\n\s\sFROM\st1\sLEFT\sJOIN\st2\sON\strue\r\n\sWHERE\s(b\sIS\sNOT\sNULL)\sIS\sfalse;\r\n</verbatim></blockquote>\r\n\r\nAnother\smanifestation\sof\sthe\sproblem\swhich\sruns\son\shistorical\sversions\r\nof\sSQLite\sbut\swhich\sdoes\snot\swork\son\sPostgreSQL\sis\sthis:\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(a\sINT);\r\nINSERT\sINTO\st1(a)\sVALUES(1);\r\nCREATE\sTABLE\st2(b\sINT);\r\nSELECT\sa,\sb\r\n\s\sFROM\st1\sLEFT\sJOIN\st2\sON\s0\r\n\sWHERE\s(b\sIS\sNOT\sNULL)=0;\r\n</verbatim></blockquote>\r\n\r\nThis\sproblem\sfirst\sappeared\swith\scheck-in\s[dd568c27b1d76563]\s-\sthe\r\nLEFT\sJOIN\sstrength\sreduction\soptimization\s-\sand\sfirst\sappeared\sin\r\nrelease\s3.23.0\s(2018-04-02).\s\sThe\sproblem\swas\sreported\r\non\sthe\sSQLite\susers\smailing\slist.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Incorrect\sresult\sfrom\sLEFT\sJOIN
J type Code_Defect
K 5948e09b8c415bc45da5cfc75c8eb327bbb53886
U drh
Z 5d8b7b2152378b26415e4c361fa2c926