SQLite

Artifact [f6a379b6]
Login

Artifact f6a379b6db04dbd185d32fc69b51b7e0ebc45a4f:

Ticket change [f6a379b6] - New ticket [2326c258] Incorrect result on a LEFT JOIN with an ORDER BY. by drh 2015-02-24 15:30:52.
D 2015-02-24T15:30:52.274
J foundin 3.8.8
J icomment The\sfollowing\sSQL\shas\stwo\sidentical\sjoins,\sone\swith\sand\sthe\sother\swithout\san\r\nORDER\sBY\sclause.\s\sThe\sone\swithout\sORDER\sBY\scorrectly\sreturns\stwo\srows\sof\s\r\nresult.\s\sThe\sone\swith\sORDER\sBY\sreturns\sno\srows.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\sA(Name\stext);\r\nCREATE\sTABLE\sItems(ItemName\stext\s,\sName\stext);\r\nINSERT\sINTO\sItems\sVALUES('Item1','Parent');\r\nINSERT\sINTO\sItems\sVALUES('Item2','Parent');\r\nCREATE\sTABLE\sB(Name\stext);\r\n\r\nSELECT\sItems.ItemName\r\n\s\sFROM\sItems\r\n\s\s\s\sLEFT\sJOIN\sA\sON\s(A.Name\s=\sItems.ItemName\sand\sItems.ItemName\s=\s'dummy')\r\n\s\s\s\sLEFT\sJOIN\sB\sON\s(B.Name\s=\sItems.ItemName)\r\n\s\sWHERE\sItems.Name\s=\s'Parent';\r\n\r\nSELECT\sItems.ItemName\r\n\s\sFROM\sItems\r\n\s\s\s\sLEFT\sJOIN\sA\sON\s(A.Name\s=\sItems.ItemName\sand\sItems.ItemName\s=\s'dummy')\r\n\s\s\s\sLEFT\sJOIN\sB\sON\s(B.Name\s=\sItems.ItemName)\r\n\s\sWHERE\sItems.Name\s=\s'Parent'\r\nORDER\sBY\sItems.ItemName;\r\n</verbatim></blockquote>\r\n\r\nThis\serror\sseems\sto\shave\sbeen\sintroduced\sby\scheck-in\s[d95d0313c447f5]\r\nwhich\swas\sfirst\sdelivered\sin\sSQLite\sversion\s3.8.8.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title Incorrect\sresult\son\sa\sLEFT\sJOIN\swith\san\sORDER\sBY
J type Code_Defect
K 2326c258d02ead33d69faa63de8f4686b9b1b9d9
U drh
Z 3d2c04fa651a893b7e93c59859fb5c34