Artifact
5bbf9ac188ced88e4cab74c2f49a3a5e1566fa36:
Ticket change
[5bbf9ac1]
- New ticket
[6709574d] COLLATE clause in ORDER BY causes incorrect EXCEPT.
by
drh
2013-05-06 15:21:16.
D 2013-05-06T15:21:16.959
J foundin 3.7.16.2
J icomment In\sthe\scode\sshown\sbelow\s(which\sis\sintended\sto\sbe\srun\sfrom\sthe\ssqlite3.exe\r\ncommand-line\stool)\sthe\sthree\sSELECT\sstatements\sshould\sall\sgenerate\sthe\ssame\r\nanswer.\s\sBut\sthe\sthird\sone\sdoes\snot.\s\sIt\sis\sas\sif\sthe\sCOLLATE\sclause\son\sthe\r\nORDER\sBY\ssomehow\sgot\spulled\sinto\sthe\sEXCEPT\soperator.\s\sNote\sthat\sthe\s".print"\r\ncommands\sare\sinstructions\sto\sthe\ssqlite3.exe\sshell\sprogram\sto\soutput\r\ndelimiter\slines\sso\sthat\syou\scan\smore\seasily\stell\swhere\sthe\soutput\sof\sone\r\nquery\sends\sand\sthe\snext\squery\sbegins.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\st1(a);\r\nINSERT\sINTO\st1\sVALUES('abc'),('def');\r\nCREATE\sTABLE\st2(a);\r\nINSERT\sINTO\st2\sVALUES('DEF');\r\n\r\nSELECT\sa\sFROM\st1\sEXCEPT\sSELECT\sa\sFROM\st2\sORDER\sBY\sa;\r\n.print\s-----\r\nSELECT\sa\sFROM\s(SELECT\sa\sFROM\st1\sEXCEPT\sSELECT\sa\sFROM\st2)\r\n\sORDER\sBY\sa\sCOLLATE\snocase;\r\n.print\s-----\r\nSELECT\sa\sFROM\st1\sEXCEPT\sSELECT\sa\sFROM\st2\sORDER\sBY\sa\sCOLLATE\snocase;\r\n</verbatim></blockquote>\r\n\r\nBisecting\sshows\sthat\sthis\sproblem\swas\sintroduced\sin\sSQLite\r\nversion\s3.6.0\sby\scheck-in\s[8bbfa97837a74ef]\son\s2008-06-15.
J login drh
J mimetype text/x-fossil-wiki
J severity Severe
J status Open
J title COLLATE\sclause\sin\sORDER\sBY\scauses\sincorrect\sEXCEPT
J type Code_Defect
K 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7
U drh
Z 7ce962a2ef9a0985c540de6d9b3ac78f