Annotation of embedaddon/sqlite3/test/tkt2822.test, revision 1.1
1.1 ! misho 1: # 2007 Dec 4
! 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 is to test that the issues surrounding expressions in
! 13: # ORDER BY clauses on compound SELECT statements raised by ticket
! 14: # #2822 have been dealt with.
! 15: #
! 16: # $Id: tkt2822.test,v 1.6 2008/08/20 16:35:10 drh Exp $
! 17: #
! 18:
! 19: set testdir [file dirname $argv0]
! 20: source $testdir/tester.tcl
! 21:
! 22: ifcapable !compound {
! 23: finish_test
! 24: return
! 25: }
! 26:
! 27: # The ORDER BY matching algorithm is three steps:
! 28: #
! 29: # (1) If the ORDER BY term is an integer constant i, then
! 30: # 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.z
! 33: # but simply x) then look for a column alias with the same
! 34: # name. If found, then sort by that column.
! 35: #
! 36: # (3) Evaluate the term as an expression and sort by the
! 37: # 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 one
! 41: # of the result columns. The sequences of three rules is
! 42: # attempted first on the left-most SELECT. If that doesn't
! 43: # 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 compound
! 47: # queries where a search is made of SELECTs to the right
! 48: # if the left-most SELECT does not match is not a part of
! 49: # standard SQL either. This extension is unique to SQLite
! 50: # as far as we know.
! 51: #
! 52: # Rule (2) was added by the changes ticket #2822. Prior to
! 53: # that changes, SQLite did not support rule (2), making it
! 54: # technically in violation of standard SQL semantics.
! 55: # No body noticed because rule (3) has the same effect as
! 56: # 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:
! 65: do_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: #
! 82: do_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}
! 87: do_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: #
! 98: do_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:
! 104: do_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}
! 112: do_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: #
! 120: do_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}
! 128: do_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, an
! 135: # attempt is made to find a match in subsequent SELECT statements.
! 136: #
! 137: do_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}
! 142: do_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}
! 151: do_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: #
! 162: do_test tkt2822-4.1 {
! 163: catchsql {
! 164: SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x
! 165: }
! 166: } {1 {1st ORDER BY term does not match any column in the result set}}
! 167: do_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 the
! 179: # the t3.b value (rule 3).
! 180: #
! 181: do_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}
! 190: do_test tkt2822-5.2 {
! 191: # Case does not matter. b should match B
! 192: execsql {
! 193: SELECT a AS b FROM t3 ORDER BY B;
! 194: }
! 195: } {1 9}
! 196: do_test tkt2822-5.3 {
! 197: # Quoting should not matter
! 198: execsql {
! 199: SELECT a AS 'b' FROM t3 ORDER BY "B";
! 200: }
! 201: } {1 9}
! 202: do_test tkt2822-5.4 {
! 203: # Quoting should not matter
! 204: 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 than
! 210: # a label. It therefore matches by rule (3) instead of rule (2).
! 211: #
! 212: do_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 queries
! 219: #
! 220: do_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, 2
! 230: }
! 231: } {1 7 1 8 7 2 9 2}
! 232: do_test tkt2822-6.2 {
! 233: execsql {
! 234: SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
! 235: ORDER BY PX, YX
! 236: }
! 237: } {1 7 1 8 7 2 9 2}
! 238: do_test tkt2822-6.3 {
! 239: execsql {
! 240: SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
! 241: ORDER BY XX, QX
! 242: }
! 243: } {1 7 1 8 7 2 9 2}
! 244: do_test tkt2822-6.4 {
! 245: execsql {
! 246: SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
! 247: ORDER BY QX, XX
! 248: }
! 249: } {7 2 9 2 1 7 1 8}
! 250: do_test tkt2822-6.5 {
! 251: execsql {
! 252: SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
! 253: ORDER BY t6b.x, QX
! 254: }
! 255: } {1 7 1 8 7 2 9 2}
! 256: do_test tkt2822-6.6 {
! 257: execsql {
! 258: SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b
! 259: ORDER BY t6a.q, XX
! 260: }
! 261: } {7 2 9 2 1 7 1 8}
! 262:
! 263: # More error message tests. This is really more of a test of the
! 264: # %r ordinal value formatting capablity added to sqlite3_snprintf()
! 265: # by ticket #2822.
! 266: #
! 267: do_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}}
! 276: do_test tkt2822-7.2 {
! 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}}
! 281: do_test tkt2822-7.3 {
! 282: catchsql {
! 283: SELECT * FROM t7 ORDER BY 1, 2, 0;
! 284: }
! 285: } {1 {3rd ORDER BY term out of range - should be between 1 and 25}}
! 286: do_test tkt2822-7.4 {
! 287: catchsql {
! 288: SELECT * FROM t7 ORDER BY 1, 2, 3, 0;
! 289: }
! 290: } {1 {4th ORDER BY term out of range - should be between 1 and 25}}
! 291: do_test tkt2822-7.9 {
! 292: catchsql {
! 293: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 0;
! 294: }
! 295: } {1 {9th ORDER BY term out of range - should be between 1 and 25}}
! 296: do_test tkt2822-7.10 {
! 297: catchsql {
! 298: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 0;
! 299: }
! 300: } {1 {10th ORDER BY term out of range - should be between 1 and 25}}
! 301: do_test tkt2822-7.11 {
! 302: catchsql {
! 303: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0;
! 304: }
! 305: } {1 {11th ORDER BY term out of range - should be between 1 and 25}}
! 306: do_test tkt2822-7.12 {
! 307: catchsql {
! 308: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 0;
! 309: }
! 310: } {1 {12th ORDER BY term out of range - should be between 1 and 25}}
! 311: do_test tkt2822-7.13 {
! 312: catchsql {
! 313: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 0;
! 314: }
! 315: } {1 {13th ORDER BY term out of range - should be between 1 and 25}}
! 316: do_test tkt2822-7.20 {
! 317: catchsql {
! 318: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
! 319: 11,12,13,14,15,16,17,18,19, 0
! 320: }
! 321: } {1 {20th ORDER BY term out of range - should be between 1 and 25}}
! 322: do_test tkt2822-7.21 {
! 323: catchsql {
! 324: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
! 325: 11,12,13,14,15,16,17,18,19, 20, 0
! 326: }
! 327: } {1 {21st ORDER BY term out of range - should be between 1 and 25}}
! 328: do_test tkt2822-7.22 {
! 329: catchsql {
! 330: SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
! 331: 11,12,13,14,15,16,17,18,19, 20, 21, 0
! 332: }
! 333: } {1 {22nd ORDER BY term out of range - should be between 1 and 25}}
! 334:
! 335:
! 336: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt2822.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test