niceSOFT/sqlite: Official Git mirror of the SQLite source tree

test/tabfunc01.test
810 строк25 KB
drh
Improvements to handling of inequality constraints on generate_series().value
15 июн 2026, 19:27
15 июн 2026, 19:272161a3d
# 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 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;
} {30 30 25 25 20 20 15 15 10 10 5 5 0 0}
do_execsql_test tabfunc01-1.10 {
  SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
} {30 30 25 25 20 20 15 15 10 10 5 5 0 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 0x13:
}
do_eqp_test tabfunc01-3.11 {
  SELECT value FROM generate_series(1,10) ORDER BY +value;
} {
  QUERY PLAN
  |--SCAN generate_series VIRTUAL TABLE INDEX 0x3:
  `--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 0x13:
}
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 0x3:
  `--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 0x17:
     `--RIGHT
        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
}
  

# 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}

# 2025-03-13 forum post bf2dc8e909983511
#
do_execsql_test tabfunc01-5.1 {
  SELECT value
    FROM generate_series(60,73,6)
   WHERE value=66;
} 66
do_execsql_test tabfunc01-5.2 {
  SELECT value
    FROM generate_series(73,60,-6)
   WHERE value=67;
} 67

# 2025-03-22 forum post 0d5d63257e3ff4f6
#
do_execsql_test tabfunc01-6.1 {
  SELECT value FROM generate_series(1,10) WHERE value<5.5;
} {1 2 3 4 5}
do_execsql_test tabfunc01-6.2 {
  SELECT value FROM generate_series(1,10) WHERE value<5.0;
} {1 2 3 4}
do_execsql_test tabfunc01-6.3 {
  SELECT value FROM generate_series(1,10) WHERE value<=5.5;
} {1 2 3 4 5}
do_execsql_test tabfunc01-6.4 {
  SELECT value FROM generate_series(1,10) WHERE value<=5.0;
} {1 2 3 4 5}
do_execsql_test tabfunc01-6.5 {
  SELECT value FROM generate_series(1,10) WHERE value>5.5;
} {6 7 8 9 10}
do_execsql_test tabfunc01-6.6 {
  SELECT value FROM generate_series(1,10) WHERE value>5.0;
} {6 7 8 9 10}
do_execsql_test tabfunc01-6.7 {
  SELECT value FROM generate_series(1,10) WHERE value>=5.5;
} {6 7 8 9 10}
do_execsql_test tabfunc01-6.8 {
  SELECT value FROM generate_series(1,10) WHERE value>=5.0;
} {5 6 7 8 9 10}
do_execsql_test tabfunc01-6.9 {
  SELECT value FROM generate_series(10,1,-1) WHERE value<5.5;
} {5 4 3 2 1}
do_execsql_test tabfunc01-6.10 {
  SELECT value FROM generate_series(10,1,-1) WHERE value<5.0;
} {4 3 2 1}
do_execsql_test tabfunc01-6.11 {
  SELECT value FROM generate_series(10,1,-1) WHERE value<=5.5;
} {5 4 3 2 1}
do_execsql_test tabfunc01-6.12 {
  SELECT value FROM generate_series(10,1,-1) WHERE value<=5.0;
} {5 4 3 2 1}
do_execsql_test tabfunc01-6.13 {
  SELECT value FROM generate_series(10,1,-1) WHERE value>5.5;
} {10 9 8 7 6}
do_execsql_test tabfunc01-6.14 {
  SELECT value FROM generate_series(10,1,-1) WHERE value>5.0;
} {10 9 8 7 6}
do_execsql_test tabfunc01-6.15 {
  SELECT value FROM generate_series(10,1,-1) WHERE value>=5.5;
} {10 9 8 7 6}
do_execsql_test tabfunc01-6.16 {
  SELECT value FROM generate_series(10,1,-1) WHERE value>=5.0;
} {10 9 8 7 6 5}
do_execsql_test tabfunc01-6.17 {
  SELECT value FROM generate_series(1,10) WHERE value==5.5;
} {}
do_execsql_test tabfunc01-6.18 {
  SELECT value FROM generate_series(1,10) WHERE value==5.0;
} {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]
  sqlite3_create_function db
  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://sqlite.org/src/info/2ae0c599b735d59e
# Verification of testtag-20230227a
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}}
}

#-----------------------------------------------------------------------------
# 2024-04-26  LIMIT and OFFSET passed into virtual tables
# https://sqlite.org/forum/forumpost/c243b8f856
#
do_execsql_test tabfunc01-900 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT 10 OFFSET 5;
} {6 7 8 9 10 101 102 103 104}
do_execsql_test tabfunc01-910 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT -1 OFFSET 5;
} {6 7 8 9 10 101 102 103 104}
do_execsql_test tabfunc01-920 {
  SELECT * FROM (
    SELECT * FROM generate_series(1,10)
    UNION ALL
    SELECT * FROM generate_series(101,104)
  ) LIMIT -1 OFFSET 0;
} {1 2 3 4 5 6 7 8 9 10 101 102 103 104}

# Also:  https://sqlite.org/forum/forumpost/ce1a06c2c8
#
do_execsql_test tabfunc01-930 {
  SELECT * FROM generate_series(9223372036854775807,
                                -9223372036854775808,
                                -9223372036854775808);
} {9223372036854775807 -1}
do_execsql_test tabfunc01-931 {
  SELECT * FROM generate_series(9223372036854775807,
                                -9223372036854775808,
                                -9223372036854775808)
   LIMIT 100 OFFSET 1;
} {-1}
do_execsql_test tabfunc01-932 {
  SELECT * FROM generate_series(9223372036854775807,
                                -9223372036854775808,
                                -9223372036854775808)
   LIMIT 100 OFFSET 2;
} {}
do_execsql_test tabfunc01-933 {
  SELECT * FROM generate_series(9223372036854775807,
                                -9223372036854775808,
                                -9223372036854775808)
   LIMIT 100 OFFSET 9223372036854775807;
} {}
do_execsql_test tabfunc01-940 {
  SELECT * FROM generate_series(1,11,2);
} {1 3 5 7 9 11}
do_execsql_test tabfunc01-941 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 4;
} {9 11}
do_execsql_test tabfunc01-942 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 5;
} {11}
do_execsql_test tabfunc01-943 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 6;
} {}
do_execsql_test tabfunc01-944 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 7;
} {}
do_execsql_test tabfunc01-945 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 9223372036854775807;
} {}
do_execsql_test tabfunc01-950 {
  SELECT * FROM generate_series(1,11,1) LIMIT 100 OFFSET 10;
} {11}
do_execsql_test tabfunc01-951 {
  SELECT * FROM generate_series(1,11,2) LIMIT 100 OFFSET 9223372036854775807;
} {}
do_execsql_test tabfunc01-952 {
  SELECT * FROM generate_series(11,1,-1) LIMIT 100 OFFSET 10;
} {1}
do_execsql_test tabfunc01-953 {
  SELECT * FROM generate_series(11,1,-1) LIMIT 100 OFFSET 9223372036854775807;
} {}
do_execsql_test tabfunc01-954 {
  SELECT * FROM generate_series(1,11,1) LIMIT 3 OFFSET 3;
} {4 5 6}


#-------------------------------------------------------------------------
# Forum post https://sqlite.org/forum/forumpost/e7c3ae1215
#
foreach {tn where res} {
  1000 "where value = 2" 2
  1010 "where value in (2)" 2
  1020 "where value in (select 2)" 2
  1030 "where value = 2 OR value = 4" {2 4}
  1040 "where value in (2, 4)" {2 4}
} {
  do_execsql_test $tn "
    SELECT value FROM generate_series(1, 5) $where
  " $res
}

do_execsql_test 1100 {
  select 1 as c_0
    from        
    generate_series(1, 1) as ref_3
    where (ref_3.value) in (select 1);
} {1}

# 2025-03-18 /forumpost/1e17219c88
# The generate_series() table-valued function is modified so that its
# rowid is always its value. That way it can be used on the RHS of a
# RIGHT JOIN.
#
do_execsql_test 1200 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(value INT);
  INSERT INTO t1 VALUES (1),(2),(3);
  SELECT t1.value, t2.value
    FROM t1 RIGHT JOIN generate_series(1,3,1) AS t2 USING(value);
} {1 1 2 2 3 3}

#--------------------------------------------------------------------------
# New test cases for generate_series associated with the refactor on branch
# serices-refactor circa 2025-09-27.
#
do_execsql_test 1300 {
  SELECT value
    FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
   WHERE value BETWEEN 1 AND 5;
} {2 4}
do_execsql_test 1301 {
  SELECT value
    FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
   WHERE value BETWEEN 0 AND 6;
} {0 2 4 6}
do_execsql_test 1302 {
  SELECT value
    FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
   WHERE value BETWEEN 0.5 AND 6.25;
} {2 4 6}
do_execsql_test 1303 {
  SELECT value
    FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
   WHERE value BETWEEN 9223372036854775803 AND 9223372036854775807
} {9223372036854775804 9223372036854775806}
do_execsql_test 1304 {
  SELECT value
    FROM generate_series(-9223372036854775808, 9223372036854775807, 2)
   WHERE value BETWEEN 9223372036854775803 AND 9223372036854775807
   ORDER BY value DESC;
} {9223372036854775806 9223372036854775804}

do_execsql_test 1310 {
  SELECT value FROM generate_series(0) LIMIT 5;
} {0 1 2 3 4}
do_execsql_test 1311 {
  SELECT value FROM generate_series(5) LIMIT 5;
} {5 6 7 8 9}
do_execsql_test 1312 {
  SELECT value FROM generate_series(0) WHERE stop=6;
} {0 1 2 3 4 5 6}
do_execsql_test 1313 {
  SELECT value FROM generate_series(1,10) WHERE step IS NULL;
} {}
do_execsql_test 1314 {
  SELECT value FROM generate_series(0xfffffffd);
} {4294967293 4294967294 4294967295}
do_execsql_test 1315 {
  SELECT value FROM generate_series(4,3,1);
} {}
do_execsql_test 1316 {
  SELECT value FROM generate_series(3,4,-1);
} {}

do_execsql_test 1320 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value=9.2234e18;
} {}
do_execsql_test 1321 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value=-9.2234e18;
} {}
do_execsql_test 1322 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value>9223372036854775807;
} {}
do_execsql_test 1323 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value>9223372036854775806;
} {9223372036854775807}
do_execsql_test 1324 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value>=9223372036854775807;
} {9223372036854775807}

do_execsql_test 1330 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value<-9223372036854775808;
} {}
do_execsql_test 1331 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value<=-9223372036854775808;
} {-9223372036854775808}
do_execsql_test 1332 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value<-9223372036854775807;
} {-9223372036854775808}
do_execsql_test 1333 {
  SELECT value FROM generate_series(-9223372036854775808,+9223372036854775807)
   WHERE value BETWEEN 4 AND 1;
} {}

do_execsql_test 1340 {
  SELECT value FROM generate_series(100,0,-10)
   WHERE value BETWEEN 33 AND 66;
} {60 50 40}
do_execsql_test 1341 {
  SELECT value FROM generate_series(100,0,-10)
   WHERE value BETWEEN 33 AND 60;
} {60 50 40}
do_execsql_test 1342 {
  SELECT value FROM generate_series(100,0,-10)
   WHERE value BETWEEN 33 AND 59;
} {50 40}
do_execsql_test 1343 {
  SELECT value
    FROM generate_series(-9223372036854760000,-9223372036854775808,-10000);
} {-9223372036854760000 -9223372036854770000}
do_execsql_test 1344 {
  SELECT value
    FROM generate_series(-9223372036854760000,-9223372036854775808,-10000)
   WHERE value < -9223372036854770001;
} {}
do_execsql_test 1345 {
  SELECT value
    FROM generate_series(9223372036854760000,9223372036854775807,10000);
} {9223372036854760000 9223372036854770000}
do_execsql_test 1346 {
  SELECT value
    FROM generate_series(9223372036854760000,9223372036854775807,10000)
   WHERE value > 9223372036854770001;
} {}

do_execsql_test 1350 {
  SELECT value FROM generate_series(100,0,-10)
   WHERE value BETWEEN 33 AND 38;
} {}
do_execsql_test 1351 {
  SELECT value FROM generate_series(0,100,+10)
   WHERE value BETWEEN 33 AND 38;
} {}

do_execsql_test 1360 {
  SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808);
} {0 -9223372036854775808}
do_execsql_test 1361 {
  SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
  LIMIT 1 OFFSET 0;
} {0}
do_execsql_test 1362 {
  SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
   ORDER BY value ASC;
} {-9223372036854775808 0}
do_execsql_test 1363 {
  SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
   ORDER BY value ASC LIMIT 10 OFFSET 1;
} {0}
do_execsql_test 1364 {
  SELECT * FROM generate_series(0,-9223372036854775808,-9223372036854775808)
   ORDER BY value ASC LIMIT 10 OFFSET 40000000;
} {}

do_execsql_test 1370 {
  SELECT * FROM generate_series(0,0,0);
} {}

# Forum post 2026-05-09T07:41:32Z
set subtest 1
foreach bound {
  9007199254740991.0
  9007199254740991.1
  9007199254740991.5
  9007199254740991.9
  9007199254740992.0
  9007199254740992.1
  9007199254740992.5
  9007199254740992.9
  9007199254740993.0
  9007199254740993.1
  9007199254740993.5
} {
  foreach {range boundsign} {
    (+9007199254740990,+9007199254740995)  +
    (-9007199254740995,-9007199254740990)  -
  } {
    set bx $boundsign$bound
    foreach op {< > <= >=} {
      do_execsql_test 1380.$subtest "
         SELECT 'value $op $bx' WHERE (
            SELECT group_concat(value) FROM generate_series $range
             WHERE value $op $bx)<>(
            SELECT group_concat(value) FROM generate_series $range
             WHERE +value $op $bx);
      " {}
      incr subtest
    }
  }
}

reset_db
load_static_extension db series
do_execsql_test 1400 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y);
}
do_catchsql_test 1410 {
  SELECT x, y, value
  FROM (t1 RIGHT JOIN generate_series(t2.y,5) AS value) JOIN t2;
} {1 {table-function argument references tables to its right}}
do_catchsql_test 1420 {
  SELECT x, y, value 
  FROM t2 JOIN (t1 RIGHT JOIN generate_series(t2.y,5) AS value) 
} {1 {no such column: t2.y}}

# Bug 2026-06-15T04:37:48Z
do_execsql_test 1500 {
  SELECT value FROM generate_series(1) WHERE value>1e19 LIMIT 3;
} {}
do_execsql_test 1501 {
  SELECT value FROM generate_series(1) WHERE value>=1e19 LIMIT 3;
} {}
do_execsql_test 1502 {
  SELECT value FROM generate_series(1) WHERE value<-1e19 LIMIT 3;
} {}
do_execsql_test 1503 {
  SELECT value FROM generate_series(1) WHERE value<=-1e19 LIMIT 3;
} {}
do_execsql_test 1504 {
  SELECT value FROM generate_series(1,9223372036854775807)
   WHERE value>=9223372036854775807.0
   LIMIT 3;
} {9223372036854775807}
do_execsql_test 1505 {
  SELECT value FROM generate_series(1,9223372036854775807)
   WHERE value>9223372036854775807.0
   LIMIT 3;
} {}

# NB: Large floating point values can not convert to integers exactly.
# This affects subsequent results.
#
do_execsql_test 1506 {
  SELECT 9223372036854774784 = 9223372036854775000.0;
} {1}

do_execsql_test 1507 {
  SELECT value FROM generate_series(1,9223372036854775807)
   WHERE value>=9223372036854775000.0
   LIMIT 3;
} {9223372036854774784 9223372036854774785 9223372036854774786}
do_execsql_test 1508 {
  SELECT value FROM generate_series(1,9223372036854775807)
   WHERE value>9223372036854775000.0
   LIMIT 3;
} {9223372036854774785 9223372036854774786 9223372036854774787}

do_execsql_test 1510 {
  SELECT value FROM generate_series(-9223372036854775808,1)
   WHERE value>=-9223372036854775000.0
   LIMIT 3;
} {-9223372036854774784 -9223372036854774783 -9223372036854774782}
do_execsql_test 1511 {
  SELECT value FROM generate_series(-9223372036854775808,1)
   WHERE value>-9223372036854775000.0
   LIMIT 3;
} {-9223372036854774783 -9223372036854774782 -9223372036854774781}

do_execsql_test 1520 {
  SELECT value FROM generate_series(9223372036854774784)
   WHERE value<=9223372036854775000.0
   LIMIT 3;
} {9223372036854774784}
do_execsql_test 1521 {
  SELECT value FROM generate_series(9223372036854774784)
   WHERE value<9223372036854775000.0
   LIMIT 3;
} {}
do_execsql_test 1522 {
  SELECT value FROM generate_series(9223372036854775805,9223372036854775807)
   WHERE value<=9223372036854775807.0
   LIMIT 3;
} {9223372036854775805 9223372036854775806 9223372036854775807}
do_execsql_test 1522ck {
  SELECT value FROM generate_series(9223372036854775805,9223372036854775807)
   WHERE +value<=9223372036854775807.0
   LIMIT 3;
} {9223372036854775805 9223372036854775806 9223372036854775807}
do_execsql_test 1523 {
  SELECT value FROM generate_series(9223372036854775805,9223372036854775807)
   WHERE value<9223372036854775807.0
   LIMIT 3;
} {9223372036854775805 9223372036854775806 9223372036854775807}
do_execsql_test 1523ck {
  SELECT value FROM generate_series(9223372036854775805,9223372036854775807)
   WHERE +value<9223372036854775807.0
   LIMIT 3;
} {9223372036854775805 9223372036854775806 9223372036854775807}


# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr

finish_test