xref: /sqlite-3.40.0/test/resolver01.test (revision e35463b3) 
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
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# "ORDER BY y" binds to the output result-set column named "y"
23# if available.  If no output column is named "y", then try to
24# bind against an input column named "y".
25#
26# This is classical SQL92 behavior.
27#
28do_test resolver01-1.1 {
29  catchsql {
30    CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22);
31    CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44);
32    SELECT 1 AS y FROM t1, t2 ORDER BY y;
33  }
34} {0 1}
35do_test resolver01-1.2 {
36  catchsql {
37    SELECT 1 AS yy FROM t1, t2 ORDER BY y;
38  }
39} {1 {ambiguous column name: y}}
40do_test resolver01-1.3 {
41  catchsql {
42    CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22);
43    SELECT x AS y FROM t3 ORDER BY y;
44  }
45} {0 {11 33}}
46do_test resolver01-1.4 {
47  catchsql {
48    SELECT x AS yy FROM t3 ORDER BY y;
49  }
50} {0 {33 11}}
51
52# SQLite allows the WHERE clause to reference output columns if there is
53# no other way to resolve the name.
54#
55do_test resolver01-1.5 {
56  catchsql {
57    SELECT x AS yy FROM t3 ORDER BY yy;
58  }
59} {0 {11 33}}
60do_test resolver01-1.6 {
61  catchsql {
62    SELECT x AS yy FROM t3 ORDER BY 1;
63  }
64} {0 {11 33}}
65
66# The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y".
67# The "y" binds more tightly to output columns than to input columns.
68#
69# This is for compatibility with SQL92 and with historical SQLite behavior.
70# Note that PostgreSQL considers "y COLLATE nocase" to be an expression
71# and thus PostgreSQL treats this case as if it where the 3.x case below.
72#
73do_test resolver01-2.1 {
74  catchsql {
75    SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase;
76  }
77} {0 2}
78do_test resolver01-2.2 {
79  catchsql {
80    SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase;
81  }
82} {1 {ambiguous column name: y}}
83do_test resolver01-2.3 {
84  catchsql {
85    SELECT x AS y FROM t3 ORDER BY y COLLATE nocase;
86  }
87} {0 {11 33}}
88do_test resolver01-2.4 {
89  catchsql {
90    SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase;
91  }
92} {0 {33 11}}
93do_test resolver01-2.5 {
94  catchsql {
95    SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase;
96  }
97} {0 {11 33}}
98do_test resolver01-2.6 {
99  catchsql {
100    SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase;
101  }
102} {0 {11 33}}
103
104# But if the form is "ORDER BY expr" then bind more tightly to the
105# the input column names and only use the output column names if no
106# input column name matches.
107#
108# This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL.
109# Note that Oracle works differently.
110#
111do_test resolver01-3.1 {
112  catchsql {
113    SELECT 3 AS y FROM t1, t2 ORDER BY +y;
114  }
115} {1 {ambiguous column name: y}}
116do_test resolver01-3.2 {
117  catchsql {
118    SELECT 2 AS yy FROM t1, t2 ORDER BY +y;
119  }
120} {1 {ambiguous column name: y}}
121do_test resolver01-3.3 {
122  catchsql {
123    SELECT x AS y FROM t3 ORDER BY +y;
124  }
125} {0 {33 11}}
126do_test resolver01-3.4 {
127  catchsql {
128    SELECT x AS yy FROM t3 ORDER BY +y;
129  }
130} {0 {33 11}}
131do_test resolver01-3.5 {
132  catchsql {
133    SELECT x AS yy FROM t3 ORDER BY +yy
134  }
135} {0 {11 33}}
136
137# This is the test case given in ticket [f617ea3125e9] (with table name
138# changed from "t1" to "t4".  The behavior of (1) and (3) match with
139# PostgreSQL, but we intentionally break with PostgreSQL to provide
140# SQL92 behavior for case (2).
141#
142do_execsql_test resolver01-4.1 {
143  CREATE TABLE t4(m CHAR(2));
144  INSERT INTO t4 VALUES('az');
145  INSERT INTO t4 VALUES('by');
146  INSERT INTO t4 VALUES('cx');
147  SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
148  SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
149  SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
150} {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}
151
152finish_test
153