Annotation of embedaddon/sqlite3/test/selectB.test, revision 1.1
1.1 ! misho 1: # 2008 June 24
! 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: # This file implements regression tests for SQLite library.
! 12: #
! 13: # $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $
! 14:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: ifcapable !compound {
! 19: finish_test
! 20: return
! 21: }
! 22:
! 23: proc test_transform {testname sql1 sql2 results} {
! 24: set ::vdbe1 [list]
! 25: set ::vdbe2 [list]
! 26: db eval "explain $sql1" { lappend ::vdbe1 $opcode }
! 27: db eval "explain $sql2" { lappend ::vdbe2 $opcode }
! 28:
! 29: do_test $testname.transform {
! 30: set ::vdbe1
! 31: } $::vdbe2
! 32:
! 33: set ::sql1 $sql1
! 34: do_test $testname.sql1 {
! 35: execsql $::sql1
! 36: } $results
! 37:
! 38: set ::sql2 $sql2
! 39: do_test $testname.sql2 {
! 40: execsql $::sql2
! 41: } $results
! 42: }
! 43:
! 44: do_test selectB-1.1 {
! 45: execsql {
! 46: CREATE TABLE t1(a, b, c);
! 47: CREATE TABLE t2(d, e, f);
! 48:
! 49: INSERT INTO t1 VALUES( 2, 4, 6);
! 50: INSERT INTO t1 VALUES( 8, 10, 12);
! 51: INSERT INTO t1 VALUES(14, 16, 18);
! 52:
! 53: INSERT INTO t2 VALUES(3, 6, 9);
! 54: INSERT INTO t2 VALUES(12, 15, 18);
! 55: INSERT INTO t2 VALUES(21, 24, 27);
! 56: }
! 57: } {}
! 58:
! 59: for {set ii 1} {$ii <= 2} {incr ii} {
! 60:
! 61: if {$ii == 2} {
! 62: do_test selectB-2.1 {
! 63: execsql {
! 64: CREATE INDEX i1 ON t1(a);
! 65: CREATE INDEX i2 ON t2(d);
! 66: }
! 67: } {}
! 68: }
! 69:
! 70: test_transform selectB-$ii.2 {
! 71: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 72: } {
! 73: SELECT a FROM t1 UNION ALL SELECT d FROM t2
! 74: } {2 8 14 3 12 21}
! 75:
! 76: test_transform selectB-$ii.3 {
! 77: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
! 78: } {
! 79: SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
! 80: } {2 3 8 12 14 21}
! 81:
! 82: test_transform selectB-$ii.4 {
! 83: SELECT * FROM
! 84: (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 85: WHERE a>10 ORDER BY 1
! 86: } {
! 87: SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
! 88: } {12 14 21}
! 89:
! 90: test_transform selectB-$ii.5 {
! 91: SELECT * FROM
! 92: (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 93: WHERE a>10 ORDER BY a
! 94: } {
! 95: SELECT a FROM t1 WHERE a>10
! 96: UNION ALL
! 97: SELECT d FROM t2 WHERE d>10
! 98: ORDER BY a
! 99: } {12 14 21}
! 100:
! 101: test_transform selectB-$ii.6 {
! 102: SELECT * FROM
! 103: (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
! 104: WHERE a>10 ORDER BY a
! 105: } {
! 106: SELECT a FROM t1 WHERE a>10
! 107: UNION ALL
! 108: SELECT d FROM t2 WHERE d>12 AND d>10
! 109: ORDER BY a
! 110: } {14 21}
! 111:
! 112: test_transform selectB-$ii.7 {
! 113: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
! 114: LIMIT 2
! 115: } {
! 116: SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
! 117: } {2 3}
! 118:
! 119: test_transform selectB-$ii.8 {
! 120: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
! 121: LIMIT 2 OFFSET 3
! 122: } {
! 123: SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
! 124: } {12 14}
! 125:
! 126: test_transform selectB-$ii.9 {
! 127: SELECT * FROM (
! 128: SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
! 129: )
! 130: } {
! 131: SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
! 132: } {2 8 14 3 12 21 6 12 18}
! 133:
! 134: test_transform selectB-$ii.10 {
! 135: SELECT * FROM (
! 136: SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
! 137: ) ORDER BY 1
! 138: } {
! 139: SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
! 140: ORDER BY 1
! 141: } {2 3 6 8 12 12 14 18 21}
! 142:
! 143: test_transform selectB-$ii.11 {
! 144: SELECT * FROM (
! 145: SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
! 146: ) WHERE a>=10 ORDER BY 1 LIMIT 3
! 147: } {
! 148: SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
! 149: UNION ALL SELECT c FROM t1 WHERE c>=10
! 150: ORDER BY 1 LIMIT 3
! 151: } {12 12 14}
! 152:
! 153: test_transform selectB-$ii.12 {
! 154: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
! 155: } {
! 156: SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
! 157: } {2 8}
! 158:
! 159: # An ORDER BY in a compound subqueries defeats flattening. Ticket #3773
! 160: # test_transform selectB-$ii.13 {
! 161: # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
! 162: # } {
! 163: # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
! 164: # } {2 3 8 12 14 21}
! 165: #
! 166: # test_transform selectB-$ii.14 {
! 167: # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
! 168: # } {
! 169: # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
! 170: # } {21 14 12 8 3 2}
! 171: #
! 172: # test_transform selectB-$ii.14 {
! 173: # SELECT * FROM (
! 174: # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
! 175: # ) LIMIT 2 OFFSET 2
! 176: # } {
! 177: # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
! 178: # LIMIT 2 OFFSET 2
! 179: # } {12 8}
! 180: #
! 181: # test_transform selectB-$ii.15 {
! 182: # SELECT * FROM (
! 183: # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
! 184: # )
! 185: # } {
! 186: # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
! 187: # } {2 4 3 6 8 10 12 15 14 16 21 24}
! 188: }
! 189:
! 190: do_test selectB-3.0 {
! 191: execsql {
! 192: DROP INDEX i1;
! 193: DROP INDEX i2;
! 194: }
! 195: } {}
! 196:
! 197: for {set ii 3} {$ii <= 6} {incr ii} {
! 198:
! 199: switch $ii {
! 200: 4 {
! 201: optimization_control db query-flattener off
! 202: }
! 203: 5 {
! 204: optimization_control db query-flattener on
! 205: do_test selectB-5.0 {
! 206: execsql {
! 207: CREATE INDEX i1 ON t1(a);
! 208: CREATE INDEX i2 ON t1(b);
! 209: CREATE INDEX i3 ON t1(c);
! 210: CREATE INDEX i4 ON t2(d);
! 211: CREATE INDEX i5 ON t2(e);
! 212: CREATE INDEX i6 ON t2(f);
! 213: }
! 214: } {}
! 215: }
! 216: 6 {
! 217: optimization_control db query-flattener off
! 218: }
! 219: }
! 220:
! 221: do_test selectB-$ii.1 {
! 222: execsql {
! 223: SELECT DISTINCT * FROM
! 224: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
! 225: ORDER BY 1;
! 226: }
! 227: } {6 12 15 18 24}
! 228:
! 229: do_test selectB-$ii.2 {
! 230: execsql {
! 231: SELECT c, count(*) FROM
! 232: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
! 233: GROUP BY c ORDER BY 1;
! 234: }
! 235: } {6 2 12 1 15 1 18 1 24 1}
! 236: do_test selectB-$ii.3 {
! 237: execsql {
! 238: SELECT c, count(*) FROM
! 239: (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
! 240: GROUP BY c HAVING count(*)>1;
! 241: }
! 242: } {6 2}
! 243: do_test selectB-$ii.4 {
! 244: execsql {
! 245: SELECT t4.c, t3.a FROM
! 246: (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
! 247: WHERE t3.a=14
! 248: ORDER BY 1
! 249: }
! 250: } {6 14 6 14 12 14 15 14 18 14 24 14}
! 251:
! 252: do_test selectB-$ii.5 {
! 253: execsql {
! 254: SELECT d FROM t2
! 255: EXCEPT
! 256: SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 257: }
! 258: } {}
! 259: do_test selectB-$ii.6 {
! 260: execsql {
! 261: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 262: EXCEPT
! 263: SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
! 264: }
! 265: } {}
! 266: do_test selectB-$ii.7 {
! 267: execsql {
! 268: SELECT c FROM t1
! 269: EXCEPT
! 270: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 271: }
! 272: } {12}
! 273: do_test selectB-$ii.8 {
! 274: execsql {
! 275: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 276: EXCEPT
! 277: SELECT c FROM t1
! 278: }
! 279: } {9 15 24 27}
! 280: do_test selectB-$ii.9 {
! 281: execsql {
! 282: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 283: EXCEPT
! 284: SELECT c FROM t1
! 285: ORDER BY c DESC
! 286: }
! 287: } {27 24 15 9}
! 288:
! 289: do_test selectB-$ii.10 {
! 290: execsql {
! 291: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 292: UNION
! 293: SELECT c FROM t1
! 294: ORDER BY c DESC
! 295: }
! 296: } {27 24 18 15 12 9 6}
! 297: do_test selectB-$ii.11 {
! 298: execsql {
! 299: SELECT c FROM t1
! 300: UNION
! 301: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 302: ORDER BY c
! 303: }
! 304: } {6 9 12 15 18 24 27}
! 305: do_test selectB-$ii.12 {
! 306: execsql {
! 307: SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
! 308: ORDER BY c
! 309: }
! 310: } {6 9 12 15 18 18 24 27}
! 311: do_test selectB-$ii.13 {
! 312: execsql {
! 313: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 314: UNION
! 315: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 316: ORDER BY 1
! 317: }
! 318: } {6 9 15 18 24 27}
! 319:
! 320: do_test selectB-$ii.14 {
! 321: execsql {
! 322: SELECT c FROM t1
! 323: INTERSECT
! 324: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 325: ORDER BY 1
! 326: }
! 327: } {6 18}
! 328: do_test selectB-$ii.15 {
! 329: execsql {
! 330: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 331: INTERSECT
! 332: SELECT c FROM t1
! 333: ORDER BY 1
! 334: }
! 335: } {6 18}
! 336: do_test selectB-$ii.16 {
! 337: execsql {
! 338: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 339: INTERSECT
! 340: SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
! 341: ORDER BY 1
! 342: }
! 343: } {6 9 15 18 24 27}
! 344:
! 345: do_test selectB-$ii.17 {
! 346: execsql {
! 347: SELECT * FROM (
! 348: SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
! 349: ) LIMIT 2
! 350: }
! 351: } {2 8}
! 352:
! 353: do_test selectB-$ii.18 {
! 354: execsql {
! 355: SELECT * FROM (
! 356: SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
! 357: ) LIMIT 2
! 358: }
! 359: } {14 3}
! 360:
! 361: do_test selectB-$ii.19 {
! 362: execsql {
! 363: SELECT * FROM (
! 364: SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
! 365: )
! 366: }
! 367: } {0 1 1 0}
! 368:
! 369: do_test selectB-$ii.20 {
! 370: execsql {
! 371: SELECT DISTINCT * FROM (
! 372: SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
! 373: )
! 374: }
! 375: } {0 1}
! 376:
! 377: do_test selectB-$ii.21 {
! 378: execsql {
! 379: SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
! 380: }
! 381: } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
! 382:
! 383: do_test selectB-$ii.22 {
! 384: execsql {
! 385: SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
! 386: }
! 387: } {3 12 21 345}
! 388:
! 389: do_test selectB-$ii.23 {
! 390: execsql {
! 391: SELECT x, y FROM (
! 392: SELECT a AS x, b AS y FROM t1
! 393: UNION ALL
! 394: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
! 395: UNION ALL
! 396: SELECT a*100, b*100 FROM t1
! 397: ) ORDER BY 1;
! 398: }
! 399: } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}
! 400:
! 401: do_test selectB-$ii.24 {
! 402: execsql {
! 403: SELECT x, y FROM (
! 404: SELECT a AS x, b AS y FROM t1
! 405: UNION ALL
! 406: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
! 407: UNION ALL
! 408: SELECT a*100, b*100 FROM t1
! 409: ) ORDER BY 1;
! 410: }
! 411: } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
! 412:
! 413: do_test selectB-$ii.25 {
! 414: execsql {
! 415: SELECT x+y FROM (
! 416: SELECT a AS x, b AS y FROM t1
! 417: UNION ALL
! 418: SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
! 419: UNION ALL
! 420: SELECT a*100, b*100 FROM t1
! 421: ) WHERE y+x NOT NULL ORDER BY 1;
! 422: }
! 423: } {6 18 30 260.2 600 1800 3000}
! 424: }
! 425:
! 426: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to selectB.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