mirror of
https://github.com/sqlite/sqlite.git
synced 2026-10-25 09:59:22 +03:00
ce365d6b7e
a common table expression. LEVEL has the value of 1 on the first iteration and successively larger integer values of subsequent iterations. It cannot have a table qualifier. Actual columns named "level" can still be accessed by including the table name qualifier. FossilOrigin-Name: cc1cb3217800ff666a00bf4f544a5a477589bc1b
107 lines
2.9 KiB
Plaintext
107 lines
2.9 KiB
Plaintext
# 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
|