SQLite

Check-in [77db4d85e7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change the generate_series() table-valued function so that its rowid is just an alias for its value. This allows it to be used as the RHS operand of a RIGHT JOIN. This fixes the issue raised by forum post 1e17219c88.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 77db4d85e70fbf358ae2321c2601966666bdb4d971d7c113ce30a3e541458ee8
User & Date: drh 2025-03-18 20:15:16.250
Context
2025-03-19
11:53
Avoid running test cases involving ANSI control characters or Unicode on Windows in a slave interpreter, as that combination does not work. (check-in: c7fd71c77f user: stephan tags: trunk)
10:14
Merge trunk into the cygwin-fixes branch. (check-in: 34eadd374b user: stephan tags: cygwin-fixes)
2025-03-18
20:28
Fix two obscure logic problems that cause incorrect answers, found by a third-party fuzzer. (check-in: 1a8f763c31 user: drh tags: branch-3.49)
20:15
Change the generate_series() table-valued function so that its rowid is just an alias for its value. This allows it to be used as the RHS operand of a RIGHT JOIN. This fixes the issue raised by forum post 1e17219c88. (check-in: 77db4d85e7 user: drh tags: trunk)
19:21
Fix a problem that could occur when the RHS of an IN operator was a compound SELECT featuring an ORDER BY on a subquery that was flattened into one of the component SELECTs introduced by [baa83b460c677c21]. Forum post /forumpost/1e17219c88. (check-in: 7101ccd533 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/series.c.
︙︙
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

**     CREATE TABLE generate_series(
**       value,
**       start HIDDEN,
**       stop HIDDEN,
**       step HIDDEN
**     );
**
** The virtual table also has a rowid, logically equivalent to n+1 where
** "n" is the ascending integer in the aforesaid production definition.
**
** Function arguments in queries against this virtual table are translated
** into equality constraints against successive hidden columns.  In other
** words, the following pairs of queries are equivalent to each other:
**
**    SELECT * FROM generate_series(0,100,5);
**    SELECT * FROM generate_series WHERE start=0 AND stop=100 AND step=5;








|
<








56
57
58
59
60
61
62
63

64
65
66
67
68
69
70

**     CREATE TABLE generate_series(
**       value,
**       start HIDDEN,
**       stop HIDDEN,
**       step HIDDEN
**     );
**
** The virtual table also has a rowid which is an alias for the value.

**
** Function arguments in queries against this virtual table are translated
** into equality constraints against successive hidden columns.  In other
** words, the following pairs of queries are equivalent to each other:
**
**    SELECT * FROM generate_series(0,100,5);
**    SELECT * FROM generate_series WHERE start=0 AND stop=100 AND step=5;
︙︙
272
273
274
275
276
277
278

279
280
281
282
283
284
285

  sqlite3_vtab **ppVtab,
  char **pzErrUnused
){
  sqlite3_vtab *pNew;
  int rc;

/* Column numbers */

#define SERIES_COLUMN_VALUE 0
#define SERIES_COLUMN_START 1
#define SERIES_COLUMN_STOP  2
#define SERIES_COLUMN_STEP  3

  (void)pUnused;
  (void)argcUnused;








>








271
272
273
274
275
276
277
278
279
280
281
282
283
284
285

  sqlite3_vtab **ppVtab,
  char **pzErrUnused
){
  sqlite3_vtab *pNew;
  int rc;

/* Column numbers */
#define SERIES_COLUMN_ROWID (-1)
#define SERIES_COLUMN_VALUE 0
#define SERIES_COLUMN_START 1
#define SERIES_COLUMN_STOP  2
#define SERIES_COLUMN_STEP  3

  (void)pUnused;
  (void)argcUnused;
︙︙
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379

#ifndef LARGEST_UINT64
#define LARGEST_INT64  (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
#define LARGEST_UINT64 (0xffffffff|(((sqlite3_uint64)0xffffffff)<<32))
#define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
#endif

/*
** Return the rowid for the current row, logically equivalent to n+1 where
** "n" is the ascending integer in the aforesaid production definition.
*/
static int seriesRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
  series_cursor *pCur = (series_cursor*)cur;
  sqlite3_uint64 n = pCur->ss.uSeqIndexNow;
  *pRowid = (sqlite3_int64)((n<LARGEST_UINT64)? n+1 : 0);
  return SQLITE_OK;
}

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/








|
<



|
<








359
360
361
362
363
364
365
366

367
368
369
370

371
372
373
374
375
376
377

#ifndef LARGEST_UINT64
#define LARGEST_INT64  (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
#define LARGEST_UINT64 (0xffffffff|(((sqlite3_uint64)0xffffffff)<<32))
#define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
#endif

/*
** The rowid is the same as the value.

*/
static int seriesRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
  series_cursor *pCur = (series_cursor*)cur;
  *pRowid = pCur->ss.iValueNow;

  return SQLITE_OK;
}

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/
︙︙
653
654
655
656
657
658
659
660



