Annotation of embedaddon/sqlite3/test/subquery.test, revision 1.1
1.1 ! misho 1: # 2005 January 19
! 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. The
! 12: # focus of this script is testing correlated subqueries
! 13: #
! 14: # $Id: subquery.test,v 1.17 2009/01/09 01:12:28 drh Exp $
! 15: #
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: ifcapable !subquery {
! 21: finish_test
! 22: return
! 23: }
! 24:
! 25: do_test subquery-1.1 {
! 26: execsql {
! 27: BEGIN;
! 28: CREATE TABLE t1(a,b);
! 29: INSERT INTO t1 VALUES(1,2);
! 30: INSERT INTO t1 VALUES(3,4);
! 31: INSERT INTO t1 VALUES(5,6);
! 32: INSERT INTO t1 VALUES(7,8);
! 33: CREATE TABLE t2(x,y);
! 34: INSERT INTO t2 VALUES(1,1);
! 35: INSERT INTO t2 VALUES(3,9);
! 36: INSERT INTO t2 VALUES(5,25);
! 37: INSERT INTO t2 VALUES(7,49);
! 38: COMMIT;
! 39: }
! 40: execsql {
! 41: SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
! 42: }
! 43: } {1 1 3 9 5 25}
! 44: do_test subquery-1.2 {
! 45: execsql {
! 46: UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
! 47: SELECT * FROM t1;
! 48: }
! 49: } {1 3 3 13 5 31 7 57}
! 50:
! 51: do_test subquery-1.3 {
! 52: execsql {
! 53: SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
! 54: }
! 55: } {3}
! 56: do_test subquery-1.4 {
! 57: execsql {
! 58: SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
! 59: }
! 60: } {13 31 57}
! 61:
! 62: # Simple tests to make sure correlated subqueries in WHERE clauses
! 63: # are used by the query optimizer correctly.
! 64: do_test subquery-1.5 {
! 65: execsql {
! 66: SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
! 67: }
! 68: } {1 1 3 3 5 5 7 7}
! 69: do_test subquery-1.6 {
! 70: execsql {
! 71: CREATE INDEX i1 ON t1(a);
! 72: SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
! 73: }
! 74: } {1 1 3 3 5 5 7 7}
! 75: do_test subquery-1.7 {
! 76: execsql {
! 77: SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
! 78: }
! 79: } {1 1 3 3 5 5 7 7}
! 80:
! 81: # Try an aggregate in both the subquery and the parent query.
! 82: do_test subquery-1.8 {
! 83: execsql {
! 84: SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
! 85: }
! 86: } {2}
! 87:
! 88: # Test a correlated subquery disables the "only open the index" optimization.
! 89: do_test subquery-1.9.1 {
! 90: execsql {
! 91: SELECT (y*2)>b FROM t1, t2 WHERE a=x;
! 92: }
! 93: } {0 1 1 1}
! 94: do_test subquery-1.9.2 {
! 95: execsql {
! 96: SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
! 97: }
! 98: } {3 5 7}
! 99:
! 100: # Test that the flattening optimization works with subquery expressions.
! 101: do_test subquery-1.10.1 {
! 102: execsql {
! 103: SELECT (SELECT a), b FROM t1;
! 104: }
! 105: } {1 3 3 13 5 31 7 57}
! 106: do_test subquery-1.10.2 {
! 107: execsql {
! 108: SELECT * FROM (SELECT (SELECT a), b FROM t1);
! 109: }
! 110: } {1 3 3 13 5 31 7 57}
! 111: do_test subquery-1.10.3 {
! 112: execsql {
! 113: SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
! 114: }
! 115: } {16}
! 116: do_test subquery-1.10.4 {
! 117: execsql {
! 118: CREATE TABLE t5 (val int, period text PRIMARY KEY);
! 119: INSERT INTO t5 VALUES(5, '2001-3');
! 120: INSERT INTO t5 VALUES(10, '2001-4');
! 121: INSERT INTO t5 VALUES(15, '2002-1');
! 122: INSERT INTO t5 VALUES(5, '2002-2');
! 123: INSERT INTO t5 VALUES(10, '2002-3');
! 124: INSERT INTO t5 VALUES(15, '2002-4');
! 125: INSERT INTO t5 VALUES(10, '2003-1');
! 126: INSERT INTO t5 VALUES(5, '2003-2');
! 127: INSERT INTO t5 VALUES(25, '2003-3');
! 128: INSERT INTO t5 VALUES(5, '2003-4');
! 129:
! 130: SELECT period, vsum
! 131: FROM (SELECT
! 132: a.period,
! 133: (select sum(val) from t5 where period between a.period and '2002-4') vsum
! 134: FROM t5 a where a.period between '2002-1' and '2002-4')
! 135: WHERE vsum < 45 ;
! 136: }
! 137: } {2002-2 30 2002-3 25 2002-4 15}
! 138: do_test subquery-1.10.5 {
! 139: execsql {
! 140: SELECT period, vsum from
! 141: (select a.period,
! 142: (select sum(val) from t5 where period between a.period and '2002-4') vsum
! 143: FROM t5 a where a.period between '2002-1' and '2002-4')
! 144: WHERE vsum < 45 ;
! 145: }
! 146: } {2002-2 30 2002-3 25 2002-4 15}
! 147: do_test subquery-1.10.6 {
! 148: execsql {
! 149: DROP TABLE t5;
! 150: }
! 151: } {}
! 152:
! 153:
! 154:
! 155: #------------------------------------------------------------------
! 156: # The following test cases - subquery-2.* - are not logically
! 157: # organized. They're here largely because they were failing during
! 158: # one stage of development of sub-queries.
! 159: #
! 160: do_test subquery-2.1 {
! 161: execsql {
! 162: SELECT (SELECT 10);
! 163: }
! 164: } {10}
! 165: do_test subquery-2.2.1 {
! 166: execsql {
! 167: CREATE TABLE t3(a PRIMARY KEY, b);
! 168: INSERT INTO t3 VALUES(1, 2);
! 169: INSERT INTO t3 VALUES(3, 1);
! 170: }
! 171: } {}
! 172: do_test subquery-2.2.2 {
! 173: execsql {
! 174: SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
! 175: }
! 176: } {1 2}
! 177: do_test subquery-2.2.3 {
! 178: execsql {
! 179: DROP TABLE t3;
! 180: }
! 181: } {}
! 182: do_test subquery-2.3.1 {
! 183: execsql {
! 184: CREATE TABLE t3(a TEXT);
! 185: INSERT INTO t3 VALUES('10');
! 186: }
! 187: } {}
! 188: do_test subquery-2.3.2 {
! 189: execsql {
! 190: SELECT a IN (10.0, 20) FROM t3;
! 191: }
! 192: } {0}
! 193: do_test subquery-2.3.3 {
! 194: execsql {
! 195: DROP TABLE t3;
! 196: }
! 197: } {}
! 198: do_test subquery-2.4.1 {
! 199: execsql {
! 200: CREATE TABLE t3(a TEXT);
! 201: INSERT INTO t3 VALUES('XX');
! 202: }
! 203: } {}
! 204: do_test subquery-2.4.2 {
! 205: execsql {
! 206: SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
! 207: }
! 208: } {1}
! 209: do_test subquery-2.4.3 {
! 210: execsql {
! 211: DROP TABLE t3;
! 212: }
! 213: } {}
! 214: do_test subquery-2.5.1 {
! 215: execsql {
! 216: CREATE TABLE t3(a INTEGER);
! 217: INSERT INTO t3 VALUES(10);
! 218:
! 219: CREATE TABLE t4(x TEXT);
! 220: INSERT INTO t4 VALUES('10.0');
! 221: }
! 222: } {}
! 223: do_test subquery-2.5.2 {
! 224: # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
! 225: # has text affinity and the LHS has integer affinity. The rule is
! 226: # that we try to convert both sides to an integer before doing the
! 227: # comparision. Hence, the integer value 10 in t3 will compare equal
! 228: # to the string value '10.0' in t4 because the t4 value will be
! 229: # converted into an integer.
! 230: execsql {
! 231: SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
! 232: }
! 233: } {10.0}
! 234: do_test subquery-2.5.3.1 {
! 235: # The t4i index cannot be used to resolve the "x IN (...)" constraint
! 236: # because the constraint has integer affinity but t4i has text affinity.
! 237: execsql {
! 238: CREATE INDEX t4i ON t4(x);
! 239: SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
! 240: }
! 241: } {10.0}
! 242: do_test subquery-2.5.3.2 {
! 243: # Verify that the t4i index was not used in the previous query
! 244: set ::sqlite_query_plan
! 245: } {t4 {}}
! 246: do_test subquery-2.5.4 {
! 247: execsql {
! 248: DROP TABLE t3;
! 249: DROP TABLE t4;
! 250: }
! 251: } {}
! 252:
! 253: #------------------------------------------------------------------
! 254: # The following test cases - subquery-3.* - test tickets that
! 255: # were raised during development of correlated subqueries.
! 256: #
! 257:
! 258: # Ticket 1083
! 259: ifcapable view {
! 260: do_test subquery-3.1 {
! 261: catchsql { DROP TABLE t1; }
! 262: catchsql { DROP TABLE t2; }
! 263: execsql {
! 264: CREATE TABLE t1(a,b);
! 265: INSERT INTO t1 VALUES(1,2);
! 266: CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
! 267: CREATE TABLE t2(p,q);
! 268: INSERT INTO t2 VALUES(2,9);
! 269: SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
! 270: }
! 271: } {2}
! 272: do_test subquery-3.1.1 {
! 273: execsql {
! 274: SELECT * FROM v1 WHERE EXISTS(SELECT 1);
! 275: }
! 276: } {2}
! 277: } else {
! 278: catchsql { DROP TABLE t1; }
! 279: catchsql { DROP TABLE t2; }
! 280: execsql {
! 281: CREATE TABLE t1(a,b);
! 282: INSERT INTO t1 VALUES(1,2);
! 283: CREATE TABLE t2(p,q);
! 284: INSERT INTO t2 VALUES(2,9);
! 285: }
! 286: }
! 287:
! 288: # Ticket 1084
! 289: do_test subquery-3.2 {
! 290: catchsql {
! 291: CREATE TABLE t1(a,b);
! 292: INSERT INTO t1 VALUES(1,2);
! 293: }
! 294: execsql {
! 295: SELECT (SELECT t1.a) FROM t1;
! 296: }
! 297: } {1}
! 298:
! 299: # Test Cases subquery-3.3.* test correlated subqueries where the
! 300: # parent query is an aggregate query. Ticket #1105 is an example
! 301: # of such a query.
! 302: #
! 303: do_test subquery-3.3.1 {
! 304: execsql {
! 305: SELECT a, (SELECT b) FROM t1 GROUP BY a;
! 306: }
! 307: } {1 2}
! 308: do_test subquery-3.3.2 {
! 309: catchsql {DROP TABLE t2}
! 310: execsql {
! 311: CREATE TABLE t2(c, d);
! 312: INSERT INTO t2 VALUES(1, 'one');
! 313: INSERT INTO t2 VALUES(2, 'two');
! 314: SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
! 315: }
! 316: } {1 one}
! 317: do_test subquery-3.3.3 {
! 318: execsql {
! 319: INSERT INTO t1 VALUES(2, 4);
! 320: SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
! 321: }
! 322: } {2 two}
! 323: do_test subquery-3.3.4 {
! 324: execsql {
! 325: SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
! 326: }
! 327: } {1 one 2 two}
! 328: do_test subquery-3.3.5 {
! 329: execsql {
! 330: SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
! 331: }
! 332: } {1 1 2 1}
! 333:
! 334: #------------------------------------------------------------------
! 335: # These tests - subquery-4.* - use the TCL statement cache to try
! 336: # and expose bugs to do with re-using statements that have been
! 337: # passed to sqlite3_reset().
! 338: #
! 339: # One problem was that VDBE memory cells were not being initialised
! 340: # to NULL on the second and subsequent executions.
! 341: #
! 342: do_test subquery-4.1.1 {
! 343: execsql {
! 344: SELECT (SELECT a FROM t1);
! 345: }
! 346: } {1}
! 347: do_test subquery-4.2 {
! 348: execsql {
! 349: DELETE FROM t1;
! 350: SELECT (SELECT a FROM t1);
! 351: }
! 352: } {{}}
! 353: do_test subquery-4.2.1 {
! 354: execsql {
! 355: CREATE TABLE t3(a PRIMARY KEY);
! 356: INSERT INTO t3 VALUES(10);
! 357: }
! 358: execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
! 359: } {}
! 360: do_test subquery-4.2.2 {
! 361: execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
! 362: } {}
! 363:
! 364: #------------------------------------------------------------------
! 365: # The subquery-5.* tests make sure string literals in double-quotes
! 366: # are handled efficiently. Double-quote literals are first checked
! 367: # to see if they match any column names. If there is not column name
! 368: # match then those literals are used a string constants. When a
! 369: # double-quoted string appears, we want to make sure that the search
! 370: # for a matching column name did not cause an otherwise static subquery
! 371: # to become a dynamic (correlated) subquery.
! 372: #
! 373: do_test subquery-5.1 {
! 374: proc callcntproc {n} {
! 375: incr ::callcnt
! 376: return $n
! 377: }
! 378: set callcnt 0
! 379: db function callcnt callcntproc
! 380: execsql {
! 381: CREATE TABLE t4(x,y);
! 382: INSERT INTO t4 VALUES('one',1);
! 383: INSERT INTO t4 VALUES('two',2);
! 384: INSERT INTO t4 VALUES('three',3);
! 385: INSERT INTO t4 VALUES('four',4);
! 386: CREATE TABLE t5(a,b);
! 387: INSERT INTO t5 VALUES(1,11);
! 388: INSERT INTO t5 VALUES(2,22);
! 389: INSERT INTO t5 VALUES(3,33);
! 390: INSERT INTO t5 VALUES(4,44);
! 391: SELECT b FROM t5 WHERE a IN
! 392: (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
! 393: }
! 394: } {22}
! 395: do_test subquery-5.2 {
! 396: # This is the key test. The subquery should have only run once. If
! 397: # The double-quoted identifier "two" were causing the subquery to be
! 398: # processed as a correlated subquery, then it would have run 4 times.
! 399: set callcnt
! 400: } {1}
! 401:
! 402:
! 403: # Ticket #1380. Make sure correlated subqueries on an IN clause work
! 404: # correctly when the left-hand side of the IN operator is constant.
! 405: #
! 406: do_test subquery-6.1 {
! 407: set callcnt 0
! 408: execsql {
! 409: SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
! 410: }
! 411: } {one two three four}
! 412: do_test subquery-6.2 {
! 413: set callcnt
! 414: } {4}
! 415: do_test subquery-6.3 {
! 416: set callcnt 0
! 417: execsql {
! 418: SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
! 419: }
! 420: } {one two three four}
! 421: do_test subquery-6.4 {
! 422: set callcnt
! 423: } {1}
! 424:
! 425: if 0 { ############# disable until we get #2652 fixed
! 426: # Ticket #2652. Allow aggregate functions of outer queries inside
! 427: # a non-aggregate subquery.
! 428: #
! 429: do_test subquery-7.1 {
! 430: execsql {
! 431: CREATE TABLE t7(c7);
! 432: INSERT INTO t7 VALUES(1);
! 433: INSERT INTO t7 VALUES(2);
! 434: INSERT INTO t7 VALUES(3);
! 435: CREATE TABLE t8(c8);
! 436: INSERT INTO t8 VALUES(100);
! 437: INSERT INTO t8 VALUES(200);
! 438: INSERT INTO t8 VALUES(300);
! 439: CREATE TABLE t9(c9);
! 440: INSERT INTO t9 VALUES(10000);
! 441: INSERT INTO t9 VALUES(20000);
! 442: INSERT INTO t9 VALUES(30000);
! 443:
! 444: SELECT (SELECT c7+c8 FROM t7) FROM t8;
! 445: }
! 446: } {101 201 301}
! 447: do_test subquery-7.2 {
! 448: execsql {
! 449: SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
! 450: }
! 451: } {103 203 303}
! 452: do_test subquery-7.3 {
! 453: execsql {
! 454: SELECT (SELECT c7+max(c8) FROM t8) FROM t7
! 455: }
! 456: } {301}
! 457: do_test subquery-7.4 {
! 458: execsql {
! 459: SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
! 460: }
! 461: } {303}
! 462: do_test subquery-7.5 {
! 463: execsql {
! 464: SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
! 465: }
! 466: } {300}
! 467: do_test subquery-7.6 {
! 468: execsql {
! 469: SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
! 470: }
! 471: } {30101 30102 30103}
! 472: do_test subquery-7.7 {
! 473: execsql {
! 474: SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
! 475: }
! 476: } {30101 30102 30103}
! 477: do_test subquery-7.8 {
! 478: execsql {
! 479: SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
! 480: }
! 481: } {10103}
! 482: do_test subquery-7.9 {
! 483: execsql {
! 484: SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
! 485: }
! 486: } {10301 10302 10303}
! 487: do_test subquery-7.10 {
! 488: execsql {
! 489: SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
! 490: }
! 491: } {30101 30102 30103}
! 492: do_test subquery-7.11 {
! 493: execsql {
! 494: SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
! 495: }
! 496: } {30303}
! 497: } ;############# Disabled
! 498:
! 499: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to subquery.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