Artifact
4776331e7fafe22dc1aaffd1b94308fc35d2c229cff5bf7c4834062f2e90b969:
Ticket change
[4776331e]
- New ticket
[ce68383b] Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index.
by
drh
2017-07-18 16:57:15.
D 2017-07-18T16:57:15.470
J foundin 3.19.3
J icomment The\ssame\sLEFT\sJOIN\squery\sgives\sdifferent\sresults\sdepending\son\swhether\sor\r\nnot\sautomatic\sindexes\sare\senabled.\s\sThe\scode\sbelow\sillustrates:\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(w\sint);\r\nCREATE\sTABLE\st2(y\sint);\r\nINSERT\sINTO\st1\sVALUES(NULL);\r\nINSERT\sINTO\st2\sVALUES(1);\r\nCREATE\sINDEX\st2y\sON\st2(y);\r\nSELECT\s*\sFROM\st1\sLEFT\sJOIN\st2\sWHERE\st2.y\sIS\st1.w;\r\nPRAGMA\sautomatic_index=off;\r\nSELECT\s*\sFROM\st1\sLEFT\sJOIN\st2\sWHERE\st2.y\sIS\st1.w;\r\n</verbatim></blockquote>\r\n\r\nBoth\squeries\sshould\sreturn\sno\srows.\s\sBut\swhen\sautomatic\sindexes\sare\sturned\son\r\n(the\sdefault)\sa\ssingle\srow\sof\stwo\sNULLs\sis\sreturned.\r\n\r\nBisecting\sshows\sthis\sproblem\swas\sintroduced\sby\scheck-in\s[3428043cd0029445]\r\n\r\nThis\sproblem\swas\sdiscovered\sduring\sinternal\stesting\sand\sanalysis\sand\shas\r\nnever\sbeen\sreported\sin\sthe\swild.
J login drh
J mimetype text/x-fossil-wiki
J severity Important
J status Open
J title Incorrect\sLEFT\sJOIN\sresult\swith\sIS\soperator\sin\sWHERE\sclause\sand\sauto-index
J type Code_Defect
K ce68383bf6aba2f8e2b0344d067364e22daf59dc
U drh
Z e8e0c035f77166cf1b847219ea8c7d70