summaryrefslogtreecommitdiffstats
path: root/test/tabfunc01.test
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--test/tabfunc01.test331
1 files changed, 331 insertions, 0 deletions
diff --git a/test/tabfunc01.test b/test/tabfunc01.test
new file mode 100644
index 0000000..0a7e3bc
--- /dev/null
+++ b/test/tabfunc01.test
@@ -0,0 +1,331 @@
+# 2015-08-19
+#
+# 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 tests for table-valued-functions implemented using
+# eponymous virtual tables.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix tabfunc01
+
+ifcapable !vtab {
+ finish_test
+ return
+}
+load_static_extension db series
+load_static_extension db carray
+load_static_extension db remember
+
+do_execsql_test tabfunc01-1.1 {
+ SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2;
+} {1 | 3 | 5 | 7 | 9 |}
+do_execsql_test tabfunc01-1.1b {
+ PRAGMA table_xinfo(generate_series);
+} {0 value {} 0 {} 0 0 1 start {} 0 {} 0 1 2 stop {} 0 {} 0 1 3 step {} 0 {} 0 1}
+do_execsql_test tabfunc01-1.2 {
+ SELECT *, '|' FROM generate_series(0) LIMIT 5;
+} {0 | 1 | 2 | 3 | 4 |}
+do_catchsql_test tabfunc01-1.2b {
+ SELECT *, '|' FROM generate_series LIMIT 5;
+} {1 {first argument to "generate_series()" missing or unusable}}
+do_catchsql_test tabfunc01-1.2c {
+ SELECT *, '|' FROM generate_series(value) LIMIT 5;
+} {1 {first argument to "generate_series()" missing or unusable}}
+do_catchsql_test tabfunc01-1.3 {
+ CREATE VIRTUAL TABLE t1 USING generate_series;
+} {1 {no such module: generate_series}}
+do_execsql_test tabfunc01-1.4 {
+ SELECT * FROM generate_series(1,9,2);
+} {1 3 5 7 9}
+do_execsql_test tabfunc01-1.5 {
+ SELECT * FROM generate_series(1,9);
+} {1 2 3 4 5 6 7 8 9}
+do_execsql_test tabfunc01-1.6 {
+ SELECT * FROM generate_series(1,10) WHERE step=3;
+} {1 4 7 10}
+do_catchsql_test tabfunc01-1.7 {
+ SELECT * FROM generate_series(1,9,2,11);
+} {1 {too many arguments on generate_series() - max 3}}
+
+do_execsql_test tabfunc01-1.8 {
+ SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
+} {30 25 20 15 10 5 0}
+do_execsql_test tabfunc01-1.9 {
+ SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC;
+} {1 30 2 25 3 20 4 15 5 10 6 5 7 0}
+do_execsql_test tabfunc01-1.10 {
+ SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
+} {7 30 6 25 5 20 4 15 3 10 2 5 1 0}
+
+do_execsql_test tabfunc01-1.20 {
+ CREATE VIEW v1(a,b) AS VALUES(1,2),(3,4);
+ SELECT * FROM v1;
+} {1 2 3 4}
+do_catchsql_test tabfunc01-1.21.1 {
+ SELECT * FROM v1(55);
+} {1 {'v1' is not a function}}
+do_catchsql_test tabfunc01-1.21.2 {
+ SELECT * FROM v1();
+} {1 {'v1' is not a function}}
+do_execsql_test tabfunc01-1.22 {
+ CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
+ SELECT * FROM v2;
+} {1 2 3 4 5}
+do_catchsql_test tabfunc01-1.23.1 {
+ SELECT * FROM v2(55);
+} {1 {'v2' is not a function}}
+do_catchsql_test tabfunc01-1.23.2 {
+ SELECT * FROM v2();
+} {1 {'v2' is not a function}}
+do_execsql_test tabfunc01-1.24 {
+ CREATE TABLE t0(x);
+ INSERT INTO t0(x) VALUES(123),(456),(789);
+ SELECT * FROM t0 ORDER BY x;
+} {123 456 789}
+do_catchsql_test tabfunc01-1.25 {
+ SELECT * FROM t0(55) ORDER BY x;
+} {1 {'t0' is not a function}}
+do_catchsql_test tabfunc01-1.26 {
+ WITH w0 AS (SELECT * FROM t0)
+ INSERT INTO t0(x) SELECT * FROM w0()
+} {1 {'w0' is not a function}}
+
+do_execsql_test tabfunc01-2.1 {
+ CREATE TABLE t1(x);
+ INSERT INTO t1(x) VALUES(2),(3);
+ SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
+} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
+do_execsql_test tabfunc01-2.2 {
+ SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x)
+ ORDER BY 1, 2;
+} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
+
+do_execsql_test tabfunc01-2.50 {
+ SELECT * FROM generate_series(0) LIMIT 5;
+} {0 1 2 3 4}
+
+do_execsql_test tabfunc01-3.1 {
+ SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
+} {1 2 3}
+
+do_eqp_test tabfunc01-3.10 {
+ SELECT value FROM generate_series(1,10) ORDER BY value;
+} {
+ QUERY PLAN
+ `--SCAN generate_series VIRTUAL TABLE INDEX 19:
+}
+do_eqp_test tabfunc01-3.11 {
+ SELECT value FROM generate_series(1,10) ORDER BY +value;
+} {
+ QUERY PLAN
+ |--SCAN generate_series VIRTUAL TABLE INDEX 3:
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+do_eqp_test tabfunc01-3.12 {
+ SELECT value FROM generate_series(1,10) ORDER BY value, stop;
+} {
+ QUERY PLAN
+ `--SCAN generate_series VIRTUAL TABLE INDEX 19:
+}
+do_eqp_test tabfunc01-3.13 {
+ SELECT value FROM generate_series(1,10) ORDER BY stop, value;
+} {
+ QUERY PLAN
+ |--SCAN generate_series VIRTUAL TABLE INDEX 3:
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+
+
+do_eqp_test tabfunc01-3.20 {
+ WITH t1(a) AS (
+ SELECT value FROM generate_series(0,10,2)
+ UNION ALL
+ SELECT value FROM generate_series(9,18,3)
+ )
+ SELECT * FROM t1 ORDER BY a;
+} {
+ QUERY PLAN
+ `--MERGE (UNION ALL)
+ |--LEFT
+ | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
+ `--RIGHT
+ `--SCAN generate_series VIRTUAL TABLE INDEX 23:
+}
+
+
+# Eponymous virtual table exists in all schemas.
+#
+do_execsql_test tabfunc01-4.1 {
+ SELECT * FROM main.generate_series(1,4)
+} {1 2 3 4}
+do_execsql_test tabfunc01-4.2 {
+ SELECT * FROM temp.generate_series(1,4)
+} {1 2 3 4}
+do_execsql_test tabfunc01-4.3 {
+ ATTACH ':memory:' AS aux1;
+ CREATE TABLE aux1.t1(a,b,c);
+ SELECT * FROM aux1.generate_series(1,4)
+} {1 2 3 4}
+
+# 2018-12-03: Fix bug reported by by private email.
+do_execsql_test tabfunc01-4.4 {
+ SELECT * FROM (generate_series(1,5,2)) AS x LIMIT 10;
+} {1 3 5}
+
+# The next series of tests is verifying that virtual table are able
+# to optimize the IN operator, even on terms that are not marked "omit".
+# When the generate_series virtual table is compiled for the testfixture,
+# the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which
+# causes the xBestIndex method of generate_series to leave the
+# sqlite3_index_constraint_usage.omit flag set to 0, which should cause
+# the SQLite core to verify the start=, stop=, and step= constraints on
+# each step of output. At one point, the IN operator could not be used
+# by virtual tables unless omit was set.
+#
+do_execsql_test tabfunc01-500 {
+ SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
+ ORDER BY +1;
+} {1 7 11 17}
+
+# Table-valued functions on the RHS of an IN operator
+#
+do_execsql_test tabfunc01-600 {
+ CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
+ WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
+ INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
+ SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
+} {(002) (012) (022) (032) (042) (052)}
+
+
+do_test tabfunc01-700 {
+ set PTR1 [intarray_addr 5 7 13 17 23]
+ db eval {
+ SELECT b FROM t600, carray(inttoptr($PTR1),5) WHERE a=value;
+ }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-701 {
+ db eval {
+ SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int32');
+ }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-702 {
+ db eval {
+ SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),4,'int32');
+ }
+} {(005) (007) (013) (017)}
+do_catchsql_test tabfunc01-710 {
+ SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int33');
+} {1 {unknown datatype: 'int33'}}
+
+do_test tabfunc01-720 {
+ set PTR2 [int64array_addr 5 7 13 17 23]
+ db eval {
+ SELECT b FROM t600, carray(inttoptr($PTR2),5,'int64') WHERE a=value;
+ }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-721 {
+ db eval {
+ SELECT remember(123,inttoptr($PTR2));
+ SELECT value FROM carray(inttoptr($PTR2),5,'int64');
+ }
+} {123 123 7 13 17 23}
+do_test tabfunc01-722 {
+ set PTR3 [expr {$PTR2+16}]
+ db eval {
+ SELECT remember(987,inttoptr($PTR3));
+ SELECT value FROM carray(inttoptr($PTR2),5,'int64');
+ }
+} {987 123 7 987 17 23}
+
+do_test tabfunc01-730 {
+ set PTR4 [doublearray_addr 5.0 7.0 13.0 17.0 23.0]
+ db eval {
+ SELECT b FROM t600, carray(inttoptr($PTR4),5,'double') WHERE a=value;
+ }
+} {(005) (007) (013) (017) (023)}
+
+do_test tabfunc01-740 {
+ set PTR5 [textarray_addr x5 x7 x13 x17 x23]
+ db eval {
+ SELECT b FROM t600, carray(inttoptr($PTR5),5,'char*')
+ WHERE a=trim(value,'x');
+ }
+} {(005) (007) (013) (017) (023)}
+
+do_test tabfunc01-750 {
+ db eval {
+ SELECT aa.value, bb.value, '|'
+ FROM carray(inttoptr($PTR4),5,'double') AS aa
+ JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
+ }
+} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
+
+# ticket https://www.sqlite.org/src/info/2ae0c599b735d59e
+do_test tabfunc01-751 {
+ db eval {
+ SELECT aa.value, bb.value, '|'
+ FROM carray(inttoptr($PTR4),5,'double') AS aa
+ LEFT JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
+ }
+} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
+
+ifcapable altertable {
+ do_test tabfunc01-800 {
+ catchsql {
+ ALTER TABLE generate_series ADD COLUMN col2;
+ }
+ } {1 {virtual tables may not be altered}}
+ do_test tabfunc01-810 {
+ catchsql {
+ ALTER TABLE generate_series RENAME TO flubber;
+ }
+ } {1 {table generate_series may not be altered}}
+ do_test tabfunc01-820 {
+ catchsql {
+ ALTER TABLE generate_series RENAME start TO flubber;
+ }
+ } {1 {table generate_series may not be altered}}
+ do_test tabfunc01-830 {
+ catchsql {
+ ALTER TABLE generate_series DROP COLUMN start;
+ }
+ } {1 {table generate_series may not be altered}}
+ do_test tabfunc01-900 {
+ catchsql {
+ ALTER TABLE pragma_compile_options ADD COLUMN col2;
+ }
+ } {1 {virtual tables may not be altered}}
+ do_test tabfunc01-910 {
+ catchsql {
+ ALTER TABLE pragma_compile_options RENAME TO flubber;
+ }
+ } {1 {table pragma_compile_options may not be altered}}
+ do_test tabfunc01-920 {
+ catchsql {
+ ALTER TABLE pragma_compile_options RENAME start TO flubber;
+ }
+ } {1 {table pragma_compile_options may not be altered}}
+ do_test tabfunc01-930 {
+ catchsql {
+ ALTER TABLE pragma_compile_options DROP COLUMN start;
+ }
+ } {1 {table pragma_compile_options may not be altered}}
+}
+
+
+# Free up memory allocations
+intarray_addr
+int64array_addr
+doublearray_addr
+textarray_addr
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-25 18:14:52 +0000