D 2017-11-21T15:19:27.424
J foundin 3.21.0
J icomment The\squery\sin\sthe\sSQL\sshown\sbelow\sgives\san\sincorrect\sanswer\safter\r\nANALYZE\shas\sbeen\srun:\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(a\sINTEGER\sPRIMARY\sKEY,\sb\sINTEGER);\r\nCREATE\sINDEX\st1b\sON\st1(b);\r\nCREATE\sTABLE\st2(x\sINTEGER\sPRIMARY\sKEY,\sy\sINTEGER);\r\nCREATE\sINDEX\st2y\sON\st2(y);\r\nWITH\sRECURSIVE\sc(x)\sAS\s(VALUES(0)\sUNION\sALL\sSELECT\sx+1\sFROM\sc\sWHERE\sx<49)\r\n\s\sINSERT\sINTO\st1(b)\sSELECT\sx/10\s-\s1\sFROM\sc;\r\nWITH\sRECURSIVE\sc(x)\sAS\s(VALUES(-1)\sUNION\sALL\sSELECT\sx+1\sFROM\sc\sWHERE\sx<19)\r\n\s\sINSERT\sINTO\st2(x,y)\sSELECT\sx,\s1\sFROM\sc;\r\nSELECT\sDISTINCT\sy\sFROM\st1,\st2\sWHERE\sb=x\sAND\sb<>-1;\r\nANALYZE;\r\nSELECT\sDISTINCT\sy\sFROM\st1,\st2\sWHERE\sb=x\sAND\sb<>-1;\r\n</verbatim></blockquote>\r\n\r\nThis\sproblem\sseems\sto\shave\sbeen\sintroduced\sby\sthe\sskip-ahead-distinct\r\noptimization\sof\scheck-in\s[f489b5bb6b35665],\sfirst\sincluded\sin\sSQLite\r\nversion\s3.19.0.\s\sThe\sproblem\swas\sreported\son\sthe\spublic\smailing\slist\r\nby\sDavid\sRaymond.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Incorrect\sresult\sdue\sto\sthe\sskip-ahead-distinct\soptimization
J type Code_Defect
K ef9318757b152e3a26e95923e8fe8c178a7e629c
U drh
Z f480d4b6ed46115a63980ae1fa24e8ed