# 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 with1

do_execsql_test 1.0 {
  CREATE TABLE t1(x INTEGER, y INTEGER);
  WITH x(a) AS ( SELECT * FROM t1) SELECT 10
} {10}

do_execsql_test 1.1 {
  SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
} {10}

do_execsql_test 1.2 {
  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
} {}

do_execsql_test 1.3 {
  WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
} {}

do_execsql_test 1.4 {
  WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
} {}
 
#--------------------------------------------------------------------------

do_execsql_test 2.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  WITH tmp AS ( SELECT * FROM t1 ) SELECT x FROM tmp;
} {1 2}

do_execsql_test 2.2 {
  WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
} {1 2}

do_execsql_test 2.3 {
  SELECT * FROM (
    WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
  );
} {1 2}

do_execsql_test 2.4 {
  WITH tmp1(a) AS ( SELECT * FROM t1 ),
       tmp2(x) AS ( SELECT * FROM tmp1)
  SELECT * FROM tmp2;
} {1 2}

do_execsql_test 2.5 {
  WITH tmp2(x) AS ( SELECT * FROM tmp1),
       tmp1(a) AS ( SELECT * FROM t1 )
  SELECT * FROM tmp2;
} {1 2}

#-------------------------------------------------------------------------
do_catchsql_test 3.1 {
  WITH tmp2(x) AS ( SELECT * FROM tmp1),
       tmp1(a) AS ( SELECT * FROM tmp2 )
  SELECT * FROM tmp1;
} {1 {illegal recursive defininition in cte: tmp1}}

do_catchsql_test 3.2 {
  CREATE TABLE t2(x INTEGER);
  WITH tmp(a) AS (SELECT * FROM t1),
       tmp(a) AS (SELECT * FROM t1)
  SELECT * FROM tmp;
} {1 {duplicate cte name: tmp}}

do_execsql_test 3.3 {
  CREATE TABLE t3(x);
  CREATE TABLE t4(x);

  INSERT INTO t3 VALUES('T3');
  INSERT INTO t4 VALUES('T4');

  WITH t3(a) AS (SELECT * FROM t4)
  SELECT * FROM t3;
} {T4}

do_execsql_test 3.4 {
  WITH tmp  AS ( SELECT * FROM t3 ),
       tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
  SELECT * FROM tmp2;
} {T4}

do_execsql_test 3.5 {
  WITH tmp  AS ( SELECT * FROM t3 ),
       tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
  SELECT * FROM tmp2;
} {T3}

do_catchsql_test 3.6 {
  WITH tmp AS ( SELECT * FROM t3 ),
  SELECT * FROM tmp;
} {1 {near "SELECT": syntax error}}

#-------------------------------------------------------------------------
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
  INSERT INTO t1 VALUES(4);

  WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
  DELETE FROM t1 WHERE x IN dset;
  SELECT * FROM t1;
} {1 3}

do_execsql_test 4.2 {
  WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
  INSERT INTO t1 SELECT * FROM iset;
  SELECT * FROM t1;
} {1 3 2 4}

do_execsql_test 4.3 {
  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
  SELECT * FROM t1;
} {1 3 8 9}

#-------------------------------------------------------------------------
#
do_execsql_test 5.1 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
  SELECT x FROM i LIMIT 10;
} {1 2 3 4 5 6 7 8 9 10}

do_catchsql_test 5.2 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
  SELECT x FROM i LIMIT 10;
} {1 {ORDER BY in a recursive query is not allowed}}

do_catchsql_test 5.3 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 10 )
  SELECT x FROM i LIMIT 10;
} {1 {LIMIT in a recursive query is not allowed}}

do_execsql_test 5.4 {
  WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
  SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}

do_execsql_test 5.5 {
  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
  SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}

finish_test



