AryaWu/sqlite

1# 2013-04-132#3# The author disclaims copyright to this source code. In place of4# 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 that13# figures out what identifiers in the SQL statement refer to) that14# 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 21set testdir [file dirname $argv0]22source $testdir/tester.tcl23 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 to26# bind against an input column named "y".27#28# This is classical SQL92 behavior.29#30do_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}37do_test resolver01-1.2 {38  catchsql {39    SELECT 1 AS yy FROM t1, t2 ORDER BY y;40  }41} {1 {ambiguous column name: y}}42do_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}}48do_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 is55# no other way to resolve the name.56#57do_test resolver01-1.5 {58  catchsql {59    SELECT x AS yy FROM t3 ORDER BY yy;60  }61} {0 {11 33}}62do_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 expression73# and thus PostgreSQL treats this case as if it where the 3.x case below.74#75do_test resolver01-2.1 {76  catchsql {77    SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase;78  }79} {0 2}80do_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}}85do_test resolver01-2.3 {86  catchsql {87    SELECT x AS y FROM t3 ORDER BY y COLLATE nocase;88  }89} {0 {11 33}}90do_test resolver01-2.4 {91  catchsql {92    SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase;93  }94} {0 {33 11}}95do_test resolver01-2.5 {96  catchsql {97    SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase;98  }99} {0 {11 33}}100do_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 the107# the input column names and only use the output column names if no108# input column name matches.109#110# This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL.111# Note that Oracle works differently.112#113do_test resolver01-3.1 {114  catchsql {115    SELECT 3 AS y FROM t1, t2 ORDER BY +y;116  }117} {1 {ambiguous column name: y}}118do_test resolver01-3.2 {119  catchsql {120    SELECT 2 AS yy FROM t1, t2 ORDER BY +y;121  }122} {1 {ambiguous column name: y}}123do_test resolver01-3.3 {124  catchsql {125    SELECT x AS y FROM t3 ORDER BY +y;126  }127} {0 {33 11}}128do_test resolver01-3.4 {129  catchsql {130    SELECT x AS yy FROM t3 ORDER BY +y;131  }132} {0 {33 11}}133do_test resolver01-3.5 {134  catchsql {135    SELECT x AS yy FROM t3 ORDER BY +yy136  }137} {0 {11 33}}138 139# This is the test case given in ticket [f617ea3125e9] (with table name140# changed from "t1" to "t4".  The behavior of (1) and (3) match with141# PostgreSQL, but we intentionally break with PostgreSQL to provide142# SQL92 behavior for case (2).143#144do_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 binds156# 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#161do_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#171do_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 does176# the sensible thing.177#178do_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}181do_execsql_test resolver01-5.4 {182  SELECT count(*), substr(m,2,1) AS mx FROM t5183   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 Golsteijn187# that originally reported the problem of ticket [1c69be2dafc28].188#189do_execsql_test resolver01-6.1 {190  CREATE TABLE t61(name);191  SELECT min(name) FROM t61 GROUP BY lower(name);192} {}193do_execsql_test resolver01-6.2 {194  SELECT min(name) AS name FROM t61 GROUP BY lower(name); 195} {}196do_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 name202    FROM t63203   GROUP BY lower(name);204} {1 {} 1 {}}205 206do_execsql_test resolver01-7.1 {207  SELECT 2 AS x WHERE (SELECT x AS y WHERE 3>y);208} {2}209do_execsql_test resolver01-7.2 {210  SELECT 2 AS x WHERE (SELECT x AS y WHERE 1>y);211} {}212 213 214 215 216finish_test217