[sql] Remove SQLite 3.10.2 reference directory.

third_party/sqlite/sqlite-src-3100200/test/with2.test

-# 2014 January 11
-#
-# 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 file is testing the WITH clause.
-#
-
-set testdir [file dirname $argv0]
-source $testdir/tester.tcl
-set ::testprefix with2
-
-ifcapable {!cte} {
-  finish_test
-  return
-}
-
-do_execsql_test 1.0 {
-  CREATE TABLE t1(a);
-  INSERT INTO t1 VALUES(1);
-  INSERT INTO t1 VALUES(2);
-}
-
-do_execsql_test 1.1 {
-  WITH x1 AS (SELECT * FROM t1)
-  SELECT sum(a) FROM x1;
-} {3}
-
-do_execsql_test 1.2 {
-  WITH x1 AS (SELECT * FROM t1)
-  SELECT (SELECT sum(a) FROM x1);
-} {3}
-
-do_execsql_test 1.3 {
-  WITH x1 AS (SELECT * FROM t1)
-  SELECT (SELECT sum(a) FROM x1);
-} {3}
-
-do_execsql_test 1.4 {
-  CREATE TABLE t2(i);
-  INSERT INTO t2 VALUES(2);
-  INSERT INTO t2 VALUES(3);
-  INSERT INTO t2 VALUES(5);
-
-  WITH x1   AS (SELECT i FROM t2),
-       i(a) AS (
-         SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10
-       )
-  SELECT a FROM i WHERE a NOT IN x1
-} {1 4 6 7 8 9 10}
-
-do_execsql_test 1.5 {
-  WITH x1 AS (SELECT a FROM t1),
-       x2 AS (SELECT i FROM t2),
-       x3 AS (SELECT * FROM x1, x2 WHERE x1.a IN x2 AND x2.i IN x1)
-  SELECT * FROM x3 
-} {2 2}
-
-do_execsql_test 1.6 {
-  CREATE TABLE t3 AS SELECT 3 AS x;
-  CREATE TABLE t4 AS SELECT 4 AS x;
-
-  WITH x1 AS (SELECT * FROM t3),
-       x2 AS (
-         WITH t3 AS (SELECT * FROM t4)
-         SELECT * FROM x1
-       )
-  SELECT * FROM x2;
-} {3}
-
-do_execsql_test 1.7 {
-  WITH x2 AS (
-         WITH t3 AS (SELECT * FROM t4)
-         SELECT * FROM t3
-       )
-  SELECT * FROM x2;
-} {4}
-
-do_execsql_test 1.8 {
-  WITH x2 AS (
-         WITH t3 AS (SELECT * FROM t4)
-         SELECT * FROM main.t3
-       )
-  SELECT * FROM x2;
-} {3}
-
-do_execsql_test 1.9 {
-  WITH x1 AS (SELECT * FROM t1)
-  SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1);
-} {3 2}
-
-do_execsql_test 1.10 {
-  WITH x1 AS (SELECT * FROM t1)
-  SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1), a FROM x1;
-} {3 2 1 3 2 2}
-
-do_execsql_test 1.11 {
-  WITH 
-  i(x) AS ( 
-    WITH 
-    j(x) AS ( SELECT * FROM i ), 
-    i(x) AS ( SELECT * FROM t1 )
-    SELECT * FROM j
-  )
-  SELECT * FROM i;
-} {1 2}
-
-do_execsql_test 1.12 {
-  WITH r(i) AS (
-    VALUES('.')
-    UNION ALL
-    SELECT i || '.' FROM r, (
-      SELECT x FROM x INTERSECT SELECT y FROM y
-    ) WHERE length(i) < 10
-  ),
-  x(x) AS ( VALUES(1) UNION ALL VALUES(2) UNION ALL VALUES(3) ),
-  y(y) AS ( VALUES(2) UNION ALL VALUES(4) UNION ALL VALUES(6) )
-
-  SELECT * FROM r;
-} {. .. ... .... ..... ...... ....... ........ ......... ..........}
-
-do_execsql_test 1.13 {
-  WITH r(i) AS (
-    VALUES('.')
-    UNION ALL
-    SELECT i || '.' FROM r, ( SELECT x FROM x WHERE x=2 ) WHERE length(i) < 10
-  ),
-  x(x) AS ( VALUES(1) UNION ALL VALUES(2) UNION ALL VALUES(3) )
-
-  SELECT * FROM r ORDER BY length(i) DESC;
-} {.......... ......... ........ ....... ...... ..... .... ... .. .}
-
-do_execsql_test 1.14 {
-  WITH 
-  t4(x) AS ( 
-    VALUES(4)
-    UNION ALL 
-    SELECT x+1 FROM t4 WHERE x<10
-  )
-  SELECT * FROM t4;
-} {4 5 6 7 8 9 10}
-
-do_execsql_test 1.15 {
-  WITH 
-  t4(x) AS ( 
-    VALUES(4)
-    UNION ALL 
-    SELECT x+1 FROM main.t4 WHERE x<10
-  )
-  SELECT * FROM t4;
-} {4 5}
-
-do_catchsql_test 1.16 {
-  WITH 
-  t4(x) AS ( 
-    VALUES(4)
-    UNION ALL 
-    SELECT x+1 FROM t4, main.t4, t4 WHERE x<10
-  )
-  SELECT * FROM t4;
-} {1 {multiple references to recursive table: t4}}
-
-
-#---------------------------------------------------------------------------
-# Check that variables can be used in CTEs.
-#
-set ::min [expr 3]
-set ::max [expr 9]
-do_execsql_test 2.1 {
-  WITH i(x) AS (
-    VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max
-  )
-  SELECT * FROM i;
-} {3 4 5 6 7 8 9}
-
-do_execsql_test 2.2 {
-  WITH i(x) AS (
-    VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max
-  )
-  SELECT x FROM i JOIN i AS j USING (x);
-} {3 4 5 6 7 8 9}
-
-#---------------------------------------------------------------------------
-# Check that circular references are rejected.
-#
-do_catchsql_test 3.1 {
-  WITH i(x, y) AS ( VALUES(1, (SELECT x FROM i)) )
-  SELECT * FROM i;
-} {1 {circular reference: i}}
-
-do_catchsql_test 3.2 {
-  WITH 
-  i(x) AS ( SELECT * FROM j ),
-  j(x) AS ( SELECT * FROM k ),
-  k(x) AS ( SELECT * FROM i )
-  SELECT * FROM i;
-} {1 {circular reference: i}}
-
-do_catchsql_test 3.3 {
-  WITH 
-  i(x) AS ( SELECT * FROM (SELECT * FROM j) ),
-  j(x) AS ( SELECT * FROM (SELECT * FROM i) )
-  SELECT * FROM i;
-} {1 {circular reference: i}}
-
-do_catchsql_test 3.4 {
-  WITH 
-  i(x) AS ( SELECT * FROM (SELECT * FROM j) ),
-  j(x) AS ( SELECT * FROM (SELECT * FROM i) )
-  SELECT * FROM j;
-} {1 {circular reference: j}}
-
-do_catchsql_test 3.5 {
-  WITH 
-  i(x) AS ( 
-    WITH j(x) AS ( SELECT * FROM i )
-    SELECT * FROM j
-  )
-  SELECT * FROM i;
-} {1 {circular reference: i}}
-
-#---------------------------------------------------------------------------
-# Try empty and very long column lists.
-#
-do_catchsql_test 4.1 {
-  WITH x() AS ( SELECT 1,2,3 )
-  SELECT * FROM x;
-} {1 {near ")": syntax error}}
-
-proc genstmt {n} {
-  for {set i 1} {$i<=$n} {incr i} {
-    lappend cols "c$i"
-    lappend vals $i
-  }
-  return "
-    WITH x([join $cols ,]) AS (SELECT [join $vals ,])
-    SELECT (c$n == $n) FROM x
-  "
-}
-
-do_execsql_test  4.2 [genstmt 10] 1
-do_execsql_test  4.3 [genstmt 100] 1
-do_execsql_test  4.4 [genstmt 255] 1
-set nLimit [sqlite3_limit db SQLITE_LIMIT_COLUMN -1]
-do_execsql_test  4.5 [genstmt [expr $nLimit-1]] 1
-do_execsql_test  4.6 [genstmt $nLimit] 1
-do_catchsql_test 4.7 [genstmt [expr $nLimit+1]] \
-  {1 {too many columns in result set}}
-
-#---------------------------------------------------------------------------
-# Check that adding a WITH clause to an INSERT disables the xfer 
-# optimization.
-#
-proc do_xfer_test {tn bXfer sql {res {}}} {
-  set ::sqlite3_xferopt_count 0
-  uplevel [list do_test $tn [subst -nocommands {
-    set dres [db eval {$sql}]
-    list [set ::sqlite3_xferopt_count] [set dres]
-  }] [list $bXfer $res]]
-}
-
-do_execsql_test 5.1 {
-  DROP TABLE IF EXISTS t1;
-  DROP TABLE IF EXISTS t2;
-  CREATE TABLE t1(a, b);
-  CREATE TABLE t2(a, b);
-}
-
-do_xfer_test 5.2 1 { INSERT INTO t1 SELECT * FROM t2 }
-do_xfer_test 5.3 0 { INSERT INTO t1 SELECT a, b FROM t2 }
-do_xfer_test 5.4 0 { INSERT INTO t1 SELECT b, a FROM t2 }
-do_xfer_test 5.5 0 { 
-  WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM x 
-}
-do_xfer_test 5.6 0 { 
-  WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM t2 
-}
-do_xfer_test 5.7 0 { 
- INSERT INTO t1 WITH x AS ( SELECT * FROM t2 ) SELECT * FROM x
-}
-do_xfer_test 5.8 0 { 
- INSERT INTO t1 WITH x(a,b) AS ( SELECT * FROM t2 ) SELECT * FROM x
-}
-
-#---------------------------------------------------------------------------
-# Check that syntax (and other) errors in statements with WITH clauses
-# attached to them do not cause problems (e.g. memory leaks).
-#
-do_execsql_test 6.1 {
-  DROP TABLE IF EXISTS t1;
-  DROP TABLE IF EXISTS t2;
-  CREATE TABLE t1(a, b);
-  CREATE TABLE t2(a, b);
-}
-
-do_catchsql_test 6.2 {
-  WITH x AS (SELECT * FROM t1)
-  INSERT INTO t2 VALUES(1, 2,);
-} {1 {near ")": syntax error}}
-
-do_catchsql_test 6.3 {
-  WITH x AS (SELECT * FROM t1)
-  INSERT INTO t2 SELECT a, b, FROM t1;
-} {1 {near "FROM": syntax error}}
-
-do_catchsql_test 6.3 {
-  WITH x AS (SELECT * FROM t1)
-  INSERT INTO t2 SELECT a, b FROM abc;
-} {1 {no such table: abc}}
-
-do_catchsql_test 6.4 {
-  WITH x AS (SELECT * FROM t1)
-  INSERT INTO t2 SELECT a, b, FROM t1 a a a;
-} {1 {near "FROM": syntax error}}
-
-do_catchsql_test 6.5 {
-  WITH x AS (SELECT * FROM t1)
-  DELETE FROM t2 WHERE;
-} {1 {near ";": syntax error}}
-
-do_catchsql_test 6.6 { 
-  WITH x AS (SELECT * FROM t1) DELETE FROM t2 WHERE
-} {/1 {near .* syntax error}/}
-
-do_catchsql_test 6.7 { 
-  WITH x AS (SELECT * FROM t1) DELETE FROM t2 WHRE 1;
-} {/1 {near .* syntax error}/}
-
-do_catchsql_test 6.8 { 
-  WITH x AS (SELECT * FROM t1) UPDATE t2 SET a = 10, b = ;
-} {/1 {near .* syntax error}/}
-
-do_catchsql_test 6.9 { 
-  WITH x AS (SELECT * FROM t1) UPDATE t2 SET a = 10, b = 1 WHERE a===b;
-} {/1 {near .* syntax error}/}
-
-do_catchsql_test 6.10 {
-  WITH x(a,b) AS (
-    SELECT 1, 1
-    UNION ALL
-    SELECT a*b,a+b FROM x WHERE c=2
-  )
-  SELECT * FROM x
-} {1 {no such column: c}}
-
-#-------------------------------------------------------------------------
-# Recursive queries in IN(...) expressions.
-#
-do_execsql_test 7.1 {
-  CREATE TABLE t5(x INTEGER);
-  CREATE TABLE t6(y INTEGER);
-
-  WITH s(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM s WHERE x<49 )
-  INSERT INTO t5 
-  SELECT * FROM s;
-
-  INSERT INTO t6 
-  WITH s(x) AS ( VALUES(2) UNION ALL SELECT x+2 FROM s WHERE x<49 )
-  SELECT * FROM s;
-}
-
-do_execsql_test 7.2 {
-  SELECT * FROM t6 WHERE y IN (SELECT x FROM t5)
-} {14 28 42}
-
-do_execsql_test 7.3 {
-  WITH ss AS (SELECT x FROM t5)
-  SELECT * FROM t6 WHERE y IN (SELECT x FROM ss)
-} {14 28 42}
-
-do_execsql_test 7.4 {
-  WITH ss(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM ss WHERE x<49 )
-  SELECT * FROM t6 WHERE y IN (SELECT x FROM ss)
-} {14 28 42}
-
-do_execsql_test 7.5 {
-  SELECT * FROM t6 WHERE y IN (
-    WITH ss(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM ss WHERE x<49 )
-    SELECT x FROM ss
-  )
-} {14 28 42}
-
-#-------------------------------------------------------------------------
-# At one point the following was causing an assertion failure and a 
-# memory leak.
-#
-do_execsql_test 8.1 {
-  CREATE TABLE t7(y);
-  INSERT INTO t7 VALUES(NULL);
-  CREATE VIEW v AS SELECT * FROM t7 ORDER BY y;
-}
-
-do_execsql_test 8.2 {
-  WITH q(a) AS (
-    SELECT 1
-    UNION 
-    SELECT a+1 FROM q, v WHERE a<5
-  )
-  SELECT * FROM q;
-} {1 2 3 4 5}
-
-do_execsql_test 8.3 {
-  WITH q(a) AS (
-    SELECT 1
-    UNION ALL
-    SELECT a+1 FROM q, v WHERE a<5
-  )
-  SELECT * FROM q;
-} {1 2 3 4 5}
-
-
-finish_test