SQLite

Check-in [0b1dbd60]
Login

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

Overview
Comment:For expressions like (x, y) IN (SELECT ...) where the SELECT uses window-functions, require that all columns on the LHS be indexed before an index can be used. Fix for [d9ed4ebe].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0b1dbd60f5db3abe2097dbc0b6de9671685ca5eaf7d3fc8e3f87ff5065a9d114
User & Date: dan 2019-12-23 14:20:46
References
2019-12-23
14:36 Closed ticket [3ab4a9f0]: Debug assertion sqlite3VdbeExec: Assertion `pC!=0' failed plus 6 other changes (artifact: 515462bc user: dan)
Context
2019-12-23
15:17
Fix a case in which SQLite could fail to identify "x BETWEEN ? AND ?" being true as implying that x is not null. Ticket [dfd66334]. (check-in: 2f179749 user: dan tags: trunk)
14:20
For expressions like (x, y) IN (SELECT ...) where the SELECT uses window-functions, require that all columns on the LHS be indexed before an index can be used. Fix for [d9ed4ebe]. (check-in: 0b1dbd60 user: dan tags: trunk)
13:24
Fix a shift-overflow problem in yesterday's check-in [36fdeb4f0a66970a] that OSSFuzz helpfully discovered overnight. Thanks Google. (check-in: bff38e2b user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

3802
3803
3804
3805
3806
3807
3808

3809
3810
3811
3812
3813
3814
3815
** Duplicate ephemeral cursors are used for self-joins of materialized views.
*/
case OP_OpenDup: {
  VdbeCursor *pOrig;    /* The original cursor to be duplicated */
  VdbeCursor *pCx;      /* The new cursor */

  pOrig = p->apCsr[pOp->p2];

  assert( pOrig->pBtx!=0 );  /* Only ephemeral cursors can be duplicated */

  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->isEphemeral = 1;
  pCx->pKeyInfo = pOrig->pKeyInfo;







>







3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
** Duplicate ephemeral cursors are used for self-joins of materialized views.
*/
case OP_OpenDup: {
  VdbeCursor *pOrig;    /* The original cursor to be duplicated */
  VdbeCursor *pCx;      /* The new cursor */

  pOrig = p->apCsr[pOp->p2];
  assert( pOrig );
  assert( pOrig->pBtx!=0 );  /* Only ephemeral cursors can be duplicated */

  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->isEphemeral = 1;
  pCx->pKeyInfo = pOrig->pKeyInfo;

Changes to src/wherecode.c.

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
  Parse *pParse,        /* The parsing context */
  int iEq,              /* Look at loop terms starting here */
  WhereLoop *pLoop,     /* The current loop */
  Expr *pX              /* The IN expression to be reduced */
){
  sqlite3 *db = pParse->db;
  Expr *pNew;
#ifndef SQLITE_OMIT_WINDOWFUNC
  /* The SELECT statement at pX->x.pSelect has already been resolved. This
  ** means that its window functions have already been identified and 
  ** linked into the Select.pWin list. However, if there are multiple 
  ** window functions and they do not all use the same window frame, only
  ** those that use the same window frame as the first resolved are listed
  ** in Select.pWin. Any others are delegated to sub-selects created by the 
  ** call to sqlite3WindowRewrite() made when coding the SELECT statement.
  ** But - sqlite3WindowRewrite() is a no-op if Select.pWin==0. And if 
  ** removing the unindexable terms happens to remove all window functions
  ** in the Select.pWin list, then Select.pWin ends up set to NULL - meaning
  ** that the other window functions are never processed. To work around
  ** this, ensure that sqlite3WindowRewrite() has been called to create the
  ** required sub-selects before the unindexable terms are removed. See
  ** ticket #f00d096ca.  */
  if( sqlite3WindowRewrite(pParse, pX->x.pSelect) ) return 0;
#endif
  pNew = sqlite3ExprDup(db, pX, 0);
  if( db->mallocFailed==0 ){
    ExprList *pOrigRhs = pNew->x.pSelect->pEList;  /* Original unmodified RHS */
    ExprList *pOrigLhs = pNew->pLeft->x.pList;     /* Original unmodified LHS */
    ExprList *pRhs = 0;         /* New RHS after modifications */
    ExprList *pLhs = 0;         /* New LHS after mods */
    int i;                      /* Loop counter */







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







412
413
414
415
416
417
418

















419
420
421
422
423
424
425
  Parse *pParse,        /* The parsing context */
  int iEq,              /* Look at loop terms starting here */
  WhereLoop *pLoop,     /* The current loop */
  Expr *pX              /* The IN expression to be reduced */
){
  sqlite3 *db = pParse->db;
  Expr *pNew;

















  pNew = sqlite3ExprDup(db, pX, 0);
  if( db->mallocFailed==0 ){
    ExprList *pOrigRhs = pNew->x.pSelect->pEList;  /* Original unmodified RHS */
    ExprList *pOrigLhs = pNew->pLeft->x.pList;     /* Original unmodified LHS */
    ExprList *pRhs = 0;         /* New RHS after modifications */
    ExprList *pLhs = 0;         /* New LHS after mods */
    int i;                      /* Loop counter */

Changes to src/whereexpr.c.

1351
1352
1353
1354
1355
1356
1357
1358

1359
1360
1361
1362



1363
1364
1365
1366
1367
1368
1369

  /* If there is a vector IN term - e.g. "(a, b) IN (SELECT ...)" - create
  ** a virtual term for each vector component. The expression object
  ** used by each such virtual term is pExpr (the full vector IN(...) 
  ** expression). The WhereTerm.iField variable identifies the index within
  ** the vector on the LHS that the virtual term represents.
  **
  ** This only works if the RHS is a simple SELECT, not a compound

  */
  if( pWC->op==TK_AND && pExpr->op==TK_IN && pTerm->iField==0
   && pExpr->pLeft->op==TK_VECTOR
   && pExpr->x.pSelect->pPrior==0



  ){
    int i;
    for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
      int idxNew;
      idxNew = whereClauseInsert(pWC, pExpr, TERM_VIRTUAL);
      pWC->a[idxNew].iField = i+1;
      exprAnalyze(pSrc, pWC, idxNew);







|
>




>
>
>







1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373

  /* If there is a vector IN term - e.g. "(a, b) IN (SELECT ...)" - create
  ** a virtual term for each vector component. The expression object
  ** used by each such virtual term is pExpr (the full vector IN(...) 
  ** expression). The WhereTerm.iField variable identifies the index within
  ** the vector on the LHS that the virtual term represents.
  **
  ** This only works if the RHS is a simple SELECT (not a compound) that does
  ** not use window functions.
  */
  if( pWC->op==TK_AND && pExpr->op==TK_IN && pTerm->iField==0
   && pExpr->pLeft->op==TK_VECTOR
   && pExpr->x.pSelect->pPrior==0
#ifndef SQLITE_OMIT_WINDOWFUNC
   && pExpr->x.pSelect->pWin==0
#endif
  ){
    int i;
    for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
      int idxNew;
      idxNew = whereClauseInsert(pWC, pExpr, TERM_VIRTUAL);
      pWC->a[idxNew].iField = i+1;
      exprAnalyze(pSrc, pWC, idxNew);

Changes to test/window1.test.

1342
1343
1344
1345
1346
1347
1348











1349
1350
} {0}
do_execsql_test 38.20 {
  SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
} {1 1.0}
do_execsql_test 38.30 {
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
} {1}












finish_test







>
>
>
>
>
>
>
>
>
>
>


1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
} {0}
do_execsql_test 38.20 {
  SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
} {1 1.0}
do_execsql_test 38.30 {
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
} {1}

reset_db
do_execsql_test 39.1 {
  CREATE TABLE t0(c0 UNIQUE);
}
do_execsql_test 39.2 {
  SELECT FIRST_VALUE(0) OVER();
} {0}
do_execsql_test 39.3 {
  SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0);
}

finish_test