test/tabfunc01.test

   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
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix tabfunc01
  19
  20 ifcapable !vtab {
  21   finish_test
  22   return
  23 }
  24 load_static_extension db series
  25 load_static_extension db carray
  26 load_static_extension db remember
  27
  28 do_execsql_test tabfunc01-1.1 {
  29   SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2;
  30 } {1 | 3 | 5 | 7 | 9 |}
  31 do_execsql_test tabfunc01-1.1b {
  32   PRAGMA table_xinfo(generate_series);
  33 } {0 value {} 0 {} 0 0 1 start {} 0 {} 0 1 2 stop {} 0 {} 0 1 3 step {} 0 {} 0 1}
  34 do_execsql_test tabfunc01-1.2 {
  35   SELECT *, '|' FROM generate_series LIMIT 5;
  36 } {0 | 1 | 2 | 3 | 4 |}
  37 do_catchsql_test tabfunc01-1.3 {
  38   CREATE VIRTUAL TABLE t1 USING generate_series;
  39 } {1 {no such module: generate_series}}
  40 do_execsql_test tabfunc01-1.4 {
  41   SELECT * FROM generate_series(1,9,2);
  42 } {1 3 5 7 9}
  43 do_execsql_test tabfunc01-1.5 {
  44   SELECT * FROM generate_series(1,9);
  45 } {1 2 3 4 5 6 7 8 9}
  46 do_execsql_test tabfunc01-1.6 {
  47   SELECT * FROM generate_series(1,10) WHERE step=3;
  48 } {1 4 7 10}
  49 do_catchsql_test tabfunc01-1.7 {
  50   SELECT * FROM generate_series(1,9,2,11);
  51 } {1 {too many arguments on generate_series() - max 3}}
  52
  53 do_execsql_test tabfunc01-1.8 {
  54   SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
  55 } {30 25 20 15 10 5 0}
  56 do_execsql_test tabfunc01-1.9 {
  57   SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC;
  58 } {1 30 2 25 3 20 4 15 5 10 6 5 7 0}
  59 do_execsql_test tabfunc01-1.10 {
  60   SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
  61 } {7 30 6 25 5 20 4 15 3 10 2 5 1 0}
  62
  63 do_execsql_test tabfunc01-1.20 {
  64   CREATE VIEW v1(a,b) AS VALUES(1,2),(3,4);
  65   SELECT * FROM v1;
  66 } {1 2 3 4}
  67 do_catchsql_test tabfunc01-1.21.1 {
  68   SELECT * FROM v1(55);
  69 } {1 {'v1' is not a function}}
  70 do_catchsql_test tabfunc01-1.21.2 {
  71   SELECT * FROM v1();
  72 } {1 {'v1' is not a function}}
  73 do_execsql_test tabfunc01-1.22 {
  74   CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
  75   SELECT * FROM v2;
  76 } {1 2 3 4 5}
  77 do_catchsql_test tabfunc01-1.23.1 {
  78   SELECT * FROM v2(55);
  79 } {1 {'v2' is not a function}}
  80 do_catchsql_test tabfunc01-1.23.2 {
  81   SELECT * FROM v2();
  82 } {1 {'v2' is not a function}}
  83 do_execsql_test tabfunc01-1.24 {
  84   CREATE TABLE t0(x);
  85   INSERT INTO t0(x) VALUES(123),(456),(789);
  86   SELECT * FROM t0 ORDER BY x;
  87 } {123 456 789}
  88 do_catchsql_test tabfunc01-1.25 {
  89   SELECT * FROM t0(55) ORDER BY x;
  90 } {1 {'t0' is not a function}}
  91 do_catchsql_test tabfunc01-1.26 {
  92   WITH w0 AS (SELECT * FROM t0)
  93   INSERT INTO t0(x) SELECT * FROM w0()
  94 } {1 {'w0' is not a function}}
  95
  96 do_execsql_test tabfunc01-2.1 {
  97   CREATE TABLE t1(x);
  98   INSERT INTO t1(x) VALUES(2),(3);
  99   SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
 100 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
 101 do_execsql_test tabfunc01-2.2 {
 102   SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x)
 103   ORDER BY 1, 2;
 104 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
 105
 106 do_execsql_test tabfunc01-2.50 {
 107   SELECT * FROM generate_series() LIMIT 5;
 108 } {0 1 2 3 4}
 109
 110 do_execsql_test tabfunc01-3.1 {
 111   SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
 112 } {1 2 3}
 113
 114 # Eponymous virtual table exists in all schemas.
 115 #
 116 do_execsql_test tabfunc01-4.1 {
 117   SELECT * FROM main.generate_series(1,4)
 118 } {1 2 3 4}
 119 do_execsql_test tabfunc01-4.2 {
 120   SELECT * FROM temp.generate_series(1,4)
 121 } {1 2 3 4}
 122 do_execsql_test tabfunc01-4.3 {
 123   ATTACH ':memory:' AS aux1;
 124   CREATE TABLE aux1.t1(a,b,c);
 125   SELECT * FROM aux1.generate_series(1,4)
 126 } {1 2 3 4}
 127
 128 # The next series of tests is verifying that virtual table are able
 129 # to optimize the IN operator, even on terms that are not marked "omit".
 130 # When the generate_series virtual table is compiled for the testfixture,
 131 # the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which
 132 # causes the xBestIndex method of generate_series to leave the
 133 # sqlite3_index_constraint_usage.omit flag set to 0, which should cause
 134 # the SQLite core to verify the start=, stop=, and step= constraints on
 135 # each step of output.  At one point, the IN operator could not be used
 136 # by virtual tables unless omit was set.
 137 #
 138 do_execsql_test tabfunc01-500 {
 139   SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
 140   ORDER BY +1;
 141 } {1 7 11 17}
 142
 143 # Table-valued functions on the RHS of an IN operator
 144 #
 145 do_execsql_test tabfunc01-600 {
 146   CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
 147   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
 148     INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
 149   SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
 150 } {(002) (012) (022) (032) (042) (052)}
 151
 152
 153 do_test tabfunc01-700 {
 154   set PTR1 [intarray_addr 5 7 13 17 23]
 155   db eval {
 156     SELECT b FROM t600, carray(inttoptr($PTR1),5) WHERE a=value;
 157   }
 158 } {(005) (007) (013) (017) (023)}
 159 do_test tabfunc01-701 {
 160   db eval {
 161     SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int32');
 162   }
 163 } {(005) (007) (013) (017) (023)}
 164 do_test tabfunc01-702 {
 165   db eval {
 166     SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),4,'int32');
 167   }
 168 } {(005) (007) (013) (017)}
 169 do_catchsql_test tabfunc01-710 {
 170   SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int33');
 171 } {1 {unknown datatype: 'int33'}}
 172
 173 do_test tabfunc01-720 {
 174   set PTR2 [int64array_addr 5 7 13 17 23]
 175   db eval {
 176     SELECT b FROM t600, carray(inttoptr($PTR2),5,'int64') WHERE a=value;
 177   }
 178 } {(005) (007) (013) (017) (023)}
 179 do_test tabfunc01-721 {
 180   db eval {
 181     SELECT remember(123,inttoptr($PTR2));
 182     SELECT value FROM carray(inttoptr($PTR2),5,'int64');
 183   }
 184 } {123 123 7 13 17 23}
 185 do_test tabfunc01-722 {
 186   set PTR3 [expr {$PTR2+16}]
 187   db eval {
 188     SELECT remember(987,inttoptr($PTR3));
 189     SELECT value FROM carray(inttoptr($PTR2),5,'int64');
 190   }
 191 } {987 123 7 987 17 23}
 192
 193 do_test tabfunc01-730 {
 194   set PTR4 [doublearray_addr 5.0 7.0 13.0 17.0 23.0]
 195   db eval {
 196     SELECT b FROM t600, carray(inttoptr($PTR4),5,'double') WHERE a=value;
 197   }
 198 } {(005) (007) (013) (017) (023)}
 199
 200 do_test tabfunc01-740 {
 201   set PTR5 [textarray_addr x5 x7 x13 x17 x23]
 202   db eval {
 203     SELECT b FROM t600, carray(inttoptr($PTR5),5,'char*')
 204      WHERE a=trim(value,'x');
 205   }
 206 } {(005) (007) (013) (017) (023)}
 207
 208 do_test tabfunc01-750 {
 209   db eval {
 210     SELECT aa.value, bb.value, '|'
 211       FROM carray(inttoptr($PTR4),5,'double') AS aa
 212       JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
 213   }
 214 } {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
 215
 216 # Free up memory allocations
 217 intarray_addr
 218 int64array_addr
 219 doublearray_addr
 220 textarray_addr
 221
 222 finish_test