OLD | NEW |
(Empty) | |
| 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 |
OLD | NEW |