gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/resolver01.test (about) 1 # 2013-04-13 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 # This file tests features of the name resolver (the component that 13 # figures out what identifiers in the SQL statement refer to) that 14 # were fixed by ticket [2500cdb9be]. 15 # 16 # See also tickets [1c69be2daf] and [f617ea3125] from 2013-08-14. 17 # 18 # Also a fuzzer-discovered problem on 2015-04-23. 19 # 20 21 set testdir [file dirname $argv0] 22 source $testdir/tester.tcl 23 24 # "ORDER BY y" binds to the output result-set column named "y" 25 # if available. If no output column is named "y", then try to 26 # bind against an input column named "y". 27 # 28 # This is classical SQL92 behavior. 29 # 30 do_test resolver01-1.1 { 31 catchsql { 32 CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22); 33 CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44); 34 SELECT 1 AS y FROM t1, t2 ORDER BY y; 35 } 36 } {0 1} 37 do_test resolver01-1.2 { 38 catchsql { 39 SELECT 1 AS yy FROM t1, t2 ORDER BY y; 40 } 41 } {1 {ambiguous column name: y}} 42 do_test resolver01-1.3 { 43 catchsql { 44 CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22); 45 SELECT x AS y FROM t3 ORDER BY y; 46 } 47 } {0 {11 33}} 48 do_test resolver01-1.4 { 49 catchsql { 50 SELECT x AS yy FROM t3 ORDER BY y; 51 } 52 } {0 {33 11}} 53 54 # SQLite allows the WHERE clause to reference output columns if there is 55 # no other way to resolve the name. 56 # 57 do_test resolver01-1.5 { 58 catchsql { 59 SELECT x AS yy FROM t3 ORDER BY yy; 60 } 61 } {0 {11 33}} 62 do_test resolver01-1.6 { 63 catchsql { 64 SELECT x AS yy FROM t3 ORDER BY 1; 65 } 66 } {0 {11 33}} 67 68 # The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y". 69 # The "y" binds more tightly to output columns than to input columns. 70 # 71 # This is for compatibility with SQL92 and with historical SQLite behavior. 72 # Note that PostgreSQL considers "y COLLATE nocase" to be an expression 73 # and thus PostgreSQL treats this case as if it where the 3.x case below. 74 # 75 do_test resolver01-2.1 { 76 catchsql { 77 SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase; 78 } 79 } {0 2} 80 do_test resolver01-2.2 { 81 catchsql { 82 SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase; 83 } 84 } {1 {ambiguous column name: y}} 85 do_test resolver01-2.3 { 86 catchsql { 87 SELECT x AS y FROM t3 ORDER BY y COLLATE nocase; 88 } 89 } {0 {11 33}} 90 do_test resolver01-2.4 { 91 catchsql { 92 SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase; 93 } 94 } {0 {33 11}} 95 do_test resolver01-2.5 { 96 catchsql { 97 SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase; 98 } 99 } {0 {11 33}} 100 do_test resolver01-2.6 { 101 catchsql { 102 SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase; 103 } 104 } {0 {11 33}} 105 106 # But if the form is "ORDER BY expr" then bind more tightly to the 107 # the input column names and only use the output column names if no 108 # input column name matches. 109 # 110 # This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL. 111 # Note that Oracle works differently. 112 # 113 do_test resolver01-3.1 { 114 catchsql { 115 SELECT 3 AS y FROM t1, t2 ORDER BY +y; 116 } 117 } {1 {ambiguous column name: y}} 118 do_test resolver01-3.2 { 119 catchsql { 120 SELECT 2 AS yy FROM t1, t2 ORDER BY +y; 121 } 122 } {1 {ambiguous column name: y}} 123 do_test resolver01-3.3 { 124 catchsql { 125 SELECT x AS y FROM t3 ORDER BY +y; 126 } 127 } {0 {33 11}} 128 do_test resolver01-3.4 { 129 catchsql { 130 SELECT x AS yy FROM t3 ORDER BY +y; 131 } 132 } {0 {33 11}} 133 do_test resolver01-3.5 { 134 catchsql { 135 SELECT x AS yy FROM t3 ORDER BY +yy 136 } 137 } {0 {11 33}} 138 139 # This is the test case given in ticket [f617ea3125e9] (with table name 140 # changed from "t1" to "t4". The behavior of (1) and (3) match with 141 # PostgreSQL, but we intentionally break with PostgreSQL to provide 142 # SQL92 behavior for case (2). 143 # 144 do_execsql_test resolver01-4.1 { 145 CREATE TABLE t4(m CHAR(2)); 146 INSERT INTO t4 VALUES('az'); 147 INSERT INTO t4 VALUES('by'); 148 INSERT INTO t4 VALUES('cx'); 149 SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m; 150 SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary; 151 SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m); 152 } {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x} 153 154 ########################################################################## 155 # Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds 156 # more tightly to the input tables in all cases. 157 # 158 # This first case case has been wrong in SQLite for time out of mind. 159 # For SQLite version 3.7.17 the answer was two rows, which is wrong. 160 # 161 do_execsql_test resolver01-5.1 { 162 CREATE TABLE t5(m CHAR(2)); 163 INSERT INTO t5 VALUES('ax'); 164 INSERT INTO t5 VALUES('bx'); 165 INSERT INTO t5 VALUES('cy'); 166 SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2; 167 } {1 x 1 x 1 y} 168 169 # This case is unambiguous and has always been correct. 170 # 171 do_execsql_test resolver01-5.2 { 172 SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2; 173 } {1 x 1 x 1 y} 174 175 # This case is not allowed in standard SQL, but SQLite allows and does 176 # the sensible thing. 177 # 178 do_execsql_test resolver01-5.3 { 179 SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2; 180 } {1 y 2 x} 181 do_execsql_test resolver01-5.4 { 182 SELECT count(*), substr(m,2,1) AS mx FROM t5 183 GROUP BY substr(m,2,1) ORDER BY 1, 2; 184 } {1 y 2 x} 185 186 # These test case weere provided in the 2013-08-14 email from Rob Golsteijn 187 # that originally reported the problem of ticket [1c69be2dafc28]. 188 # 189 do_execsql_test resolver01-6.1 { 190 CREATE TABLE t61(name); 191 SELECT min(name) FROM t61 GROUP BY lower(name); 192 } {} 193 do_execsql_test resolver01-6.2 { 194 SELECT min(name) AS name FROM t61 GROUP BY lower(name); 195 } {} 196 do_execsql_test resolver01-6.3 { 197 CREATE TABLE t63(name); 198 INSERT INTO t63 VALUES (NULL); 199 INSERT INTO t63 VALUES ('abc'); 200 SELECT count(), 201 NULLIF(name,'abc') AS name 202 FROM t63 203 GROUP BY lower(name); 204 } {1 {} 1 {}} 205 206 do_execsql_test resolver01-7.1 { 207 SELECT 2 AS x WHERE (SELECT x AS y WHERE 3>y); 208 } {2} 209 do_execsql_test resolver01-7.2 { 210 SELECT 2 AS x WHERE (SELECT x AS y WHERE 1>y); 211 } {} 212 213 214 215 216 finish_test