1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/tkt2822.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,336 @@
1.4 +# 2007 Dec 4
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +#
1.15 +# This file is to test that the issues surrounding expressions in
1.16 +# ORDER BY clauses on compound SELECT statements raised by ticket
1.17 +# #2822 have been dealt with.
1.18 +#
1.19 +# $Id: tkt2822.test,v 1.6 2008/08/20 16:35:10 drh Exp $
1.20 +#
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +ifcapable !compound {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +# The ORDER BY matching algorithm is three steps:
1.31 +#
1.32 +# (1) If the ORDER BY term is an integer constant i, then
1.33 +# sort by the i-th column of the result set.
1.34 +#
1.35 +# (2) If the ORDER BY term is an identifier (not x.y or x.y.z
1.36 +# but simply x) then look for a column alias with the same
1.37 +# name. If found, then sort by that column.
1.38 +#
1.39 +# (3) Evaluate the term as an expression and sort by the
1.40 +# value of the expression.
1.41 +#
1.42 +# For a compound SELECT the rules are modified slightly.
1.43 +# In the third rule, the expression must exactly match one
1.44 +# of the result columns. The sequences of three rules is
1.45 +# attempted first on the left-most SELECT. If that doesn't
1.46 +# work, we move to the right, one by one.
1.47 +#
1.48 +# Rule (3) is not in standard SQL - it is an SQLite extension,
1.49 +# though one copied from PostgreSQL. The rule for compound
1.50 +# queries where a search is made of SELECTs to the right
1.51 +# if the left-most SELECT does not match is not a part of
1.52 +# standard SQL either. This extension is unique to SQLite
1.53 +# as far as we know.
1.54 +#
1.55 +# Rule (2) was added by the changes ticket #2822. Prior to
1.56 +# that changes, SQLite did not support rule (2), making it
1.57 +# technically in violation of standard SQL semantics.
1.58 +# No body noticed because rule (3) has the same effect as
1.59 +# rule (2) except in some obscure cases.
1.60 +#
1.61 +
1.62 +
1.63 +# Test plan:
1.64 +#
1.65 +# tkt2822-1.* - Simple identifier as ORDER BY expression.
1.66 +# tkt2822-2.* - More complex ORDER BY expressions.
1.67 +
1.68 +do_test tkt2822-0.1 {
1.69 + execsql {
1.70 + CREATE TABLE t1(a, b, c);
1.71 + CREATE TABLE t2(a, b, c);
1.72 +
1.73 + INSERT INTO t1 VALUES(1, 3, 9);
1.74 + INSERT INTO t1 VALUES(3, 9, 27);
1.75 + INSERT INTO t1 VALUES(5, 15, 45);
1.76 +
1.77 + INSERT INTO t2 VALUES(2, 6, 18);
1.78 + INSERT INTO t2 VALUES(4, 12, 36);
1.79 + INSERT INTO t2 VALUES(6, 18, 54);
1.80 + }
1.81 +} {}
1.82 +
1.83 +# Test the "ORDER BY <integer>" syntax.
1.84 +#
1.85 +do_test tkt2822-1.1 {
1.86 + execsql {
1.87 + SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1;
1.88 + }
1.89 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.90 +do_test tkt2822-1.2 {
1.91 + execsql {
1.92 + SELECT a, CAST (b AS TEXT), c FROM t1
1.93 + UNION ALL
1.94 + SELECT a, b, c FROM t2
1.95 + ORDER BY 2;
1.96 + }
1.97 +} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
1.98 +
1.99 +# Test the "ORDER BY <identifier>" syntax.
1.100 +#
1.101 +do_test tkt2822-2.1 {
1.102 + execsql {
1.103 + SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a;
1.104 + }
1.105 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.106 +
1.107 +do_test tkt2822-2.2 {
1.108 + execsql {
1.109 + SELECT a, CAST (b AS TEXT) AS x, c FROM t1
1.110 + UNION ALL
1.111 + SELECT a, b, c FROM t2
1.112 + ORDER BY x;
1.113 + }
1.114 +} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
1.115 +do_test tkt2822-2.3 {
1.116 + execsql {
1.117 + SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a;
1.118 + }
1.119 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.120 +
1.121 +# Test the "ORDER BY <expression>" syntax.
1.122 +#
1.123 +do_test tkt2822-3.1 {
1.124 + execsql {
1.125 + SELECT a, CAST (b AS TEXT) AS x, c FROM t1
1.126 + UNION ALL
1.127 + SELECT a, b, c FROM t2
1.128 + ORDER BY CAST (b AS TEXT);
1.129 + }
1.130 +} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
1.131 +do_test tkt2822-3.2 {
1.132 + execsql {
1.133 + SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a;
1.134 + }
1.135 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.136 +
1.137 +# Test that if a match cannot be found in the leftmost SELECT, an
1.138 +# attempt is made to find a match in subsequent SELECT statements.
1.139 +#
1.140 +do_test tkt2822-3.3 {
1.141 + execsql {
1.142 + SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x;
1.143 + }
1.144 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.145 +do_test tkt2822-3.4 {
1.146 + # But the leftmost SELECT takes precedence.
1.147 + execsql {
1.148 + SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1
1.149 + UNION ALL
1.150 + SELECT a, b, c FROM t2
1.151 + ORDER BY a;
1.152 + }
1.153 +} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
1.154 +do_test tkt2822-3.5 {
1.155 + execsql {
1.156 + SELECT a, b, c FROM t2
1.157 + UNION ALL
1.158 + SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1
1.159 + ORDER BY a;
1.160 + }
1.161 +} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
1.162 +
1.163 +# Test some error conditions (ORDER BY clauses that match no column).
1.164 +#
1.165 +do_test tkt2822-4.1 {
1.166 + catchsql {
1.167 + SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x
1.168 + }
1.169 +} {1 {1st ORDER BY term does not match any column in the result set}}
1.170 +do_test tkt2822-4.2 {
1.171 + catchsql {
1.172 + SELECT a, CAST (b AS TEXT) AS x, c FROM t1
1.173 + UNION ALL
1.174 + SELECT a, b, c FROM t2
1.175 + ORDER BY CAST (b AS INTEGER);
1.176 + }
1.177 +} {1 {1st ORDER BY term does not match any column in the result set}}
1.178 +
1.179 +# Tests for rule (2).
1.180 +#
1.181 +# The "ORDER BY b" should match the column alias (rule 2), not the
1.182 +# the t3.b value (rule 3).
1.183 +#
1.184 +do_test tkt2822-5.1 {
1.185 + execsql {
1.186 + CREATE TABLE t3(a,b);
1.187 + INSERT INTO t3 VALUES(1,8);
1.188 + INSERT INTO t3 VALUES(9,2);
1.189 +
1.190 + SELECT a AS b FROM t3 ORDER BY b;
1.191 + }
1.192 +} {1 9}
1.193 +do_test tkt2822-5.2 {
1.194 + # Case does not matter. b should match B
1.195 + execsql {
1.196 + SELECT a AS b FROM t3 ORDER BY B;
1.197 + }
1.198 +} {1 9}
1.199 +do_test tkt2822-5.3 {
1.200 + # Quoting should not matter
1.201 + execsql {
1.202 + SELECT a AS 'b' FROM t3 ORDER BY "B";
1.203 + }
1.204 +} {1 9}
1.205 +do_test tkt2822-5.4 {
1.206 + # Quoting should not matter
1.207 + execsql {
1.208 + SELECT a AS "b" FROM t3 ORDER BY [B];
1.209 + }
1.210 +} {1 9}
1.211 +
1.212 +# In "ORDER BY +b" the term is now an expression rather than
1.213 +# a label. It therefore matches by rule (3) instead of rule (2).
1.214 +#
1.215 +do_test tkt2822-5.5 {
1.216 + execsql {
1.217 + SELECT a AS b FROM t3 ORDER BY +b;
1.218 + }
1.219 +} {9 1}
1.220 +
1.221 +# Tests for rule 2 in compound queries
1.222 +#
1.223 +do_test tkt2822-6.1 {
1.224 + execsql {
1.225 + CREATE TABLE t6a(p,q);
1.226 + INSERT INTO t6a VALUES(1,8);
1.227 + INSERT INTO t6a VALUES(9,2);
1.228 + CREATE TABLE t6b(x,y);
1.229 + INSERT INTO t6b VALUES(1,7);
1.230 + INSERT INTO t6b VALUES(7,2);
1.231 +
1.232 + SELECT p, q FROM t6a UNION ALL SELECT x, y FROM t6b ORDER BY 1, 2
1.233 + }
1.234 +} {1 7 1 8 7 2 9 2}
1.235 +do_test tkt2822-6.2 {
1.236 + execsql {
1.237 + SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
1.238 + ORDER BY PX, YX
1.239 + }
1.240 +} {1 7 1 8 7 2 9 2}
1.241 +do_test tkt2822-6.3 {
1.242 + execsql {
1.243 + SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
1.244 + ORDER BY XX, QX
1.245 + }
1.246 +} {1 7 1 8 7 2 9 2}
1.247 +do_test tkt2822-6.4 {
1.248 + execsql {
1.249 + SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
1.250 + ORDER BY QX, XX
1.251 + }
1.252 +} {7 2 9 2 1 7 1 8}
1.253 +do_test tkt2822-6.5 {
1.254 + execsql {
1.255 + SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
1.256 + ORDER BY t6b.x, QX
1.257 + }
1.258 +} {1 7 1 8 7 2 9 2}
1.259 +do_test tkt2822-6.6 {
1.260 + execsql {
1.261 + SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
1.262 + ORDER BY t6a.q, XX
1.263 + }
1.264 +} {7 2 9 2 1 7 1 8}
1.265 +
1.266 +# More error message tests. This is really more of a test of the
1.267 +# %r ordinal value formatting capablity added to sqlite3_snprintf()
1.268 +# by ticket #2822.
1.269 +#
1.270 +do_test tkt2822-7.1 {
1.271 + execsql {
1.272 + CREATE TABLE t7(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,
1.273 + a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25);
1.274 + }
1.275 + catchsql {
1.276 + SELECT * FROM t7 ORDER BY 0;
1.277 + }
1.278 +} {1 {1st ORDER BY term out of range - should be between 1 and 25}}
1.279 +do_test tkt2822-7.2 {
1.280 + catchsql {
1.281 + SELECT * FROM t7 ORDER BY 1, 0;
1.282 + }
1.283 +} {1 {2nd ORDER BY term out of range - should be between 1 and 25}}
1.284 +do_test tkt2822-7.3 {
1.285 + catchsql {
1.286 + SELECT * FROM t7 ORDER BY 1, 2, 0;
1.287 + }
1.288 +} {1 {3rd ORDER BY term out of range - should be between 1 and 25}}
1.289 +do_test tkt2822-7.4 {
1.290 + catchsql {
1.291 + SELECT * FROM t7 ORDER BY 1, 2, 3, 0;
1.292 + }
1.293 +} {1 {4th ORDER BY term out of range - should be between 1 and 25}}
1.294 +do_test tkt2822-7.9 {
1.295 + catchsql {
1.296 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 0;
1.297 + }
1.298 +} {1 {9th ORDER BY term out of range - should be between 1 and 25}}
1.299 +do_test tkt2822-7.10 {
1.300 + catchsql {
1.301 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 0;
1.302 + }
1.303 +} {1 {10th ORDER BY term out of range - should be between 1 and 25}}
1.304 +do_test tkt2822-7.11 {
1.305 + catchsql {
1.306 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0;
1.307 + }
1.308 +} {1 {11th ORDER BY term out of range - should be between 1 and 25}}
1.309 +do_test tkt2822-7.12 {
1.310 + catchsql {
1.311 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 0;
1.312 + }
1.313 +} {1 {12th ORDER BY term out of range - should be between 1 and 25}}
1.314 +do_test tkt2822-7.13 {
1.315 + catchsql {
1.316 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 0;
1.317 + }
1.318 +} {1 {13th ORDER BY term out of range - should be between 1 and 25}}
1.319 +do_test tkt2822-7.20 {
1.320 + catchsql {
1.321 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
1.322 + 11,12,13,14,15,16,17,18,19, 0
1.323 + }
1.324 +} {1 {20th ORDER BY term out of range - should be between 1 and 25}}
1.325 +do_test tkt2822-7.21 {
1.326 + catchsql {
1.327 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
1.328 + 11,12,13,14,15,16,17,18,19, 20, 0
1.329 + }
1.330 +} {1 {21st ORDER BY term out of range - should be between 1 and 25}}
1.331 +do_test tkt2822-7.22 {
1.332 + catchsql {
1.333 + SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
1.334 + 11,12,13,14,15,16,17,18,19, 20, 21, 0
1.335 + }
1.336 +} {1 {22nd ORDER BY term out of range - should be between 1 and 25}}
1.337 +
1.338 +
1.339 +finish_test