Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/subquery.test

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