/ Artifact Content
Login

Artifact d28d654485803572fd084ed1b7ae92bf843af5e343e7af81db7681c4aa72100d:

Ticket change [d28d6544] - New ticket [892fc34f] Incorrect query result when a LEFT JOIN subquery is flattened. by drh 2017-06-20 16:15:03.
D 2017-06-20T16:15:03.189
J foundin 3.19.3
J icomment The\sfollowing\sSQL\sreturns\sa\sdifferent\sresult\sin\sSQLite\sversion\s3.19.3\scompared\s\r\nto\sSQLite\sversion\s3.18.0\sand\sPostgreSQL.\s\sThe\sresult\sis\salso\sdifferent\sif\r\nautomatic\sindexes\sare\sdisabled\susing\s"PRAGMA\sautomatic_index=OFF".\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(id\sINTEGER\sPRIMARY\sKEY);\r\nCREATE\sTABLE\st2(id\sINTEGER\sPRIMARY\sKEY,\sc2\sINTEGER);\r\nCREATE\sTABLE\st3(id\sINTEGER\sPRIMARY\sKEY,\sc3\sINTEGER);\r\nINSERT\sINTO\st1(id)\sVALUES(456);\r\nINSERT\sINTO\st3(id)\sVALUES(1),(2);\r\n\r\nSELECT\st1.id,\sx2.id,\sx3.id\r\nFROM\st1\r\nLEFT\sJOIN\s(SELECT\s*\sFROM\st2)\sAS\sx2\sON\st1.id=x2.c2\r\nLEFT\sJOIN\st3\sAS\sx3\sON\sx2.id=x3.c3;\r\n</verbatim></blockquote>\r\n\r\nThis\sproblem\swas\sintroduced\sby\sthe\sLEFT\sJOIN\ssubquery\sflattener\soptimization\r\nof\scheck-in\s[41c27bc0ff]\s(which\sis\salso\sthe\scause\sof\sother\sbugs\ssuch\sas\r\n[cad1ab4cb7b0]\sand\s[7fde638e94287d]).\s\sThe\sproblem\sfirst\sappeared\sin\sthe\r\n3.19.0\srelease\s(2017-05-22).\r\n\r\nThis\sproblem\swas\sreported\son\sthe\sSQLite\susers\smailing\slist\sby\sEric\sSink.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Incorrect\squery\sresult\swhen\sa\sLEFT\sJOIN\ssubquery\sis\sflattened
J type Code_Defect
K 892fc34f173e99d8aa03d52fbf42e0f9f2dfb210
U drh
Z c820de60cac08561ca78d8992f3240b9