sl@0: # 2005 January 19
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #*************************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script is testing correlated subqueries
sl@0: #
sl@0: # $Id: subquery.test,v 1.16 2008/07/10 00:32:42 drh Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !subquery {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: do_test subquery-1.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     CREATE TABLE t1(a,b);
sl@0:     INSERT INTO t1 VALUES(1,2);
sl@0:     INSERT INTO t1 VALUES(3,4);
sl@0:     INSERT INTO t1 VALUES(5,6);
sl@0:     INSERT INTO t1 VALUES(7,8);
sl@0:     CREATE TABLE t2(x,y);
sl@0:     INSERT INTO t2 VALUES(1,1);
sl@0:     INSERT INTO t2 VALUES(3,9);
sl@0:     INSERT INTO t2 VALUES(5,25);
sl@0:     INSERT INTO t2 VALUES(7,49);
sl@0:     COMMIT;
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
sl@0:   }
sl@0: } {1 1 3 9 5 25}
sl@0: do_test subquery-1.2 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {1 3 3 13 5 31 7 57}
sl@0: 
sl@0: do_test subquery-1.3 {
sl@0:   execsql {
sl@0:     SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
sl@0:   }
sl@0: } {3}
sl@0: do_test subquery-1.4 {
sl@0:   execsql {
sl@0:     SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
sl@0:   }
sl@0: } {13 31 57}
sl@0: 
sl@0: # Simple tests to make sure correlated subqueries in WHERE clauses
sl@0: # are used by the query optimizer correctly.
sl@0: do_test subquery-1.5 {
sl@0:   execsql {
sl@0:     SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
sl@0:   }
sl@0: } {1 1 3 3 5 5 7 7}
sl@0: do_test subquery-1.6 {
sl@0:   execsql {
sl@0:     CREATE INDEX i1 ON t1(a);
sl@0:     SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
sl@0:   }
sl@0: } {1 1 3 3 5 5 7 7}
sl@0: do_test subquery-1.7 {
sl@0:   execsql {
sl@0:     SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
sl@0:   }
sl@0: } {1 1 3 3 5 5 7 7}
sl@0: 
sl@0: # Try an aggregate in both the subquery and the parent query.
sl@0: do_test subquery-1.8 {
sl@0:   execsql {
sl@0:     SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
sl@0:   }
sl@0: } {2}
sl@0: 
sl@0: # Test a correlated subquery disables the "only open the index" optimization.
sl@0: do_test subquery-1.9.1 {
sl@0:   execsql {
sl@0:     SELECT (y*2)>b FROM t1, t2 WHERE a=x;
sl@0:   }
sl@0: } {0 1 1 1}
sl@0: do_test subquery-1.9.2 {
sl@0:   execsql {
sl@0:     SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x); 
sl@0:   }
sl@0: } {3 5 7}
sl@0: 
sl@0: # Test that the flattening optimization works with subquery expressions.
sl@0: do_test subquery-1.10.1 {
sl@0:   execsql {
sl@0:     SELECT (SELECT a), b FROM t1;
sl@0:   }
sl@0: } {1 3 3 13 5 31 7 57}
sl@0: do_test subquery-1.10.2 {
sl@0:   execsql {
sl@0:     SELECT * FROM (SELECT (SELECT a), b FROM t1);
sl@0:   }
sl@0: } {1 3 3 13 5 31 7 57}
sl@0: do_test subquery-1.10.3 {
sl@0:   execsql {
sl@0:     SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
sl@0:   }
sl@0: } {16}
sl@0: do_test subquery-1.10.4 {
sl@0:   execsql {
sl@0:     CREATE TABLE t5 (val int, period text PRIMARY KEY);
sl@0:     INSERT INTO t5 VALUES(5, '2001-3');
sl@0:     INSERT INTO t5 VALUES(10, '2001-4');
sl@0:     INSERT INTO t5 VALUES(15, '2002-1');
sl@0:     INSERT INTO t5 VALUES(5, '2002-2');
sl@0:     INSERT INTO t5 VALUES(10, '2002-3');
sl@0:     INSERT INTO t5 VALUES(15, '2002-4');
sl@0:     INSERT INTO t5 VALUES(10, '2003-1');
sl@0:     INSERT INTO t5 VALUES(5, '2003-2');
sl@0:     INSERT INTO t5 VALUES(25, '2003-3');
sl@0:     INSERT INTO t5 VALUES(5, '2003-4');
sl@0: 
sl@0:     SELECT period, vsum
sl@0:     FROM (SELECT 
sl@0:       a.period,
sl@0:       (select sum(val) from t5 where period between a.period and '2002-4') vsum
sl@0:       FROM t5 a where a.period between '2002-1' and '2002-4')
sl@0:     WHERE vsum < 45 ;
sl@0:   }
sl@0: } {2002-2 30 2002-3 25 2002-4 15}
sl@0: do_test subquery-1.10.5 {
sl@0:   execsql {
sl@0:     SELECT period, vsum from
sl@0:       (select a.period,
sl@0:       (select sum(val) from t5 where period between a.period and '2002-4') vsum
sl@0:     FROM t5 a where a.period between '2002-1' and '2002-4') 
sl@0:     WHERE vsum < 45 ;
sl@0:   }
sl@0: } {2002-2 30 2002-3 25 2002-4 15}
sl@0: do_test subquery-1.10.6 {
sl@0:   execsql {
sl@0:     DROP TABLE t5;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: 
sl@0: 
sl@0: #------------------------------------------------------------------
sl@0: # The following test cases - subquery-2.* - are not logically
sl@0: # organized. They're here largely because they were failing during
sl@0: # one stage of development of sub-queries.
sl@0: #
sl@0: do_test subquery-2.1 {
sl@0:   execsql {
sl@0:     SELECT (SELECT 10);
sl@0:   }
sl@0: } {10}
sl@0: do_test subquery-2.2.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a PRIMARY KEY, b);
sl@0:     INSERT INTO t3 VALUES(1, 2);
sl@0:     INSERT INTO t3 VALUES(3, 1);
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.2.2 {
sl@0:   execsql {
sl@0:     SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
sl@0:   }
sl@0: } {1 2}
sl@0: do_test subquery-2.2.3 {
sl@0:   execsql {
sl@0:     DROP TABLE t3;
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.3.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a TEXT);
sl@0:     INSERT INTO t3 VALUES('10');
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.3.2 {
sl@0:   execsql {
sl@0:     SELECT a IN (10.0, 20) FROM t3;
sl@0:   }
sl@0: } {0}
sl@0: do_test subquery-2.3.3 {
sl@0:   execsql {
sl@0:     DROP TABLE t3;
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.4.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a TEXT);
sl@0:     INSERT INTO t3 VALUES('XX');
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.4.2 {
sl@0:   execsql {
sl@0:     SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
sl@0:   }
sl@0: } {1}
sl@0: do_test subquery-2.4.3 {
sl@0:   execsql {
sl@0:     DROP TABLE t3;
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.5.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a INTEGER);
sl@0:     INSERT INTO t3 VALUES(10);
sl@0: 
sl@0:     CREATE TABLE t4(x TEXT);
sl@0:     INSERT INTO t4 VALUES('10.0');
sl@0:   }
sl@0: } {}
sl@0: do_test subquery-2.5.2 {
sl@0:   # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
sl@0:   # has text affinity and the LHS has integer affinity.  The rule is
sl@0:   # that we try to convert both sides to an integer before doing the
sl@0:   # comparision.  Hence, the integer value 10 in t3 will compare equal
sl@0:   # to the string value '10.0' in t4 because the t4 value will be
sl@0:   # converted into an integer.
sl@0:   execsql {
sl@0:     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
sl@0:   }
sl@0: } {10.0}
sl@0: do_test subquery-2.5.3.1 {
sl@0:   # The t4i index cannot be used to resolve the "x IN (...)" constraint
sl@0:   # because the constraint has integer affinity but t4i has text affinity.
sl@0:   execsql {
sl@0:     CREATE INDEX t4i ON t4(x);
sl@0:     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
sl@0:   }
sl@0: } {10.0}
sl@0: do_test subquery-2.5.3.2 {
sl@0:   # Verify that the t4i index was not used in the previous query
sl@0:   set ::sqlite_query_plan
sl@0: } {t4 {}}
sl@0: do_test subquery-2.5.4 {
sl@0:   execsql {
sl@0:     DROP TABLE t3;
sl@0:     DROP TABLE t4;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: #------------------------------------------------------------------
sl@0: # The following test cases - subquery-3.* - test tickets that
sl@0: # were raised during development of correlated subqueries.
sl@0: #
sl@0: 
sl@0: # Ticket 1083
sl@0: ifcapable view {
sl@0:   do_test subquery-3.1 {
sl@0:     catchsql { DROP TABLE t1; }
sl@0:     catchsql { DROP TABLE t2; }
sl@0:     execsql {
sl@0:       CREATE TABLE t1(a,b);
sl@0:       INSERT INTO t1 VALUES(1,2);
sl@0:       CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
sl@0:       CREATE TABLE t2(p,q);
sl@0:       INSERT INTO t2 VALUES(2,9);
sl@0:       SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
sl@0:     }
sl@0:   } {2}
sl@0: } else {
sl@0:   catchsql { DROP TABLE t1; }
sl@0:   catchsql { DROP TABLE t2; }
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a,b);
sl@0:     INSERT INTO t1 VALUES(1,2);
sl@0:     CREATE TABLE t2(p,q);
sl@0:     INSERT INTO t2 VALUES(2,9);
sl@0:   }
sl@0: }
sl@0: 
sl@0: # Ticket 1084
sl@0: do_test subquery-3.2 {
sl@0:   catchsql {
sl@0:     CREATE TABLE t1(a,b);
sl@0:     INSERT INTO t1 VALUES(1,2);
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT (SELECT t1.a) FROM t1;
sl@0:   }
sl@0: } {1}
sl@0: 
sl@0: # Test Cases subquery-3.3.* test correlated subqueries where the
sl@0: # parent query is an aggregate query. Ticket #1105 is an example
sl@0: # of such a query.
sl@0: #
sl@0: do_test subquery-3.3.1 {
sl@0:   execsql {
sl@0:     SELECT a, (SELECT b) FROM t1 GROUP BY a;
sl@0:   }
sl@0: } {1 2}
sl@0: do_test subquery-3.3.2 {
sl@0:   catchsql {DROP TABLE t2}
sl@0:   execsql {
sl@0:     CREATE TABLE t2(c, d);
sl@0:     INSERT INTO t2 VALUES(1, 'one');
sl@0:     INSERT INTO t2 VALUES(2, 'two');
sl@0:     SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
sl@0:   }
sl@0: } {1 one}
sl@0: do_test subquery-3.3.3 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(2, 4);
sl@0:     SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
sl@0:   }
sl@0: } {2 two}
sl@0: do_test subquery-3.3.4 {
sl@0:   execsql {
sl@0:     SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
sl@0:   }
sl@0: } {1 one 2 two}
sl@0: do_test subquery-3.3.5 {
sl@0:   execsql {
sl@0:     SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
sl@0:   }
sl@0: } {1 1 2 1}
sl@0: 
sl@0: #------------------------------------------------------------------
sl@0: # These tests - subquery-4.* - use the TCL statement cache to try 
sl@0: # and expose bugs to do with re-using statements that have been 
sl@0: # passed to sqlite3_reset().
sl@0: #
sl@0: # One problem was that VDBE memory cells were not being initialised
sl@0: # to NULL on the second and subsequent executions.
sl@0: #
sl@0: do_test subquery-4.1.1 {
sl@0:   execsql {
sl@0:     SELECT (SELECT a FROM t1);
sl@0:   }
sl@0: } {1}
sl@0: do_test subquery-4.2 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     SELECT (SELECT a FROM t1);
sl@0:   }
sl@0: } {{}}
sl@0: do_test subquery-4.2.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a PRIMARY KEY);
sl@0:     INSERT INTO t3 VALUES(10);
sl@0:   }
sl@0:   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
sl@0: } {}
sl@0: do_test subquery-4.2.2 {
sl@0:   execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
sl@0: } {}
sl@0: 
sl@0: #------------------------------------------------------------------
sl@0: # The subquery-5.* tests make sure string literals in double-quotes
sl@0: # are handled efficiently.  Double-quote literals are first checked
sl@0: # to see if they match any column names.  If there is not column name
sl@0: # match then those literals are used a string constants.  When a
sl@0: # double-quoted string appears, we want to make sure that the search
sl@0: # for a matching column name did not cause an otherwise static subquery
sl@0: # to become a dynamic (correlated) subquery.
sl@0: #
sl@0: do_test subquery-5.1 {
sl@0:   proc callcntproc {n} {
sl@0:     incr ::callcnt
sl@0:     return $n
sl@0:   }
sl@0:   set callcnt 0
sl@0:   db function callcnt callcntproc
sl@0:   execsql {
sl@0:     CREATE TABLE t4(x,y);
sl@0:     INSERT INTO t4 VALUES('one',1);
sl@0:     INSERT INTO t4 VALUES('two',2);
sl@0:     INSERT INTO t4 VALUES('three',3);
sl@0:     INSERT INTO t4 VALUES('four',4);
sl@0:     CREATE TABLE t5(a,b);
sl@0:     INSERT INTO t5 VALUES(1,11);
sl@0:     INSERT INTO t5 VALUES(2,22);
sl@0:     INSERT INTO t5 VALUES(3,33);
sl@0:     INSERT INTO t5 VALUES(4,44);
sl@0:     SELECT b FROM t5 WHERE a IN 
sl@0:        (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
sl@0:   }
sl@0: } {22}
sl@0: do_test subquery-5.2 {
sl@0:   # This is the key test.  The subquery should have only run once.  If
sl@0:   # The double-quoted identifier "two" were causing the subquery to be
sl@0:   # processed as a correlated subquery, then it would have run 4 times.
sl@0:   set callcnt
sl@0: } {1}
sl@0: 
sl@0: 
sl@0: # Ticket #1380.  Make sure correlated subqueries on an IN clause work
sl@0: # correctly when the left-hand side of the IN operator is constant.
sl@0: #
sl@0: do_test subquery-6.1 {
sl@0:   set callcnt 0
sl@0:   execsql {
sl@0:     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
sl@0:   }
sl@0: } {one two three four}
sl@0: do_test subquery-6.2 {
sl@0:   set callcnt
sl@0: } {4}
sl@0: do_test subquery-6.3 {
sl@0:   set callcnt 0
sl@0:   execsql {
sl@0:     SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
sl@0:   }
sl@0: } {one two three four}
sl@0: do_test subquery-6.4 {
sl@0:   set callcnt
sl@0: } {1}
sl@0: 
sl@0: if 0 {   #############  disable until we get #2652 fixed
sl@0: # Ticket #2652.  Allow aggregate functions of outer queries inside
sl@0: # a non-aggregate subquery.
sl@0: #
sl@0: do_test subquery-7.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t7(c7);
sl@0:     INSERT INTO t7 VALUES(1);
sl@0:     INSERT INTO t7 VALUES(2);
sl@0:     INSERT INTO t7 VALUES(3);
sl@0:     CREATE TABLE t8(c8);
sl@0:     INSERT INTO t8 VALUES(100);
sl@0:     INSERT INTO t8 VALUES(200);
sl@0:     INSERT INTO t8 VALUES(300);
sl@0:     CREATE TABLE t9(c9);
sl@0:     INSERT INTO t9 VALUES(10000);
sl@0:     INSERT INTO t9 VALUES(20000);
sl@0:     INSERT INTO t9 VALUES(30000);
sl@0: 
sl@0:     SELECT (SELECT c7+c8 FROM t7) FROM t8;
sl@0:   }
sl@0: } {101 201 301}
sl@0: do_test subquery-7.2 {
sl@0:   execsql {
sl@0:     SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
sl@0:   }
sl@0: } {103 203 303}
sl@0: do_test subquery-7.3 {
sl@0:   execsql {
sl@0:     SELECT (SELECT c7+max(c8) FROM t8) FROM t7
sl@0:   }
sl@0: } {301}
sl@0: do_test subquery-7.4 {
sl@0:   execsql {
sl@0:     SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
sl@0:   }
sl@0: } {303}
sl@0: do_test subquery-7.5 {
sl@0:   execsql {
sl@0:     SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
sl@0:   }
sl@0: } {300}
sl@0: do_test subquery-7.6 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {30101 30102 30103}
sl@0: do_test subquery-7.7 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {30101 30102 30103}
sl@0: do_test subquery-7.8 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {10103}
sl@0: do_test subquery-7.9 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {10301 10302 10303}
sl@0: do_test subquery-7.10 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {30101 30102 30103}
sl@0: do_test subquery-7.11 {
sl@0:   execsql {
sl@0:     SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
sl@0:   }
sl@0: } {30303}
sl@0: }  ;############# Disabled
sl@0: 
sl@0: finish_test