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.16 2008/07/10 00:32:42 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 } else {

   273   catchsql { DROP TABLE t1; }

   274   catchsql { DROP TABLE t2; }

   275   execsql {

   276     CREATE TABLE t1(a,b);

   277     INSERT INTO t1 VALUES(1,2);

   278     CREATE TABLE t2(p,q);

   279     INSERT INTO t2 VALUES(2,9);

   280   }

   281 }

   282 

   283 # Ticket 1084

   284 do_test subquery-3.2 {

   285   catchsql {

   286     CREATE TABLE t1(a,b);

   287     INSERT INTO t1 VALUES(1,2);

   288   }

   289   execsql {

   290     SELECT (SELECT t1.a) FROM t1;

   291   }

   292 } {1}

   293 

   294 # Test Cases subquery-3.3.* test correlated subqueries where the

   295 # parent query is an aggregate query. Ticket #1105 is an example

   296 # of such a query.

   297 #

   298 do_test subquery-3.3.1 {

   299   execsql {

   300     SELECT a, (SELECT b) FROM t1 GROUP BY a;

   301   }

   302 } {1 2}

   303 do_test subquery-3.3.2 {

   304   catchsql {DROP TABLE t2}

   305   execsql {

   306     CREATE TABLE t2(c, d);

   307     INSERT INTO t2 VALUES(1, 'one');

   308     INSERT INTO t2 VALUES(2, 'two');

   309     SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;

   310   }

   311 } {1 one}

   312 do_test subquery-3.3.3 {

   313   execsql {

   314     INSERT INTO t1 VALUES(2, 4);

   315     SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;

   316   }

   317 } {2 two}

   318 do_test subquery-3.3.4 {

   319   execsql {

   320     SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;

   321   }

   322 } {1 one 2 two}

   323 do_test subquery-3.3.5 {

   324   execsql {

   325     SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;

   326   }

   327 } {1 1 2 1}

   328 

   329 #------------------------------------------------------------------

   330 # These tests - subquery-4.* - use the TCL statement cache to try 

   331 # and expose bugs to do with re-using statements that have been 

   332 # passed to sqlite3_reset().

   333 #

   334 # One problem was that VDBE memory cells were not being initialised

   335 # to NULL on the second and subsequent executions.

   336 #

   337 do_test subquery-4.1.1 {

   338   execsql {

   339     SELECT (SELECT a FROM t1);

   340   }

   341 } {1}

   342 do_test subquery-4.2 {

   343   execsql {

   344     DELETE FROM t1;

   345     SELECT (SELECT a FROM t1);

   346   }

   347 } {{}}

   348 do_test subquery-4.2.1 {

   349   execsql {

   350     CREATE TABLE t3(a PRIMARY KEY);

   351     INSERT INTO t3 VALUES(10);

   352   }

   353   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}

   354 } {}

   355 do_test subquery-4.2.2 {

   356   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}

   357 } {}

   358 

   359 #------------------------------------------------------------------

   360 # The subquery-5.* tests make sure string literals in double-quotes

   361 # are handled efficiently.  Double-quote literals are first checked

   362 # to see if they match any column names.  If there is not column name

   363 # match then those literals are used a string constants.  When a

   364 # double-quoted string appears, we want to make sure that the search

   365 # for a matching column name did not cause an otherwise static subquery

   366 # to become a dynamic (correlated) subquery.

   367 #

   368 do_test subquery-5.1 {

   369   proc callcntproc {n} {

   370     incr ::callcnt

   371     return $n

   372   }

   373   set callcnt 0

   374   db function callcnt callcntproc

   375   execsql {

   376     CREATE TABLE t4(x,y);

   377     INSERT INTO t4 VALUES('one',1);

   378     INSERT INTO t4 VALUES('two',2);

   379     INSERT INTO t4 VALUES('three',3);

   380     INSERT INTO t4 VALUES('four',4);

   381     CREATE TABLE t5(a,b);

   382     INSERT INTO t5 VALUES(1,11);

   383     INSERT INTO t5 VALUES(2,22);

   384     INSERT INTO t5 VALUES(3,33);

   385     INSERT INTO t5 VALUES(4,44);

   386     SELECT b FROM t5 WHERE a IN 

   387        (SELECT callcnt(y)+0 FROM t4 WHERE x="two")

   388   }

   389 } {22}

   390 do_test subquery-5.2 {

   391   # This is the key test.  The subquery should have only run once.  If

   392   # The double-quoted identifier "two" were causing the subquery to be

   393   # processed as a correlated subquery, then it would have run 4 times.

   394   set callcnt

   395 } {1}

   396 

   397 

   398 # Ticket #1380.  Make sure correlated subqueries on an IN clause work

   399 # correctly when the left-hand side of the IN operator is constant.

   400 #

   401 do_test subquery-6.1 {

   402   set callcnt 0

   403   execsql {

   404     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)

   405   }

   406 } {one two three four}

   407 do_test subquery-6.2 {

   408   set callcnt

   409 } {4}

   410 do_test subquery-6.3 {

   411   set callcnt 0

   412   execsql {

   413     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)

   414   }

   415 } {one two three four}

   416 do_test subquery-6.4 {

   417   set callcnt

   418 } {1}

   419 

   420 if 0 {   #############  disable until we get #2652 fixed

   421 # Ticket #2652.  Allow aggregate functions of outer queries inside

   422 # a non-aggregate subquery.

   423 #

   424 do_test subquery-7.1 {

   425   execsql {

   426     CREATE TABLE t7(c7);

   427     INSERT INTO t7 VALUES(1);

   428     INSERT INTO t7 VALUES(2);

   429     INSERT INTO t7 VALUES(3);

   430     CREATE TABLE t8(c8);

   431     INSERT INTO t8 VALUES(100);

   432     INSERT INTO t8 VALUES(200);

   433     INSERT INTO t8 VALUES(300);

   434     CREATE TABLE t9(c9);

   435     INSERT INTO t9 VALUES(10000);

   436     INSERT INTO t9 VALUES(20000);

   437     INSERT INTO t9 VALUES(30000);

   438 

   439     SELECT (SELECT c7+c8 FROM t7) FROM t8;

   440   }

   441 } {101 201 301}

   442 do_test subquery-7.2 {

   443   execsql {

   444     SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;

   445   }

   446 } {103 203 303}

   447 do_test subquery-7.3 {

   448   execsql {

   449     SELECT (SELECT c7+max(c8) FROM t8) FROM t7

   450   }

   451 } {301}

   452 do_test subquery-7.4 {

   453   execsql {

   454     SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7

   455   }

   456 } {303}

   457 do_test subquery-7.5 {

   458   execsql {

   459     SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7

   460   }

   461 } {300}

   462 do_test subquery-7.6 {

   463   execsql {

   464     SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7

   465   }

   466 } {30101 30102 30103}

   467 do_test subquery-7.7 {

   468   execsql {

   469     SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7

   470   }

   471 } {30101 30102 30103}

   472 do_test subquery-7.8 {

   473   execsql {

   474     SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7

   475   }

   476 } {10103}

   477 do_test subquery-7.9 {

   478   execsql {

   479     SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7

   480   }

   481 } {10301 10302 10303}

   482 do_test subquery-7.10 {

   483   execsql {

   484     SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7

   485   }

   486 } {30101 30102 30103}

   487 do_test subquery-7.11 {

   488   execsql {

   489     SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7

   490   }

   491 } {30303}

   492 }  ;############# Disabled

   493 

   494 finish_test