D 2018-04-24T14:30:17.910
J foundin 3.23.1
J icomment The\sfollowing\sSQL\sdemonstrates\show\san\sindex\son\san\sexpression\sthat\scan\sbe\r\nnon-NULL\seven\sif\sall\sits\sarguments\sare\sNULL\scan\slead\sto\san\sincorrect\sanswer\r\nin\sa\sLEFT\sJOIN.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(x);\r\nCREATE\sTABLE\st2(y,\sz);\r\nINSERT\sINTO\st1\sVALUES('key');\r\nINSERT\sINTO\st2\sVALUES('key',\s-1);\r\nSELECT\scount(*)\sFROM\st1\sLEFT\sJOIN\st2\sON\s(x=y)\sWHERE\sifnull(z,\s0)\s>=0;\r\nCREATE\sINDEX\st2i\sON\st2(y,\sifnull(z,\s0));\r\nSELECT\scount(*)\sFROM\st1\sLEFT\sJOIN\st2\sON\s(x=y)\sWHERE\sifnull(z,\s0)\s>=0;\r\n</verbatim></blockquote>\r\n\r\nThis\sproblem\shas\sbeen\sin\sthe\scode\sever\ssince\ssupport\sfor\sindexes\son\r\nexpression\swas\sadded\sby\scheck-in\s[2131a5ca53f0e9b0b98a9dd9a20e495]\r\non\s2015-09-04.\s\sThe\sproblem\sfirst\sappeared\sin\sthe\s3.9.0\srelease.\r\n\r\nThis\sproblem\swas\sdiscovered\sduring\sinternal\stesting\sand\sanalysis\r\nand\shas\snot\sbeen\sobserved\sin\sthe\swild.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Index\son\sexpression\sleads\sto\san\sincorrect\sLEFT\sJOIN
J type Code_Defect
K 4ba5abf65c5b0f9a96a7a40cd18b854478d40ced
U drh
Z c7c4cc7bc0dfdf0a67bc054455d5d49b