Debian Codegraph @

test/tabfunc01.test

raw
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 remember
26
27 do_execsql_test tabfunc01-1.1 {
28 SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2;
29 } {1 | 3 | 5 | 7 | 9 |}
30 do_execsql_test tabfunc01-1.1b {
31 PRAGMA table_xinfo(generate_series);
32 } {0 value {} 0 {} 0 0 1 start {} 0 {} 0 1 2 stop {} 0 {} 0 1 3 step {} 0 {} 0 1}
33 do_execsql_test tabfunc01-1.2 {
34 SELECT *, '|' FROM generate_series(0) LIMIT 5;
35 } {0 | 1 | 2 | 3 | 4 |}
36 do_catchsql_test tabfunc01-1.2b {
37 SELECT *, '|' FROM generate_series LIMIT 5;
38 } {1 {first argument to "generate_series()" missing or unusable}}
39 do_catchsql_test tabfunc01-1.2c {
40 SELECT *, '|' FROM generate_series(value) LIMIT 5;
41 } {1 {first argument to "generate_series()" missing or unusable}}
42 do_catchsql_test tabfunc01-1.3 {
43 CREATE VIRTUAL TABLE t1 USING generate_series;
44 } {1 {no such module: generate_series}}
45 do_execsql_test tabfunc01-1.4 {
46 SELECT * FROM generate_series(1,9,2);
47 } {1 3 5 7 9}
48 do_execsql_test tabfunc01-1.5 {
49 SELECT * FROM generate_series(1,9);
50 } {1 2 3 4 5 6 7 8 9}
51 do_execsql_test tabfunc01-1.6 {
52 SELECT * FROM generate_series(1,10) WHERE step=3;
53 } {1 4 7 10}
54 do_catchsql_test tabfunc01-1.7 {
55 SELECT * FROM generate_series(1,9,2,11);
56 } {1 {too many arguments on generate_series() - max 3}}
57
58 do_execsql_test tabfunc01-1.8 {
59 SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
60 } {30 25 20 15 10 5 0}
61 do_execsql_test tabfunc01-1.9 {
62 SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC;
63 } {30 30 25 25 20 20 15 15 10 10 5 5 0 0}
64 do_execsql_test tabfunc01-1.10 {
65 SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
66 } {30 30 25 25 20 20 15 15 10 10 5 5 0 0}
67
68 do_execsql_test tabfunc01-1.20 {
69 CREATE VIEW v1(a,b) AS VALUES(1,2),(3,4);
70 SELECT * FROM v1;
71 } {1 2 3 4}
72 do_catchsql_test tabfunc01-1.21.1 {
73 SELECT * FROM v1(55);
74 } {1 {'v1' is not a function}}
75 do_catchsql_test tabfunc01-1.21.2 {
76 SELECT * FROM v1();
77 } {1 {'v1' is not a function}}
78 do_execsql_test tabfunc01-1.22 {
79 CREATE VIEW v2(x) AS SELECT value FROM generate_series(1,5);
80 SELECT * FROM v2;
81 } {1 2 3 4 5}
82 do_catchsql_test tabfunc01-1.23.1 {
83 SELECT * FROM v2(55);
84 } {1 {'v2' is not a function}}
85 do_catchsql_test tabfunc01-1.23.2 {
86 SELECT * FROM v2();
87 } {1 {'v2' is not a function}}
88 do_execsql_test tabfunc01-1.24 {
89 CREATE TABLE t0(x);
90 INSERT INTO t0(x) VALUES(123),(456),(789);
91 SELECT * FROM t0 ORDER BY x;
92 } {123 456 789}
93 do_catchsql_test tabfunc01-1.25 {
94 SELECT * FROM t0(55) ORDER BY x;
95 } {1 {'t0' is not a function}}
96 do_catchsql_test tabfunc01-1.26 {
97 WITH w0 AS (SELECT * FROM t0)
98 INSERT INTO t0(x) SELECT * FROM w0()
99 } {1 {'w0' is not a function}}
100
101 do_execsql_test tabfunc01-2.1 {
102 CREATE TABLE t1(x);
103 INSERT INTO t1(x) VALUES(2),(3);
104 SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
105 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
106 do_execsql_test tabfunc01-2.2 {
107 SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x)
108 ORDER BY 1, 2;
109 } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
110
111 do_execsql_test tabfunc01-2.50 {
112 SELECT * FROM generate_series(0) LIMIT 5;
113 } {0 1 2 3 4}
114
115 do_execsql_test tabfunc01-3.1 {
116 SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
117 } {1 2 3}
118
119 do_eqp_test tabfunc01-3.10 {
120 SELECT value FROM generate_series(1,10) ORDER BY value;
121 } {
122 QUERY PLAN
123 `--SCAN generate_series VIRTUAL TABLE INDEX 0x13:
124 }
125 do_eqp_test tabfunc01-3.11 {
126 SELECT value FROM generate_series(1,10) ORDER BY +value;
127 } {
128 QUERY PLAN
129 |--SCAN generate_series VIRTUAL TABLE INDEX 0x3:
130 `--USE TEMP B-TREE FOR ORDER BY
131 }
132 do_eqp_test tabfunc01-3.12 {
133 SELECT value FROM generate_series(1,10) ORDER BY value, stop;
134 } {
135 QUERY PLAN
136 `--SCAN generate_series VIRTUAL TABLE INDEX 0x13:
137 }
138 do_eqp_test tabfunc01-3.13 {
139 SELECT value FROM generate_series(1,10) ORDER BY stop, value;
140 } {
141 QUERY PLAN
142 |--SCAN generate_series VIRTUAL TABLE INDEX 0x3:
143 `--USE TEMP B-TREE FOR ORDER BY
144 }
145
146
147 do_eqp_test tabfunc01-3.20 {
148 WITH t1(a) AS (
149 SELECT value FROM generate_series(0,10,2)
150 UNION ALL
151 SELECT value FROM generate_series(9,18,3)
152 )
153 SELECT * FROM t1 ORDER BY a;
154 } {
155 QUERY PLAN
156 `--MERGE (UNION ALL)
157 |--LEFT
158 | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
159 `--RIGHT
160 `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
161 }
162
163
164 # Eponymous virtual table exists in all schemas.
165 #
166 do_execsql_test tabfunc01-4.1 {
167 SELECT * FROM main.generate_series(1,4)
168 } {1 2 3 4}
169 do_execsql_test tabfunc01-4.2 {
170 SELECT * FROM temp.generate_series(1,4)
171 } {1 2 3 4}
172 do_execsql_test tabfunc01-4.3 {
173 ATTACH ':memory:' AS aux1;
174 CREATE TABLE aux1.t1(a,b,c);
175 SELECT * FROM aux1.generate_series(1,4)
176 } {1 2 3 4}
177
178 # 2018-12-03: Fix bug reported by by private email.
179 do_execsql_test tabfunc01-4.4 {
180 SELECT * FROM (generate_series(1,5,2)) AS x LIMIT 10;
181 } {1 3 5}
182
183 # 2025-03-13 forum post bf2dc8e909983511
184 #
185 do_execsql_test tabfunc01-5.1 {
186 SELECT value
187 FROM generate_series(60,73,6)
188 WHERE value=66;
189 } 66
190 do_execsql_test tabfunc01-5.2 {
191 SELECT value
192 FROM generate_series(73,60,-6)
193 WHERE value=67;
194 } 67
195
196 # 2025-03-22 forum post 0d5d63257e3ff4f6
197 #
198 do_execsql_test tabfunc01-6.1 {
199 SELECT value FROM generate_series(1,10) WHERE value<5.5;
200 } {1 2 3 4 5}
201 do_execsql_test tabfunc01-6.2 {
202 SELECT value FROM generate_series(1,10) WHERE value<5.0;
203 } {1 2 3 4}
204 do_execsql_test tabfunc01-6.3 {
205 SELECT value FROM generate_series(1,10) WHERE value<=5.5;
206 } {1 2 3 4 5}
207 do_execsql_test tabfunc01-6.4 {
208 SELECT value FROM generate_series(1,10) WHERE value<=5.0;
209 } {1 2 3 4 5}
210 do_execsql_test tabfunc01-6.5 {
211 SELECT value FROM generate_series(1,10) WHERE value>5.5;
212 } {6 7 8 9 10}
213 do_execsql_test tabfunc01-6.6 {
214 SELECT value FROM generate_series(1,10) WHERE value>5.0;
215 } {6 7 8 9 10}
216 do_execsql_test tabfunc01-6.7 {
217 SELECT value FROM generate_series(1,10) WHERE value>=5.5;
218 } {6 7 8 9 10}
219 do_execsql_test tabfunc01-6.8 {
220 SELECT value FROM generate_series(1,10) WHERE value>=5.0;
221 } {5 6 7 8 9 10}
222 do_execsql_test tabfunc01-6.9 {
223 SELECT value FROM generate_series(10,1,-1) WHERE value<5.5;
224 } {5 4 3 2 1}
225 do_execsql_test tabfunc01-6.10 {
226 SELECT value FROM generate_series(10,1,-1) WHERE value<5.0;
227 } {4 3 2 1}
228 do_execsql_test tabfunc01-6.11 {
229 SELECT value FROM generate_series(10,1,-1) WHERE value<=5.5;
230 } {5 4 3 2 1}
231 do_execsql_test tabfunc01-6.12 {
232 SELECT value FROM generate_series(10,1,-1) WHERE value<=5.0;
233 } {5 4 3 2 1}
234 do_execsql_test tabfunc01-6.13 {
235 SELECT value FROM generate_series(10,1,-1) WHERE value>5.5;
236 } {10 9 8 7 6}
237 do_execsql_test tabfunc01-6.14 {
238 SELECT value FROM generate_series(10,1,-1) WHERE value>5.0;
239 } {10 9 8 7 6}
240 do_execsql_test tabfunc01-6.15 {
241 SELECT value FROM generate_series(10,1,-1) WHERE value>=5.5;
242 } {10 9 8 7 6}
243 do_execsql_test tabfunc01-6.16 {
244 SELECT value FROM generate_series(10,1,-1) WHERE value>=5.0;
245 } {10 9 8 7 6 5}
246 do_execsql_test tabfunc01-6.17 {
247 SELECT value FROM generate_series(1,10) WHERE value==5.5;
248 } {}
249 do_execsql_test tabfunc01-6.18 {
250 SELECT value FROM generate_series(1,10) WHERE value==5.0;
251 } {5}
252
253 # The next series of tests is verifying that virtual table are able
254 # to optimize the IN operator, even on terms that are not marked "omit".
255 # When the generate_series virtual table is compiled for the testfixture,
256 # the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which
257 # causes the xBestIndex method of generate_series to leave the
258 # sqlite3_index_constraint_usage.omit flag set to 0, which should cause
259 # the SQLite core to verify the start=, stop=, and step= constraints on
260 # each step of output. At one point, the IN operator could not be used
261 # by virtual tables unless omit was set.
262 #
263 do_execsql_test tabfunc01-500 {
264 SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
265 ORDER BY +1;
266 } {1 7 11 17}
267
268 # Table-valued functions on the RHS of an IN operator
269 #
270 do_execsql_test tabfunc01-600 {
271 CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
272 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
273 INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
274 SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
275 } {(002) (012) (022) (032) (042) (052)}
276
277
278 do_test tabfunc01-700 {
279 set PTR1 [intarray_addr 5 7 13 17 23]
280 sqlite3_create_function db
281 db eval {
282 SELECT b FROM t600, carray(inttoptr($PTR1),5) WHERE a=value;
283 }
284 } {(005) (007) (013) (017) (023)}
285 do_test tabfunc01-701 {
286 db eval {
287 SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int32');
288 }
289 } {(005) (007) (013) (017) (023)}
290 do_test tabfunc01-702 {
291 db eval {
292 SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),4,'int32');
293 }
294 } {(005) (007) (013) (017)}
295 do_catchsql_test tabfunc01-710 {
296 SELECT b FROM t600 WHERE a IN carray(inttoptr($PTR1),5,'int33');
297 } {1 {unknown datatype: 'int33'}}
298
299 do_test tabfunc01-720 {
300 set PTR2 [int64array_addr 5 7 13 17 23]
301 db eval {
302 SELECT b FROM t600, carray(inttoptr($PTR2),5,'int64') WHERE a=value;
303 }
304 } {(005) (007) (013) (017) (023)}
305 do_test tabfunc01-721 {
306 db eval {
307 SELECT remember(123,inttoptr($PTR2));
308 SELECT value FROM carray(inttoptr($PTR2),5,'int64');
309 }
310 } {123 123 7 13 17 23}
311 do_test tabfunc01-722 {
312 set PTR3 [expr {$PTR2+16}]
313 db eval {
314 SELECT remember(987,inttoptr($PTR3));
315 SELECT value FROM carray(inttoptr($PTR2),5,'int64');
316 }
317 } {987 123 7 987 17 23}
318
319 do_test tabfunc01-730 {
320 set PTR4 [doublearray_addr 5.0 7.0 13.0 17.0 23.0]
321 db eval {
322 SELECT b FROM t600, carray(inttoptr($PTR4),5,'double') WHERE a=value;
323 }
324 } {(005) (007) (013) (017) (023)}
325
326 do_test tabfunc01-740 {
327 set PTR5 [textarray_addr x5 x7 x13 x17 x23]
328 db eval {
329 SELECT b FROM t600, carray(inttoptr($PTR5),5,'char*')
330 WHERE a=trim(value,'x');
331 }
332 } {(005) (007) (013) (017) (023)}
333
334 do_test tabfunc01-750 {
335 db eval {
336 SELECT aa.value, bb.value, '|'
337 FROM carray(inttoptr($PTR4),5,'double') AS aa
338 JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
339 }
340 } {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
341
342 # ticket https://sqlite.org/src/info/2ae0c599b735d59e
343 # Verification of testtag-20230227a
344 do_test tabfunc01-751 {
345 db eval {
346 SELECT aa.value, bb.value, '|'
347 FROM carray(inttoptr($PTR4),5,'double') AS aa
348 LEFT JOIN carray(inttoptr($PTR5),5,'char*') AS bb ON aa.rowid=bb.rowid;
349 }
350 } {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
351
352 ifcapable altertable {
353 do_test tabfunc01-800 {
354 catchsql {
355 ALTER TABLE generate_series ADD COLUMN col2;
356 }
357 } {1 {virtual tables may not be altered}}
358 do_test tabfunc01-810 {
359 catchsql {
360 ALTER TABLE generate_series RENAME TO flubber;
361 }
362 } {1 {table generate_series may not be altered}}
363 do_test tabfunc01-820 {
364 catchsql {
365 ALTER TABLE generate_series RENAME start TO flubber;
366 }
367 } {1 {table generate_series may not be altered}}
368 do_test tabfunc01-830 {
369 catchsql {
370 ALTER TABLE generate_series DROP COLUMN start;
371 }
372 } {1 {table generate_series may not be altered}}
373 do_test tabfunc01-900 {
374 catchsql {
375 ALTER TABLE pragma_compile_options ADD COLUMN col2;
376 }
377 } {1 {virtual tables may not be altered}}
378 do_test tabfunc01-910 {
379 catchsql {
380 ALTER TABLE pragma_compile_options RENAME TO flubber;
381 }
382 } {1 {table pragma_compile_options may not be altered}}
383 do_test tabfunc01-920 {
384 catchsql {
385 ALTER TABLE pragma_compile_options RENAME start TO flubber;
386 }
387 } {1 {table pragma_compile_options may not be altered}}
388 do_test tabfunc01-930 {
389 catchsql {
390 ALTER TABLE pragma_compile_options DROP COLUMN start;
391 }
392 } {1 {table pragma_compile_options may not be altered}}
393 }
394
395 #-----------------------------------------------------------------------------
396 # 2024-04-26 LIMIT and OFFSET passed into virtual tables
397 # https://sqlite.org/forum/forumpost/c243b8f856
398 #
399 do_execsql_test tabfunc01-900 {
400 SELECT * FROM (
401 SELECT * FROM generate_series(1,10)
402 UNION ALL
403 SELECT * FROM generate_series(101,104)
404 ) LIMIT 10 OFFSET 5;
405 } {6 7 8 9 10 101 102 103 104}
406 do_execsql_test tabfunc01-910 {
407 SELECT * FROM (
408 SELECT * FROM generate_series(1,10)
409 UNION ALL
410 SELECT * FROM generate_series(101,104)
411 ) LIMIT -1 OFFSET 5;
412 } {6 7 8 9 10 101 102 103 104}
413 do_execsql_test tabfunc01-920 {
414 SELECT * FROM (
415 SELECT * FROM generate_series(1,10)
416 UNION ALL
417 SELECT * FROM generate_series(101,104)
418 ) LIMIT -1 OFFSET 0;
419 } {1 2 3 4 5 6 7 8 9 10 101 102 103 104}
420
421 # Also: https://sqlite.org/forum/forumpost/ce1a06c2c8
422 #
423 do_execsql_test tabfunc01-930 {
424 SELECT * FROM generate_series(9223372036854775807,
425 -9223372036854775808,
426 -9223372036854775808);
427 } {9223372036854775807 -1}
428 do_execsql_test tabfunc01-931 {
429 SELECT * FROM generate_series(9223372036854775807,
430 -9223372036854775808,
431 -9223372036854775808)
432 LIMIT 100 OFFSET 1;
433 } {-1}
434 do_execsql_test tabfunc01-932 {
435 SELECT * FROM generate_series(9223372036854775807,
436 -9223372036854775808,
437 -9223372036854775808)
438 LIMIT 100 OFFSET 2;
439 } {}
440 do_execsql_test tabfunc01-933 {
441 SELECT * FROM generate_series(9223372036854775807,
442 -9223372036854775808,
443 -9223372036854775808)
444 LIMIT 100 OFFSET 9223372036854775807;
445 } {}
446 do_execsql_test tabfunc01-940 {
447 SELECT * FROM generate_series(1,11,2);
448 } {1 3 5 7 9 11}
449 do_execsql_test tabfunc01-941 {
450 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 4;
451 } {9 11}
452 do_execsql_test tabfunc01-942 {
453 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 5;
454 } {11}
455 do_execsql_test tabfunc01-943 {
456 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 6;
457 } {}
458 do_execsql_test tabfunc01-944 {
459 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 7;
460 } {}
461 do_execsql_test tabfunc01-945 {
462 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 9223372036854775807;
463 } {}
464 do_execsql_test tabfunc01-950 {
465 SELECT * FROM generate_series(1,11,1) LIMIT 100 OFFSET 10;
466 } {11}
467 do_execsql_test tabfunc01-951 {
468 SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 9223372036854775807;
469 } {}
470 do_execsql_test tabfunc01-952 {
471 SELECT * FROM generate_series(11,1,-1) LIMIT 100 OFFSET 10;
472 } {1}
473 do_execsql_test tabfunc01-953 {
474 SELECT * FROM generate_series(11,1,-1) LIMIT 100 OFFSET 9223372036854775807;
475 } {}
476 do_execsql_test tabfunc01-954 {
477 SELECT * FROM generate_series(1,11,1) LIMIT 3 OFFSET 3;
478 } {4 5 6}
479
480
481 #-------------------------------------------------------------------------
482 # Forum post https://sqlite.org/forum/forumpost/e7c3ae1215
483 #
484 foreach {tn where res} {
485 1000 "where value = 2" 2
486 1010 "where value in (2)" 2
487 1020 "where value in (select 2)" 2
488 1030 "where value = 2 OR value = 4" {2 4}
489 1040 "where value in (2, 4)" {2 4}
490 } {
491 do_execsql_test $tn "
492 SELECT value FROM generate_series(1, 5) $where
493 " $res
494 }
495
496 do_execsql_test 1100 {
497 select 1 as c_0
498 from
499 generate_series(1, 1) as ref_3
500 where (ref_3.value) in (select 1);
501 } {1}
502
503 # 2025-03-18 /forumpost/1e17219c88
504 # The generate_series() table-valued function is modified so that its
505 # rowid is always its value. That way it can be used on the RHS of a
506 # RIGHT JOIN.
507 #
508 do_execsql_test 1200 {
509 DROP TABLE IF EXISTS t1;
510 CREATE TABLE t1(value INT);
511 INSERT INTO t1 VALUES (1),(2),(3);
512 SELECT t1.value, t2.value
513 FROM t1 RIGHT JOIN generate_series(1,3,1) AS t2 USING(value);
514 } {1 1 2 2 3 3}
515
516 #--------------------------------------------------------------------------
517 # New test cases for generate_series associated with the refactor on branch
518 # serices-refactor circa 2025-09-27.
519 #
520 do_execsql_test 1300 {
521 SELECT value
522 FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
523 WHERE value BETWEEN 1 AND 5;
524 } {2 4}
525 do_execsql_test 1301 {
526 SELECT value
527 FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
528 WHERE value BETWEEN 0 AND 6;
529 } {0 2 4 6}
530 do_execsql_test 1302 {
531 SELECT value
532 FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
533 WHERE value BETWEEN 0.5 AND 6.25;
534 } {2 4 6}
535 do_execsql_test 1303 {
536 SELECT value
537 FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
538 WHERE value BETWEEN 9223372036854775803 AND 9223372036854775807
539 } {9223372036854775804 9223372036854775806}
540 do_execsql_test 1304 {
541 SELECT value
542 FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
543 WHERE value BETWEEN 9223372036854775803 AND 9223372036854775807
544 ORDER BY value DESC;
545 } {9223372036854775806 9223372036854775804}
546
547 do_execsql_test 1310 {
548 SELECT value FROM generate_series(0) LIMIT 5;
549 } {0 1 2 3 4}
550 do_execsql_test 1311 {
551 SELECT value FROM generate_series(5) LIMIT 5;
552 } {5 6 7 8 9}
553 do_execsql_test 1312 {
554 SELECT value FROM generate_series(0) WHERE stop=6;
555 } {0 1 2 3 4 5 6}
556 do_execsql_test 1313 {
557 SELECT value FROM generate_series(1,10) WHERE step IS NULL;
558 } {}
559 do_execsql_test 1314 {
560 SELECT value FROM generate_series(0xfffffffd);
561 } {4294967293 4294967294 4294967295}
562 do_execsql_test 1315 {
563 SELECT value FROM generate_series(4,3,1);
564 } {}
565 do_execsql_test 1316 {
566 SELECT value FROM generate_series(3,4,-1);
567 } {}
568
569 do_execsql_test 1320 {
570 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
571 WHERE value=9.2234e18;
572 } {}
573 do_execsql_test 1321 {
574 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
575 WHERE value=-9.2234e18;
576 } {}
577 do_execsql_test 1322 {
578 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
579 WHERE value>9223372036854775807;
580 } {}
581 do_execsql_test 1323 {
582 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
583 WHERE value>9223372036854775806;
584 } {9223372036854775807}
585 do_execsql_test 1324 {
586 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
587 WHERE value>=9223372036854775807;
588 } {9223372036854775807}
589
590 do_execsql_test 1330 {
591 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
592 WHERE value<-9223372036854775808;
593 } {}
594 do_execsql_test 1331 {
595 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
596 WHERE value<=-9223372036854775808;
597 } {-9223372036854775808}
598 do_execsql_test 1332 {
599 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
600 WHERE value<-9223372036854775807;
601 } {-9223372036854775808}
602 do_execsql_test 1333 {
603 SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
604 WHERE value BETWEEN 4 AND 1;
605 } {}
606
607 do_execsql_test 1340 {
608 SELECT value FROM generate_series(100,0,-10)
609 WHERE value BETWEEN 33 AND 66;
610 } {60 50 40}
611 do_execsql_test 1341 {
612 SELECT value FROM generate_series(100,0,-10)
613 WHERE value BETWEEN 33 AND 60;
614 } {60 50 40}
615 do_execsql_test 1342 {
616 SELECT value FROM generate_series(100,0,-10)
617 WHERE value BETWEEN 33 AND 59;
618 } {50 40}
619 do_execsql_test 1343 {
620 SELECT value
621 FROM generate_series(-9223372036854760000,-9223372036854775808,-10000);
622 } {-9223372036854760000 -9223372036854770000}
623 do_execsql_test 1344 {
624 SELECT value
625 FROM generate_series(-9223372036854760000,-9223372036854775808,-10000)
626 WHERE value < -9223372036854770001;
627 } {}
628 do_execsql_test 1345 {
629 SELECT value
630 FROM generate_series(9223372036854760000,9223372036854775807,10000);
631 } {9223372036854760000 9223372036854770000}
632 do_execsql_test 1346 {
633 SELECT value
634 FROM generate_series(9223372036854760000,9223372036854775807,10000)
635 WHERE value > 9223372036854770001;
636 } {}
637
638 do_execsql_test 1350 {
639 SELECT value FROM generate_series(100,0,-10)
640 WHERE value BETWEEN 33 AND 38;
641 } {}
642 do_execsql_test 1351 {
643 SELECT value FROM generate_series(0,100,+10)
644 WHERE value BETWEEN 33 AND 38;
645 } {}
646
647 do_execsql_test 1360 {
648 SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808);
649 } {0 -9223372036854775808}
650 do_execsql_test 1361 {
651 SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
652 LIMIT 1 OFFSET 0;
653 } {0}
654 do_execsql_test 1362 {
655 SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
656 ORDER BY value ASC;
657 } {-9223372036854775808 0}
658 do_execsql_test 1363 {
659 SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
660 ORDER BY value ASC LIMIT 10 OFFSET 1;
661 } {0}
662 do_execsql_test 1364 {
663 SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
664 ORDER BY value ASC LIMIT 10 OFFSET 40000000;
665 } {}
666
667 do_execsql_test 1370 {
668 SELECT * FROM generate_series(0,0,0);
669 } {}
670
671 # Forum post 2026-05-09T07:41:32Z
672 set subtest 1
673 foreach bound {
674 9007199254740991.0
675 9007199254740991.1
676 9007199254740991.5
677 9007199254740991.9
678 9007199254740992.0
679 9007199254740992.1
680 9007199254740992.5
681 9007199254740992.9
682 9007199254740993.0
683 9007199254740993.1
684 9007199254740993.5
685 } {
686 foreach {range boundsign} {
687 (+9007199254740990,+9007199254740995) +
688 (-9007199254740995,-9007199254740990) -
689 } {
690 set bx $boundsign$bound
691 foreach op {< > <= >=} {
692 do_execsql_test 1380.$subtest "
693 SELECT 'value $op $bx' WHERE (
694 SELECT group_concat(value) FROM generate_series $range
695 WHERE value $op $bx)<>(
696 SELECT group_concat(value) FROM generate_series $range
697 WHERE +value $op $bx);
698 " {}
699 incr subtest
700 }
701 }
702 }
703
704 reset_db
705 load_static_extension db series
706 do_execsql_test 1400 {
707 CREATE TABLE t1(x);
708 CREATE TABLE t2(y);
709 }
710 do_catchsql_test 1410 {
711 SELECT x, y, value
712 FROM (t1 RIGHT JOIN generate_series(t2.y,5) AS value) JOIN t2;
713 } {1 {table-function argument references tables to its right}}
714 do_catchsql_test 1420 {
715 SELECT x, y, value
716 FROM t2 JOIN (t1 RIGHT JOIN generate_series(t2.y,5) AS value)
717 } {1 {no such column: t2.y}}
718
719
720 # Free up memory allocations
721 intarray_addr
722 int64array_addr
723 doublearray_addr
724 textarray_addr
725
726 finish_test