Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix some problems with multi-column IN(SELECT...) processing. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
719a3b2035a335ca8b9704646b1d6410 |
User & Date: | dan 2016-07-27 19:33:04.107 |
Context
2016-07-28
| ||
13:59 | Merge latest trunk changes into this branch. (check-in: 9685880f7b user: dan tags: rowvalue) | |
2016-07-27
| ||
19:33 | Fix some problems with multi-column IN(SELECT...) processing. (check-in: 719a3b2035 user: dan tags: rowvalue) | |
16:03 | Initialize a variable in where.c to avoid a valgrind warning. (check-in: 4d59df02d3 user: dan tags: trunk) | |
2016-07-26
| ||
18:15 | Merge latest trunk changes into this branch. (check-in: d4f3d52c5a user: dan tags: rowvalue) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
2476 2477 2478 2479 2480 2481 2482 | sqlite3VdbeGoto(v, destIfFalse); } sqlite3VdbeResolveLabel(v, labelOk); sqlite3ReleaseTempReg(pParse, regCkNull); }else{ /* If the LHS is NULL, then the result is either false or NULL depending | | < < | > > | < | > > | | | | | | < | | 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 | sqlite3VdbeGoto(v, destIfFalse); } sqlite3VdbeResolveLabel(v, labelOk); sqlite3ReleaseTempReg(pParse, regCkNull); }else{ /* If the LHS is NULL, then the result is either false or NULL depending ** on whether the RHS is empty or not, respectively. */ if( destIfNull==destIfFalse ){ for(i=0; i<nVector; i++){ Expr *p = exprVectorField(pExpr->pLeft, i); if( sqlite3ExprCanBeNull(p) ){ sqlite3VdbeAddOp2(v, OP_IsNull, r1+aiMap[i], destIfNull); } } }else if( nVector==1 && sqlite3ExprCanBeNull(pExpr->pLeft) ){ int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); VdbeCoverage(v); sqlite3VdbeGoto(v, destIfNull); sqlite3VdbeJumpHere(v, addr1); } if( eType==IN_INDEX_ROWID ){ /* In this case, the RHS is the ROWID of table b-tree */ sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1); VdbeCoverage(v); }else if( nVector>1 && eType==IN_INDEX_EPH && destIfNull!=destIfFalse ){ int regNull = sqlite3GetTempReg(pParse); int r2 = sqlite3GetTempReg(pParse); int r3 = sqlite3GetTempReg(pParse); int r4 = sqlite3GetTempReg(pParse); int addrNext; int addrIf; |
︙ | ︙ | |||
3490 3491 3492 3493 3494 3495 3496 | sqlite3VdbeAddOp1(v, OP_RealAffinity, target); } #endif break; } case TK_VECTOR: { | | | 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 | sqlite3VdbeAddOp1(v, OP_RealAffinity, target); } #endif break; } 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 ); |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
763 764 765 766 767 768 769 | assert( pNC->nRef>=nRef ); if( nRef!=pNC->nRef ){ ExprSetProperty(pExpr, EP_VarSelect); pNC->ncFlags |= NC_VarSelect; } if( pExpr->op==TK_SELECT && pExpr->x.pSelect->pEList->nExpr>1 ){ | < < < | < < < < < < < < < < < < < < < < < < < < < < | < | 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 | assert( pNC->nRef>=nRef ); if( nRef!=pNC->nRef ){ ExprSetProperty(pExpr, EP_VarSelect); pNC->ncFlags |= NC_VarSelect; } if( pExpr->op==TK_SELECT && pExpr->x.pSelect->pEList->nExpr>1 ){ ExprSetProperty(pExpr, EP_Vector); } } break; } case TK_VARIABLE: { notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr); break; } case TK_VECTOR: { ExprSetProperty(pExpr, EP_Vector); break; } } return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue; } /* |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2323 2324 2325 2326 2327 2328 2329 | #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ #define EP_Alias 0x400000 /* Is an alias for a result set column */ | < | | 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 | #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ #define EP_Alias 0x400000 /* Is an alias for a result set column */ #define EP_Vector 0x800000 /* This expression is a row value */ /* ** Combinations of two or more EP_* flags */ #define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */ /* |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3473 3474 3475 3476 3477 3478 3479 | /* Loop through all columns of the index and deal with the ones ** that are not constrained by == or IN. */ rev = revSet = 0; distinctColumns = 0; for(j=0; j<nColumn; j++){ | | > > > > > > | | | < < > > > > > | < | | | | | > > > > > > > > > > | 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 | /* Loop through all columns of the index and deal with the ones ** that are not constrained by == or IN. */ rev = revSet = 0; distinctColumns = 0; for(j=0; j<nColumn; j++){ u8 bOnce = 1; /* True to run the ORDER BY search loop */ assert( j>=pLoop->u.btree.nEq || (pLoop->aLTerm[j]==0)==(j<pLoop->nSkip) ); if( j<pLoop->u.btree.nEq && j>=pLoop->nSkip ){ u16 eOp = pLoop->aLTerm[j]->eOperator; /* Skip over == and IS and ISNULL terms. (Also skip IN terms when ** doing WHERE_ORDERBY_LIMIT processing). ** ** If the current term is a column of an ((?,?) IN (SELECT...)) ** expression for which the SELECT returns more than one column, ** check that it is the only column used by this loop. Otherwise, ** if it is one of two or more, none of the columns can be ** considered to match an ORDER BY term. */ if( (eOp & eqOpMask)!=0 ){ if( eOp & WO_ISNULL ){ testcase( isOrderDistinct ); isOrderDistinct = 0; } continue; }else if( eOp & WO_IN ){ Expr *pX = pLoop->aLTerm[j]->pExpr; for(i=j+1; i<pLoop->u.btree.nEq; i++){ if( pLoop->aLTerm[i]->pExpr==pX ){ assert( (pLoop->aLTerm[i]->eOperator & WO_IN) ); bOnce = 0; break; } } } } /* Get the column number in the table (iColumn) and sort order ** (revIdx) for the j-th column of the index. */ if( pIndex ){ iColumn = pIndex->aiColumn[j]; |
︙ | ︙ | |||
3515 3516 3517 3518 3519 3520 3521 | ){ isOrderDistinct = 0; } /* Find the ORDER BY term that corresponds to the j-th column ** of the index and mark that ORDER BY term off */ | < | 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 | ){ isOrderDistinct = 0; } /* Find the ORDER BY term that corresponds to the j-th column ** of the index and mark that ORDER BY term off */ isMatch = 0; for(i=0; bOnce && i<nOrderBy; i++){ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); testcase( wctrlFlags & WHERE_GROUPBY ); testcase( wctrlFlags & WHERE_DISTINCTBY ); if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0; |
︙ | ︙ | |||
4008 4009 4010 4011 4012 4013 4014 | } }else{ pWInfo->nOBSat = pFrom->isOrdered; pWInfo->revMask = pFrom->revLoop; if( pWInfo->nOBSat<=0 ){ pWInfo->nOBSat = 0; if( nLoop>0 ){ | | | 4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 | } }else{ pWInfo->nOBSat = pFrom->isOrdered; pWInfo->revMask = pFrom->revLoop; if( pWInfo->nOBSat<=0 ){ pWInfo->nOBSat = 0; if( nLoop>0 ){ Bitmask m = 0; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m); if( rc==pWInfo->pOrderBy->nExpr ){ pWInfo->bOrderedInnerLoop = 1; pWInfo->revMask = m; } } |
︙ | ︙ |
Changes to test/in.test.
︙ | ︙ | |||
636 637 638 639 640 641 642 643 644 | } } {1 {sub-select returns 2 columns - expected 1}} do_test in-13.X { db nullvalue "" } {} finish_test | > > > > > > > > > > > | 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 | } } {1 {sub-select returns 2 columns - expected 1}} do_test in-13.X { db nullvalue "" } {} # At one point the following was causing valgrind to report a "jump # depends on unitialized location" problem. # do_execsql_test in-14.0 { CREATE TABLE c1(a); INSERT INTO c1 VALUES(1), (2), (4), (3); } do_execsql_test in-14.1 { SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1 } {1 2 3 4} finish_test |
Changes to test/rowvalue3.test.
︙ | ︙ | |||
13 14 15 16 17 18 19 20 21 22 23 24 25 26 | # where the SELECT statement returns more than one column. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix rowvalue3 do_execsql_test 1.0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a, b); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t1 VALUES(7, 8, 9); | > > > > > > > > | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | # where the SELECT statement returns more than one column. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix rowvalue3 # Drop all auxiliary indexes from the main database opened by handle [db]. # proc drop_all_indexes {} { set L [db eval { SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%' }] foreach idx $L { db eval "DROP INDEX $idx" } } do_execsql_test 1.0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a, b); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t1 VALUES(7, 8, 9); |
︙ | ︙ | |||
56 57 58 59 60 61 62 | INSERT INTO kk VALUES('d', 'e'); -- INSERT INTO kk VALUES('x', 'x'); } foreach {tn idx} { 1 { } | | | > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | INSERT INTO kk VALUES('d', 'e'); -- INSERT INTO kk VALUES('x', 'x'); } foreach {tn idx} { 1 { } 2 { CREATE INDEX z1idx ON z1(x, y) } 3 { CREATE UNIQUE INDEX z1idx ON z1(x, y) } 4 { CREATE INDEX z1idx ON kk(a, b) } } { execsql "DROP INDEX IF EXISTS z1idx" execsql $idx do_execsql_test 2.$tn.1 { SELECT * FROM z1 WHERE x IN (SELECT a FROM kk) } {d e f} |
︙ | ︙ | |||
83 84 85 86 87 88 89 | } {} do_execsql_test 2.$tn.5 { SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk) } {d e f} } | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > | > > > > > > | > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 | } {} do_execsql_test 2.$tn.5 { SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk) } {d e f} } #------------------------------------------------------------------------- # do_execsql_test 3.0 { CREATE TABLE c1(a, b, c, d); INSERT INTO c1(rowid, a, b) VALUES(1, NULL, 1); INSERT INTO c1(rowid, a, b) VALUES(2, 2, NULL); INSERT INTO c1(rowid, a, b) VALUES(3, 2, 2); INSERT INTO c1(rowid, a, b) VALUES(4, 3, 3); INSERT INTO c1(rowid, a, b, c, d) VALUES(101, 'a', 'b', 1, 1); INSERT INTO c1(rowid, a, b, c, d) VALUES(102, 'a', 'b', 1, 2); INSERT INTO c1(rowid, a, b, c, d) VALUES(103, 'a', 'b', 1, 3); INSERT INTO c1(rowid, a, b, c, d) VALUES(104, 'a', 'b', 2, 1); INSERT INTO c1(rowid, a, b, c, d) VALUES(105, 'a', 'b', 2, 2); INSERT INTO c1(rowid, a, b, c, d) VALUES(106, 'a', 'b', 2, 3); INSERT INTO c1(rowid, a, b, c, d) VALUES(107, 'a', 'b', 3, 1); INSERT INTO c1(rowid, a, b, c, d) VALUES(108, 'a', 'b', 3, 2); INSERT INTO c1(rowid, a, b, c, d) VALUES(109, 'a', 'b', 3, 3); } foreach {tn idx} { 1 { } 2 { CREATE INDEX c1ab ON c1(a, b); } 3 { CREATE INDEX c1ba ON c1(b, a); } 4 { CREATE INDEX c1cd ON c1(c, d); } 5 { CREATE INDEX c1dc ON c1(d, c); } } { drop_all_indexes foreach {tn2 sql res} { 1 "SELECT (1, 2) IN (SELECT a, b FROM c1)" {0} 2 "SELECT (1, 1) IN (SELECT a, b FROM c1)" {{}} 3 "SELECT (2, 1) IN (SELECT a, b FROM c1)" {{}} 4 "SELECT (2, 2) IN (SELECT a, b FROM c1)" {1} 5 "SELECT c, d FROM c1 WHERE (c, d) IN (SELECT d, c FROM c1)" { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 6 "SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC" { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 7 { SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC, d ASC } { 3 1 3 2 3 3 2 1 2 2 2 3 1 1 1 2 1 3 } 8 { SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c ASC, d DESC } { 1 3 1 2 1 1 2 3 2 2 2 1 3 3 3 2 3 1 } 9 { SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c ASC, d ASC } { 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 } 10 { SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC, d DESC } { 3 3 3 2 3 1 2 3 2 2 2 1 1 3 1 2 1 1 } } { do_execsql_test 3.$tn.$tn2 $sql $res } } #------------------------------------------------------------------------- do_execsql_test 4.0 { CREATE TABLE hh(a, b, c); INSERT INTO hh VALUES('a', 'a', 1); INSERT INTO hh VALUES('a', 'b', 2); INSERT INTO hh VALUES('b', 'a', 3); INSERT INTO hh VALUES('b', 'b', 4); CREATE TABLE k1(x, y); INSERT INTO k1 VALUES('a', 'a'); INSERT INTO k1 VALUES('b', 'b'); INSERT INTO k1 VALUES('a', 'b'); INSERT INTO k1 VALUES('b', 'a'); } foreach {tn idx} { 1 { } 2 { CREATE INDEX h1 ON hh(a, b); } 3 { CREATE UNIQUE INDEX k1idx ON k1(x, y) } 4 { CREATE UNIQUE INDEX k1idx ON k1(x, y DESC) } 5 { CREATE INDEX h1 ON hh(a, b); CREATE UNIQUE INDEX k1idx ON k1(x, y); } 6 { CREATE INDEX h1 ON hh(a, b); CREATE UNIQUE INDEX k1idx ON k1(x, y DESC); } } { drop_all_indexes execsql $idx foreach {tn2 orderby res} { 1 "a ASC, b ASC" {1 2 3 4} 2 "a ASC, b DESC" {2 1 4 3} 3 "a DESC, b ASC" {3 4 1 2} 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 |