AryaWu/sqlite
0
1# 2007 Dec 42#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 is to test that the issues surrounding expressions in13# ORDER BY clauses on compound SELECT statements raised by ticket14# #2822 have been dealt with.15#16# $Id: tkt2822.test,v 1.6 2008/08/20 16:35:10 drh Exp $17#18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22ifcapable !compound {23 finish_test24 return25}26 27# The ORDER BY matching algorithm is three steps:28# 29# (1) If the ORDER BY term is an integer constant i, then30# sort by the i-th column of the result set.31# 32# (2) If the ORDER BY term is an identifier (not x.y or x.y.z33# but simply x) then look for a column alias with the same34# name. If found, then sort by that column.35# 36# (3) Evaluate the term as an expression and sort by the37# value of the expression.38# 39# For a compound SELECT the rules are modified slightly.40# In the third rule, the expression must exactly match one41# of the result columns. The sequences of three rules is42# attempted first on the left-most SELECT. If that doesn't43# work, we move to the right, one by one.44#45# Rule (3) is not in standard SQL - it is an SQLite extension,46# though one copied from PostgreSQL. The rule for compound47# queries where a search is made of SELECTs to the right48# if the left-most SELECT does not match is not a part of49# standard SQL either. This extension is unique to SQLite50# as far as we know.51#52# Rule (2) was added by the changes ticket #2822. Prior to53# that changes, SQLite did not support rule (2), making it54# technically in violation of standard SQL semantics. 55# No body noticed because rule (3) has the same effect as56# rule (2) except in some obscure cases.57#58 59 60# Test plan:61#62# tkt2822-1.* - Simple identifier as ORDER BY expression.63# tkt2822-2.* - More complex ORDER BY expressions.64 65do_test tkt2822-0.1 {66 execsql {67 CREATE TABLE t1(a, b, c);68 CREATE TABLE t2(a, b, c);69 70 INSERT INTO t1 VALUES(1, 3, 9);71 INSERT INTO t1 VALUES(3, 9, 27);72 INSERT INTO t1 VALUES(5, 15, 45);73 74 INSERT INTO t2 VALUES(2, 6, 18);75 INSERT INTO t2 VALUES(4, 12, 36);76 INSERT INTO t2 VALUES(6, 18, 54);77 }78} {}79 80# Test the "ORDER BY <integer>" syntax.81#82do_test tkt2822-1.1 {83 execsql {84 SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1;85 }86} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}87do_test tkt2822-1.2 {88 execsql {89 SELECT a, CAST (b AS TEXT), c FROM t1 90 UNION ALL 91 SELECT a, b, c FROM t2 92 ORDER BY 2;93 }94} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}95 96# Test the "ORDER BY <identifier>" syntax.97#98do_test tkt2822-2.1 {99 execsql {100 SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a;101 }102} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}103 104do_test tkt2822-2.2 {105 execsql {106 SELECT a, CAST (b AS TEXT) AS x, c FROM t1 107 UNION ALL 108 SELECT a, b, c FROM t2 109 ORDER BY x;110 }111} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}112do_test tkt2822-2.3 {113 execsql {114 SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a;115 }116} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}117 118# Test the "ORDER BY <expression>" syntax.119#120do_test tkt2822-3.1 {121 execsql {122 SELECT a, CAST (b AS TEXT) AS x, c FROM t1 123 UNION ALL 124 SELECT a, b, c FROM t2 125 ORDER BY CAST (b AS TEXT);126 }127} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}128do_test tkt2822-3.2 {129 execsql {130 SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a;131 }132} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}133 134# Test that if a match cannot be found in the leftmost SELECT, an135# attempt is made to find a match in subsequent SELECT statements.136#137do_test tkt2822-3.3 {138 execsql {139 SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x;140 }141} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}142do_test tkt2822-3.4 {143 # But the leftmost SELECT takes precedence.144 execsql {145 SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 146 UNION ALL 147 SELECT a, b, c FROM t2 148 ORDER BY a;149 }150} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}151do_test tkt2822-3.5 {152 execsql {153 SELECT a, b, c FROM t2 154 UNION ALL 155 SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 156 ORDER BY a;157 }158} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}159 160# Test some error conditions (ORDER BY clauses that match no column).161#162do_test tkt2822-4.1 {163 catchsql {164 SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x165 }166} {1 {1st ORDER BY term does not match any column in the result set}}167do_test tkt2822-4.2 {168 catchsql {169 SELECT a, CAST (b AS TEXT) AS x, c FROM t1 170 UNION ALL 171 SELECT a, b, c FROM t2 172 ORDER BY CAST (b AS INTEGER);173 }174} {1 {1st ORDER BY term does not match any column in the result set}}175 176# Tests for rule (2).177#178# The "ORDER BY b" should match the column alias (rule 2), not the179# the t3.b value (rule 3). 180#181do_test tkt2822-5.1 {182 execsql {183 CREATE TABLE t3(a,b);184 INSERT INTO t3 VALUES(1,8);185 INSERT INTO t3 VALUES(9,2);186 187 SELECT a AS b FROM t3 ORDER BY b;188 }189} {1 9}190do_test tkt2822-5.2 {191 # Case does not matter. b should match B192 execsql {193 SELECT a AS b FROM t3 ORDER BY B;194 }195} {1 9}196do_test tkt2822-5.3 {197 # Quoting should not matter198 execsql {199 SELECT a AS 'b' FROM t3 ORDER BY "B";200 }201} {1 9}202do_test tkt2822-5.4 {203 # Quoting should not matter204 execsql {205 SELECT a AS "b" FROM t3 ORDER BY [B];206 }207} {1 9}208 209# In "ORDER BY +b" the term is now an expression rather than210# a label. It therefore matches by rule (3) instead of rule (2).211# 212do_test tkt2822-5.5 {213 execsql {214 SELECT a AS b FROM t3 ORDER BY +b;215 }216} {9 1}217 218# Tests for rule 2 in compound queries219#220do_test tkt2822-6.1 {221 execsql {222 CREATE TABLE t6a(p,q);223 INSERT INTO t6a VALUES(1,8);224 INSERT INTO t6a VALUES(9,2);225 CREATE TABLE t6b(x,y);226 INSERT INTO t6b VALUES(1,7);227 INSERT INTO t6b VALUES(7,2);228 229 SELECT p, q FROM t6a UNION ALL SELECT x, y FROM t6b ORDER BY 1, 2230 }231} {1 7 1 8 7 2 9 2}232do_test tkt2822-6.2 {233 execsql {234 SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b235 ORDER BY PX, YX236 }237} {1 7 1 8 7 2 9 2}238do_test tkt2822-6.3 {239 execsql {240 SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b241 ORDER BY XX, QX242 }243} {1 7 1 8 7 2 9 2}244do_test tkt2822-6.4 {245 execsql {246 SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b247 ORDER BY QX, XX248 }249} {7 2 9 2 1 7 1 8}250do_test tkt2822-6.5 {251 execsql {252 SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b253 ORDER BY t6b.x, QX254 }255} {1 7 1 8 7 2 9 2}256do_test tkt2822-6.6 {257 execsql {258 SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b259 ORDER BY t6a.q, XX260 }261} {7 2 9 2 1 7 1 8}262 263# More error message tests. This is really more of a test of the264# %r ordinal value formatting capablity added to sqlite3_snprintf()265# by ticket #2822.266#267do_test tkt2822-7.1 {268 execsql {269 CREATE TABLE t7(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,270 a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25);271 }272 catchsql {273 SELECT * FROM t7 ORDER BY 0;274 }275} {1 {1st ORDER BY term out of range - should be between 1 and 25}}276do_test tkt2822-7.2.1 {277 catchsql {278 SELECT * FROM t7 ORDER BY 1, 0;279 }280} {1 {2nd ORDER BY term out of range - should be between 1 and 25}}281do_test tkt2822-7.2.2 {282 catchsql {283 SELECT * FROM t7 ORDER BY 1, 26;284 }285} {1 {2nd ORDER BY term out of range - should be between 1 and 25}}286do_test tkt2822-7.2.3 {287 catchsql {288 SELECT * FROM t7 ORDER BY 1, 65536;289 }290} {1 {2nd ORDER BY term out of range - should be between 1 and 25}}291do_test tkt2822-7.3 {292 catchsql {293 SELECT * FROM t7 ORDER BY 1, 2, 0;294 }295} {1 {3rd ORDER BY term out of range - should be between 1 and 25}}296do_test tkt2822-7.4 {297 catchsql {298 SELECT * FROM t7 ORDER BY 1, 2, 3, 0;299 }300} {1 {4th ORDER BY term out of range - should be between 1 and 25}}301do_test tkt2822-7.9 {302 catchsql {303 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 0;304 }305} {1 {9th ORDER BY term out of range - should be between 1 and 25}}306do_test tkt2822-7.10 {307 catchsql {308 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 0;309 }310} {1 {10th ORDER BY term out of range - should be between 1 and 25}}311do_test tkt2822-7.11 {312 catchsql {313 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0;314 }315} {1 {11th ORDER BY term out of range - should be between 1 and 25}}316do_test tkt2822-7.12 {317 catchsql {318 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 0;319 }320} {1 {12th ORDER BY term out of range - should be between 1 and 25}}321do_test tkt2822-7.13 {322 catchsql {323 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 0;324 }325} {1 {13th ORDER BY term out of range - should be between 1 and 25}}326do_test tkt2822-7.20 {327 catchsql {328 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,329 11,12,13,14,15,16,17,18,19, 0330 }331} {1 {20th ORDER BY term out of range - should be between 1 and 25}}332do_test tkt2822-7.21 {333 catchsql {334 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,335 11,12,13,14,15,16,17,18,19, 20, 0336 }337} {1 {21st ORDER BY term out of range - should be between 1 and 25}}338do_test tkt2822-7.22 {339 catchsql {340 SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,341 11,12,13,14,15,16,17,18,19, 20, 21, 0342 }343} {1 {22nd ORDER BY term out of range - should be between 1 and 25}}344 345 346finish_test347