1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
# 2013-04-13
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file tests features of the name resolver (the component that
# 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);
CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44);
SELECT 1 AS y FROM t1, t2 ORDER BY y;
}
} {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-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;
}
} {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
|
