D 2017-07-29T03:26:32.827
J foundin 3.19.0
J icomment The\sname\sof\sthe\soutput\scolumn\son\sthe\sSELECT\sstatement\sbelow\sas\sreported\sby\sthe\r\nsqlite3_column_name()\sinterface\schanged\sin\sbetween\sversions\s3.18.0\sand\s3.19.0.\r\n\r\n<blockquote><verbatim>\r\nCREATE\sTABLE\sx(a,b);\r\nINSERT\sINTO\sx\sVALUES(1,2);\r\nSELECT\sx.a\sFROM\sx\sLEFT\sJOIN\s(SELECT\s*\sFROM\sx)\sAS\sy;\r\n</verbatim></blockquote>\r\n\r\nSQLite\sversion\s3.18.0\sand\searlier\sreports\r\nthe\scolumn\sname\sas\s"a".\r\nBut\sin\sSQLite\s3.19.0\sand\s3.20.0,\sthe\r\ncolumn\sname\sis\sreported\sas\s"x.a".\r\n\r\nThe\sSQLite\sdocumentation\sclearly\sstates\sthat,\sif\sthere\sis\sno\sAS\sclause,\r\nthe\snames\sof\soutput\scolumns\sare\sindeterminate,\sarbitrary,\sand\ssubject\s\r\nto\schange.\s\sThe\scolumn\snames\sare\sintended\sfor\sdisplay\spurposes\sonly.\r\nAnd\syet,\smany\sapplications\sdepend\son\sparticular\scolumn\snames.\r\nThis\sname\sshift\sresulted\sat\sleast\sone\sapplication\serror.\r\n\r\nThe\sproblem\swas\sintroduced\sby\scheck-in\s[41c27bc0ff1d3135],\swhich\sadded\sa\snew\r\noptimization\sfor\sdoing\squery\sflattening\son\sthe\sright\soperand\sof\sa\sLEFT\sJOIN.\r\n\r\n<b>Analysis:</b>\r\n\r\nWithout\san\sAS\sclause,\sif\sthe\soutput\scolumn\sis\sa\sdirect\sreference\sto\sone\r\nof\sthe\sinput\stable\scolumns,\sthen\sthe\soutput\scolumn\sname\swill\sbe\sthe\ssame\r\nas\sthe\sinput\scolumn\sname.\s\sExcept,\sif\squery\sflattening\soccurs,\sthe\soutput\r\ncolumn\sname\sbecomes\san\sexact\scopy\sof\sthe\sinput\sSQL\stext.\r\n\r\nThe\sexception\sexplains\sthe\sname\sshift.\s\sPrior\sto\sversion\s3.19.0,\sthe\squery\r\nflattener\swould\snot\soperate\son\sa\sLEFT\sJOIN.\s\sHence\sthe\soutput\scolumn\sname\r\nwas\sderived\sfrom\sthe\sinput\scolumn\sname\sand\swas\s"a".\s\sBut\sonce\sthe\sLEFT\sJOIN\r\nquery\sflattener\soptimization\swas\sadded\sby\scheck-in\s[41c27bc0ff1d3135],\sthe\r\ncolumn\snaming\sexception\smentioned\sin\sthe\sprevious\sparagraph\skicked\sin\sand\r\ncaused\sthe\scolumn\sto\sbe\snamed\sas\sit\swas\styped\sin\sthe\sinput\sSQL:\s\s"x.a".
J login drh
J mimetype text/x-fossil-wiki
J severity Important
J status Open
J title Column\sname\sshift\sdue\sto\sLEFT\sJOIN\squery\sflattening
J type Code_Defect
K de3403bf5ae5f72ed6638f6360339254c8c62f53
U drh
Z 2f9da5608c594f70e4cfec044432fc35