test/with3.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 and in particular the
# LEVEL pseudo-column
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix with3

ifcapable {!cte} {
  finish_test
  return
}

do_execsql_test 1.0 {
  WITH RECURSIVE
    cnt(x) AS (VALUES(10) UNION SELECT x+1 FROM cnt WHERE level<10)
  SELECT * FROM cnt;
} {10 11 12 13 14 15 16 17 18 19}
do_execsql_test 1.1 {
  WITH RECURSIVE
    cnt(x,y) AS (VALUES(10,0) UNION SELECT x+1,level FROM cnt WHERE level<6)
  SELECT x, y, '|' FROM cnt;
} {10 0 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |}
do_execsql_test 1.2 {
  WITH RECURSIVE
    cnt(x,level) AS (
       VALUES(10,99)
       UNION
       SELECT x+1, level FROM cnt WHERE level<6
  )
  SELECT x, level, '|' FROM cnt;
} {10 99 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |}
do_execsql_test 1.3 {
  WITH RECURSIVE
    cnt(x,level) AS (
       VALUES(10,99)
       UNION
       SELECT x+1, cnt.level FROM cnt WHERE level<6
  )
  SELECT x, level, '|' FROM cnt;
} {10 99 | 11 99 | 12 99 | 13 99 | 14 99 | 15 99 |}
do_execsql_test 1.4 {
  WITH RECURSIVE
    cnt(x,level) AS (
       VALUES(10,0)
       UNION
       SELECT x+1, cnt.level+level FROM cnt WHERE level<6
  )
  SELECT x, level, '|' FROM cnt;
} {10 0 | 11 1 | 12 3 | 13 6 | 14 10 | 15 15 |}
do_execsql_test 1.5 {
  CREATE TABLE t1(level);
  WITH RECURSIVE
    cnt(x) AS (VALUES(10) UNION SELECT x*10 FROM cnt WHERE level<4)
  INSERT INTO t1 SELECT x FROM cnt;
  SELECT * FROM t1;
} {10 100 1000 10000}
do_execsql_test 1.6 {
  WITH RECURSIVE
    cnt(x, level) AS (
      VALUES(1,1)
      UNION
      SELECT x+1, level*t1.level FROM cnt, t1 WHERE level<3
    )
  SELECT x, level FROM cnt ORDER BY x, level;
} {1 1 2 10 2 100 2 1000 2 10000 3 20 3 200 3 2000 3 20000}

do_execsql_test 1.11 {
  CREATE TEMP TABLE powersoftwo(a,b);
  WITH RECURSIVE
    tmp(a,b) AS (VALUES(0,1) UNION SELECT a+1, b*2 FROM tmp WHERE level<32)
  INSERT INTO powersoftwo SELECT a, b FROM tmp;
  WITH RECURSIVE
    cnt(x,y) AS (
      VALUES(0,0) UNION
      SELECT x+1, (x+1)*(SELECT b FROM powersoftwo WHERE a=level) FROM cnt
       WHERE level<5
    )
  SELECT * FROM cnt;
} {0 0 1 2 2 8 3 24 4 64}

do_catchsql_test 2.1 {
  WITH RECURSIVE
    cnt(x) AS (VALUES(1) UNION SELECT x+1 FROM cnt WHERE level<10)
  SELECT x, level FROM cnt;
} {1 {no such column: level}}
do_catchsql_test 2.2 {
  WITH RECURSIVE
    cnt(x) AS (VALUES(level) UNION SELECT x+1 FROM cnt WHERE level<10)
  SELECT x FROM cnt;
} {1 {no such column: level}}



finish_test