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
\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
\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