Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the generated_series() table-valued-function to respond to LIMIT and OFFSET. Use this to add new test cases for LIMIT and OFFSET on virtual tables in a compound SELECT. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | vtab-limit-fix |
Files: | files | file ages | folders |
SHA3-256: |
408d47ecaa3b906d0886f76a22b76339 |
User & Date: | drh 2024-04-26 13:30:48 |
Context
2024-04-26
| ||
13:38 | Fix handling of LIMIT and OFFSET in virtual tables that are part of a compound SELECT. (check-in: 40421c1c user: drh tags: vtab-limit-fix) | |
13:30 | Enhance the generated_series() table-valued-function to respond to LIMIT and OFFSET. Use this to add new test cases for LIMIT and OFFSET on virtual tables in a compound SELECT. (check-in: 408d47ec user: drh tags: vtab-limit-fix) | |
12:01 | Add test demonstrating the problem at forum post c243b8f856. No fix yet. (check-in: 1685495c user: dan tags: vtab-limit-fix) | |
Changes
Changes to ext/misc/series.c.
︙ | ︙ | |||
372 373 374 375 376 377 378 | ** once prior to any call to seriesColumn() or seriesRowid() or ** seriesEof(). ** ** The query plan selected by seriesBestIndex is passed in the idxNum ** parameter. (idxStr is not used in this implementation.) idxNum ** is a bitmask showing which constraints are available: ** | | | | < < | | > > | | | > > > > > > > > > > > > > > > > > > | | | | | > > > | | > > > > > > > > > > > > > > > > | | > | 372 373 374 375 376 377 378 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 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 | ** once prior to any call to seriesColumn() or seriesRowid() or ** seriesEof(). ** ** The query plan selected by seriesBestIndex is passed in the idxNum ** parameter. (idxStr is not used in this implementation.) idxNum ** is a bitmask showing which constraints are available: ** ** 0x01: start=VALUE ** 0x02: stop=VALUE ** 0x04: step=VALUE ** 0x08: descending order ** 0x10: ascending order ** 0x20: LIMIT VALUE ** 0x40: OFFSET VALUE ** ** This routine should initialize the cursor and position it so that it ** is pointing at the first row, or pointing off the end of the table ** (so that seriesEof() will return true) if the table is empty. */ static int seriesFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStrUnused, int argc, sqlite3_value **argv ){ series_cursor *pCur = (series_cursor *)pVtabCursor; int i = 0; (void)idxStrUnused; if( idxNum & 0x01 ){ pCur->ss.iBase = sqlite3_value_int64(argv[i++]); }else{ pCur->ss.iBase = 0; } if( idxNum & 0x02 ){ pCur->ss.iTerm = sqlite3_value_int64(argv[i++]); }else{ pCur->ss.iTerm = 0xffffffff; } if( idxNum & 0x04 ){ pCur->ss.iStep = sqlite3_value_int64(argv[i++]); if( pCur->ss.iStep==0 ){ pCur->ss.iStep = 1; }else if( pCur->ss.iStep<0 ){ if( (idxNum & 16)==0 ) idxNum |= 8; } }else{ pCur->ss.iStep = 1; } if( idxNum & 0x20 ){ sqlite3_int64 iLimit = sqlite3_value_int64(argv[i++]); sqlite3_int64 iTerm; if( idxNum & 0x40 ){ sqlite3_int64 iOffset = sqlite3_value_int64(argv[i++]); if( iOffset>0 ){ pCur->ss.iBase += pCur->ss.iStep*iOffset; } } if( iLimit>=0 ){ iTerm = pCur->ss.iBase + (iLimit - 1)*pCur->ss.iStep; if( pCur->ss.iStep<0 ){ if( iTerm>pCur->ss.iTerm ) pCur->ss.iTerm = iTerm; }else{ if( iTerm<pCur->ss.iTerm ) pCur->ss.iTerm = iTerm; } } } for(i=0; i<argc; i++){ if( sqlite3_value_type(argv[i])==SQLITE_NULL ){ /* If any of the constraints have a NULL value, then return no rows. ** See ticket https://www.sqlite.org/src/info/fac496b61722daf2 */ pCur->ss.iBase = 1; pCur->ss.iTerm = 0; pCur->ss.iStep = 1; break; } } if( idxNum & 0x08 ){ pCur->ss.isReversing = pCur->ss.iStep > 0; }else{ pCur->ss.isReversing = pCur->ss.iStep < 0; } setupSequence( &pCur->ss ); return SQLITE_OK; } /* ** SQLite will invoke this method one or more times while planning a query ** that uses the generate_series virtual table. This routine needs to create ** a query plan for each invocation and compute an estimated cost for that ** plan. ** ** In this implementation idxNum is used to represent the ** query plan. idxStr is unused. ** ** The query plan is represented by bits in idxNum: ** ** 0x01 start = $value -- constraint exists ** 0x02 stop = $value -- constraint exists ** 0x04 step = $value -- constraint exists ** 0x08 output is in descending order ** 0x10 output is in ascending order ** 0x20 LIMIT $value -- constraint exists ** 0x40 OFFSET $value -- constraint exists */ static int seriesBestIndex( sqlite3_vtab *pVTab, sqlite3_index_info *pIdxInfo ){ int i, j; /* Loop over constraints */ int idxNum = 0; /* The query plan bitmask */ int bStartSeen = 0; /* EQ constraint seen on the START column */ int unusableMask = 0; /* Mask of unusable constraints */ int nArg = 0; /* Number of arguments that seriesFilter() expects */ int aIdx[5]; /* Constraints on start, stop, step, LIMIT, OFFSET */ const struct sqlite3_index_constraint *pConstraint; /* This implementation assumes that the start, stop, and step columns ** are the last three columns in the virtual table. */ assert( SERIES_COLUMN_STOP == SERIES_COLUMN_START+1 ); assert( SERIES_COLUMN_STEP == SERIES_COLUMN_START+2 ); aIdx[0] = aIdx[1] = aIdx[2] = aIdx[3] = aIdx[4] = -1; pConstraint = pIdxInfo->aConstraint; for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){ int iCol; /* 0 for start, 1 for stop, 2 for step */ int iMask; /* bitmask for those column */ int op = pConstraint->op; if( op!=SQLITE_INDEX_CONSTRAINT_EQ ){ if( op==SQLITE_INDEX_CONSTRAINT_LIMIT ){ aIdx[3] = i; idxNum |= 0x20; }else if( op==SQLITE_INDEX_CONSTRAINT_OFFSET ){ aIdx[4] = i; idxNum |= 0x40; } continue; } if( pConstraint->iColumn<SERIES_COLUMN_START ) continue; iCol = pConstraint->iColumn - SERIES_COLUMN_START; assert( iCol>=0 && iCol<=2 ); iMask = 1 << iCol; if( iCol==0 ) bStartSeen = 1; if( pConstraint->usable==0 ){ unusableMask |= iMask; continue; }else if( pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){ idxNum |= iMask; aIdx[iCol] = i; } } if( aIdx[3]==0 ){ /* Ignore OFFSET if LIMIT is omitted */ idxNum &= ~0x60; aIdx[4] = 0; } for(i=0; i<5; i++){ if( (j = aIdx[i])>=0 ){ pIdxInfo->aConstraintUsage[j].argvIndex = ++nArg; pIdxInfo->aConstraintUsage[j].omit = !SQLITE_SERIES_CONSTRAINT_VERIFY || i>=3; } } /* The current generate_column() implementation requires at least one ** argument (the START value). Legacy versions assumed START=0 if the ** first argument was omitted. Compile with -DZERO_ARGUMENT_GENERATE_SERIES ** to obtain the legacy behavior */ #ifndef ZERO_ARGUMENT_GENERATE_SERIES |
︙ | ︙ | |||
513 514 515 516 517 518 519 | if( (idxNum & 3)==3 ){ /* Both start= and stop= boundaries are available. This is the ** the preferred case */ pIdxInfo->estimatedCost = (double)(2 - ((idxNum&4)!=0)); pIdxInfo->estimatedRows = 1000; if( pIdxInfo->nOrderBy>=1 && pIdxInfo->aOrderBy[0].iColumn==0 ){ if( pIdxInfo->aOrderBy[0].desc ){ | | | | 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 | if( (idxNum & 3)==3 ){ /* Both start= and stop= boundaries are available. This is the ** the preferred case */ pIdxInfo->estimatedCost = (double)(2 - ((idxNum&4)!=0)); pIdxInfo->estimatedRows = 1000; if( pIdxInfo->nOrderBy>=1 && pIdxInfo->aOrderBy[0].iColumn==0 ){ if( pIdxInfo->aOrderBy[0].desc ){ idxNum |= 0x08; }else{ idxNum |= 0x10; } pIdxInfo->orderByConsumed = 1; } }else{ /* If either boundary is missing, we have to generate a huge span ** of numbers. Make this case very expensive so that the query ** planner will work hard to avoid it. */ |
︙ | ︙ |
Changes to test/tabfunc01.test.
︙ | ︙ | |||
317 318 319 320 321 322 323 324 325 326 327 328 329 330 | } {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}} } # Free up memory allocations intarray_addr int64array_addr doublearray_addr textarray_addr | > > > > > > > > > > > > > > > > > > > > > > > > > > | 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 | } {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} # Free up memory allocations intarray_addr int64array_addr doublearray_addr textarray_addr |
︙ | ︙ |