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(0) LIMIT 5;
  36 } {0 | 1 | 2 | 3 | 4 |}
  37 do_catchsql_test tabfunc01-1.2b {
  38   SELECT *, '|' FROM generate_series LIMIT 5;
  39 } {1 {first argument to "generate_series()" missing or unusable}}
  40 do_catchsql_test tabfunc01-1.2c {
  41   SELECT *, '|' FROM generate_series(value) LIMIT 5;
  42 } {1 {first argument to "generate_series()" missing or unusable}}
  43 do_catchsql_test tabfunc01-1.3 {
  44   CREATE VIRTUAL TABLE t1 USING generate_series;
  45 } {1 {no such module: generate_series}}
  46 do_execsql_test tabfunc01-1.4 {
  47   SELECT * FROM generate_series(1,9,2);
  48 } {1 3 5 7 9}
  49 do_execsql_test tabfunc01-1.5 {
  50   SELECT * FROM generate_series(1,9);
  51 } {1 2 3 4 5 6 7 8 9}
  52 do_execsql_test tabfunc01-1.6 {
  53   SELECT * FROM generate_series(1,10) WHERE step=3;
  54 } {1 4 7 10}
  55 do_catchsql_test tabfunc01-1.7 {
  56   SELECT * FROM generate_series(1,9,2,11);
  57 } {1 {too many arguments on generate_series() - max 3}}
  58
  59 do_execsql_test tabfunc01-1.8 {
  60   SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
  61 } {30 25 20 15 10 5 0}
  62 do_execsql_test tabfunc01-1.9 {
  63   SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC;
  64 } {7 30 6 25 5 20 4 15 3 10 2 5 1 0}
  65 do_execsql_test tabfunc01-1.10 {
  66   SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
  67 } {7 30 6 25 5 20 4 15 3 10 2 5 1 0}
  68
  69 do_execsql_test tabfunc01-1.20 {
  70   CREATE VIEW v1(a,b) AS VALUES(1,2),(3,4);
  71   SELECT * FROM v1;
  72 } {1 2 3 4}
  73 do_catchsql_test tabfunc01-1.21.1 {
  74   SELECT * FROM v1(55);
  75 } {1 {'v1' is not a function}}
  76 do_catchsql_test tabfunc01-1.21.2 {
  77   SELECT * FROM v1();
  78 } {1 {'v1' is not a function}}
  79 do_execsql_test tabfunc01-1.22 {
  80   CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
  81   SELECT * FROM v2;
  82 } {1 2 3 4 5}
  83 do_catchsql_test tabfunc01-1.23.1 {
  84   SELECT * FROM v2(55);
  85 } {1 {'v2' is not a function}}
  86 do_catchsql_test tabfunc01-1.23.2 {
  87   SELECT * FROM v2();
  88 } {1 {'v2' is not a function}}
  89 do_execsql_test tabfunc01-1.24 {
  90   CREATE TABLE t0(x);
  91   INSERT INTO t0(x) VALUES(123),(456),(789);
  92   SELECT * FROM t0 ORDER BY x;
  93 } {123 456 789}
  94 do_catchsql_test tabfunc01-1.25 {
  95   SELECT * FROM t0(55) ORDER BY x;
  96 } {1 {'t0' is not a function}}
  97 do_catchsql_test tabfunc01-1.26 {
  98   WITH w0 AS (SELECT * FROM t0)
  99   INSERT INTO t0(x) SELECT * FROM w0()
 100 } {1 {'w0' is not a function}}
 101
 102 do_execsql_test tabfunc01-2.1 {
 103   CREATE TABLE t1(x);
 104   INSERT INTO t1(x) VALUES(2),(3);
 105   SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
 106 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
 107 do_execsql_test tabfunc01-2.2 {
 108   SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x)
 109   ORDER BY 1, 2;
 110 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
 111
 112 do_execsql_test tabfunc01-2.50 {
 113   SELECT * FROM generate_series(0) LIMIT 5;
 114 } {0 1 2 3 4}
 115
 116 do_execsql_test tabfunc01-3.1 {
 117   SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
 118 } {1 2 3}
 119
 120 do_eqp_test tabfunc01-3.10 {
 121   SELECT value FROM generate_series(1,10) ORDER BY value;
 122 } {
 123   QUERY PLAN
 124   `--SCAN generate_series VIRTUAL TABLE INDEX 19:
 125 }
 126 do_eqp_test tabfunc01-3.11 {
 127   SELECT value FROM generate_series(1,10) ORDER BY +value;
 128 } {
 129   QUERY PLAN
 130   |--SCAN generate_series VIRTUAL TABLE INDEX 3:
 131   `--USE TEMP B-TREE FOR ORDER BY
 132 }
 133 do_eqp_test tabfunc01-3.12 {
 134   SELECT value FROM generate_series(1,10) ORDER BY value, stop;
 135 } {
 136   QUERY PLAN
 137   `--SCAN generate_series VIRTUAL TABLE INDEX 19:
 138 }
 139 do_eqp_test tabfunc01-3.13 {
 140   SELECT value FROM generate_series(1,10) ORDER BY stop, value;
 141 } {
 142   QUERY PLAN
 143   |--SCAN generate_series VIRTUAL TABLE INDEX 3:
 144   `--USE TEMP B-TREE FOR ORDER BY
 145 }
 146
 147
 148 do_eqp_test tabfunc01-3.20 {
 149   WITH t1(a) AS (
 150     SELECT value FROM generate_series(0,10,2)
 151     UNION ALL
 152     SELECT value FROM generate_series(9,18,3)
 153   )
 154   SELECT * FROM t1 ORDER BY a;
 155 } {
 156   QUERY PLAN
 157   `--MERGE (UNION ALL)
 158      |--LEFT
 159      |  `--SCAN generate_series VIRTUAL TABLE INDEX 23:
 160      `--RIGHT
 161         `--SCAN generate_series VIRTUAL TABLE INDEX 23:
 162 }
 163
 164
 165 # Eponymous virtual table exists in all schemas.
 166 #
 167 do_execsql_test tabfunc01-4.1 {
 168   SELECT * FROM main.generate_series(1,4)
 169 } {1 2 3 4}
 170 do_execsql_test tabfunc01-4.2 {
 171   SELECT * FROM temp.generate_series(1,4)
 172 } {1 2 3 4}
 173 do_execsql_test tabfunc01-4.3 {
 174   ATTACH ':memory:' AS aux1;
 175   CREATE TABLE aux1.t1(a,b,c);
 176   SELECT * FROM aux1.generate_series(1,4)
 177 } {1 2 3 4}
 178
 179 # 2018-12-03: Fix bug reported by by private email.
 180 do_execsql_test tabfunc01-4.4 {
 181   SELECT * FROM (generate_series(1,5,2)) AS x LIMIT 10;
 182 } {1 3 5}
 183
 184 # The next series of tests is verifying that virtual table are able
 185 # to optimize the IN operator, even on terms that are not marked "omit".
 186 # When the generate_series virtual table is compiled for the testfixture,
 187 # the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which
 188 # causes the xBestIndex method of generate_series to leave the
 189 # sqlite3_index_constraint_usage.omit flag set to 0, which should cause
 190 # the SQLite core to verify the start=, stop=, and step= constraints on
 191 # each step of output.  At one point, the IN operator could not be used
 192 # by virtual tables unless omit was set.
 193 #
 194 do_execsql_test tabfunc01-500 {
 195   SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
 196   ORDER BY +1;
 197 } {1 7 11 17}
 198
 199 # Table-valued functions on the RHS of an IN operator
 200 #
 201 do_execsql_test tabfunc01-600 {
 202   CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
 203   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
 204     INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
 205   SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
 206 } {(002) (012) (022) (032) (042) (052)}
 207
 208
 209 do_test tabfunc01-700 {
 210   set PTR1 [intarray_addr 5 7 13 17 23]
 211   db eval {
 212     SELECT b FROM t600, carray(inttoptr($PTR1),5) WHERE a=value;
 213   }
 214 } {(005) (007) (013) (017) (023)}
 215 do_test tabfunc01-701 {
 216   db eval {
 217     SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int32');
 218   }
 219 } {(005) (007) (013) (017) (023)}
 220 do_test tabfunc01-702 {
 221   db eval {
 222     SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),4,'int32');
 223   }
 224 } {(005) (007) (013) (017)}
 225 do_catchsql_test tabfunc01-710 {
 226   SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int33');
 227 } {1 {unknown datatype: 'int33'}}
 228
 229 do_test tabfunc01-720 {
 230   set PTR2 [int64array_addr 5 7 13 17 23]
 231   db eval {
 232     SELECT b FROM t600, carray(inttoptr($PTR2),5,'int64') WHERE a=value;
 233   }
 234 } {(005) (007) (013) (017) (023)}
 235 do_test tabfunc01-721 {
 236   db eval {
 237     SELECT remember(123,inttoptr($PTR2));
 238     SELECT value FROM carray(inttoptr($PTR2),5,'int64');
 239   }
 240 } {123 123 7 13 17 23}
 241 do_test tabfunc01-722 {
 242   set PTR3 [expr {$PTR2+16}]
 243   db eval {
 244     SELECT remember(987,inttoptr($PTR3));
 245     SELECT value FROM carray(inttoptr($PTR2),5,'int64');
 246   }
 247 } {987 123 7 987 17 23}
 248
 249 do_test tabfunc01-730 {
 250   set PTR4 [doublearray_addr 5.0 7.0 13.0 17.0 23.0]
 251   db eval {
 252     SELECT b FROM t600, carray(inttoptr($PTR4),5,'double') WHERE a=value;
 253   }
 254 } {(005) (007) (013) (017) (023)}
 255
 256 do_test tabfunc01-740 {
 257   set PTR5 [textarray_addr x5 x7 x13 x17 x23]
 258   db eval {
 259     SELECT b FROM t600, carray(inttoptr($PTR5),5,'char*')
 260      WHERE a=trim(value,'x');
 261   }
 262 } {(005) (007) (013) (017) (023)}
 263
 264 do_test tabfunc01-750 {
 265   db eval {
 266     SELECT aa.value, bb.value, '|'
 267       FROM carray(inttoptr($PTR4),5,'double') AS aa
 268       JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
 269   }
 270 } {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
 271
 272 # ticket https://www.sqlite.org/src/info/2ae0c599b735d59e
 273 # Verification of testtag-20230227a
 274 do_test tabfunc01-751 {
 275   db eval {
 276     SELECT aa.value, bb.value, '|'
 277       FROM carray(inttoptr($PTR4),5,'double') AS aa
 278       LEFT JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
 279   }
 280 } {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
 281
 282 ifcapable altertable {
 283   do_test tabfunc01-800 {
 284     catchsql {
 285       ALTER TABLE generate_series ADD COLUMN col2;
 286     }
 287   } {1 {virtual tables may not be altered}}
 288   do_test tabfunc01-810 {
 289     catchsql {
 290       ALTER TABLE generate_series RENAME TO flubber;
 291     }
 292   } {1 {table generate_series may not be altered}}
 293   do_test tabfunc01-820 {
 294     catchsql {
 295       ALTER TABLE generate_series RENAME  start TO flubber;
 296     }
 297   } {1 {table generate_series may not be altered}}
 298   do_test tabfunc01-830 {
 299     catchsql {
 300       ALTER TABLE generate_series DROP COLUMN start;
 301     }
 302   } {1 {table generate_series may not be altered}}
 303   do_test tabfunc01-900 {
 304     catchsql {
 305       ALTER TABLE pragma_compile_options ADD COLUMN col2;
 306     }
 307   } {1 {virtual tables may not be altered}}
 308   do_test tabfunc01-910 {
 309     catchsql {
 310       ALTER TABLE pragma_compile_options RENAME TO flubber;
 311     }
 312   } {1 {table pragma_compile_options may not be altered}}
 313   do_test tabfunc01-920 {
 314     catchsql {
 315       ALTER TABLE pragma_compile_options RENAME  start TO flubber;
 316     }
 317   } {1 {table pragma_compile_options may not be altered}}
 318   do_test tabfunc01-930 {
 319     catchsql {
 320       ALTER TABLE pragma_compile_options DROP COLUMN start;
 321     }
 322   } {1 {table pragma_compile_options may not be altered}}
 323 }
 324
 325 #-----------------------------------------------------------------------------
 326 # 2024-04-26  LIMIT and OFFSET passed into virtual tables
 327 # https://sqlite.org/forum/forumpost/c243b8f856
 328 #
 329 do_execsql_test tabfunc01-900 {
 330   SELECT * FROM (
 331     SELECT * FROM generate_series(1,10)
 332     UNION ALL
 333     SELECT * FROM generate_series(101,104)
 334   ) LIMIT 10 OFFSET 5;
 335 } {6 7 8 9 10 101 102 103 104}
 336 do_execsql_test tabfunc01-910 {
 337   SELECT * FROM (
 338     SELECT * FROM generate_series(1,10)
 339     UNION ALL
 340     SELECT * FROM generate_series(101,104)
 341   ) LIMIT -1 OFFSET 5;
 342 } {6 7 8 9 10 101 102 103 104}
 343 do_execsql_test tabfunc01-920 {
 344   SELECT * FROM (
 345     SELECT * FROM generate_series(1,10)
 346     UNION ALL
 347     SELECT * FROM generate_series(101,104)
 348   ) LIMIT -1 OFFSET 0;
 349 } {1 2 3 4 5 6 7 8 9 10 101 102 103 104}
 350
 351
 352 # Free up memory allocations
 353 intarray_addr
 354 int64array_addr
 355 doublearray_addr
 356 textarray_addr
 357
 358 finish_test