1# 2015-08-19 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 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. 9# 10#*********************************************************************** 11# 12# This file implements tests for table-valued-functions implemented using 13# eponymous virtual tables. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix tabfunc01 19 20ifcapable !vtab { 21 finish_test 22 return 23} 24load_static_extension db series 25 26do_execsql_test tabfunc01-1.1 { 27 SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2; 28} {1 | 3 | 5 | 7 | 9 |} 29do_execsql_test tabfunc01-1.2 { 30 SELECT *, '|' FROM generate_series LIMIT 5; 31} {0 | 1 | 2 | 3 | 4 |} 32do_catchsql_test tabfunc01-1.3 { 33 CREATE VIRTUAL TABLE t1 USING generate_series; 34} {1 {no such module: generate_series}} 35do_execsql_test tabfunc01-1.4 { 36 SELECT * FROM generate_series(1,9,2); 37} {1 3 5 7 9} 38do_execsql_test tabfunc01-1.5 { 39 SELECT * FROM generate_series(1,9); 40} {1 2 3 4 5 6 7 8 9} 41do_execsql_test tabfunc01-1.6 { 42 SELECT * FROM generate_series(1,10) WHERE step=3; 43} {1 4 7 10} 44do_catchsql_test tabfunc01-1.7 { 45 SELECT * FROM generate_series(1,9,2,11); 46} {1 {too many arguments on generate_series() - max 3}} 47 48do_execsql_test tabfunc01-1.8 { 49 SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC; 50} {30 25 20 15 10 5 0} 51do_execsql_test tabfunc01-1.9 { 52 SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC; 53} {1 30 2 25 3 20 4 15 5 10 6 5 7 0} 54do_execsql_test tabfunc01-1.10 { 55 SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC; 56} {7 30 6 25 5 20 4 15 3 10 2 5 1 0} 57 58do_execsql_test tabfunc01-1.20 { 59 CREATE VIEW v1(a,b) AS VALUES(1,2),(3,4); 60 SELECT * FROM v1; 61} {1 2 3 4} 62do_catchsql_test tabfunc01-1.21.1 { 63 SELECT * FROM v1(55); 64} {1 {'v1' is not a function}} 65do_catchsql_test tabfunc01-1.21.2 { 66 SELECT * FROM v1(); 67} {1 {'v1' is not a function}} 68do_execsql_test tabfunc01-1.22 { 69 CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5); 70 SELECT * FROM v2; 71} {1 2 3 4 5} 72do_catchsql_test tabfunc01-1.23.1 { 73 SELECT * FROM v2(55); 74} {1 {'v2' is not a function}} 75do_catchsql_test tabfunc01-1.23.2 { 76 SELECT * FROM v2(); 77} {1 {'v2' is not a function}} 78do_execsql_test tabfunc01-1.24 { 79 CREATE TABLE t0(x); 80 INSERT INTO t0(x) VALUES(123),(456),(789); 81 SELECT * FROM t0 ORDER BY x; 82} {123 456 789} 83do_catchsql_test tabfunc01-1.25 { 84 SELECT * FROM t0(55) ORDER BY x; 85} {1 {'t0' is not a function}} 86do_catchsql_test tabfunc01-1.26 { 87 WITH w0 AS (SELECT * FROM t0) 88 INSERT INTO t0(x) SELECT * FROM w0() 89} {1 {'w0' is not a function}} 90 91do_execsql_test tabfunc01-2.1 { 92 CREATE TABLE t1(x); 93 INSERT INTO t1(x) VALUES(2),(3); 94 SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2 95} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |} 96do_execsql_test tabfunc01-2.2 { 97 SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x) 98 ORDER BY 1, 2; 99} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |} 100 101do_execsql_test tabfunc01-2.50 { 102 SELECT * FROM generate_series() LIMIT 5; 103} {0 1 2 3 4} 104 105do_execsql_test tabfunc01-3.1 { 106 SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1; 107} {1 2 3} 108 109# Eponymous virtual table exists in the "main" schema only 110# 111do_execsql_test tabfunc01-4.1 { 112 SELECT * FROM main.generate_series(1,4) 113} {1 2 3 4} 114do_catchsql_test tabfunc01-4.2 { 115 SELECT * FROM temp.generate_series(1,4) 116} {1 {no such table: temp.generate_series}} 117do_catchsql_test tabfunc01-4.3 { 118 ATTACH ':memory:' AS aux1; 119 CREATE TABLE aux1.t1(a,b,c); 120 SELECT * FROM aux1.generate_series(1,4) 121} {1 {no such table: aux1.generate_series}} 122 123finish_test 124