D 2013-07-08T18:02:53.386
J foundin 3.7.17
J icomment The\sfollowing\sSQL\sshould\sreturn\sa\ssingle\srow\sof\sresult.\s\sBut\sdue\sto\sthe\r\ntransitive\sconstraint\soptimization,\sit\seither\shits\san\sassertion\sfault\sor\r\nit\sreturns\szero\srows\s(depending\son\swhether\sthe\sSQLITE_DEBUG\scompile-time\soption\r\nis\sused.)\s\sNote\sthat\sSQLITE_ENABLE_STAT3\sis\srequired\sfor\sthis\sbug\sto\sappear.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(w\sINTEGER\sPRIMARY\sKEY,\sx);\r\nCREATE\sTABLE\st2(y\sINTEGER,\sz);\r\nINSERT\sINTO\st1\sVALUES(1,2);\r\nINSERT\sINTO\st2\sVALUES(1,3);\r\nSELECT\s*\r\n\s\sFROM\st1\sCROSS\sJOIN\st2\r\n\sWHERE\sw=y\sAND\sy\sIS\sNOT\sNULL;\r\n</verbatim></blockquote>\r\n\r\nThe\sproblem\sappears\sto\shave\sbeen\sintroduced\sby\scheck-in\s[38852f158ab].\r\n\r\nLow-level\stechnical\sdetails:\s\sThe\s"y\sIS\sNOT\sNULL"\sconstraint\sis\stranslated\r\ninto\sa\s"y>NULL"\svirtual\sconstraint\swhen\sSQLITE_ENABLE_STAT3\sis\sdefined.\s\s\r\nThe\sy>NULL\sconstraint\stagged\sto\sindicate\sthat\sthe\susual\snot-NULL\scheck\son\s\r\nthe\sright-hand\sside\sshould\sbe\r\nskipped\ssince\sthis\sis\san\sartificial\sconstraint.\s\sThe\snormal\sindex\shandler\s\r\nknows\sto\scheck\sfor\sthat\stag\sand\sskip\sthe\snot-NULL\scheck.\s\sBut\safter\sthe\r\ntransitive\sconstraints\swere\sadded,\sthere\sis\snow\salso\sa\s"w>NULL"\svirtual\r\nconstraint\s(because\sw=x).\s\sThe\sw>NULL\sconstraint\sis\salso\stagged,\sbut\sthe\s\r\nINTEGER\sPRIMARY\sKEY\shandlers\sdoes\snot\sknow\sto\scheck\sfor\sthat\stag\sand\sto\s\r\nskip\sthe\snot-NULL\scheck.\s\sIt\ssees\sthat\sthe\sright-hand\sside\sof\sthe\sconstraint\r\nis\sNULL\sand\sall\sfails\sthe\sconstraint,\sresulting\sin\sno\soutput\srows.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Incorrect\sjoin\sresult\sor\sassertion\sfault\sdue\sto\stransitive\sconstraints
J type Code_Defect
K d805526eae253103dc307740dcf859b6d701c2f4
U drh
Z ac6d2265d9b885eb6a779489079b72bc