/ Check-in [4dfebff2]
Login
☰Home Timeline Branches

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

Overview
Comment:Change the way "(a, b) = (SELECT *)" expressions are handled in where.c if there is an index on one of the columns only.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 4dfebff2924f46284d5b9cda69175f79b29d6028
User & Date: dan 2016-07-29 18:12:12
Context
2016-07-29
20:58
Fix some issues with vector range constraints and the column cache. Also vector range constraints and rowid columns. (check-in: 42607366 user: dan tags: rowvalue)
18:12
Change the way "(a, b) = (SELECT *)" expressions are handled in where.c if there is an index on one of the columns only. (check-in: 4dfebff2 user: dan tags: rowvalue)
2016-07-28
19:47
Fix further issues with multi-column IN(...) operators. Also some error handling cases surrounding row values. (check-in: cc3f6542 user: dan tags: rowvalue)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c. 

3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533

    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "invalid use of row value");
      break;
    }

    case TK_SELECT_COLUMN: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );
      assert( pLeft->op==TK_SELECT || pLeft->op==TK_REGISTER );
      if( pLeft->op==TK_SELECT ){
        pLeft->iTable = sqlite3CodeSubselect(pParse, pLeft, 0, 0);
        pLeft->op = TK_REGISTER;
      }
      inReg = pLeft->iTable + pExpr->iColumn;
      break;
    }

    /*
    ** Form A:
    **   CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **
    ** Form B:
    **   CASE WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **








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








3508
3509
3510
3511
3512
3513
3514












3515
3516
3517
3518
3519
3520
3521

    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "invalid use of row value");
      break;
    }













    /*
    ** Form A:
    **   CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **
    ** Form B:
    **   CASE WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **

Changes to src/sqliteInt.h. 

3932
3933
3934
3935
3936
3937
3938
3939

3940
3941
3942
3943
3944
3945
3946

void sqlite3RootPageMoved(sqlite3*, int, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);

void sqlite3SelectPrep(Parse*, Select*, NameContext*);
void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
int sqlite3ResolveExprListNames(NameContext*, ExprList*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);








|
>








3932
3933
3934
3935
3936
3937
3938
3939
3940
3941
3942
3943
3944
3945
3946
3947

void sqlite3RootPageMoved(sqlite3*, int, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse*, Expr *, int, int);
int sqlite3ExprCheckIN(Parse*, Expr*);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
int sqlite3ResolveExprListNames(NameContext*, ExprList*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);

Changes to src/wherecode.c. 

355
356
357
358
359
360
361























362

363
364
365
366
367
368
369
....
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
....
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604

  Expr *pX = pTerm->pExpr;
  Vdbe *v = pParse->pVdbe;
  int iReg;                  /* Register holding results */

  assert( pLevel->pWLoop->aLTerm[iEq]==pTerm );
  assert( iTarget>0 );
  if( pX->op==TK_EQ || pX->op==TK_IS ){























    iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);

  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;
................................................................................
      sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
      testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
      testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
      testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
    }

    /* Disable the start and end range terms if possible */
    /* disableTerm(pLevel, pRangeStart); */
    /* disableTerm(pLevel, pRangeEnd); */

    /* Seek the table cursor, if required */
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0 ){
        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
................................................................................
    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    u16 wctrlFlags;                    /* Flags for sub-WHERE clause */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
    Table *pTab = pTabItem->pTab;
   
    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->eOperator & WO_OR );
    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
    pOrWc = &pTerm->u.pOrInfo->wc;
    pLevel->op = OP_Return;
    pLevel->p1 = regReturn;








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







 







<
<
<
<







 







|








355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
....
1509
1510
1511
1512
1513
1514
1515




1516
1517
1518
1519
1520
1521
1522
....
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624

  Expr *pX = pTerm->pExpr;
  Vdbe *v = pParse->pVdbe;
  int iReg;                  /* Register holding results */

  assert( pLevel->pWLoop->aLTerm[iEq]==pTerm );
  assert( iTarget>0 );
  if( pX->op==TK_EQ || pX->op==TK_IS ){
    Expr *pRight = pX->pRight;
    if( pRight->op==TK_SELECT_COLUMN ){
      /* This case occurs for expressions like "(a, b) == (SELECT ...)". */
      WhereLoop *pLoop = pLevel->pWLoop;
      int i;
      Expr *pSub = pRight->pLeft;
      assert( pSub->op==TK_SELECT );
      for(i=pLoop->nSkip; i<iEq; i++){
        Expr *pExpr = pLoop->aLTerm[i]->pExpr->pRight;
        if( pExpr && pExpr->op==TK_SELECT_COLUMN && pExpr->pLeft==pSub ) break;
      }

      if( i==iEq ){
        iReg = sqlite3CodeSubselect(pParse, pSub, 0, 0);
        for(/*no-op*/; i<pLoop->nLTerm; i++){
          Expr *pExpr = pLoop->aLTerm[i]->pExpr->pRight;
          if( pExpr && pExpr->op==TK_SELECT_COLUMN && pExpr->pLeft==pSub ){
            sqlite3VdbeAddOp2(v, OP_Copy, iReg+pExpr->iColumn, iTarget-iEq+i);
          }
        }
      }
      iReg = iTarget;
    }else{
      iReg = sqlite3ExprCodeTarget(pParse, pRight, iTarget);
    }
  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;
................................................................................
      sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
      testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
      testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
      testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
    }





    /* Seek the table cursor, if required */
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0 ){
        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
................................................................................
    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    u16 wctrlFlags;                    /* Flags for sub-WHERE clause */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
    Table *pTab = pTabItem->pTab;

    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->eOperator & WO_OR );
    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
    pOrWc = &pTerm->u.pOrInfo->wc;
    pLevel->op = OP_Return;
    pLevel->p1 = regReturn;

