src/third_party/sqlite/src/test/subquery.test

   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