D 2022-10-31T13:24:09.036
J foundin 3.39.4
J icomment Dbsqlfuzz\sdiscovered\sthat\sthe\sfinal\squery\sin\sthe\sSQL\sbelow\sgets\sdifferent\r\nanswers\sdepending\son\swhether\sor\snot\squery\sflattening\sis\senabled:\r\n\r\n>\s~~~~\r\nCREATE\sTABLE\st0(c0\sINT);\r\nINSERT\sINTO\st0\sVALUES(0);\r\nCREATE\sVIEW\sv0(c0)\sAS\sSELECT\sCAST(t0.c0\sAS\sINTEGER)\sFROM\st0;\r\nCREATE\sTABLE\st1_a(a\sINTEGER\sPRIMARY\sKEY,\sb\sTEXT);\r\nINSERT\sINTO\st1_a\sVALUES(1,'one'),(4,'four');\r\nCREATE\sTABLE\st1_b(c\sINTEGER\sPRIMARY\sKEY,\sd\sTEXT);\r\nINSERT\sINTO\st1_b\sVALUES(2,'two'),(5,'five');\r\nCREATE\sVIEW\st1\sAS\s\r\n\s\sSELECT\sa,\sb\sFROM\st1_a\s\s\sUNION\sALL\r\n\s\sSELECT\sc,\sc\sFROM\st1_b;\r\nSELECT\s*\sFROM\s(\r\n\s\sSELECT\st1.a\sAS\sa,\st1.b\sAS\sb,\st0.c0\sAS\sc,\sv0.c0\sAS\sd\r\n\s\s\s\sFROM\st0\sLEFT\sJOIN\sv0\sON\sv0.c0>'0',t1\r\n)\sAS\st2\sWHERE\sb='2';\r\n.testctrl\soptimization\s1\r\nSELECT\s*\sFROM\s(\r\n\s\sSELECT\st1.a,\st1.b,\st0.c0\sAS\sc,\sv0.c0\sAS\sd\r\n\s\s\s\sFROM\st0\sLEFT\sJOIN\sv0\sON\sv0.c0>'0',t1\r\n)\sAS\st2\sWHERE\sb='2';\r\n~~~~\r\n\r\nPostgreSQL\sdoes\snot\saccept\sthe\sdefinition\sof\sthe\sview\sT1\sdue\sto\sa\stype\r\nmismatch.\s\sTo\smake\sT1\sacceptable\sto\sPG,\sit\shas\sto\sbe\schanged\sby\sinserting\r\na\sCAST\sas\sfollows:\r\n\r\n>\s~~~~\r\nCREATE\sVIEW\st1\sAS\s\r\n\s\sSELECT\sa,\sb\sFROM\st1_a\s\s\sUNION\sALL\r\n\s\sSELECT\sc,\sCAST(c\sAS\stext)\sFROM\st1_b;\r\n~~~~\r\n\r\nWith\sthat\schange\sto\st1,\sPG\sand\sSQLite\sget\sthe\ssame\sanswer,\sand\sSQLite\r\nalways\sworks,\swith\sor\swithout\sthe\squery\sflattener.\s\sSo\swe\spresume\sthat\r\nthe\sanswer\swith\squery\sflattening\senabled\sis\scorrect.\r\n\r\nSQLite\sgot\sthe\scorrect\sanswer\sprior\sto\s[](df12f097224ebc94)\s(2020-10-03)\r\nwhich\swas\sa\sfix\sfor\sticket\s[](95302bdb08fb6495).\s\sAfter\sthat\schange,\r\nan\sincorrect\sanswer\swas\sobtained\s(no\srows)\sregardless\sof\swhether\sor\snot\r\nthe\squery\sflattener\swas\sused.\r\n\r\nBeginning\swith\s[](df1d6482f9e92daf)\son\s2020-12-19,\swhen\sthe\squery\sflattener\r\nis\senabled,\sthe\scompile\sfails\swith\san\serror:\s\s"ON\sclause\sreferences\stables\sto\sits\sright".\r\n\r\nBeginning\swith\s[](d554f710a5abbe64)\son\s2020-12-21,\sattempts\sto\srun\sthe\r\nquery\sresult\sin\san\sassertion\sfault.\s\sThe\sassertion\sfault\sproblem\swas\r\nfixed\sby\s[](e4f8a79fd8b3be9b)\son\s2021-02-13.\s\sEver\ssince\sthen,\sthe\squery\r\nhas\sreturned\sthe\scorrect\sanswer\swhen\squery\sflattening\sis\senabled,\sand\san\r\nincorrect\sanswer\swhen\squery\sflattening\sis\sdisabled.
J login drh
J mimetype text/x-markdown
J severity Important
J status Open
J title Incorrect\sanswer\swhen\sflattening\sa\sUNION\sALL\scompound
J type Code_Defect
K 57c47526c34f01e8ac5e63a7dc3bc10653eff76b
U drh
Z 4f3330a31ad575844d84260c988a9e3d