3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
20 CREATE TABLE x1(a, b, c);
25 INSERT INTO x1(a, b, c) VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
27 do_execsql_test 1.1.1 {
28 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
30 do_execsql_test 1.1.2 {
39 do_execsql_test 1.2.0 {
42 do_execsql_test 1.2.1 {
43 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 4, 4, 4;
45 } {1 1 1 2 2 2 3 3 3 4 4 4}
47 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 4
49 do_execsql_test 1.2.2 {
52 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5)
53 UNION ALL SELECT 6, 6, 6;
55 } {1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 6}
57 do_execsql_test 1.2.3 {
60 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
61 UNION ALL SELECT 6, 6, 6;
63 } {1 1 1 2 2 2 3 3 3 4 4 4 6 6 6}
65 do_execsql_test 1.2.4 {
67 INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 6, 6, 6;
79 do_execsql_test 1.2.5 {
82 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3),
83 (4, 4, $a), (5, 5, $b), (6, 6, $c)
86 do_execsql_test 1.2.6 {
97 #-------------------------------------------------------------------------
98 # SQLITE_LIMIT_COMPOUND_SELECT set to 0.
102 do_execsql_test 2.0 {
103 CREATE TABLE x1(a, b, c);
106 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 3
108 do_catchsql_test 2.1.1 {
109 INSERT INTO x1 VALUES
120 } {1 {all VALUES must have the same number of terms}}
122 do_catchsql_test 2.1.2 {
123 INSERT INTO x1 VALUES
134 } {1 {all VALUES must have the same number of terms}}
136 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0
138 do_execsql_test 2.2 {
139 INSERT INTO x1 VALUES
151 do_execsql_test 2.3 {
152 INSERT INTO x1 VALUES
168 #-------------------------------------------------------------------------
171 do_execsql_test 3.0 {
172 CREATE TABLE y1(x, y);
175 do_execsql_test 3.1.1 {
177 INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 5);
179 do_execsql_test 3.1.2 {
182 do_execsql_test 3.2.1 {
184 INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 6)
185 , (row_number() OVER (), 7)
187 do_execsql_test 3.1.2 {
191 #-------------------------------------------------------------------------
194 do_execsql_test 4.0 {
195 CREATE TABLE x1(a PRIMARY KEY, b) WITHOUT ROWID;
198 foreach {tn iLimit} {1 0 2 3} {
199 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT $iLimit
201 do_execsql_test 4.1.1 {
203 INSERT INTO x1 VALUES
205 (2, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d')) ))
207 do_execsql_test 4.1.2 {
211 do_execsql_test 4.2.1 {
213 INSERT INTO x1 VALUES
218 (5, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d')) ))
220 do_execsql_test 4.2.2 {
222 } {1 1 2 2 3 3 4 4 5 a}
224 do_execsql_test 4.3.1 {
226 INSERT INTO x1 VALUES
227 (1, (SELECT * FROM (VALUES('a'), ('b'), ('c'), ('d'), ('e')) ))
229 do_execsql_test 4.3.2 {
234 #------------------------------------------------------------------------
237 do_execsql_test 5.0 {
238 CREATE VIEW v1 AS VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
240 do_execsql_test 5.1 {
242 } {1 2 3 4 5 6 7 8 9}
244 #-------------------------------------------------------------------------
246 do_execsql_test 6.0 {
248 INSERT INTO t1 VALUES(1), (2);
251 do_execsql_test 6.1 {
252 SELECT ( VALUES( x ), ( x ) ) FROM t1;
255 #-------------------------------------------------------------------------
257 do_execsql_test 6.0 {
259 INSERT INTO t1 VALUES('x'), ('y');
262 do_execsql_test 6.1 {
263 SELECT * FROM t1, (VALUES(1), (2))