Changes to src/whereexpr.c. 

958
959
960
961
962
963
964
965

966
967
968
969
970
971
972
...
975
976
977
978
979
980
981

982
983
984
985
986
987
988

  pTerm->eOperator = 0;
  if( allowedOp(op) ){
    int iCur, iColumn;
    Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft);
    Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
    u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;

    if( op==TK_IN && pTerm->iField>0 ){

      assert( pLeft->op==TK_VECTOR );
      pLeft = pLeft->x.pList->a[pTerm->iField-1].pExpr;
    }

    if( exprMightBeIndexed(pSrc, op, prereqLeft, pLeft, &iCur, &iColumn) ){
      pTerm->leftCursor = iCur;
      pTerm->u.leftColumn = iColumn;
................................................................................
    if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
    if( pRight 
     && exprMightBeIndexed(pSrc, op, pTerm->prereqRight, pRight, &iCur,&iColumn)
    ){
      WhereTerm *pNew;
      Expr *pDup;
      u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */

      if( pTerm->leftCursor>=0 ){
        int idxNew;
        pDup = sqlite3ExprDup(db, pExpr, 0);
        if( db->mallocFailed ){
          sqlite3ExprDelete(db, pDup);
          return;
        }








|
>







 







>








958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
...
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990

  pTerm->eOperator = 0;
  if( allowedOp(op) ){
    int iCur, iColumn;
    Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft);
    Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
    u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;

    if( pTerm->iField>0 ){
      assert( op==TK_IN );
      assert( pLeft->op==TK_VECTOR );
      pLeft = pLeft->x.pList->a[pTerm->iField-1].pExpr;
    }

    if( exprMightBeIndexed(pSrc, op, prereqLeft, pLeft, &iCur, &iColumn) ){
      pTerm->leftCursor = iCur;
      pTerm->u.leftColumn = iColumn;
................................................................................
    if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
    if( pRight 
     && exprMightBeIndexed(pSrc, op, pTerm->prereqRight, pRight, &iCur,&iColumn)
    ){
      WhereTerm *pNew;
      Expr *pDup;
      u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
      assert( pTerm->iField==0 );
      if( pTerm->leftCursor>=0 ){
        int idxNew;
        pDup = sqlite3ExprDup(db, pExpr, 0);
        if( db->mallocFailed ){
          sqlite3ExprDelete(db, pDup);
          return;
        }

Changes to test/rowvalue3.test. 

42
43
44
45
46
47
48

49
50
51
52
53
54
55
...
203
204
205
206
207
208
209


210
211
212
213
214

  5  "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1
  6  "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0
  7  "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)"  1
  8  "SELECT (5, 4) IN (SELECT +b, +a FROM t1)"  1
  9  "SELECT (1, 2) IN (SELECT rowid, b FROM t1)"  1
  10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)"  1
  11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)"  {}

} {
  do_execsql_test 1.$tn $sql $res
}

#-------------------------------------------------------------------------

do_execsql_test 2.0 {
................................................................................
    4 "a DESC, b DESC" {4 3 2 1}
  } {
    do_execsql_test 4.$tn.$tn2 "
      SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby
    " $res
  }
}




finish_test










>







 







>
>






42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217

  5  "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1
  6  "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0
  7  "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)"  1
  8  "SELECT (5, 4) IN (SELECT +b, +a FROM t1)"  1
  9  "SELECT (1, 2) IN (SELECT rowid, b FROM t1)"  1
  10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)"  1
  11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)"  {}
  12 "SELECT 1 FROM t1 WHERE (a, b) = (SELECT +a, +b FROM t1)" {1}
} {
  do_execsql_test 1.$tn $sql $res
}

#-------------------------------------------------------------------------

do_execsql_test 2.0 {
................................................................................
    4 "a DESC, b DESC" {4 3 2 1}
  } {
    do_execsql_test 4.$tn.$tn2 "
      SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby
    " $res
  }
}

#-------------------------------------------------------------------------


finish_test

This page was generated in about 0.034s by Fossil 2.12 [39a5289b9b] 2020-08-13 11:49:39