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 |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
4dfebff2924f46284d5b9cda69175f79 |
User & Date: | dan 2016-07-29 18:12:12.900 |
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: 42607366bf 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: 4dfebff292 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: cc3f6542be user: dan tags: rowvalue) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
3508 3509 3510 3511 3512 3513 3514 | } case TK_VECTOR: { sqlite3ErrorMsg(pParse, "invalid use of row value"); break; } | < < < < < < < < < < < < | 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 | 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*); | | > | 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 | 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 ){ | > > > > > > > > > > > > > > > > > > > > > > > | > | 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 | 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; |
︙ | ︙ | |||
1485 1486 1487 1488 1489 1490 1491 | 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 ); } | < < < < | 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 | 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); |
︙ | ︙ | |||
1590 1591 1592 1593 1594 1595 1596 | 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; | | | 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 | 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 | 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; | | > > | 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 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; pTerm->eOperator = operatorMask(op) & opMask; } 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 | 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 { | > | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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 { |
︙ | ︙ | |||
203 204 205 206 207 208 209 210 211 212 213 214 | 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 | > > | 204 205 206 207 208 209 210 211 212 213 214 215 216 217 | 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 |