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

} 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