diff options
author | Hans-Christoph Steiner <hans@eds.org> | 2014-10-16 22:51:35 -0400 |
---|---|---|
committer | Hans-Christoph Steiner <hans@eds.org> | 2014-10-16 22:51:35 -0400 |
commit | 569c6676a6ddb0ff73821d7693b5e18ddef809b9 (patch) | |
tree | 833538da7bba39105daff45e265aef386a200acd /test/resolver01.test | |
parent | 08119c361d1181b3e8f1abb429236e488a664753 (diff) |
Imported Upstream version 3.2.0upstream
Diffstat (limited to 'test/resolver01.test')
-rw-r--r-- | test/resolver01.test | 173 |
1 files changed, 171 insertions, 2 deletions
diff --git a/test/resolver01.test b/test/resolver01.test index 3ca6ace..7d95a21 100644 --- a/test/resolver01.test +++ b/test/resolver01.test @@ -13,10 +13,18 @@ # figures out what identifiers in the SQL statement refer to) that # were fixed by ticket [2500cdb9be] # +# See also tickets [1c69be2daf] and [f617ea3125] from 2013-08-14. +# set testdir [file dirname $argv0] source $testdir/tester.tcl +# "ORDER BY y" binds to the output result-set column named "y" +# if available. If no output column is named "y", then try to +# bind against an input column named "y". +# +# This is classical SQL92 behavior. +# do_test resolver01-1.1 { catchsql { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22); @@ -26,14 +34,175 @@ do_test resolver01-1.1 { } {0 1} do_test resolver01-1.2 { catchsql { + SELECT 1 AS yy FROM t1, t2 ORDER BY y; + } +} {1 {ambiguous column name: y}} +do_test resolver01-1.3 { + catchsql { + CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22); + SELECT x AS y FROM t3 ORDER BY y; + } +} {0 {11 33}} +do_test resolver01-1.4 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY y; + } +} {0 {33 11}} + +# SQLite allows the WHERE clause to reference output columns if there is +# no other way to resolve the name. +# +do_test resolver01-1.5 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY yy; + } +} {0 {11 33}} +do_test resolver01-1.6 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY 1; + } +} {0 {11 33}} + +# The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y". +# The "y" binds more tightly to output columns than to input columns. +# +# This is for compatibility with SQL92 and with historical SQLite behavior. +# Note that PostgreSQL considers "y COLLATE nocase" to be an expression +# and thus PostgreSQL treats this case as if it where the 3.x case below. +# +do_test resolver01-2.1 { + catchsql { SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase; } } {0 2} -do_test resolver01-1.3 { +do_test resolver01-2.2 { + catchsql { + SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase; + } +} {1 {ambiguous column name: y}} +do_test resolver01-2.3 { + catchsql { + SELECT x AS y FROM t3 ORDER BY y COLLATE nocase; + } +} {0 {11 33}} +do_test resolver01-2.4 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase; + } +} {0 {33 11}} +do_test resolver01-2.5 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase; + } +} {0 {11 33}} +do_test resolver01-2.6 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase; + } +} {0 {11 33}} + +# But if the form is "ORDER BY expr" then bind more tightly to the +# the input column names and only use the output column names if no +# input column name matches. +# +# This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL. +# Note that Oracle works differently. +# +do_test resolver01-3.1 { catchsql { SELECT 3 AS y FROM t1, t2 ORDER BY +y; } -} {0 3} +} {1 {ambiguous column name: y}} +do_test resolver01-3.2 { + catchsql { + SELECT 2 AS yy FROM t1, t2 ORDER BY +y; + } +} {1 {ambiguous column name: y}} +do_test resolver01-3.3 { + catchsql { + SELECT x AS y FROM t3 ORDER BY +y; + } +} {0 {33 11}} +do_test resolver01-3.4 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY +y; + } +} {0 {33 11}} +do_test resolver01-3.5 { + catchsql { + SELECT x AS yy FROM t3 ORDER BY +yy + } +} {0 {11 33}} + +# This is the test case given in ticket [f617ea3125e9] (with table name +# changed from "t1" to "t4". The behavior of (1) and (3) match with +# PostgreSQL, but we intentionally break with PostgreSQL to provide +# SQL92 behavior for case (2). +# +do_execsql_test resolver01-4.1 { + CREATE TABLE t4(m CHAR(2)); + INSERT INTO t4 VALUES('az'); + INSERT INTO t4 VALUES('by'); + INSERT INTO t4 VALUES('cx'); + SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m; + SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary; + SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m); +} {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x} + +########################################################################## +# Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds +# more tightly to the input tables in all cases. +# +# This first case case has been wrong in SQLite for time out of mind. +# For SQLite version 3.7.17 the answer was two rows, which is wrong. +# +do_execsql_test resolver01-5.1 { + CREATE TABLE t5(m CHAR(2)); + INSERT INTO t5 VALUES('ax'); + INSERT INTO t5 VALUES('bx'); + INSERT INTO t5 VALUES('cy'); + SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2; +} {1 x 1 x 1 y} + +# This case is unambiguous and has always been correct. +# +do_execsql_test resolver01-5.2 { + SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2; +} {1 x 1 x 1 y} + +# This case is not allowed in standard SQL, but SQLite allows and does +# the sensible thing. +# +do_execsql_test resolver01-5.3 { + SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2; +} {1 y 2 x} +do_execsql_test resolver01-5.4 { + SELECT count(*), substr(m,2,1) AS mx FROM t5 + GROUP BY substr(m,2,1) ORDER BY 1, 2; +} {1 y 2 x} + +# These test case weere provided in the 2013-08-14 email from Rob Golsteijn +# that originally reported the problem of ticket [1c69be2dafc28]. +# +do_execsql_test resolver01-6.1 { + CREATE TABLE t61(name); + SELECT min(name) FROM t61 GROUP BY lower(name); +} {} +do_execsql_test resolver01-6.2 { + SELECT min(name) AS name FROM t61 GROUP BY lower(name); +} {} +do_execsql_test resolver01-6.3 { + CREATE TABLE t63(name); + INSERT INTO t63 VALUES (NULL); + INSERT INTO t63 VALUES ('abc'); + SELECT count(), + NULLIF(name,'abc') AS name + FROM t63 + GROUP BY lower(name); +} {1 {} 1 {}} + + + finish_test |