<i>CAST takes implicit COLLATE of its operand</i>.
by
mrigger
2019-06-12 15:37:21.
D 2019-06-12T15:37:21.681
J icomment Consider\sthe\sfollowing\sexample:\r\n\r\n<pre>\r\nCREATE\sTABLE\st0(c0\sCOLLATE\sNOCASE);\r\nINSERT\sINTO\st0(c0)\sVALUES\s('a');\r\nSELECT\s*\sFROM\st0\sWHERE\sCAST(t0.c0\sAS\sTEXT)\s=\s'A';\s--\sexpected:\sno\srow\sis\sfetched,\sactual:\sa\r\n</pre>\r\n\r\nI\swould\sexpect\sthe\scomparison\sto\syield\s0,\ssince\sthe\sleft\soperand\sis\s'a',\sand\sthe\sright\sone\s'A'.\sNeither\soperand\shas\san\sexplicit\scollating\sfunction\sassigned\sand\sonly\st0.c0\shas\san\simplicit\sone.\sIt\sseems\sthat\sthe\scomparison\stakes\sthe\simplicit\scollating\sfunction\sof\sc0,\salthough\sthe\scolumn\sis\ssurrounded\sby\sthe\scast.\sThe\sdocumentation\son\simplicit\scollating\sfunctions,\showever,\sonly\smentions\sthese\scases:\r\n\r\n<pre>\r\nIf\seither\soperand\sis\sa\scolumn,\sthen\sthe\scollating\sfunction\sof\sthat\scolumn\sis\sused\swith\sprecedence\sto\sthe\sleft\soperand.\sFor\sthe\spurposes\sof\sthe\sprevious\ssentence,\sa\scolumn\sname\spreceded\sby\sone\sor\smore\sunary\s"+"\soperators\sis\sstill\sconsidered\sa\scolumn\sname.\r\n</pre>\r\n\r\nThis\sworks\sas\sexpected\sfor\sother\sfunctions\sand\soperators,\sfor\sexample:\r\n\r\n<pre>\r\nCREATE\sTABLE\st0(c0\sCOLLATE\sNOCASE);\r\nINSERT\sINTO\st0(c0)\sVALUES\s('a');\r\nSELECT\s*\sFROM\st0\sWHERE\sLOWER(t0.c0)\s=\s'A';\s--\sno\srow\sis\sfetched\r\n</pre>
J login mrigger
J mimetype text/x-fossil-wiki
J severity Critical
J status Open
J title CAST\stakes\simplicit\sCOLLATE\sof\sits\soperand
J type Code_Defect
K b148fa61059fb6c2e0299b812e64ca20bbea932f
U mrigger
Z 4ca86b8b0d543b83f4fa239a72013c78