D 2018-09-08T03:22:02.876
J foundin 3.24.0
J icomment A\sconfluence\sof\smultiple\sdefects\sin\sthe\scode\sgenerator\sassociated\swith\sthe\r\nORDER\sBY\sLIMIT\soptimization\scauses\sthe\sprepared\sstatement\sto\senter\san\sinfinite\r\nloop\swhile\srunning\sthe\sfinal\squery\sin\sthe\sfollowing\sSQL:\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(aa\sVARCHAR\sPRIMARY\sKEY\sNOT\sNULL,bb,cc,x\sVARCHAR(400));\r\nINSERT\sINTO\st1(aa,bb,cc)\sVALUES('maroon','meal','lecture');\r\nINSERT\sINTO\st1(aa,bb,cc)\sVALUES('reality','meal','catsear');\r\nCREATE\sTABLE\st2(aa\sVARCHAR\sPRIMARY\sKEY,\sdd\sINT\sDEFAULT\s1,\see,\sx\sVARCHAR(100));\r\nINSERT\sINTO\st2(aa,dd,ee)\sVALUES('maroon',0,'travel'),('reality',0,'hour');\r\nCREATE\sINDEX\st2x1\sON\st2(dd,ee);\r\nANALYZE;\r\nDROP\sTABLE\sIF\sEXISTS\ssqlite_stat4;\r\nDELETE\sFROM\ssqlite_stat1;\r\nINSERT\sINTO\ssqlite_stat1\sVALUES\r\n\s\s('t2','t2x1','3\s3\s3'),\r\n\s\s('t2','sqlite_autoindex_t2_1','3\s1'),\r\n\s\s('t1','sqlite_autoindex_t1_1','2\s1');\r\nANALYZE\ssqlite_master;\r\nPRAGMA\svdbe_trace=on;\r\nSELECT\s*\r\n\s\sFROM\st1\sLEFT\sJOIN\st2\sON\st1.aa=t2.aa\r\n\sWHERE\st1.bb='meal'\r\n\sORDER\sBY\st2.dd\sDESC\r\n\sLIMIT\s1;\r\n</verbatim></blockquote>\r\n\r\nThe\sinfinite\sloop\sis\sonly\sentered\sif\sthe\scommand-line\sshell\sis\scompiled\r\nwithout\s-DSQLITE_DEBUG\sor\swithout\s-DSQLITE_ENABLE_MODULE_COMMENTS.\s\sIf\sboth\r\nof\sthose\scompile-time\soptions\sare\senabled,\sthen\sanother\sbug\sin\sthe\scode\r\ngenerator\scauses\sthe\sORDER\sBY\sLIMIT\soptimization\sto\sbe\sdisabled,\sand\sso\sthe\r\ninfinite\sloop\sdoes\snot\soccur.\r\n\r\nThis\sproblem\swas\soriginally\sreported\son\sthe\sSQLite\susers\smailing\slist.\r\nIt\stook\sconsiderable\seffort\sto\sreproduce\sthe\sproblem\sand\sthen\sboil\sit\sdown\r\nto\sthe\srepro\sscript\sshown\sabove.\r\n\r\nThe\sscript\sabove\sfirst\sbegan\sfailing\swith\scheck-in\s[02ba8a7ba7ba71cd7abd5d]\r\nwhich\sadded\sthe\somit-left-join\soptimization.\s\sThat\soptimization\sdoes\snot\r\nseem\sto\sbe\sthe\sroot\scause,\sbut\smerely\sa\schanged\sthat\sallowed\r\nit\sto\sbe\sexpressed\swith\sthe\scurrent\sscript.\s\sThe\sORDER\sBY\sLIMIT\soptimization\r\nthat\sprobably\sis\sthe\sroot\scause\swas\sintroduced\sin\sversion\s3.14\s(2016-08-08)\r\nby\scheck-in\s[559733b09e9630fac9d].
J login drh
J mimetype text/x-fossil-wiki
J severity Critical
J status Open
J title Infinite\sloop\sdue\sto\sthe\sORDER\sBY\sLIMIT\soptimization
J type Code_Defect
K 9936b2fa443fec03ff25f9b822528c20a2200a49
U drh
Z 9ff4e79c89bdbcde23b775c458b773b2