661
662
663
664
665
666
667

        assert( op==SQLITE_INDEX_CONSTRAINT_OFFSET );
        aIdx[4] = i;
        idxNum |= 0x40;
      }
      continue;
    }
    if( pConstraint->iColumn<SERIES_COLUMN_START ){
      if( pConstraint->iColumn==SERIES_COLUMN_VALUE && pConstraint->usable ){



        switch( op ){
          case SQLITE_INDEX_CONSTRAINT_EQ:
          case SQLITE_INDEX_CONSTRAINT_IS: {
            idxNum |=  0x0080;
            idxNum &= ~0x3300;
            aIdx[5] = i;
            aIdx[6] = -1;








|
>
>
>








651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668

        assert( op==SQLITE_INDEX_CONSTRAINT_OFFSET );
        aIdx[4] = i;
        idxNum |= 0x40;
      }
      continue;
    }
    if( pConstraint->iColumn<SERIES_COLUMN_START ){
      if( (pConstraint->iColumn==SERIES_COLUMN_VALUE ||
           pConstraint->iColumn==SERIES_COLUMN_ROWID)
       && pConstraint->usable
      ){
        switch( op ){
          case SQLITE_INDEX_CONSTRAINT_EQ:
          case SQLITE_INDEX_CONSTRAINT_IS: {
            idxNum |=  0x0080;
            idxNum &= ~0x3300;
            aIdx[5] = i;
            aIdx[6] = -1;
︙︙
Changes to test/shell2.test.
︙︙
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243

244
245
246
247
248
249
250
251

ifcapable vtab {
# Verify that generate_series stays sane near 64-bit range boundaries.
# See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280
do_test shell2-1.4.10 {
 set res [catchcmd :memory: [string trim {
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
 SELECT avg(rowid),min(value),max(value) FROM generate_series(
  -9223372036854775808,9223372036854775807,1085102592571150095);
 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
  9223372036854775807);
 SELECT value,rowid FROM generate_series(-4611686018427387904,
  4611686018427387904, 4611686018427387904) ORDER BY value DESC;
 SELECT * FROM generate_series(0,-2,-1);
 SELECT * FROM generate_series(0,-2);
 SELECT * FROM generate_series(0,2) LIMIT 3;}]]
} {0 {9223372036854775807
9223372036854775807
9.5|-9223372036854775808|9223372036854775807
-9223372036854775808
-1
9223372036854775806
4611686018427387904|3
0|2

-4611686018427387904|1
0
-1
-2
0
1
2}}
}  ;# ifcapable vtab








|



|






|



|
<
>
|








220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

243
244
245
246
247
248
249
250
251

ifcapable vtab {
# Verify that generate_series stays sane near 64-bit range boundaries.
# See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280
do_test shell2-1.4.10 {
 set res [catchcmd :memory: [string trim {
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
 SELECT avg(value),min(value),max(value) FROM generate_series(
  -9223372036854775808,9223372036854775807,1085102592571150095);
 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
  9223372036854775807);
 SELECT value FROM generate_series(-4611686018427387904,
  4611686018427387904, 4611686018427387904) ORDER BY value DESC;
 SELECT * FROM generate_series(0,-2,-1);
 SELECT * FROM generate_series(0,-2);
 SELECT * FROM generate_series(0,2) LIMIT 3;}]]
} {0 {9223372036854775807
9223372036854775807
-0.5|-9223372036854775808|9223372036854775807
-9223372036854775808
-1
9223372036854775806
4611686018427387904

0
-4611686018427387904
0
-1
-2
0
1
2}}
}  ;# ifcapable vtab
︙︙
Changes to test/tabfunc01.test.
︙︙
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

} {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;
} {7 30 6 25 5 20 4 15 3 10 2 5 1 0}
do_execsql_test tabfunc01-1.10 {
  SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
} {7 30 6 25 5 20 4 15 3 10 2 5 1 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);








|


|








57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

} {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);
︙︙
379
380
381
382
383
384
385




386







387
388
389
390
391
392
393
394

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}














# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr

finish_test








>
>
>
>
|
>
>
>
>
>
>
>









379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405

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}

# Free up memory allocations
intarray_addr
int64array_addr
doublearray_addr
textarray_addr

finish_test