Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -11,10 +11,22 @@ ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. */ #include "sqliteInt.h" + +/* Forward declarations */ +static void exprCodeBetween(Parse*,Expr*,int,void(*)(Parse*,Expr*,int,int),int); +static int exprCodeVector(Parse *pParse, Expr *p, int *piToFree); + +/* +** Return the affinity character for a single column of a table. +*/ +char sqlite3TableColumnAffinity(Table *pTab, int iCol){ + assert( iColnCol ); + return iCol>=0 ? pTab->aCol[iCol].affinity : SQLITE_AFF_INTEGER; +} /* ** Return the 'affinity' of the expression pExpr if any. ** ** If pExpr is a column, a reference to a column via an 'AS' alias, @@ -37,25 +49,25 @@ op = pExpr->op; if( op==TK_SELECT ){ assert( pExpr->flags&EP_xIsSelect ); return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr); } + if( op==TK_REGISTER ) op = pExpr->op2; #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ assert( !ExprHasProperty(pExpr, EP_IntValue) ); return sqlite3AffinityType(pExpr->u.zToken, 0); } #endif - if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) - && pExpr->pTab!=0 - ){ - /* op==TK_REGISTER && pExpr->pTab!=0 happens when pExpr was originally - ** a TK_COLUMN but was previously evaluated and cached in a register */ - int j = pExpr->iColumn; - if( j<0 ) return SQLITE_AFF_INTEGER; - assert( pExpr->pTab && jpTab->nCol ); - return pExpr->pTab->aCol[j].affinity; + if( op==TK_AGG_COLUMN || op==TK_COLUMN ){ + return sqlite3TableColumnAffinity(pExpr->pTab, pExpr->iColumn); + } + if( op==TK_SELECT_COLUMN ){ + assert( pExpr->pLeft->flags&EP_xIsSelect ); + return sqlite3ExprAffinity( + pExpr->pLeft->x.pSelect->pEList->a[pExpr->iColumn].pExpr + ); } return pExpr->affinity; } /* @@ -217,11 +229,11 @@ aff = sqlite3ExprAffinity(pExpr->pLeft); if( pExpr->pRight ){ aff = sqlite3CompareAffinity(pExpr->pRight, aff); }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){ aff = sqlite3CompareAffinity(pExpr->x.pSelect->pEList->a[0].pExpr, aff); - }else if( !aff ){ + }else if( NEVER(aff==0) ){ aff = SQLITE_AFF_BLOB; } return aff; } @@ -306,10 +318,274 @@ addr = sqlite3VdbeAddOp4(pParse->pVdbe, opcode, in2, dest, in1, (void*)p4, P4_COLLSEQ); sqlite3VdbeChangeP5(pParse->pVdbe, (u8)p5); return addr; } + +/* +** Return true if expression pExpr is a vector, or false otherwise. +** +** A vector is defined as any expression that results in two or more +** columns of result. Every TK_VECTOR node is an vector because the +** parser will not generate a TK_VECTOR with fewer than two entries. +** But a TK_SELECT might be either a vector or a scalar. It is only +** considered a vector if it has two or more result columns. +*/ +int sqlite3ExprIsVector(Expr *pExpr){ + return sqlite3ExprVectorSize(pExpr)>1; +} + +/* +** If the expression passed as the only argument is of type TK_VECTOR +** return the number of expressions in the vector. Or, if the expression +** is a sub-select, return the number of columns in the sub-select. For +** any other type of expression, return 1. +*/ +int sqlite3ExprVectorSize(Expr *pExpr){ + u8 op = pExpr->op; + if( op==TK_REGISTER ) op = pExpr->op2; + if( op==TK_VECTOR ){ + return pExpr->x.pList->nExpr; + }else if( op==TK_SELECT ){ + return pExpr->x.pSelect->pEList->nExpr; + }else{ + return 1; + } +} + +#ifndef SQLITE_OMIT_SUBQUERY +/* +** Return a pointer to a subexpression of pVector that is the i-th +** column of the vector (numbered starting with 0). The caller must +** ensure that i is within range. +** +** If pVector is really a scalar (and "scalar" here includes subqueries +** that return a single column!) then return pVector unmodified. +** +** pVector retains ownership of the returned subexpression. +** +** If the vector is a (SELECT ...) then the expression returned is +** just the expression for the i-th term of the result set, and may +** not be ready for evaluation because the table cursor has not yet +** been positioned. +*/ +Expr *sqlite3VectorFieldSubexpr(Expr *pVector, int i){ + assert( iop2==0 || pVector->op==TK_REGISTER ); + if( pVector->op==TK_SELECT || pVector->op2==TK_SELECT ){ + return pVector->x.pSelect->pEList->a[i].pExpr; + }else{ + return pVector->x.pList->a[i].pExpr; + } + } + return pVector; +} +#endif /* !defined(SQLITE_OMIT_SUBQUERY) */ + +#ifndef SQLITE_OMIT_SUBQUERY +/* +** Compute and return a new Expr object which when passed to +** sqlite3ExprCode() will generate all necessary code to compute +** the iField-th column of the vector expression pVector. +** +** It is ok for pVector to be a scalar (as long as iField==0). +** In that case, this routine works like sqlite3ExprDup(). +** +** The caller owns the returned Expr object and is responsible for +** ensuring that the returned value eventually gets freed. +** +** The caller retains ownership of pVector. If pVector is a TK_SELECT, +** then the returned object will reference pVector and so pVector must remain +** valid for the life of the returned object. If pVector is a TK_VECTOR +** or a scalar expression, then it can be deleted as soon as this routine +** returns. +** +** A trick to cause a TK_SELECT pVector to be deleted together with +** the returned Expr object is to attach the pVector to the pRight field +** of the returned TK_SELECT_COLUMN Expr object. +*/ +Expr *sqlite3ExprForVectorField( + Parse *pParse, /* Parsing context */ + Expr *pVector, /* The vector. List of expressions or a sub-SELECT */ + int iField /* Which column of the vector to return */ +){ + Expr *pRet; + if( pVector->op==TK_SELECT ){ + assert( pVector->flags & EP_xIsSelect ); + /* The TK_SELECT_COLUMN Expr node: + ** + ** pLeft: pVector containing TK_SELECT + ** pRight: not used. But recursively deleted. + ** iColumn: Index of a column in pVector + ** pLeft->iTable: First in an array of register holding result, or 0 + ** if the result is not yet computed. + ** + ** sqlite3ExprDelete() specifically skips the recursive delete of + ** pLeft on TK_SELECT_COLUMN nodes. But pRight is followed, so pVector + ** can be attached to pRight to cause this node to take ownership of + ** pVector. Typically there will be multiple TK_SELECT_COLUMN nodes + ** with the same pLeft pointer to the pVector, but only one of them + ** will own the pVector. + */ + pRet = sqlite3PExpr(pParse, TK_SELECT_COLUMN, 0, 0, 0); + if( pRet ){ + pRet->iColumn = iField; + pRet->pLeft = pVector; + } + assert( pRet==0 || pRet->iTable==0 ); + }else{ + if( pVector->op==TK_VECTOR ) pVector = pVector->x.pList->a[iField].pExpr; + pRet = sqlite3ExprDup(pParse->db, pVector, 0); + } + return pRet; +} +#endif /* !define(SQLITE_OMIT_SUBQUERY) */ + +/* +** If expression pExpr is of type TK_SELECT, generate code to evaluate +** it. Return the register in which the result is stored (or, if the +** sub-select returns more than one column, the first in an array +** of registers in which the result is stored). +** +** If pExpr is not a TK_SELECT expression, return 0. +*/ +static int exprCodeSubselect(Parse *pParse, Expr *pExpr){ + int reg = 0; +#ifndef SQLITE_OMIT_SUBQUERY + if( pExpr->op==TK_SELECT ){ + reg = sqlite3CodeSubselect(pParse, pExpr, 0, 0); + } +#endif + return reg; +} + +/* +** Argument pVector points to a vector expression - either a TK_VECTOR +** or TK_SELECT that returns more than one column. This function returns +** the register number of a register that contains the value of +** element iField of the vector. +** +** If pVector is a TK_SELECT expression, then code for it must have +** already been generated using the exprCodeSubselect() routine. In this +** case parameter regSelect should be the first in an array of registers +** containing the results of the sub-select. +** +** If pVector is of type TK_VECTOR, then code for the requested field +** is generated. In this case (*pRegFree) may be set to the number of +** a temporary register to be freed by the caller before returning. +** +** Before returning, output parameter (*ppExpr) is set to point to the +** Expr object corresponding to element iElem of the vector. +*/ +static int exprVectorRegister( + Parse *pParse, /* Parse context */ + Expr *pVector, /* Vector to extract element from */ + int iField, /* Field to extract from pVector */ + int regSelect, /* First in array of registers */ + Expr **ppExpr, /* OUT: Expression element */ + int *pRegFree /* OUT: Temp register to free */ +){ + u8 op = pVector->op; + assert( op==TK_VECTOR || op==TK_REGISTER || op==TK_SELECT ); + if( op==TK_REGISTER ){ + *ppExpr = sqlite3VectorFieldSubexpr(pVector, iField); + return pVector->iTable+iField; + } + if( op==TK_SELECT ){ + *ppExpr = pVector->x.pSelect->pEList->a[iField].pExpr; + return regSelect+iField; + } + *ppExpr = pVector->x.pList->a[iField].pExpr; + return sqlite3ExprCodeTemp(pParse, *ppExpr, pRegFree); +} + +/* +** Expression pExpr is a comparison between two vector values. Compute +** the result of the comparison (1, 0, or NULL) and write that +** result into register dest. +** +** The caller must satisfy the following preconditions: +** +** if pExpr->op==TK_IS: op==TK_EQ and p5==SQLITE_NULLEQ +** if pExpr->op==TK_ISNOT: op==TK_NE and p5==SQLITE_NULLEQ +** otherwise: op==pExpr->op and p5==0 +*/ +static void codeVectorCompare( + Parse *pParse, /* Code generator context */ + Expr *pExpr, /* The comparison operation */ + int dest, /* Write results into this register */ + u8 op, /* Comparison operator */ + u8 p5 /* SQLITE_NULLEQ or zero */ +){ + Vdbe *v = pParse->pVdbe; + Expr *pLeft = pExpr->pLeft; + Expr *pRight = pExpr->pRight; + int nLeft = sqlite3ExprVectorSize(pLeft); + int i; + int regLeft = 0; + int regRight = 0; + u8 opx = op; + int addrDone = sqlite3VdbeMakeLabel(v); + + assert( nLeft==sqlite3ExprVectorSize(pRight) ); + assert( pExpr->op==TK_EQ || pExpr->op==TK_NE + || pExpr->op==TK_IS || pExpr->op==TK_ISNOT + || pExpr->op==TK_LT || pExpr->op==TK_GT + || pExpr->op==TK_LE || pExpr->op==TK_GE + ); + assert( pExpr->op==op || (pExpr->op==TK_IS && op==TK_EQ) + || (pExpr->op==TK_ISNOT && op==TK_NE) ); + assert( p5==0 || pExpr->op!=op ); + assert( p5==SQLITE_NULLEQ || pExpr->op==op ); + + p5 |= SQLITE_STOREP2; + if( opx==TK_LE ) opx = TK_LT; + if( opx==TK_GE ) opx = TK_GT; + + regLeft = exprCodeSubselect(pParse, pLeft); + regRight = exprCodeSubselect(pParse, pRight); + + for(i=0; 1 /*Loop exits by "break"*/; i++){ + int regFree1 = 0, regFree2 = 0; + Expr *pL, *pR; + int r1, r2; + assert( i>=0 && i0 ) sqlite3ExprCachePush(pParse); + r1 = exprVectorRegister(pParse, pLeft, i, regLeft, &pL, ®Free1); + r2 = exprVectorRegister(pParse, pRight, i, regRight, &pR, ®Free2); + codeCompare(pParse, pL, pR, opx, r1, r2, dest, p5); + testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt); + testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le); + testcase(op==OP_Gt); VdbeCoverageIf(v,op==OP_Gt); + testcase(op==OP_Ge); VdbeCoverageIf(v,op==OP_Ge); + testcase(op==OP_Eq); VdbeCoverageIf(v,op==OP_Eq); + testcase(op==OP_Ne); VdbeCoverageIf(v,op==OP_Ne); + sqlite3ReleaseTempReg(pParse, regFree1); + sqlite3ReleaseTempReg(pParse, regFree2); + if( i>0 ) sqlite3ExprCachePop(pParse); + if( i==nLeft-1 ){ + break; + } + if( opx==TK_EQ ){ + sqlite3VdbeAddOp2(v, OP_IfNot, dest, addrDone); VdbeCoverage(v); + p5 |= SQLITE_KEEPNULL; + }else if( opx==TK_NE ){ + sqlite3VdbeAddOp2(v, OP_If, dest, addrDone); VdbeCoverage(v); + p5 |= SQLITE_KEEPNULL; + }else{ + assert( op==TK_LT || op==TK_GT || op==TK_LE || op==TK_GE ); + sqlite3VdbeAddOp2(v, OP_ElseNotEq, 0, addrDone); + VdbeCoverageIf(v, op==TK_LT); + VdbeCoverageIf(v, op==TK_GT); + VdbeCoverageIf(v, op==TK_LE); + VdbeCoverageIf(v, op==TK_GE); + if( i==nLeft-2 ) opx = op; + } + } + sqlite3VdbeResolveLabel(v, addrDone); +} #if SQLITE_MAX_EXPR_DEPTH>0 /* ** Check that argument nHeight is less than or equal to the maximum ** expression depth allowed. If it is not, leave an error message in @@ -741,11 +1017,11 @@ /* Sanity check: Assert that the IntValue is non-negative if it exists */ assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 ); if( !ExprHasProperty(p, EP_TokenOnly) ){ /* The Expr.x union is never used at the same time as Expr.pRight */ assert( p->x.pList==0 || p->pRight==0 ); - sqlite3ExprDelete(db, p->pLeft); + if( p->pLeft && p->op!=TK_SELECT_COLUMN ) sqlite3ExprDeleteNN(db, p->pLeft); sqlite3ExprDelete(db, p->pRight); if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken); if( ExprHasProperty(p, EP_xIsSelect) ){ sqlite3SelectDelete(db, p->x.pSelect); }else{ @@ -949,11 +1225,15 @@ if( pzBuffer ){ *pzBuffer = zAlloc; } }else{ if( !ExprHasProperty(p, EP_TokenOnly) ){ - pNew->pLeft = sqlite3ExprDup(db, p->pLeft, 0); + if( pNew->op==TK_SELECT_COLUMN ){ + pNew->pLeft = p->pLeft; + }else{ + pNew->pLeft = sqlite3ExprDup(db, p->pLeft, 0); + } pNew->pRight = sqlite3ExprDup(db, p->pRight, 0); } } } return pNew; @@ -1189,10 +1469,64 @@ /* Avoid leaking memory if malloc has failed. */ sqlite3ExprDelete(db, pExpr); sqlite3ExprListDelete(db, pList); return 0; } + +/* +** pColumns and pExpr form a vector assignment which is part of the SET +** clause of an UPDATE statement. Like this: +** +** (a,b,c) = (expr1,expr2,expr3) +** Or: (a,b,c) = (SELECT x,y,z FROM ....) +** +** For each term of the vector assignment, append new entries to the +** expression list pList. In the case of a subquery on the LHS, append +** TK_SELECT_COLUMN expressions. +*/ +ExprList *sqlite3ExprListAppendVector( + Parse *pParse, /* Parsing context */ + ExprList *pList, /* List to which to append. Might be NULL */ + IdList *pColumns, /* List of names of LHS of the assignment */ + Expr *pExpr /* Vector expression to be appended. Might be NULL */ +){ + sqlite3 *db = pParse->db; + int n; + int i; + int iFirst = pList ? pList->nExpr : 0; + /* pColumns can only be NULL due to an OOM but an OOM will cause an + ** exit prior to this routine being invoked */ + if( NEVER(pColumns==0) ) goto vector_append_error; + if( pExpr==0 ) goto vector_append_error; + n = sqlite3ExprVectorSize(pExpr); + if( pColumns->nId!=n ){ + sqlite3ErrorMsg(pParse, "%d columns assigned %d values", + pColumns->nId, n); + goto vector_append_error; + } + for(i=0; inExpr==iFirst+i+1 ); + pList->a[pList->nExpr-1].zName = pColumns->a[i].zName; + pColumns->a[i].zName = 0; + } + } + if( pExpr->op==TK_SELECT ){ + if( pList && pList->a[iFirst].pExpr ){ + assert( pList->a[iFirst].pExpr->op==TK_SELECT_COLUMN ); + pList->a[iFirst].pExpr->pRight = pExpr; + pExpr = 0; + } + } + +vector_append_error: + sqlite3ExprDelete(db, pExpr); + sqlite3IdListDelete(db, pColumns); + return pList; +} /* ** Set the sort order for the last element on the given ExprList. */ void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){ @@ -1597,12 +1931,12 @@ #ifndef SQLITE_OMIT_SUBQUERY static Select *isCandidateForInOpt(Expr *pX){ Select *p; SrcList *pSrc; ExprList *pEList; - Expr *pRes; Table *pTab; + int i; if( !ExprHasProperty(pX, EP_xIsSelect) ) return 0; /* Not a subquery */ if( ExprHasProperty(pX, EP_VarSelect) ) return 0; /* Correlated subq */ p = pX->x.pSelect; if( p->pPrior ) return 0; /* Not a compound SELECT */ if( p->selFlags & (SF_Distinct|SF_Aggregate) ){ @@ -1621,14 +1955,17 @@ pTab = pSrc->a[0].pTab; assert( pTab!=0 ); assert( pTab->pSelect==0 ); /* FROM clause is not a view */ if( IsVirtual(pTab) ) return 0; /* FROM clause not a virtual table */ pEList = p->pEList; - if( pEList->nExpr!=1 ) return 0; /* One column in the result set */ - pRes = pEList->a[0].pExpr; - if( pRes->op!=TK_COLUMN ) return 0; /* Result is a column */ - assert( pRes->iTable==pSrc->a[0].iCursor ); /* Not a correlated subquery */ + assert( pEList!=0 ); + /* All SELECT results must be columns. */ + for(i=0; inExpr; i++){ + Expr *pRes = pEList->a[i].pExpr; + if( pRes->op!=TK_COLUMN ) return 0; + assert( pRes->iTable==pSrc->a[0].iCursor ); /* Not a correlated subquery */ + } return p; } #endif /* SQLITE_OMIT_SUBQUERY */ /* @@ -1638,10 +1975,11 @@ int sqlite3CodeOnce(Parse *pParse){ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ return sqlite3VdbeAddOp1(v, OP_Once, pParse->nOnce++); } +#ifndef SQLITE_OMIT_SUBQUERY /* ** Generate code that checks the left-most column of index table iCur to see if ** it contains any NULL entries. Cause the register at regHasNull to be set ** to a non-NULL value if iCur contains no NULLs. Cause register regHasNull ** to be set to NULL if iCur contains one or more NULL values. @@ -1653,10 +1991,11 @@ sqlite3VdbeAddOp3(v, OP_Column, iCur, 0, regHasNull); sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); VdbeComment((v, "first_entry_in(%d)", iCur)); sqlite3VdbeJumpHere(v, addr1); } +#endif #ifndef SQLITE_OMIT_SUBQUERY /* ** The argument is an IN operator with a list (not a subquery) on the @@ -1697,11 +2036,11 @@ ** implemented as a sequence of comparisons. ** ** An existing b-tree might be used if the RHS expression pX is a simple ** subquery such as: ** -** SELECT FROM +** SELECT , ... FROM
** ** If the RHS of the IN operator is a list or a more complex subquery, then ** an ephemeral table might need to be generated from the RHS and then ** pX->iTable made to point to the ephemeral table instead of an ** existing table. @@ -1713,18 +2052,18 @@ ** IN index will be used to loop over all values of the RHS of the ** IN operator. ** ** When IN_INDEX_LOOP is used (and the b-tree will be used to iterate ** through the set members) then the b-tree must not contain duplicates. -** An epheremal table must be used unless the selected is guaranteed -** to be unique - either because it is an INTEGER PRIMARY KEY or it -** has a UNIQUE constraint or UNIQUE index. +** An epheremal table must be used unless the selected columns are guaranteed +** to be unique - either because it is an INTEGER PRIMARY KEY or due to +** a UNIQUE constraint or index. ** ** When IN_INDEX_MEMBERSHIP is used (and the b-tree will be used ** for fast set membership tests) then an epheremal table must -** be used unless is an INTEGER PRIMARY KEY or an index can -** be found with as its left-most column. +** be used unless is a single INTEGER PRIMARY KEY column or an +** index can be found with the specified as its left-most. ** ** If the IN_INDEX_NOOP_OK and IN_INDEX_MEMBERSHIP are both set and ** if the RHS of the IN operator is a list (not a subquery) then this ** routine might decide that creating an ephemeral b-tree for membership ** testing is too expensive and return IN_INDEX_NOOP. In that case, the @@ -1741,98 +2080,190 @@ ** ** If a register is allocated and its location stored in *prRhsHasNull, then ** the value in that register will be NULL if the b-tree contains one or more ** NULL values, and it will be some non-NULL value if the b-tree contains no ** NULL values. +** +** If the aiMap parameter is not NULL, it must point to an array containing +** one element for each column returned by the SELECT statement on the RHS +** of the IN(...) operator. The i'th entry of the array is populated with the +** offset of the index column that matches the i'th column returned by the +** SELECT. For example, if the expression and selected index are: +** +** (?,?,?) IN (SELECT a, b, c FROM t1) +** CREATE INDEX i1 ON t1(b, c, a); +** +** then aiMap[] is populated with {2, 0, 1}. */ #ifndef SQLITE_OMIT_SUBQUERY -int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){ +int sqlite3FindInIndex( + Parse *pParse, /* Parsing context */ + Expr *pX, /* The right-hand side (RHS) of the IN operator */ + u32 inFlags, /* IN_INDEX_LOOP, _MEMBERSHIP, and/or _NOOP_OK */ + int *prRhsHasNull, /* Register holding NULL status. See notes */ + int *aiMap /* Mapping from Index fields to RHS fields */ +){ Select *p; /* SELECT to the right of IN operator */ int eType = 0; /* Type of RHS table. IN_INDEX_* */ int iTab = pParse->nTab++; /* Cursor of the RHS table */ int mustBeUnique; /* True if RHS must be unique */ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ assert( pX->op==TK_IN ); mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0; + + /* If the RHS of this IN(...) operator is a SELECT, and if it matters + ** whether or not the SELECT result contains NULL values, check whether + ** or not NULL is actually possible (it may not be, for example, due + ** to NOT NULL constraints in the schema). If no NULL values are possible, + ** set prRhsHasNull to 0 before continuing. */ + if( prRhsHasNull && (pX->flags & EP_xIsSelect) ){ + int i; + ExprList *pEList = pX->x.pSelect->pEList; + for(i=0; inExpr; i++){ + if( sqlite3ExprCanBeNull(pEList->a[i].pExpr) ) break; + } + if( i==pEList->nExpr ){ + prRhsHasNull = 0; + } + } /* Check to see if an existing table or index can be used to ** satisfy the query. This is preferable to generating a new - ** ephemeral table. - */ + ** ephemeral table. */ if( pParse->nErr==0 && (p = isCandidateForInOpt(pX))!=0 ){ sqlite3 *db = pParse->db; /* Database connection */ Table *pTab; /* Table
. */ - Expr *pExpr; /* Expression */ - i16 iCol; /* Index of column */ i16 iDb; /* Database idx for pTab */ + ExprList *pEList = p->pEList; + int nExpr = pEList->nExpr; assert( p->pEList!=0 ); /* Because of isCandidateForInOpt(p) */ assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */ assert( p->pSrc!=0 ); /* Because of isCandidateForInOpt(p) */ pTab = p->pSrc->a[0].pTab; - pExpr = p->pEList->a[0].pExpr; - iCol = (i16)pExpr->iColumn; - + /* Code an OP_Transaction and OP_TableLock for
. */ iDb = sqlite3SchemaToIndex(db, pTab->pSchema); sqlite3CodeVerifySchema(pParse, iDb); sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); - /* This function is only called from two places. In both cases the vdbe - ** has already been allocated. So assume sqlite3GetVdbe() is always - ** successful here. - */ - assert(v); - if( iCol<0 ){ + assert(v); /* sqlite3GetVdbe() has always been previously called */ + if( nExpr==1 && pEList->a[0].pExpr->iColumn<0 ){ + /* The "x IN (SELECT rowid FROM table)" case */ int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead); eType = IN_INDEX_ROWID; sqlite3VdbeJumpHere(v, iAddr); }else{ Index *pIdx; /* Iterator variable */ - - /* The collation sequence used by the comparison. If an index is to - ** be used in place of a temp-table, it must be ordered according - ** to this collation sequence. */ - CollSeq *pReq = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pExpr); - - /* Check that the affinity that will be used to perform the - ** comparison is the same as the affinity of the column. If - ** it is not, it is not possible to use any index. - */ - int affinity_ok = sqlite3IndexAffinityOk(pX, pTab->aCol[iCol].affinity); - - for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){ - if( (pIdx->aiColumn[0]==iCol) - && sqlite3FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)==pReq - && (!mustBeUnique || (pIdx->nKeyCol==1 && IsUniqueIndex(pIdx))) - ){ - int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); - sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb); - sqlite3VdbeSetP4KeyInfo(pParse, pIdx); - VdbeComment((v, "%s", pIdx->zName)); - assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 ); - eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0]; - - if( prRhsHasNull && !pTab->aCol[iCol].notNull ){ -#ifdef SQLITE_ENABLE_COLUMN_USED_MASK - const i64 sOne = 1; - sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, - iTab, 0, 0, (u8*)&sOne, P4_INT64); -#endif - *prRhsHasNull = ++pParse->nMem; - sqlite3SetHasNullFlag(v, iTab, *prRhsHasNull); - } - sqlite3VdbeJumpHere(v, iAddr); - } - } - } - } + int affinity_ok = 1; + int i; + + /* Check that the affinity that will be used to perform each + ** comparison is the same as the affinity of each column in table + ** on the RHS of the IN operator. If it not, it is not possible to + ** use any index of the RHS table. */ + for(i=0; ipLeft, i); + int iCol = pEList->a[i].pExpr->iColumn; + char idxaff = sqlite3TableColumnAffinity(pTab,iCol); /* RHS table */ + char cmpaff = sqlite3CompareAffinity(pLhs, idxaff); + testcase( cmpaff==SQLITE_AFF_BLOB ); + testcase( cmpaff==SQLITE_AFF_TEXT ); + switch( cmpaff ){ + case SQLITE_AFF_BLOB: + break; + case SQLITE_AFF_TEXT: + /* sqlite3CompareAffinity() only returns TEXT if one side or the + ** other has no affinity and the other side is TEXT. Hence, + ** the only way for cmpaff to be TEXT is for idxaff to be TEXT + ** and for the term on the LHS of the IN to have no affinity. */ + assert( idxaff==SQLITE_AFF_TEXT ); + break; + default: + affinity_ok = sqlite3IsNumericAffinity(idxaff); + } + } + + if( affinity_ok ){ + /* Search for an existing index that will work for this IN operator */ + for(pIdx=pTab->pIndex; pIdx && eType==0; pIdx=pIdx->pNext){ + Bitmask colUsed; /* Columns of the index used */ + Bitmask mCol; /* Mask for the current column */ + if( pIdx->nColumnnColumn==BMS-2 ); + testcase( pIdx->nColumn==BMS-1 ); + if( pIdx->nColumn>=BMS-1 ) continue; + if( mustBeUnique ){ + if( pIdx->nKeyCol>nExpr + ||(pIdx->nColumn>nExpr && !IsUniqueIndex(pIdx)) + ){ + continue; /* This index is not unique over the IN RHS columns */ + } + } + + colUsed = 0; /* Columns of index used so far */ + for(i=0; ipLeft, i); + Expr *pRhs = pEList->a[i].pExpr; + CollSeq *pReq = sqlite3BinaryCompareCollSeq(pParse, pLhs, pRhs); + int j; + + assert( pReq!=0 || pRhs->iColumn==XN_ROWID || pParse->nErr ); + for(j=0; jaiColumn[j]!=pRhs->iColumn ) continue; + assert( pIdx->azColl[j] ); + if( pReq!=0 && sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ){ + continue; + } + break; + } + if( j==nExpr ) break; + mCol = MASKBIT(j); + if( mCol & colUsed ) break; /* Each column used only once */ + colUsed |= mCol; + if( aiMap ) aiMap[i] = j; + } + + assert( i==nExpr || colUsed!=(MASKBIT(nExpr)-1) ); + if( colUsed==(MASKBIT(nExpr)-1) ){ + /* If we reach this point, that means the index pIdx is usable */ + int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); + #ifndef SQLITE_OMIT_EXPLAIN + sqlite3VdbeAddOp4(v, OP_Explain, 0, 0, 0, + sqlite3MPrintf(db, "USING INDEX %s FOR IN-OPERATOR",pIdx->zName), + P4_DYNAMIC); + #endif + sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb); + sqlite3VdbeSetP4KeyInfo(pParse, pIdx); + VdbeComment((v, "%s", pIdx->zName)); + assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 ); + eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0]; + + if( prRhsHasNull ){ + *prRhsHasNull = ++pParse->nMem; + #ifdef SQLITE_ENABLE_COLUMN_USED_MASK + i64 mask = (1<x.pList->nExpr<=2) ){ eType = IN_INDEX_NOOP; } - if( eType==0 ){ /* Could not find an existing table or index to use as the RHS b-tree. ** We will have to generate an ephemeral table to do the job. */ @@ -1866,13 +2296,66 @@ sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID); pParse->nQueryLoop = savedNQueryLoop; }else{ pX->iTable = iTab; } + + if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){ + int i, n; + n = sqlite3ExprVectorSize(pX->pLeft); + for(i=0; ipLeft; + int nVal = sqlite3ExprVectorSize(pLeft); + Select *pSelect = (pExpr->flags & EP_xIsSelect) ? pExpr->x.pSelect : 0; + char *zRet; + + assert( pExpr->op==TK_IN ); + zRet = sqlite3DbMallocZero(pParse->db, nVal+1); + if( zRet ){ + int i; + for(i=0; ipEList->a[i].pExpr, a); + }else{ + zRet[i] = a; + } + } + zRet[nVal] = '\0'; + } + return zRet; +} +#endif + +#ifndef SQLITE_OMIT_SUBQUERY +/* +** Load the Parse object passed as the first argument with an error +** message of the form: +** +** "sub-select returns N columns - expected M" +*/ +void sqlite3SubselectError(Parse *pParse, int nActual, int nExpect){ + const char *zFmt = "sub-select returns %d columns - expected %d"; + sqlite3ErrorMsg(pParse, zFmt, nActual, nExpect); +} +#endif /* ** Generate code for scalar subqueries used as a subquery expression, EXISTS, ** or IN operators. Examples: ** @@ -1895,11 +2378,13 @@ ** All this routine does is initialize the register given by rMayHaveNull ** to NULL. Calling routines will take care of changing this register ** value to non-NULL if the RHS is NULL-free. ** ** For a SELECT or EXISTS operator, return the register that holds the -** result. For IN operators or if an error occurs, the return value is 0. +** result. For a multi-column SELECT, the result is stored in a contiguous +** array of registers and the return value is the register of the left-most +** result column. Return 0 for IN operators or if an error occurs. */ #ifndef SQLITE_OMIT_SUBQUERY int sqlite3CodeSubselect( Parse *pParse, /* Parsing context */ Expr *pExpr, /* The IN, SELECT, or EXISTS operator */ @@ -1910,12 +2395,12 @@ int rReg = 0; /* Register storing resulting */ Vdbe *v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return 0; sqlite3ExprCachePush(pParse); - /* This code must be run in its entirety every time it is encountered - ** if any of the following is true: + /* The evaluation of the IN/EXISTS/SELECT must be repeated every time it + ** is encountered if any of the following is true: ** ** * The right-hand side is a correlated subquery ** * The right-hand side is an expression list containing variables ** * We are inside a trigger ** @@ -1937,21 +2422,22 @@ } #endif switch( pExpr->op ){ case TK_IN: { - char affinity; /* Affinity of the LHS of the IN */ int addr; /* Address of OP_OpenEphemeral instruction */ Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */ KeyInfo *pKeyInfo = 0; /* Key information */ - - affinity = sqlite3ExprAffinity(pLeft); + int nVal; /* Size of vector pLeft */ + + nVal = sqlite3ExprVectorSize(pLeft); + assert( !isRowid || nVal==1 ); /* Whether this is an 'x IN(SELECT...)' or an 'x IN()' ** expression it is handled the same way. An ephemeral table is - ** filled with single-field index keys representing the results - ** from the SELECT or the . + ** filled with index keys representing the results from the + ** SELECT or the . ** ** If the 'x' expression is a column value, or the SELECT... ** statement returns a column value, then the affinity of that ** column is used to build the index keys. If both 'x' and the ** SELECT... statement are columns, then numeric affinity is used @@ -1958,54 +2444,67 @@ ** if either column has NUMERIC or INTEGER affinity. If neither ** 'x' nor the SELECT... statement are columns, then numeric affinity ** is used. */ pExpr->iTable = pParse->nTab++; - addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pExpr->iTable, !isRowid); - pKeyInfo = isRowid ? 0 : sqlite3KeyInfoAlloc(pParse->db, 1, 1); + addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, + pExpr->iTable, (isRowid?0:nVal)); + pKeyInfo = isRowid ? 0 : sqlite3KeyInfoAlloc(pParse->db, nVal, 1); if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* Case 1: expr IN (SELECT ...) ** ** Generate code to write the results of the select into the temporary ** table allocated and opened above. */ Select *pSelect = pExpr->x.pSelect; - SelectDest dest; - ExprList *pEList; + ExprList *pEList = pSelect->pEList; assert( !isRowid ); - sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable); - dest.affSdst = (u8)affinity; - assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable ); - pSelect->iLimit = 0; - testcase( pSelect->selFlags & SF_Distinct ); - testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */ - if( sqlite3Select(pParse, pSelect, &dest) ){ - sqlite3KeyInfoUnref(pKeyInfo); - return 0; - } - pEList = pSelect->pEList; - assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */ - assert( pEList!=0 ); - assert( pEList->nExpr>0 ); - assert( sqlite3KeyInfoIsWriteable(pKeyInfo) ); - pKeyInfo->aColl[0] = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, - pEList->a[0].pExpr); + /* If the LHS and RHS of the IN operator do not match, that + ** error will have been caught long before we reach this point. */ + if( ALWAYS(pEList->nExpr==nVal) ){ + SelectDest dest; + int i; + sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable); + dest.zAffSdst = exprINAffinity(pParse, pExpr); + assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable ); + pSelect->iLimit = 0; + testcase( pSelect->selFlags & SF_Distinct ); + testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */ + if( sqlite3Select(pParse, pSelect, &dest) ){ + sqlite3DbFree(pParse->db, dest.zAffSdst); + sqlite3KeyInfoUnref(pKeyInfo); + return 0; + } + sqlite3DbFree(pParse->db, dest.zAffSdst); + assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */ + assert( pEList!=0 ); + assert( pEList->nExpr>0 ); + assert( sqlite3KeyInfoIsWriteable(pKeyInfo) ); + for(i=0; iaColl[i] = sqlite3BinaryCompareCollSeq( + pParse, p, pEList->a[i].pExpr + ); + } + } }else if( ALWAYS(pExpr->x.pList!=0) ){ /* Case 2: expr IN (exprlist) ** ** For each expression, build an index key from the evaluation and ** store it in the temporary table. If is a column, then use ** that columns affinity when building index keys. If is not ** a column, use numeric affinity. */ + char affinity; /* Affinity of the LHS of the IN */ int i; ExprList *pList = pExpr->x.pList; struct ExprList_item *pItem; int r1, r2, r3; + affinity = sqlite3ExprAffinity(pLeft); if( !affinity ){ affinity = SQLITE_AFF_BLOB; } if( pKeyInfo ){ assert( sqlite3KeyInfoIsWriteable(pKeyInfo) ); @@ -2057,30 +2556,41 @@ } case TK_EXISTS: case TK_SELECT: default: { - /* If this has to be a scalar SELECT. Generate code to put the - ** value of this select in a memory cell and record the number - ** of the memory cell in iColumn. If this is an EXISTS, write - ** an integer 0 (not exists) or 1 (exists) into a memory cell - ** and record that memory cell in iColumn. + /* Case 3: (SELECT ... FROM ...) + ** or: EXISTS(SELECT ... FROM ...) + ** + ** For a SELECT, generate code to put the values for all columns of + ** the first row into an array of registers and return the index of + ** the first register. + ** + ** If this is an EXISTS, write an integer 0 (not exists) or 1 (exists) + ** into a register and return that register number. + ** + ** In both cases, the query is augmented with "LIMIT 1". Any + ** preexisting limit is discarded in place of the new LIMIT 1. */ Select *pSel; /* SELECT statement to encode */ - SelectDest dest; /* How to deal with SELECt result */ + SelectDest dest; /* How to deal with SELECT result */ + int nReg; /* Registers to allocate */ testcase( pExpr->op==TK_EXISTS ); testcase( pExpr->op==TK_SELECT ); assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT ); - assert( ExprHasProperty(pExpr, EP_xIsSelect) ); + pSel = pExpr->x.pSelect; - sqlite3SelectDestInit(&dest, 0, ++pParse->nMem); + nReg = pExpr->op==TK_SELECT ? pSel->pEList->nExpr : 1; + sqlite3SelectDestInit(&dest, 0, pParse->nMem+1); + pParse->nMem += nReg; if( pExpr->op==TK_SELECT ){ dest.eDest = SRT_Mem; dest.iSdst = dest.iSDParm; - sqlite3VdbeAddOp2(v, OP_Null, 0, dest.iSDParm); + dest.nSdst = nReg; + sqlite3VdbeAddOp3(v, OP_Null, 0, dest.iSDParm, dest.iSDParm+nReg-1); VdbeComment((v, "Init subquery result")); }else{ dest.eDest = SRT_Exists; sqlite3VdbeAddOp2(v, OP_Integer, 0, dest.iSDParm); VdbeComment((v, "Init EXISTS result")); @@ -2109,66 +2619,147 @@ sqlite3ExprCachePop(pParse); return rReg; } #endif /* SQLITE_OMIT_SUBQUERY */ + +#ifndef SQLITE_OMIT_SUBQUERY +/* +** Expr pIn is an IN(...) expression. This function checks that the +** sub-select on the RHS of the IN() operator has the same number of +** columns as the vector on the LHS. Or, if the RHS of the IN() is not +** a sub-query, that the LHS is a vector of size 1. +*/ +int sqlite3ExprCheckIN(Parse *pParse, Expr *pIn){ + int nVector = sqlite3ExprVectorSize(pIn->pLeft); + if( (pIn->flags & EP_xIsSelect) ){ + if( nVector!=pIn->x.pSelect->pEList->nExpr ){ + sqlite3SubselectError(pParse, pIn->x.pSelect->pEList->nExpr, nVector); + return 1; + } + }else if( nVector!=1 ){ + if( (pIn->pLeft->flags & EP_xIsSelect) ){ + sqlite3SubselectError(pParse, nVector, 1); + }else{ + sqlite3ErrorMsg(pParse, "row value misused"); + } + return 1; + } + return 0; +} +#endif #ifndef SQLITE_OMIT_SUBQUERY /* ** Generate code for an IN expression. ** ** x IN (SELECT ...) ** x IN (value, value, ...) ** -** The left-hand side (LHS) is a scalar expression. The right-hand side (RHS) -** is an array of zero or more values. The expression is true if the LHS is -** contained within the RHS. The value of the expression is unknown (NULL) -** if the LHS is NULL or if the LHS is not contained within the RHS and the -** RHS contains one or more NULL values. +** The left-hand side (LHS) is a scalar or vector expression. The +** right-hand side (RHS) is an array of zero or more scalar values, or a +** subquery. If the RHS is a subquery, the number of result columns must +** match the number of columns in the vector on the LHS. If the RHS is +** a list of values, the LHS must be a scalar. +** +** The IN operator is true if the LHS value is contained within the RHS. +** The result is false if the LHS is definitely not in the RHS. The +** result is NULL if the presence of the LHS in the RHS cannot be +** determined due to NULLs. ** ** This routine generates code that jumps to destIfFalse if the LHS is not ** contained within the RHS. If due to NULLs we cannot determine if the LHS ** is contained in the RHS then jump to destIfNull. If the LHS is contained ** within the RHS then fall through. +** +** See the separate in-operator.md documentation file in the canonical +** SQLite source tree for additional information. */ static void sqlite3ExprCodeIN( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* The IN expression */ int destIfFalse, /* Jump here if LHS is not contained in the RHS */ int destIfNull /* Jump here if the results are unknown due to NULLs */ ){ int rRhsHasNull = 0; /* Register that is true if RHS contains NULL values */ - char affinity; /* Comparison affinity to use */ int eType; /* Type of the RHS */ - int r1; /* Temporary use register */ + int rLhs; /* Register(s) holding the LHS values */ + int rLhsOrig; /* LHS values prior to reordering by aiMap[] */ Vdbe *v; /* Statement under construction */ + int *aiMap = 0; /* Map from vector field to index column */ + char *zAff = 0; /* Affinity string for comparisons */ + int nVector; /* Size of vectors for this IN operator */ + int iDummy; /* Dummy parameter to exprCodeVector() */ + Expr *pLeft; /* The LHS of the IN operator */ + int i; /* loop counter */ + int destStep2; /* Where to jump when NULLs seen in step 2 */ + int destStep6 = 0; /* Start of code for Step 6 */ + int addrTruthOp; /* Address of opcode that determines the IN is true */ + int destNotNull; /* Jump here if a comparison is not true in step 6 */ + int addrTop; /* Top of the step-6 loop */ - /* Compute the RHS. After this step, the table with cursor - ** pExpr->iTable will contains the values that make up the RHS. - */ + pLeft = pExpr->pLeft; + if( sqlite3ExprCheckIN(pParse, pExpr) ) return; + zAff = exprINAffinity(pParse, pExpr); + nVector = sqlite3ExprVectorSize(pExpr->pLeft); + aiMap = (int*)sqlite3DbMallocZero( + pParse->db, nVector*(sizeof(int) + sizeof(char)) + 1 + ); + if( pParse->db->mallocFailed ) goto sqlite3ExprCodeIN_oom_error; + + /* Attempt to compute the RHS. After this step, if anything other than + ** IN_INDEX_NOOP is returned, the table opened ith cursor pExpr->iTable + ** contains the values that make up the RHS. If IN_INDEX_NOOP is returned, + ** the RHS has not yet been coded. */ v = pParse->pVdbe; assert( v!=0 ); /* OOM detected prior to this routine */ VdbeNoopComment((v, "begin IN expr")); eType = sqlite3FindInIndex(pParse, pExpr, IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK, - destIfFalse==destIfNull ? 0 : &rRhsHasNull); - - /* Figure out the affinity to use to create a key from the results - ** of the expression. affinityStr stores a static string suitable for - ** P4 of OP_MakeRecord. - */ - affinity = comparisonAffinity(pExpr); - - /* Code the LHS, the from " IN (...)". + destIfFalse==destIfNull ? 0 : &rRhsHasNull, aiMap); + + assert( pParse->nErr || nVector==1 || eType==IN_INDEX_EPH + || eType==IN_INDEX_INDEX_ASC || eType==IN_INDEX_INDEX_DESC + ); +#ifdef SQLITE_DEBUG + /* Confirm that aiMap[] contains nVector integer values between 0 and + ** nVector-1. */ + for(i=0; i from " IN (...)". If the LHS is a + ** vector, then it is stored in an array of nVector registers starting + ** at r1. + ** + ** sqlite3FindInIndex() might have reordered the fields of the LHS vector + ** so that the fields are in the same order as an existing index. The + ** aiMap[] array contains a mapping from the original LHS field order to + ** the field order that matches the RHS index. */ sqlite3ExprCachePush(pParse); - r1 = sqlite3GetTempReg(pParse); - sqlite3ExprCode(pParse, pExpr->pLeft, r1); + rLhsOrig = exprCodeVector(pParse, pLeft, &iDummy); + for(i=0; ix.pList; CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft); int labelOk = sqlite3VdbeMakeLabel(v); @@ -2176,108 +2767,144 @@ int regCkNull = 0; int ii; assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); if( destIfNull!=destIfFalse ){ regCkNull = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp3(v, OP_BitAnd, r1, r1, regCkNull); + sqlite3VdbeAddOp3(v, OP_BitAnd, rLhs, rLhs, regCkNull); } for(ii=0; iinExpr; ii++){ r2 = sqlite3ExprCodeTemp(pParse, pList->a[ii].pExpr, ®ToFree); if( regCkNull && sqlite3ExprCanBeNull(pList->a[ii].pExpr) ){ sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull); } if( iinExpr-1 || destIfNull!=destIfFalse ){ - sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2, + sqlite3VdbeAddOp4(v, OP_Eq, rLhs, labelOk, r2, (void*)pColl, P4_COLLSEQ); VdbeCoverageIf(v, iinExpr-1); VdbeCoverageIf(v, ii==pList->nExpr-1); - sqlite3VdbeChangeP5(v, affinity); + sqlite3VdbeChangeP5(v, zAff[0]); }else{ assert( destIfNull==destIfFalse ); - sqlite3VdbeAddOp4(v, OP_Ne, r1, destIfFalse, r2, + sqlite3VdbeAddOp4(v, OP_Ne, rLhs, destIfFalse, r2, (void*)pColl, P4_COLLSEQ); VdbeCoverage(v); - sqlite3VdbeChangeP5(v, affinity | SQLITE_JUMPIFNULL); + sqlite3VdbeChangeP5(v, zAff[0] | SQLITE_JUMPIFNULL); } sqlite3ReleaseTempReg(pParse, regToFree); } if( regCkNull ){ sqlite3VdbeAddOp2(v, OP_IsNull, regCkNull, destIfNull); VdbeCoverage(v); 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( sqlite3ExprCanBeNull(pExpr->pLeft) ){ - if( destIfNull==destIfFalse ){ - /* Shortcut for the common case where the false and NULL outcomes are - ** the same. */ - sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v); - }else{ - 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{ - /* In this case, the RHS is an index b-tree. - */ - sqlite3VdbeAddOp4(v, OP_Affinity, r1, 1, 0, &affinity, 1); - - /* If the set membership test fails, then the result of the - ** "x IN (...)" expression must be either 0 or NULL. If the set - ** contains no NULL values, then the result is 0. If the set - ** contains one or more NULL values, then the result of the - ** expression is also NULL. - */ - assert( destIfFalse!=destIfNull || rRhsHasNull==0 ); - if( rRhsHasNull==0 ){ - /* This branch runs if it is known at compile time that the RHS - ** cannot contain NULL values. This happens as the result - ** of a "NOT NULL" constraint in the database schema. - ** - ** Also run this branch if NULL is equivalent to FALSE - ** for this particular IN operator. - */ - sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1); - VdbeCoverage(v); - }else{ - /* In this branch, the RHS of the IN might contain a NULL and - ** the presence of a NULL on the RHS makes a difference in the - ** outcome. - */ - int addr1; - - /* First check to see if the LHS is contained in the RHS. If so, - ** then the answer is TRUE the presence of NULLs in the RHS does - ** not matter. If the LHS is not contained in the RHS, then the - ** answer is NULL if the RHS contains NULLs and the answer is - ** FALSE if the RHS is NULL-free. - */ - addr1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1); - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_IsNull, rRhsHasNull, destIfNull); - VdbeCoverage(v); - sqlite3VdbeGoto(v, destIfFalse); - sqlite3VdbeJumpHere(v, addr1); - } - } - } - sqlite3ReleaseTempReg(pParse, r1); + goto sqlite3ExprCodeIN_finished; + } + + /* Step 2: Check to see if the LHS contains any NULL columns. If the + ** LHS does contain NULLs then the result must be either FALSE or NULL. + ** We will then skip the binary search of the RHS. + */ + if( destIfNull==destIfFalse ){ + destStep2 = destIfFalse; + }else{ + destStep2 = destStep6 = sqlite3VdbeMakeLabel(v); + } + for(i=0; ipLeft, i); + if( sqlite3ExprCanBeNull(p) ){ + sqlite3VdbeAddOp2(v, OP_IsNull, rLhs+i, destStep2); + VdbeCoverage(v); + } + } + + /* Step 3. The LHS is now known to be non-NULL. Do the binary search + ** of the RHS using the LHS as a probe. If found, the result is + ** true. + */ + if( eType==IN_INDEX_ROWID ){ + /* In this case, the RHS is the ROWID of table b-tree and so we also + ** know that the RHS is non-NULL. Hence, we combine steps 3 and 4 + ** into a single opcode. */ + sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, rLhs); + VdbeCoverage(v); + addrTruthOp = sqlite3VdbeAddOp0(v, OP_Goto); /* Return True */ + }else{ + sqlite3VdbeAddOp4(v, OP_Affinity, rLhs, nVector, 0, zAff, nVector); + if( destIfFalse==destIfNull ){ + /* Combine Step 3 and Step 5 into a single opcode */ + sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, + rLhs, nVector); VdbeCoverage(v); + goto sqlite3ExprCodeIN_finished; + } + /* Ordinary Step 3, for the case where FALSE and NULL are distinct */ + addrTruthOp = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, + rLhs, nVector); VdbeCoverage(v); + } + + /* Step 4. If the RHS is known to be non-NULL and we did not find + ** an match on the search above, then the result must be FALSE. + */ + if( rRhsHasNull && nVector==1 ){ + sqlite3VdbeAddOp2(v, OP_NotNull, rRhsHasNull, destIfFalse); + VdbeCoverage(v); + } + + /* Step 5. If we do not care about the difference between NULL and + ** FALSE, then just return false. + */ + if( destIfFalse==destIfNull ) sqlite3VdbeGoto(v, destIfFalse); + + /* Step 6: Loop through rows of the RHS. Compare each row to the LHS. + ** If any comparison is NULL, then the result is NULL. If all + ** comparisons are FALSE then the final result is FALSE. + ** + ** For a scalar LHS, it is sufficient to check just the first row + ** of the RHS. + */ + if( destStep6 ) sqlite3VdbeResolveLabel(v, destStep6); + addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); + VdbeCoverage(v); + if( nVector>1 ){ + destNotNull = sqlite3VdbeMakeLabel(v); + }else{ + /* For nVector==1, combine steps 6 and 7 by immediately returning + ** FALSE if the first comparison is not NULL */ + destNotNull = destIfFalse; + } + for(i=0; iiTable, i, r3); + sqlite3VdbeAddOp4(v, OP_Ne, rLhs+i, destNotNull, r3, + (void*)pColl, P4_COLLSEQ); + VdbeCoverage(v); + sqlite3ReleaseTempReg(pParse, r3); + } + sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull); + if( nVector>1 ){ + sqlite3VdbeResolveLabel(v, destNotNull); + sqlite3VdbeAddOp2(v, OP_Next, pExpr->iTable, addrTop+1); + VdbeCoverage(v); + + /* Step 7: If we reach this point, we know that the result must + ** be false. */ + sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse); + } + + /* Jumps here in order to return true. */ + sqlite3VdbeJumpHere(v, addrTruthOp); + +sqlite3ExprCodeIN_finished: + if( rLhs!=rLhsOrig ) sqlite3ReleaseTempReg(pParse, rLhs); sqlite3ExprCachePop(pParse); VdbeComment((v, "end IN expr")); +sqlite3ExprCodeIN_oom_error: + sqlite3DbFree(pParse->db, aiMap); + sqlite3DbFree(pParse->db, zAff); } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_FLOATING_POINT /* @@ -2658,18 +3285,52 @@ } #endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */ /* -** Convert an expression node to a TK_REGISTER +** Convert a scalar expression node to a TK_REGISTER referencing +** register iReg. The caller must ensure that iReg already contains +** the correct value for the expression. */ static void exprToRegister(Expr *p, int iReg){ p->op2 = p->op; p->op = TK_REGISTER; p->iTable = iReg; ExprClearProperty(p, EP_Skip); } + +/* +** Evaluate an expression (either a vector or a scalar expression) and store +** the result in continguous temporary registers. Return the index of +** the first register used to store the result. +** +** If the returned result register is a temporary scalar, then also write +** that register number into *piFreeable. If the returned result register +** is not a temporary or if the expression is a vector set *piFreeable +** to 0. +*/ +static int exprCodeVector(Parse *pParse, Expr *p, int *piFreeable){ + int iResult; + int nResult = sqlite3ExprVectorSize(p); + if( nResult==1 ){ + iResult = sqlite3ExprCodeTemp(pParse, p, piFreeable); + }else{ + *piFreeable = 0; + if( p->op==TK_SELECT ){ + iResult = sqlite3CodeSubselect(pParse, p, 0, 0); + }else{ + int i; + iResult = pParse->nMem+1; + pParse->nMem += nResult; + for(i=0; ix.pList->a[i].pExpr, i+iResult); + } + } + } + return iResult; +} + /* ** Generate code into the current Vdbe to evaluate the given ** expression. Attempt to store the results in register "target". ** Return the register where results are stored. @@ -2684,13 +3345,14 @@ Vdbe *v = pParse->pVdbe; /* The VM under construction */ int op; /* The opcode being coded */ int inReg = target; /* Results stored in register inReg */ int regFree1 = 0; /* If non-zero free this temporary register */ int regFree2 = 0; /* If non-zero free this temporary register */ - int r1, r2, r3, r4; /* Various register numbers */ + int r1, r2; /* Various register numbers */ sqlite3 *db = pParse->db; /* The database connection */ Expr tempX; /* Temporary expression node */ + int p5 = 0; assert( target>0 && target<=pParse->nMem ); if( v==0 ){ assert( pParse->db->mallocFailed ); return 0; @@ -2799,43 +3461,38 @@ testcase( usedAsColumnCache(pParse, inReg, inReg) ); sqlite3ExprCacheAffinityChange(pParse, inReg, 1); break; } #endif /* SQLITE_OMIT_CAST */ + case TK_IS: + case TK_ISNOT: + op = (op==TK_IS) ? TK_EQ : TK_NE; + p5 = SQLITE_NULLEQ; + /* fall-through */ case TK_LT: case TK_LE: case TK_GT: case TK_GE: case TK_NE: case TK_EQ: { - r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); - r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); - codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, - r1, r2, inReg, SQLITE_STOREP2); - assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt); - assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le); - assert(TK_GT==OP_Gt); testcase(op==OP_Gt); VdbeCoverageIf(v,op==OP_Gt); - assert(TK_GE==OP_Ge); testcase(op==OP_Ge); VdbeCoverageIf(v,op==OP_Ge); - assert(TK_EQ==OP_Eq); testcase(op==OP_Eq); VdbeCoverageIf(v,op==OP_Eq); - assert(TK_NE==OP_Ne); testcase(op==OP_Ne); VdbeCoverageIf(v,op==OP_Ne); - testcase( regFree1==0 ); - testcase( regFree2==0 ); - break; - } - case TK_IS: - case TK_ISNOT: { - testcase( op==TK_IS ); - testcase( op==TK_ISNOT ); - r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); - r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); - op = (op==TK_IS) ? TK_EQ : TK_NE; - codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, - r1, r2, inReg, SQLITE_STOREP2 | SQLITE_NULLEQ); - VdbeCoverageIf(v, op==TK_EQ); - VdbeCoverageIf(v, op==TK_NE); - testcase( regFree1==0 ); - testcase( regFree2==0 ); + Expr *pLeft = pExpr->pLeft; + if( sqlite3ExprIsVector(pLeft) ){ + codeVectorCompare(pParse, pExpr, target, op, p5); + }else{ + r1 = sqlite3ExprCodeTemp(pParse, pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); + codeCompare(pParse, pLeft, pExpr->pRight, op, + r1, r2, inReg, SQLITE_STOREP2 | p5); + assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt); + assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le); + assert(TK_GT==OP_Gt); testcase(op==OP_Gt); VdbeCoverageIf(v,op==OP_Gt); + assert(TK_GE==OP_Ge); testcase(op==OP_Ge); VdbeCoverageIf(v,op==OP_Ge); + assert(TK_EQ==OP_Eq); testcase(op==OP_Eq); VdbeCoverageIf(v,op==OP_Eq); + assert(TK_NE==OP_Ne); testcase(op==OP_Ne); VdbeCoverageIf(v,op==OP_Ne); + testcase( regFree1==0 ); + testcase( regFree2==0 ); + } break; } case TK_AND: case TK_OR: case TK_PLUS: @@ -3057,13 +3714,25 @@ break; } #ifndef SQLITE_OMIT_SUBQUERY case TK_EXISTS: case TK_SELECT: { + int nCol; testcase( op==TK_EXISTS ); testcase( op==TK_SELECT ); - inReg = sqlite3CodeSubselect(pParse, pExpr, 0, 0); + if( op==TK_SELECT && (nCol = pExpr->x.pSelect->pEList->nExpr)!=1 ){ + sqlite3SubselectError(pParse, nCol, 1); + }else{ + inReg = sqlite3CodeSubselect(pParse, pExpr, 0, 0); + } + break; + } + case TK_SELECT_COLUMN: { + if( pExpr->pLeft->iTable==0 ){ + pExpr->pLeft->iTable = sqlite3CodeSubselect(pParse, pExpr->pLeft, 0, 0); + } + inReg = pExpr->pLeft->iTable + pExpr->iColumn; break; } case TK_IN: { int destIfFalse = sqlite3VdbeMakeLabel(v); int destIfNull = sqlite3VdbeMakeLabel(v); @@ -3088,32 +3757,11 @@ ** X is stored in pExpr->pLeft. ** Y is stored in pExpr->pList->a[0].pExpr. ** Z is stored in pExpr->pList->a[1].pExpr. */ case TK_BETWEEN: { - Expr *pLeft = pExpr->pLeft; - struct ExprList_item *pLItem = pExpr->x.pList->a; - Expr *pRight = pLItem->pExpr; - - r1 = sqlite3ExprCodeTemp(pParse, pLeft, ®Free1); - r2 = sqlite3ExprCodeTemp(pParse, pRight, ®Free2); - testcase( regFree1==0 ); - testcase( regFree2==0 ); - r3 = sqlite3GetTempReg(pParse); - r4 = sqlite3GetTempReg(pParse); - codeCompare(pParse, pLeft, pRight, OP_Ge, - r1, r2, r3, SQLITE_STOREP2); VdbeCoverage(v); - pLItem++; - pRight = pLItem->pExpr; - sqlite3ReleaseTempReg(pParse, regFree2); - r2 = sqlite3ExprCodeTemp(pParse, pRight, ®Free2); - testcase( regFree2==0 ); - codeCompare(pParse, pLeft, pRight, OP_Le, r1, r2, r4, SQLITE_STOREP2); - VdbeCoverage(v); - sqlite3VdbeAddOp3(v, OP_And, r3, r4, target); - sqlite3ReleaseTempReg(pParse, r3); - sqlite3ReleaseTempReg(pParse, r4); + exprCodeBetween(pParse, pExpr, target, 0, 0); break; } case TK_SPAN: case TK_COLLATE: case TK_UPLUS: { @@ -3175,10 +3823,14 @@ } #endif break; } + case TK_VECTOR: { + sqlite3ErrorMsg(pParse, "row value misused"); + break; + } /* ** Form A: ** CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END ** @@ -3218,12 +3870,13 @@ nExpr = pEList->nExpr; endLabel = sqlite3VdbeMakeLabel(v); if( (pX = pExpr->pLeft)!=0 ){ tempX = *pX; testcase( pX->op==TK_COLUMN ); - exprToRegister(&tempX, sqlite3ExprCodeTemp(pParse, pX, ®Free1)); + exprToRegister(&tempX, exprCodeVector(pParse, &tempX, ®Free1)); testcase( regFree1==0 ); + memset(&opCompare, 0, sizeof(opCompare)); opCompare.op = TK_EQ; opCompare.pLeft = &tempX; pTest = &opCompare; /* Ticket b351d95f9cd5ef17e9d9dbae18f5ca8611190001: ** The value in regFree1 might get SCopy-ed into the file result. @@ -3498,24 +4151,37 @@ ** ** x>=y AND x<=z ** ** Code it as such, taking care to do the common subexpression ** elimination of x. +** +** The xJumpIf parameter determines details: +** +** NULL: Store the boolean result in reg[dest] +** sqlite3ExprIfTrue: Jump to dest if true +** sqlite3ExprIfFalse: Jump to dest if false +** +** The jumpIfNull parameter is ignored if xJumpIf is NULL. */ static void exprCodeBetween( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* The BETWEEN expression */ - int dest, /* Jump here if the jump is taken */ - int jumpIfTrue, /* Take the jump if the BETWEEN is true */ + int dest, /* Jump destination or storage location */ + void (*xJump)(Parse*,Expr*,int,int), /* Action to take */ int jumpIfNull /* Take the jump if the BETWEEN is NULL */ ){ - Expr exprAnd; /* The AND operator in x>=y AND x<=z */ + Expr exprAnd; /* The AND operator in x>=y AND x<=z */ Expr compLeft; /* The x>=y term */ Expr compRight; /* The x<=z term */ Expr exprX; /* The x subexpression */ int regFree1 = 0; /* Temporary use register */ + + memset(&compLeft, 0, sizeof(Expr)); + memset(&compRight, 0, sizeof(Expr)); + memset(&exprAnd, 0, sizeof(Expr)); + assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); exprX = *pExpr->pLeft; exprAnd.op = TK_AND; exprAnd.pLeft = &compLeft; exprAnd.pRight = &compRight; @@ -3523,27 +4189,29 @@ compLeft.pLeft = &exprX; compLeft.pRight = pExpr->x.pList->a[0].pExpr; compRight.op = TK_LE; compRight.pLeft = &exprX; compRight.pRight = pExpr->x.pList->a[1].pExpr; - exprToRegister(&exprX, sqlite3ExprCodeTemp(pParse, &exprX, ®Free1)); - if( jumpIfTrue ){ - sqlite3ExprIfTrue(pParse, &exprAnd, dest, jumpIfNull); + exprToRegister(&exprX, exprCodeVector(pParse, &exprX, ®Free1)); + if( xJump ){ + xJump(pParse, &exprAnd, dest, jumpIfNull); }else{ - sqlite3ExprIfFalse(pParse, &exprAnd, dest, jumpIfNull); + exprX.flags |= EP_FromJoin; + sqlite3ExprCodeTarget(pParse, &exprAnd, dest); } sqlite3ReleaseTempReg(pParse, regFree1); /* Ensure adequate test coverage */ - testcase( jumpIfTrue==0 && jumpIfNull==0 && regFree1==0 ); - testcase( jumpIfTrue==0 && jumpIfNull==0 && regFree1!=0 ); - testcase( jumpIfTrue==0 && jumpIfNull!=0 && regFree1==0 ); - testcase( jumpIfTrue==0 && jumpIfNull!=0 && regFree1!=0 ); - testcase( jumpIfTrue!=0 && jumpIfNull==0 && regFree1==0 ); - testcase( jumpIfTrue!=0 && jumpIfNull==0 && regFree1!=0 ); - testcase( jumpIfTrue!=0 && jumpIfNull!=0 && regFree1==0 ); - testcase( jumpIfTrue!=0 && jumpIfNull!=0 && regFree1!=0 ); + testcase( xJump==sqlite3ExprIfTrue && jumpIfNull==0 && regFree1==0 ); + testcase( xJump==sqlite3ExprIfTrue && jumpIfNull==0 && regFree1!=0 ); + testcase( xJump==sqlite3ExprIfTrue && jumpIfNull!=0 && regFree1==0 ); + testcase( xJump==sqlite3ExprIfTrue && jumpIfNull!=0 && regFree1!=0 ); + testcase( xJump==sqlite3ExprIfFalse && jumpIfNull==0 && regFree1==0 ); + testcase( xJump==sqlite3ExprIfFalse && jumpIfNull==0 && regFree1!=0 ); + testcase( xJump==sqlite3ExprIfFalse && jumpIfNull!=0 && regFree1==0 ); + testcase( xJump==sqlite3ExprIfFalse && jumpIfNull!=0 && regFree1!=0 ); + testcase( xJump==0 ); } /* ** Generate code for a boolean expression such that a jump is made ** to the label "dest" if the expression is true but execution @@ -3604,10 +4272,11 @@ case TK_LE: case TK_GT: case TK_GE: case TK_NE: case TK_EQ: { + if( sqlite3ExprIsVector(pExpr->pLeft) ) goto default_expr; testcase( jumpIfNull==0 ); r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); @@ -3636,11 +4305,11 @@ testcase( regFree1==0 ); break; } case TK_BETWEEN: { testcase( jumpIfNull==0 ); - exprCodeBetween(pParse, pExpr, dest, 1, jumpIfNull); + exprCodeBetween(pParse, pExpr, dest, sqlite3ExprIfTrue, jumpIfNull); break; } #ifndef SQLITE_OMIT_SUBQUERY case TK_IN: { int destIfFalse = sqlite3VdbeMakeLabel(v); @@ -3650,10 +4319,11 @@ sqlite3VdbeResolveLabel(v, destIfFalse); break; } #endif default: { + default_expr: if( exprAlwaysTrue(pExpr) ){ sqlite3VdbeGoto(v, dest); }else if( exprAlwaysFalse(pExpr) ){ /* No-op */ }else{ @@ -3756,10 +4426,11 @@ case TK_LE: case TK_GT: case TK_GE: case TK_NE: case TK_EQ: { + if( sqlite3ExprIsVector(pExpr->pLeft) ) goto default_expr; testcase( jumpIfNull==0 ); r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); @@ -3786,11 +4457,11 @@ testcase( regFree1==0 ); break; } case TK_BETWEEN: { testcase( jumpIfNull==0 ); - exprCodeBetween(pParse, pExpr, dest, 0, jumpIfNull); + exprCodeBetween(pParse, pExpr, dest, sqlite3ExprIfFalse, jumpIfNull); break; } #ifndef SQLITE_OMIT_SUBQUERY case TK_IN: { if( jumpIfNull ){ @@ -3802,10 +4473,11 @@ } break; } #endif default: { + default_expr: if( exprAlwaysFalse(pExpr) ){ sqlite3VdbeGoto(v, dest); }else if( exprAlwaysTrue(pExpr) ){ /* no-op */ }else{ @@ -4317,14 +4989,15 @@ pParse->aTempReg[pParse->nTempReg++] = iReg; } } /* -** Allocate or deallocate a block of nReg consecutive registers +** Allocate or deallocate a block of nReg consecutive registers. */ int sqlite3GetTempRange(Parse *pParse, int nReg){ int i, n; + if( nReg==1 ) return sqlite3GetTempReg(pParse); i = pParse->iRangeReg; n = pParse->nRangeReg; if( nReg<=n ){ assert( !usedAsColumnCache(pParse, i, i+n-1) ); pParse->iRangeReg += nReg; @@ -4334,10 +5007,14 @@ pParse->nMem += nReg; } return i; } void sqlite3ReleaseTempRange(Parse *pParse, int iReg, int nReg){ + if( nReg==1 ){ + sqlite3ReleaseTempReg(pParse, iReg); + return; + } sqlite3ExprCacheRemove(pParse, iReg, nReg); if( nReg>pParse->nRangeReg ){ pParse->nRangeReg = nReg; pParse->iRangeReg = iReg; } ADDED src/in-operator.md Index: src/in-operator.md ================================================================== --- /dev/null +++ src/in-operator.md @@ -0,0 +1,107 @@ +IN-Operator Implementation Notes +================================ + +## Definitions: + +An IN operator has one of the following formats: + +> + x IN (y1,y2,y3,...,yN) + x IN (subquery) + +The "x" is referred to as the LHS (left-hand side). The list or subquery +on the right is called the RHS (right-hand side). If the RHS is a list +it must be a non-empty list. But if the RHS is a subquery, it can be an +empty set. + +The LHS can be a scalar (a single quantity) or a vector (a list of +two or or more values) or a subquery that returns one or more columns. +We use the term "vector" to mean an actually list of values or a +subquery that returns two or more columns. An isolated value or +a subquery that returns a single columns is called a scalar. + +The RHS can be a subquery that returns a single column, a subquery +that returns two or more columns, or a list of scalars. It is not +currently support for the RHS to be a list of vectors. + +The number of columns for LHS must match the number of columns for +the RHS. If the RHS is a list of values, then the LHS must be a +scalar. If the RHS is a subquery returning N columns, then the LHS +must be a vector of size N. + +NULL values can occur in either or both of the LHS and RHS. +If the LHS contains only +NULL values then we say that it is a "total-NULL". If the LHS contains +some NULL values and some non-NULL values, then it is a "partial-NULL". +For a scalar, there is no difference between a partial-NULL and a total-NULL. +The RHS is a partial-NULL if any row contains a NULL value. The RHS is +a total-NULL if it contains one or more rows that contain only NULL values. +The LHS is called "non-NULL" if it contains no NULL values. The RHS is +called "non-NULL" if it contains no NULL values in any row. + +The result of an IN operator is one of TRUE, FALSE, or NULL. A NULL result +means that it cannot be determined if the LHS is contained in the RHS due +to the presence of NULL values. In some contexts (for example, when the IN +operator occurs in a WHERE clause) +the system only needs a binary result: TRUE or NOT-TRUE. One can also +to define a binary result of FALSE and NOT-FALSE, but +it turns out that no extra optimizations are possible in that case, so if +the FALSE/NOT-FALSE binary is needed, we have to compute the three-state +TRUE/FALSE/NULL result and then combine the TRUE and NULL values into +NOT-FALSE. + +A "NOT IN" operator is computed by first computing the equivalent IN +operator, then interchanging the TRUE and FALSE results. + +## Simple Full-Scan Algorithm + +The following algorithm always compute the correct answer. However, this +algorithm is suboptimal, especially if there are many rows on the RHS. + + 1. Set the null-flag to false + 2. For each row in the RHS: +
    +
  1. Compare the LHS against the RHS +
  2. If the LHS exactly matches the RHS, immediately return TRUE +
  3. If the comparison result is NULL, set the null-flag to true +
+ 3. If the null-flag is true, return NULL. + 4. Return FALSE + +## Optimized Algorithm + +The following procedure computes the same answer as the simple full-scan +algorithm, though it does so with less work in the common case. This +is the algorithm that is implemented in SQLite. + + 1. If the RHS is a constant list of length 1 or 2, then rewrite the + IN operator as a simple expression. Implement + + x IN (y1,y2) + + as if it were + + x=y1 OR x=y2 + + This is the INDEX_NOOP optimization and is only undertaken if the + IN operator is used for membership testing. If the IN operator is + driving a loop, then skip this step entirely. + + 2. Check the LHS to see if it is a partial-NULL and if it is, jump + ahead to step 5. + + 3. Do a binary search of the RHS using the LHS as a probe. If + an exact match is found, return TRUE. + + 4. If the RHS is non-NULL then return FALSE. + + 5. If we do not need to distinguish between FALSE and NULL, + then return FALSE. + + 6. For each row in the RHS, compare that row against the LHS and + if the result is NULL, immediately return NULL. In the case + of a scalar IN operator, we only need to look at the very first + row the RHS because for a scalar RHS, all NULLs will always come + first. If the RHS is empty, this step is a no-op. + + 7. Return FALSE. Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -787,14 +787,20 @@ setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). { A = sqlite3ExprListAppend(pParse, A, Y.pExpr); sqlite3ExprListSetName(pParse, A, &X, 1); } +setlist(A) ::= setlist(A) COMMA LP idlist(X) RP EQ expr(Y). { + A = sqlite3ExprListAppendVector(pParse, A, X, Y.pExpr); +} setlist(A) ::= nm(X) EQ expr(Y). { A = sqlite3ExprListAppend(pParse, 0, Y.pExpr); sqlite3ExprListSetName(pParse, A, &X, 1); } +setlist(A) ::= LP idlist(X) RP EQ expr(Y). { + A = sqlite3ExprListAppendVector(pParse, 0, X, Y.pExpr); +} ////////////////////////// The INSERT command ///////////////////////////////// // cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). { sqlite3WithPush(pParse, W, 1); @@ -943,10 +949,21 @@ if( doNot ){ pSpan->pExpr = sqlite3PExpr(pParse, TK_NOT, pSpan->pExpr, 0, 0); } } } + +expr(A) ::= LP(L) nexprlist(X) COMMA expr(Y) RP(R). { + ExprList *pList = sqlite3ExprListAppend(pParse, X, Y.pExpr); + A.pExpr = sqlite3PExpr(pParse, TK_VECTOR, 0, 0, 0); + if( A.pExpr ){ + A.pExpr->x.pList = pList; + spanSet(&A, &L, &R); + }else{ + sqlite3ExprListDelete(pParse->db, pList); + } +} expr(A) ::= expr(A) AND(OP) expr(Y). {spanBinaryExpr(pParse,@OP,&A,&Y);} expr(A) ::= expr(A) OR(OP) expr(Y). {spanBinaryExpr(pParse,@OP,&A,&Y);} expr(A) ::= expr(A) LT|GT|GE|LE(OP) expr(Y). {spanBinaryExpr(pParse,@OP,&A,&Y);} Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -773,10 +773,37 @@ break; } case TK_VARIABLE: { notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr); break; + } + case TK_EQ: + case TK_NE: + case TK_LT: + case TK_LE: + case TK_GT: + case TK_GE: + case TK_IS: + case TK_ISNOT: { + int nLeft, nRight; + if( pParse->db->mallocFailed ) break; + assert( pExpr->pRight!=0 ); + assert( pExpr->pLeft!=0 ); + nLeft = sqlite3ExprVectorSize(pExpr->pLeft); + nRight = sqlite3ExprVectorSize(pExpr->pRight); + if( nLeft!=nRight ){ + testcase( pExpr->op==TK_EQ ); + testcase( pExpr->op==TK_NE ); + testcase( pExpr->op==TK_LT ); + testcase( pExpr->op==TK_LE ); + testcase( pExpr->op==TK_GT ); + testcase( pExpr->op==TK_GE ); + testcase( pExpr->op==TK_IS ); + testcase( pExpr->op==TK_ISNOT ); + sqlite3ErrorMsg(pParse, "row value misused"); + } + break; } } return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue; } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -86,11 +86,11 @@ ** Initialize a SelectDest structure. */ void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){ pDest->eDest = (u8)eDest; pDest->iSDParm = iParm; - pDest->affSdst = 0; + pDest->zAffSdst = 0; pDest->iSdst = 0; pDest->nSdst = 0; } @@ -657,34 +657,10 @@ sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, N, r1); sqlite3VdbeAddOp2(v, OP_IdxInsert, iTab, r1); sqlite3ReleaseTempReg(pParse, r1); } -#ifndef SQLITE_OMIT_SUBQUERY -/* -** Generate an error message when a SELECT is used within a subexpression -** (example: "a IN (SELECT * FROM table)") but it has more than 1 result -** column. We do this in a subroutine because the error used to occur -** in multiple places. (The error only occurs in one place now, but we -** retain the subroutine to minimize code disruption.) -*/ -static int checkForMultiColumnSelectError( - Parse *pParse, /* Parse context. */ - SelectDest *pDest, /* Destination of SELECT results */ - int nExpr /* Number of result columns returned by SELECT */ -){ - int eDest = pDest->eDest; - if( nExpr>1 && (eDest==SRT_Mem || eDest==SRT_Set) ){ - sqlite3ErrorMsg(pParse, "only a single result allowed for " - "a SELECT that is part of an expression"); - return 1; - }else{ - return 0; - } -} -#endif - /* ** This routine generates the code for the inside of the inner loop ** of a SELECT. ** ** If srcTab is negative, then the pEList expressions @@ -890,23 +866,23 @@ /* If we are creating a set for an "expr IN (SELECT ...)" construct, ** then there should be a single item on the stack. Write this ** item into the set table with bogus data. */ case SRT_Set: { - assert( nResultCol==1 ); - pDest->affSdst = - sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst); if( pSort ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ - pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg); + pushOntoSorter( + pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg); }else{ int r1 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult,1,r1, &pDest->affSdst, 1); - sqlite3ExprCacheAffinityChange(pParse, regResult, 1); + assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol ); + sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, + r1, pDest->zAffSdst, nResultCol); + sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); sqlite3ReleaseTempReg(pParse, r1); } break; } @@ -918,17 +894,18 @@ /* The LIMIT clause will terminate the loop for us */ break; } /* If this is a scalar select that is part of an expression, then - ** store the results in the appropriate memory cell and break out - ** of the scan loop. + ** store the results in the appropriate memory cell or array of + ** memory cells and break out of the scan loop. */ case SRT_Mem: { - assert( nResultCol==1 ); + assert( nResultCol==pDest->nSdst ); if( pSort ){ - pushOntoSorter(pParse, pSort, p, regResult, regResult, 1, nPrefixReg); + pushOntoSorter( + pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg); }else{ assert( regResult==iParm ); /* The LIMIT clause will jump out of the loop for us */ } break; @@ -1239,18 +1216,18 @@ sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); sqlite3VdbeGoto(v, addrBreak); sqlite3VdbeResolveLabel(v, pSort->labelBkOut); } iTab = pSort->iECursor; - if( eDest==SRT_Output || eDest==SRT_Coroutine ){ + if( eDest==SRT_Output || eDest==SRT_Coroutine || eDest==SRT_Mem ){ regRowid = 0; regRow = pDest->iSdst; nSortData = nColumn; }else{ regRowid = sqlite3GetTempReg(pParse); - regRow = sqlite3GetTempReg(pParse); - nSortData = 1; + regRow = sqlite3GetTempRange(pParse, nColumn); + nSortData = nColumn; } nKey = pOrderBy->nExpr - pSort->nOBSat; if( pSort->sortFlags & SORTFLAG_UseSorter ){ int regSortOut = ++pParse->nMem; iSortTab = pParse->nTab++; @@ -1281,20 +1258,18 @@ sqlite3VdbeChangeP5(v, OPFLAG_APPEND); break; } #ifndef SQLITE_OMIT_SUBQUERY case SRT_Set: { - assert( nColumn==1 ); - sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, 1, regRowid, - &pDest->affSdst, 1); - sqlite3ExprCacheAffinityChange(pParse, regRow, 1); + assert( nColumn==sqlite3Strlen30(pDest->zAffSdst) ); + sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, nColumn, regRowid, + pDest->zAffSdst, nColumn); + sqlite3ExprCacheAffinityChange(pParse, regRow, nColumn); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid); break; } case SRT_Mem: { - assert( nColumn==1 ); - sqlite3ExprCodeMove(pParse, regRow, iParm, 1); /* The LIMIT clause will terminate the loop for us */ break; } #endif default: { @@ -1309,11 +1284,15 @@ } break; } } if( regRowid ){ - sqlite3ReleaseTempReg(pParse, regRow); + if( eDest==SRT_Set ){ + sqlite3ReleaseTempRange(pParse, regRow, nColumn); + }else{ + sqlite3ReleaseTempReg(pParse, regRow); + } sqlite3ReleaseTempReg(pParse, regRowid); } /* The bottom of the loop */ sqlite3VdbeResolveLabel(v, addrContinue); @@ -1811,11 +1790,11 @@ ** Get a VDBE for the given parser context. Create a new one if necessary. ** If an error occurs, return NULL and leave a message in pParse. */ static SQLITE_NOINLINE Vdbe *allocVdbe(Parse *pParse){ Vdbe *v = pParse->pVdbe = sqlite3VdbeCreate(pParse); - if( v ) sqlite3VdbeAddOp0(v, OP_Init); + if( v ) sqlite3VdbeAddOp2(v, OP_Init, 0, 1); if( pParse->pToplevel==0 && OptimizationEnabled(pParse->db,SQLITE_FactorOutConst) ){ pParse->okConstFactor = 1; } @@ -2650,22 +2629,19 @@ sqlite3ReleaseTempReg(pParse, r1); break; } #ifndef SQLITE_OMIT_SUBQUERY - /* If we are creating a set for an "expr IN (SELECT ...)" construct, - ** then there should be a single item on the stack. Write this - ** item into the set table with bogus data. + /* If we are creating a set for an "expr IN (SELECT ...)". */ case SRT_Set: { int r1; - assert( pIn->nSdst==1 || pParse->nErr>0 ); - pDest->affSdst = - sqlite3CompareAffinity(p->pEList->a[0].pExpr, pDest->affSdst); + testcase( pIn->nSdst>1 ); r1 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iSdst, 1, r1, &pDest->affSdst,1); - sqlite3ExprCacheAffinityChange(pParse, pIn->iSdst, 1); + sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iSdst, pIn->nSdst, + r1, pDest->zAffSdst, pIn->nSdst); + sqlite3ExprCacheAffinityChange(pParse, pIn->iSdst, pIn->nSdst); sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iSDParm, r1); sqlite3ReleaseTempReg(pParse, r1); break; } @@ -4893,20 +4869,10 @@ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } -#endif - - - /* If writing to memory or generating a set - ** only a single column may be output. - */ -#ifndef SQLITE_OMIT_SUBQUERY - if( checkForMultiColumnSelectError(pParse, pDest, p->pEList->nExpr) ){ - goto select_end; - } #endif /* Try to flatten subqueries in the FROM clause up into the main query */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1731,10 +1731,11 @@ ** The SQLITE_NOTNULL flag is a combination of NULLEQ and JUMPIFNULL. ** It causes an assert() to fire if either operand to a comparison ** operator is NULL. It is added to certain comparison operators to ** prove that the operands are always NOT NULL. */ +#define SQLITE_KEEPNULL 0x08 /* Used by vector == or <> */ #define SQLITE_JUMPIFNULL 0x10 /* jumps if either operand is NULL */ #define SQLITE_STOREP2 0x20 /* Store result in reg[P2] rather than jump */ #define SQLITE_NULLEQ 0x80 /* NULL=NULL */ #define SQLITE_NOTNULL 0x90 /* Assert that operands are never NULL */ @@ -2295,13 +2296,15 @@ int nHeight; /* Height of the tree headed by this node */ #endif int iTable; /* TK_COLUMN: cursor number of table holding column ** TK_REGISTER: register number ** TK_TRIGGER: 1 -> new, 0 -> old - ** EP_Unlikely: 134217728 times likelihood */ + ** EP_Unlikely: 134217728 times likelihood + ** TK_SELECT: 1st register of result vector */ ynVar iColumn; /* TK_COLUMN: column index. -1 for rowid. - ** TK_VARIABLE: variable number (always >= 1). */ + ** TK_VARIABLE: variable number (always >= 1). + ** TK_SELECT_COLUMN: column of the result vector */ i16 iAgg; /* Which entry in pAggInfo->aCol[] or ->aFunc[] */ i16 iRightJoinTable; /* If EP_FromJoin, the right table of the join */ u8 op2; /* TK_REGISTER: original value of Expr.op ** TK_COLUMN: the value of p5 for OP_Column ** TK_AGG_FUNCTION: nesting depth */ @@ -2778,11 +2781,11 @@ ** An instance of this object describes where to put of the results of ** a SELECT statement. */ struct SelectDest { u8 eDest; /* How to dispose of the results. On of SRT_* above. */ - char affSdst; /* Affinity used when eDest==SRT_Set */ + char *zAffSdst; /* Affinity used when eDest==SRT_Set */ int iSDParm; /* A parameter used by the eDest disposal method */ int iSdst; /* Base register where results are written */ int nSdst; /* Number of registers allocated */ ExprList *pOrderBy; /* Key columns for SRT_Queue and SRT_DistQueue */ }; @@ -3510,10 +3513,11 @@ void *sqlite3TestTextToPtr(const char*); #endif #if defined(SQLITE_DEBUG) void sqlite3TreeViewExpr(TreeView*, const Expr*, u8); + void sqlite3TreeViewBareExprList(TreeView*, const ExprList*, const char*); void sqlite3TreeViewExprList(TreeView*, const ExprList*, u8, const char*); void sqlite3TreeViewSelect(TreeView*, const Select*, u8); void sqlite3TreeViewWith(TreeView*, const With*, u8); #endif @@ -3541,10 +3545,11 @@ Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*); Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*); void sqlite3ExprAssignVarNumber(Parse*, Expr*); void sqlite3ExprDelete(sqlite3*, Expr*); ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*); +ExprList *sqlite3ExprListAppendVector(Parse*,ExprList*,IdList*,Expr*); void sqlite3ExprListSetSortOrder(ExprList*,int); void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int); void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*); void sqlite3ExprListDelete(sqlite3*, ExprList*); u32 sqlite3ExprListFlags(const ExprList*); @@ -3876,10 +3881,11 @@ const char *sqlite3IndexAffinityStr(sqlite3*, Index*); void sqlite3TableAffinity(Vdbe*, Table*, int); char sqlite3CompareAffinity(Expr *pExpr, char aff2); int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity); +char sqlite3TableColumnAffinity(Table*,int); char sqlite3ExprAffinity(Expr *pExpr); int sqlite3Atoi64(const char*, i64*, int, u8); int sqlite3DecOrHexToI64(const char*, i64*); void sqlite3ErrorWithMsg(sqlite3*, int, const char*,...); void sqlite3Error(sqlite3*,int); @@ -3941,11 +3947,11 @@ 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 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*); @@ -3995,17 +4001,25 @@ void sqlite3SelectDestInit(SelectDest*,int,int); Expr *sqlite3CreateColumnExpr(sqlite3 *, SrcList *, int, int); void sqlite3BackupRestart(sqlite3_backup *); void sqlite3BackupUpdate(sqlite3_backup *, Pgno, const u8 *); + +#ifndef SQLITE_OMIT_SUBQUERY +int sqlite3ExprCheckIN(Parse*, Expr*); +#else +# define sqlite3ExprCheckIN(x,y) SQLITE_OK +#endif #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 void sqlite3AnalyzeFunctions(void); -int sqlite3Stat4ProbeSetValue(Parse*,Index*,UnpackedRecord**,Expr*,u8,int,int*); +int sqlite3Stat4ProbeSetValue( + Parse*,Index*,UnpackedRecord**,Expr*,int,int,int*); int sqlite3Stat4ValueFromExpr(Parse*, Expr*, u8, sqlite3_value**); void sqlite3Stat4ProbeFree(UnpackedRecord*); int sqlite3Stat4Column(sqlite3*, const void*, int, int, sqlite3_value**); +char sqlite3IndexColumnAffinity(sqlite3*, Index*, int); #endif /* ** The interface to the LEMON-generated parser */ @@ -4154,11 +4168,11 @@ ** Allowed flags for the 3rd parameter to sqlite3FindInIndex(). */ #define IN_INDEX_NOOP_OK 0x0001 /* OK to return IN_INDEX_NOOP */ #define IN_INDEX_MEMBERSHIP 0x0002 /* IN operator used for membership test */ #define IN_INDEX_LOOP 0x0004 /* IN operator used as a loop */ -int sqlite3FindInIndex(Parse *, Expr *, u32, int*); +int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*); int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int); int sqlite3JournalSize(sqlite3_vfs *); #ifdef SQLITE_ENABLE_ATOMIC_WRITE int sqlite3JournalCreate(sqlite3_file *); @@ -4259,6 +4273,11 @@ #if defined(SQLITE_ENABLE_DBSTAT_VTAB) || defined(SQLITE_TEST) int sqlite3DbstatRegister(sqlite3*); #endif +int sqlite3ExprVectorSize(Expr *pExpr); +int sqlite3ExprIsVector(Expr *pExpr); +Expr *sqlite3VectorFieldSubexpr(Expr*, int); +Expr *sqlite3ExprForVectorField(Parse*,Expr*,int); + #endif /* SQLITEINT_H */ Index: src/treeview.c ================================================================== --- src/treeview.c +++ src/treeview.c @@ -448,10 +448,19 @@ case TK_MATCH: { sqlite3TreeViewLine(pView, "MATCH {%d:%d}%s", pExpr->iTable, pExpr->iColumn, zFlgs); sqlite3TreeViewExpr(pView, pExpr->pRight, 0); break; + } + case TK_VECTOR: { + sqlite3TreeViewBareExprList(pView, pExpr->x.pList, "VECTOR"); + break; + } + case TK_SELECT_COLUMN: { + sqlite3TreeViewLine(pView, "SELECT-COLUMN %d", pExpr->iColumn); + sqlite3TreeViewSelect(pView, pExpr->pLeft->x.pSelect, 0); + break; } default: { sqlite3TreeViewLine(pView, "op=%d", pExpr->op); break; } @@ -464,26 +473,25 @@ sqlite3TreeViewLine(pView, "%s%s", zUniOp, zFlgs); sqlite3TreeViewExpr(pView, pExpr->pLeft, 0); } sqlite3TreeViewPop(pView); } + /* ** Generate a human-readable explanation of an expression list. */ -void sqlite3TreeViewExprList( +void sqlite3TreeViewBareExprList( TreeView *pView, const ExprList *pList, - u8 moreToFollow, const char *zLabel ){ - int i; - pView = sqlite3TreeViewPush(pView, moreToFollow); if( zLabel==0 || zLabel[0]==0 ) zLabel = "LIST"; if( pList==0 ){ sqlite3TreeViewLine(pView, "%s (empty)", zLabel); }else{ + int i; sqlite3TreeViewLine(pView, "%s", zLabel); for(i=0; inExpr; i++){ int j = pList->a[i].u.x.iOrderByCol; if( j ){ sqlite3TreeViewPush(pView, 0); @@ -491,9 +499,18 @@ } sqlite3TreeViewExpr(pView, pList->a[i].pExpr, inExpr-1); if( j ) sqlite3TreeViewPop(pView); } } +} +void sqlite3TreeViewExprList( + TreeView *pView, + const ExprList *pList, + u8 moreToFollow, + const char *zLabel +){ + pView = sqlite3TreeViewPush(pView, moreToFollow); + sqlite3TreeViewBareExprList(pView, pList, zLabel); sqlite3TreeViewPop(pView); } #endif /* SQLITE_DEBUG */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -571,11 +571,11 @@ #endif int rc = SQLITE_OK; /* Value to return */ sqlite3 *db = p->db; /* The database */ u8 resetSchemaOnFault = 0; /* Reset schema after an error if positive */ u8 encoding = ENC(db); /* The database encoding */ - int iCompare = 0; /* Result of last OP_Compare operation */ + int iCompare = 0; /* Result of last comparison */ unsigned nVmStep = 0; /* Number of virtual machine steps */ #ifndef SQLITE_OMIT_PROGRESS_CALLBACK unsigned nProgressLimit = 0;/* Invoke xProgress() when nVmStep reaches this */ #endif Mem *aMem = p->aMem; /* Copy of p->aMem */ @@ -903,11 +903,11 @@ pOp = &aOp[pcDest]; break; } /* Opcode: HaltIfNull P1 P2 P3 P4 P5 -** Synopsis: if r[P3]=null halt +** Synopsis: if r[P3]=null halt ** ** Check the value in register P3. If it is NULL then Halt using ** parameter P1, P2, and P4 as if this were a Halt instruction. If the ** value in register P3 is not NULL, then this routine is a no-op. ** The P5 parameter should be 1. @@ -1116,11 +1116,11 @@ #endif break; } /* Opcode: Null P1 P2 P3 * * -** Synopsis: r[P2..P3]=NULL +** Synopsis: r[P2..P3]=NULL ** ** Write a NULL into registers P2. If P3 greater than P2, then also write ** NULL into register P3 and every register in between P2 and P3. If P3 ** is less than P2 (typically P3 is zero) then only register P2 is ** set to NULL. @@ -1145,11 +1145,11 @@ } break; } /* Opcode: SoftNull P1 * * * * -** Synopsis: r[P1]=NULL +** Synopsis: r[P1]=NULL ** ** Set register P1 to have the value NULL as seen by the OP_MakeRecord ** instruction, but do not free any string or blob memory associated with ** the register, so that if the value was a string or blob that was ** previously copied using OP_SCopy, the copies will continue to be valid. @@ -1198,11 +1198,11 @@ UPDATE_MAX_BLOBSIZE(pOut); break; } /* Opcode: Move P1 P2 P3 * * -** Synopsis: r[P2@P3]=r[P1@P3] +** Synopsis: r[P2@P3]=r[P1@P3] ** ** Move the P3 values in register P1..P1+P3-1 over into ** registers P2..P2+P3-1. Registers P1..P1+P3-1 are ** left holding a NULL. It is an error for register ranges ** P1..P1+P3-1 and P2..P2+P3-1 to overlap. It is an error @@ -1308,11 +1308,11 @@ sqlite3VdbeMemSetInt64(pOut, pIn1->u.i); break; } /* Opcode: ResultRow P1 P2 * * * -** Synopsis: output=r[P1@P2] +** Synopsis: output=r[P1@P2] ** ** The registers P1 through P1+P2-1 contain a single row of ** results. This opcode causes the sqlite3_step() call to terminate ** with an SQLITE_ROW return code and it sets up the sqlite3_stmt ** structure to provide access to the r(P1)..r(P1+P2-1) values as @@ -1441,41 +1441,41 @@ UPDATE_MAX_BLOBSIZE(pOut); break; } /* Opcode: Add P1 P2 P3 * * -** Synopsis: r[P3]=r[P1]+r[P2] +** Synopsis: r[P3]=r[P1]+r[P2] ** ** Add the value in register P1 to the value in register P2 ** and store the result in register P3. ** If either input is NULL, the result is NULL. */ /* Opcode: Multiply P1 P2 P3 * * -** Synopsis: r[P3]=r[P1]*r[P2] +** Synopsis: r[P3]=r[P1]*r[P2] ** ** ** Multiply the value in register P1 by the value in register P2 ** and store the result in register P3. ** If either input is NULL, the result is NULL. */ /* Opcode: Subtract P1 P2 P3 * * -** Synopsis: r[P3]=r[P2]-r[P1] +** Synopsis: r[P3]=r[P2]-r[P1] ** ** Subtract the value in register P1 from the value in register P2 ** and store the result in register P3. ** If either input is NULL, the result is NULL. */ /* Opcode: Divide P1 P2 P3 * * -** Synopsis: r[P3]=r[P2]/r[P1] +** Synopsis: r[P3]=r[P2]/r[P1] ** ** Divide the value in register P1 by the value in register P2 ** and store the result in register P3 (P3=P2/P1). If the value in ** register P1 is zero, then the result is NULL. If either input is ** NULL, the result is NULL. */ /* Opcode: Remainder P1 P2 P3 * * -** Synopsis: r[P3]=r[P2]%r[P1] +** Synopsis: r[P3]=r[P2]%r[P1] ** ** Compute the remainder after integer register P2 is divided by ** register P1 and store the result in register P3. ** If the value in register P1 is zero the result is NULL. ** If either operand is NULL, the result is NULL. @@ -1704,33 +1704,33 @@ UPDATE_MAX_BLOBSIZE(pCtx->pOut); break; } /* Opcode: BitAnd P1 P2 P3 * * -** Synopsis: r[P3]=r[P1]&r[P2] +** Synopsis: r[P3]=r[P1]&r[P2] ** ** Take the bit-wise AND of the values in register P1 and P2 and ** store the result in register P3. ** If either input is NULL, the result is NULL. */ /* Opcode: BitOr P1 P2 P3 * * -** Synopsis: r[P3]=r[P1]|r[P2] +** Synopsis: r[P3]=r[P1]|r[P2] ** ** Take the bit-wise OR of the values in register P1 and P2 and ** store the result in register P3. ** If either input is NULL, the result is NULL. */ /* Opcode: ShiftLeft P1 P2 P3 * * -** Synopsis: r[P3]=r[P2]<>r[P1] +** Synopsis: r[P3]=r[P2]>>r[P1] ** ** Shift the integer value in register P2 to the right by the ** number of bits specified by the integer in register P1. ** Store the result in register P3. ** If either input is NULL, the result is NULL. @@ -1786,11 +1786,11 @@ MemSetTypeFlag(pOut, MEM_Int); break; } /* Opcode: AddImm P1 P2 * * * -** Synopsis: r[P1]=r[P1]+P2 +** Synopsis: r[P1]=r[P1]+P2 ** ** Add the constant P2 to the value in register P1. ** The result is always an integer. ** ** To force any register to be an integer, just add 0. @@ -1878,18 +1878,65 @@ if( rc ) goto abort_due_to_error; break; } #endif /* SQLITE_OMIT_CAST */ +/* Opcode: Eq P1 P2 P3 P4 P5 +** Synopsis: IF r[P3]==r[P1] +** +** Compare the values in register P1 and P3. If reg(P3)==reg(P1) then +** jump to address P2. Or if the SQLITE_STOREP2 flag is set in P5, then +** store the result of comparison in register P2. +** +** The SQLITE_AFF_MASK portion of P5 must be an affinity character - +** SQLITE_AFF_TEXT, SQLITE_AFF_INTEGER, and so forth. An attempt is made +** to coerce both inputs according to this affinity before the +** comparison is made. If the SQLITE_AFF_MASK is 0x00, then numeric +** affinity is used. Note that the affinity conversions are stored +** back into the input registers P1 and P3. So this opcode can cause +** persistent changes to registers P1 and P3. +** +** Once any conversions have taken place, and neither value is NULL, +** the values are compared. If both values are blobs then memcmp() is +** used to determine the results of the comparison. If both values +** are text, then the appropriate collating function specified in +** P4 is used to do the comparison. If P4 is not specified then +** memcmp() is used to compare text string. If both values are +** numeric, then a numeric comparison is used. If the two values +** are of different types, then numbers are considered less than +** strings and strings are considered less than blobs. +** +** If SQLITE_NULLEQ is set in P5 then the result of comparison is always either +** true or false and is never NULL. If both operands are NULL then the result +** of comparison is true. If either operand is NULL then the result is false. +** If neither operand is NULL the result is the same as it would be if +** the SQLITE_NULLEQ flag were omitted from P5. +** +** If both SQLITE_STOREP2 and SQLITE_KEEPNULL flags are set then the +** content of r[P2] is only changed if the new value is NULL or 0 (false). +** In other words, a prior r[P2] value will not be overwritten by 1 (true). +*/ +/* Opcode: Ne P1 P2 P3 P4 P5 +** Synopsis: IF r[P3]!=r[P1] +** +** This works just like the Eq opcode except that the jump is taken if +** the operands in registers P1 and P3 are not equal. See the Eq opcode for +** additional information. +** +** If both SQLITE_STOREP2 and SQLITE_KEEPNULL flags are set then the +** content of r[P2] is only changed if the new value is NULL or 1 (true). +** In other words, a prior r[P2] value will not be overwritten by 0 (false). +*/ /* Opcode: Lt P1 P2 P3 P4 P5 ** Synopsis: IF r[P3]flags */ u16 flags3; /* Copy of initial value of pIn3->flags */ pIn1 = &aMem[pOp->p1]; @@ -1989,21 +2003,22 @@ assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 ); if( (flags1&MEM_Null)!=0 && (flags3&MEM_Null)!=0 && (flags3&MEM_Cleared)==0 ){ - res = 0; /* Results are equal */ + res = 0; /* Operands are equal */ }else{ - res = 1; /* Results are not equal */ + res = 1; /* Operands are not equal */ } }else{ /* SQLITE_NULLEQ is clear and at least one operand is NULL, ** then the result is always NULL. ** The jump is taken if the SQLITE_JUMPIFNULL bit is set. */ if( pOp->p5 & SQLITE_STOREP2 ){ pOut = &aMem[pOp->p2]; + iCompare = 1; /* Operands are not equal */ memAboutToChange(p, pOut); MemSetTypeFlag(pOut, MEM_Null); REGISTER_TRACE(pOp->p2, pOut); }else{ VdbeBranchTaken(2,3); @@ -2053,16 +2068,16 @@ flags3 &= ~MEM_Zero; } res = sqlite3MemCompare(pIn3, pIn1, pOp->p4.pColl); } switch( pOp->opcode ){ - case OP_Eq: res = res==0; break; - case OP_Ne: res = res!=0; break; - case OP_Lt: res = res<0; break; - case OP_Le: res = res<=0; break; - case OP_Gt: res = res>0; break; - default: res = res>=0; break; + case OP_Eq: res2 = res==0; break; + case OP_Ne: res2 = res; break; + case OP_Lt: res2 = res<0; break; + case OP_Le: res2 = res<=0; break; + case OP_Gt: res2 = res>0; break; + default: res2 = res>=0; break; } /* Undo any changes made by applyAffinity() to the input registers. */ assert( (pIn1->flags & MEM_Dyn) == (flags1 & MEM_Dyn) ); pIn1->flags = flags1; @@ -2069,22 +2084,58 @@ assert( (pIn3->flags & MEM_Dyn) == (flags3 & MEM_Dyn) ); pIn3->flags = flags3; if( pOp->p5 & SQLITE_STOREP2 ){ pOut = &aMem[pOp->p2]; + iCompare = res; + res2 = res2!=0; /* For this path res2 must be exactly 0 or 1 */ + if( (pOp->p5 & SQLITE_KEEPNULL)!=0 ){ + /* The KEEPNULL flag prevents OP_Eq from overwriting a NULL with 1 + ** and prevents OP_Ne from overwriting NULL with 0. This flag + ** is only used in contexts where either: + ** (1) op==OP_Eq && (r[P2]==NULL || r[P2]==0) + ** (2) op==OP_Ne && (r[P2]==NULL || r[P2]==1) + ** Therefore it is not necessary to check the content of r[P2] for + ** NULL. */ + assert( pOp->opcode==OP_Ne || pOp->opcode==OP_Eq ); + assert( res2==0 || res2==1 ); + testcase( res2==0 && pOp->opcode==OP_Eq ); + testcase( res2==1 && pOp->opcode==OP_Eq ); + testcase( res2==0 && pOp->opcode==OP_Ne ); + testcase( res2==1 && pOp->opcode==OP_Ne ); + if( (pOp->opcode==OP_Eq)==res2 ) break; + } memAboutToChange(p, pOut); MemSetTypeFlag(pOut, MEM_Int); - pOut->u.i = res; + pOut->u.i = res2; REGISTER_TRACE(pOp->p2, pOut); }else{ VdbeBranchTaken(res!=0, (pOp->p5 & SQLITE_NULLEQ)?2:3); - if( res ){ + if( res2 ){ goto jump_to_p2; } } break; } + +/* Opcode: ElseNotEq * P2 * * * +** +** This opcode must immediately follow an Lt or Gt comparison operator. +** If the operands in that previous comparison had been used with an Eq +** operator and if the result of that Eq would be NULL or false (0), then +** then jump to P2. If the result of comparing the two previous operands +** using Eq would have been true (1), then fall through. +*/ +case OP_ElseNotEq: { /* same as TK_ESCAPE, jump */ + assert( pOp>aOp ); + assert( pOp[-1].opcode==OP_Lt || pOp[-1].opcode==OP_Gt ); + assert( pOp[-1].p5 & SQLITE_STOREP2 ); + VdbeBranchTaken(iCompare!=0, 2); + if( iCompare!=0 ) goto jump_to_p2; + break; +} + /* Opcode: Permutation * * * P4 * ** ** Set the permutation used by the OP_Compare operator to be the array ** of integers in P4. @@ -2331,11 +2382,11 @@ } break; } /* Opcode: IsNull P1 P2 * * * -** Synopsis: if r[P1]==NULL goto P2 +** Synopsis: if r[P1]==NULL goto P2 ** ** Jump to P2 if the value in register P1 is NULL. */ case OP_IsNull: { /* same as TK_ISNULL, jump, in1 */ pIn1 = &aMem[pOp->p1]; @@ -2359,11 +2410,11 @@ } break; } /* Opcode: Column P1 P2 P3 P4 P5 -** Synopsis: r[P3]=PX +** Synopsis: r[P3]=PX ** ** Interpret the data that cursor P1 points to as a structure built using ** the MakeRecord instruction. (See the MakeRecord opcode for additional ** information about the format of the data.) Extract the P2-th column ** from this record. If there are less that (P2+1) @@ -3869,11 +3920,10 @@ assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */ } break; } - /* Opcode: Found P1 P2 P3 P4 * ** Synopsis: key=r[P3@P4] ** ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If @@ -4302,11 +4352,11 @@ ** ** This instruction only works on tables. The equivalent instruction ** for indices is OP_IdxInsert. */ /* Opcode: InsertInt P1 P2 P3 P4 P5 -** Synopsis: intkey=P3 data=r[P2] +** Synopsis: intkey=P3 data=r[P2] ** ** This works exactly like OP_Insert except that the key is the ** integer value P3, not the value of the integer stored in register P3. */ case OP_Insert: @@ -4533,11 +4583,11 @@ p->nChange = 0; break; } /* Opcode: SorterCompare P1 P2 P3 P4 -** Synopsis: if key(P1)!=trim(r[P3],P4) goto P2 +** Synopsis: if key(P1)!=trim(r[P3],P4) goto P2 ** ** P1 is a sorter cursor. This instruction compares a prefix of the ** record blob in register P3 against a prefix of the entry that ** the sorter cursor currently points to. Only the first P4 fields ** of r[P3] and the sorter record are compared. @@ -5060,11 +5110,11 @@ pC->cacheStatus = CACHE_STALE; break; } /* Opcode: Seek P1 * P3 P4 * -** Synopsis: Move P3 to P1.rowid +** Synopsis: Move P3 to P1.rowid ** ** P1 is an open index cursor and P3 is a cursor on the corresponding ** table. This opcode does a deferred seek of the P3 table cursor ** to the row that corresponds to the current row of P1. ** @@ -5567,11 +5617,11 @@ break; } #endif /* SQLITE_OMIT_INTEGRITY_CHECK */ /* Opcode: RowSetAdd P1 P2 * * * -** Synopsis: rowset(P1)=r[P2] +** Synopsis: rowset(P1)=r[P2] ** ** Insert the integer value held by register P2 into a boolean index ** held in register P1. ** ** An assertion fails if P2 is not an integer. @@ -5587,11 +5637,11 @@ sqlite3RowSetInsert(pIn1->u.pRowSet, pIn2->u.i); break; } /* Opcode: RowSetRead P1 P2 P3 * * -** Synopsis: r[P3]=rowset(P1) +** Synopsis: r[P3]=rowset(P1) ** ** Extract the smallest value from boolean index P1 and put that value into ** register P3. Or, if boolean index P1 is initially empty, leave P3 ** unchanged and jump to instruction P2. */ @@ -6766,11 +6816,11 @@ } #endif /* Opcode: Init * P2 * P4 * -** Synopsis: Start at P2 +** Synopsis: Start at P2 ** ** Programs contain a single instance of this opcode as the very first ** opcode. ** ** If tracing is enabled (by the sqlite3_trace()) interface, then @@ -6826,12 +6876,12 @@ ){ sqlite3DebugPrintf("SQL-trace: %s\n", zTrace); } #endif /* SQLITE_DEBUG */ #endif /* SQLITE_OMIT_TRACE */ - if( pOp->p2 ) goto jump_to_p2; - break; + assert( pOp->p2>0 ); + goto jump_to_p2; } #ifdef SQLITE_ENABLE_CURSOR_HINTS /* Opcode: CursorHint P1 * * P4 * ** Index: src/vdbemem.c ================================================================== --- src/vdbemem.c +++ src/vdbemem.c @@ -1518,25 +1518,29 @@ /* ** This function is used to allocate and populate UnpackedRecord ** structures intended to be compared against sample index keys stored ** in the sqlite_stat4 table. ** -** A single call to this function attempts to populates field iVal (leftmost -** is 0 etc.) of the unpacked record with a value extracted from expression -** pExpr. Extraction of values is possible if: +** A single call to this function populates zero or more fields of the +** record starting with field iVal (fields are numbered from left to +** right starting with 0). A single field is populated if: ** ** * (pExpr==0). In this case the value is assumed to be an SQL NULL, ** ** * The expression is a bound variable, and this is a reprepare, or ** ** * The sqlite3ValueFromExpr() function is able to extract a value ** from the expression (i.e. the expression is a literal value). ** -** If a value can be extracted, the affinity passed as the 5th argument -** is applied to it before it is copied into the UnpackedRecord. Output -** parameter *pbOk is set to true if a value is extracted, or false -** otherwise. +** Or, if pExpr is a TK_VECTOR, one field is populated for each of the +** vector components that match either of the two latter criteria listed +** above. +** +** Before any value is appended to the record, the affinity of the +** corresponding column within index pIdx is applied to it. Before +** this function returns, output parameter *pnExtract is set to the +** number of values appended to the record. ** ** When this function is called, *ppRec must either point to an object ** allocated by an earlier call to this function, or must be NULL. If it ** is NULL and a value can be successfully extracted, a new UnpackedRecord ** is allocated (and *ppRec set to point to it) before returning. @@ -1548,26 +1552,37 @@ int sqlite3Stat4ProbeSetValue( Parse *pParse, /* Parse context */ Index *pIdx, /* Index being probed */ UnpackedRecord **ppRec, /* IN/OUT: Probe record */ Expr *pExpr, /* The expression to extract a value from */ - u8 affinity, /* Affinity to use */ + int nElem, /* Maximum number of values to append */ int iVal, /* Array element to populate */ - int *pbOk /* OUT: True if value was extracted */ + int *pnExtract /* OUT: Values appended to the record */ ){ - int rc; - sqlite3_value *pVal = 0; - struct ValueNewStat4Ctx alloc; - - alloc.pParse = pParse; - alloc.pIdx = pIdx; - alloc.ppRec = ppRec; - alloc.iVal = iVal; - - rc = stat4ValueFromExpr(pParse, pExpr, affinity, &alloc, &pVal); - assert( pVal==0 || pVal->db==pParse->db ); - *pbOk = (pVal!=0); + int rc = SQLITE_OK; + int nExtract = 0; + + if( pExpr==0 || pExpr->op!=TK_SELECT ){ + int i; + struct ValueNewStat4Ctx alloc; + + alloc.pParse = pParse; + alloc.pIdx = pIdx; + alloc.ppRec = ppRec; + + for(i=0; idb, pIdx, iVal+i); + alloc.iVal = iVal+i; + rc = stat4ValueFromExpr(pParse, pElem, aff, &alloc, &pVal); + if( !pVal ) break; + nExtract++; + } + } + + *pnExtract = nExtract; return rc; } /* ** Attempt to extract a value from expression pExpr using the methods Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -824,20 +824,22 @@ static sqlite3_index_info *allocateIndexInfo( Parse *pParse, WhereClause *pWC, Bitmask mUnusable, /* Ignore terms with these prereqs */ struct SrcList_item *pSrc, - ExprList *pOrderBy + ExprList *pOrderBy, + u16 *pmNoOmit /* Mask of terms not to omit */ ){ int i, j; int nTerm; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_orderby *pIdxOrderBy; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int nOrderBy; sqlite3_index_info *pIdxInfo; + u16 mNoOmit = 0; /* Count the number of possible WHERE clause constraints referring ** to this virtual table */ for(i=nTerm=0, pTerm=pWC->a; inTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; @@ -922,18 +924,28 @@ assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH ); assert( pTerm->eOperator & (WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) ); + + if( op & (WO_LT|WO_LE|WO_GT|WO_GE) + && sqlite3ExprIsVector(pTerm->pExpr->pRight) + ){ + if( i<16 ) mNoOmit |= (1 << i); + if( op==WO_LT ) pIdxCons[j].op = WO_LE; + if( op==WO_GT ) pIdxCons[j].op = WO_GE; + } + j++; } for(i=0; ia[i].pExpr; pIdxOrderBy[i].iColumn = pExpr->iColumn; pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder; } + *pmNoOmit = mNoOmit; return pIdxInfo; } /* ** The table object reference passed as the second argument to this function @@ -1205,11 +1217,11 @@ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 /* ** Return the affinity for a single column of an index. */ -static char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){ +char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){ assert( iCol>=0 && iColnColumn ); if( !pIdx->zColAff ){ if( sqlite3IndexAffinityStr(db, pIdx)==0 ) return SQLITE_AFF_BLOB; } return pIdx->zColAff[iCol]; @@ -1382,11 +1394,12 @@ if( p->nSample>0 && nEqnSampleCol ){ if( nEq==pBuilder->nRecValid ){ UnpackedRecord *pRec = pBuilder->pRec; tRowcnt a[2]; - u8 aff; + int nBtm = pLoop->u.btree.nBtm; + int nTop = pLoop->u.btree.nTop; /* Variable iLower will be set to the estimate of the number of rows in ** the index that are less than the lower bound of the range query. The ** lower bound being the concatenation of $P and $L, where $P is the ** key-prefix formed by the nEq values matched against the nEq left-most @@ -1412,12 +1425,10 @@ if( pRec ){ testcase( pRec->nField!=pBuilder->nRecValid ); pRec->nField = pBuilder->nRecValid; } - aff = sqlite3IndexColumnAffinity(pParse->db, p, nEq); - assert( nEq!=p->nKeyCol || aff==SQLITE_AFF_INTEGER ); /* Determine iLower and iUpper using ($P) only. */ if( nEq==0 ){ iLower = 0; iUpper = p->nRowEst0; }else{ @@ -1432,36 +1443,41 @@ assert( pUpper==0 || (pUpper->eOperator & (WO_LT|WO_LE))!=0 ); assert( p->aSortOrder!=0 ); if( p->aSortOrder[nEq] ){ /* The roles of pLower and pUpper are swapped for a DESC index */ SWAP(WhereTerm*, pLower, pUpper); + SWAP(int, nBtm, nTop); } /* If possible, improve on the iLower estimate using ($P:$L). */ if( pLower ){ - int bOk; /* True if value is extracted from pExpr */ + int n; /* Values extracted from pExpr */ Expr *pExpr = pLower->pExpr->pRight; - rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); - if( rc==SQLITE_OK && bOk ){ + rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, nBtm, nEq, &n); + if( rc==SQLITE_OK && n ){ tRowcnt iNew; + u16 mask = WO_GT|WO_LE; + if( sqlite3ExprVectorSize(pExpr)>n ) mask = (WO_LE|WO_LT); iLwrIdx = whereKeyStats(pParse, p, pRec, 0, a); - iNew = a[0] + ((pLower->eOperator & (WO_GT|WO_LE)) ? a[1] : 0); + iNew = a[0] + ((pLower->eOperator & mask) ? a[1] : 0); if( iNew>iLower ) iLower = iNew; nOut--; pLower = 0; } } /* If possible, improve on the iUpper estimate using ($P:$U). */ if( pUpper ){ - int bOk; /* True if value is extracted from pExpr */ + int n; /* Values extracted from pExpr */ Expr *pExpr = pUpper->pExpr->pRight; - rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); - if( rc==SQLITE_OK && bOk ){ + rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, nTop, nEq, &n); + if( rc==SQLITE_OK && n ){ tRowcnt iNew; + u16 mask = WO_GT|WO_LE; + if( sqlite3ExprVectorSize(pExpr)>n ) mask = (WO_LE|WO_LT); iUprIdx = whereKeyStats(pParse, p, pRec, 1, a); - iNew = a[0] + ((pUpper->eOperator & (WO_GT|WO_LE)) ? a[1] : 0); + iNew = a[0] + ((pUpper->eOperator & mask) ? a[1] : 0); if( iNewpNew->u.btree.pIndex; int nEq = pBuilder->pNew->u.btree.nEq; UnpackedRecord *pRec = pBuilder->pRec; - u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); @@ -1571,12 +1586,11 @@ if( nEq>=p->nColumn ){ *pnRow = 1; return SQLITE_OK; } - aff = sqlite3IndexColumnAffinity(pParse->db, p, nEq-1); - rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk); + rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, 1, nEq-1, &bOk); pBuilder->pRec = pRec; if( rc!=SQLITE_OK ) return rc; if( bOk==0 ) return SQLITE_NOTFOUND; pBuilder->nRecValid = nEq; @@ -1661,13 +1675,18 @@ pTerm->u.pOrInfo->indexable); }else{ sqlite3_snprintf(sizeof(zLeft),zLeft,"left=%d", pTerm->leftCursor); } sqlite3DebugPrintf( - "TERM-%-3d %p %s %-12s prob=%-3d op=0x%03x wtFlags=0x%04x\n", + "TERM-%-3d %p %s %-12s prob=%-3d op=0x%03x wtFlags=0x%04x", iTerm, pTerm, zType, zLeft, pTerm->truthProb, pTerm->eOperator, pTerm->wtFlags); + if( pTerm->iField ){ + sqlite3DebugPrintf(" iField=%d\n", pTerm->iField); + }else{ + sqlite3DebugPrintf("\n"); + } sqlite3TreeViewExpr(0, pTerm->pExpr, 0); } } #endif @@ -2184,10 +2203,76 @@ } } } if( pLoop->nOut > nRow-iReduce ) pLoop->nOut = nRow - iReduce; } + +/* +** Term pTerm is a vector range comparison operation. The first comparison +** in the vector can be optimized using column nEq of the index. This +** function returns the total number of vector elements that can be used +** as part of the range comparison. +** +** For example, if the query is: +** +** WHERE a = ? AND (b, c, d) > (?, ?, ?) +** +** and the index: +** +** CREATE INDEX ... ON (a, b, c, d, e) +** +** then this function would be invoked with nEq=1. The value returned in +** this case is 3. +*/ +int whereRangeVectorLen( + Parse *pParse, /* Parsing context */ + int iCur, /* Cursor open on pIdx */ + Index *pIdx, /* The index to be used for a inequality constraint */ + int nEq, /* Number of prior equality constraints on same index */ + WhereTerm *pTerm /* The vector inequality constraint */ +){ + int nCmp = sqlite3ExprVectorSize(pTerm->pExpr->pLeft); + int i; + + nCmp = MIN(nCmp, (pIdx->nColumn - nEq)); + for(i=1; ipExpr->pLeft->x.pList->a[i].pExpr; + Expr *pRhs = pTerm->pExpr->pRight; + if( pRhs->flags & EP_xIsSelect ){ + pRhs = pRhs->x.pSelect->pEList->a[i].pExpr; + }else{ + pRhs = pRhs->x.pList->a[i].pExpr; + } + + /* Check that the LHS of the comparison is a column reference to + ** the right column of the right source table. And that the sort + ** order of the index column is the same as the sort order of the + ** leftmost index column. */ + if( pLhs->op!=TK_COLUMN + || pLhs->iTable!=iCur + || pLhs->iColumn!=pIdx->aiColumn[i+nEq] + || pIdx->aSortOrder[i+nEq]!=pIdx->aSortOrder[nEq] + ){ + break; + } + + testcase( pLhs->iColumn==XN_ROWID ); + aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs)); + idxaff = sqlite3TableColumnAffinity(pIdx->pTable, pLhs->iColumn); + if( aff!=idxaff ) break; + + pColl = sqlite3BinaryCompareCollSeq(pParse, pLhs, pRhs); + if( pColl==0 ) break; + if( sqlite3StrICmp(pColl->zName, pIdx->azColl[i+nEq]) ) break; + } + return i; +} /* ** Adjust the cost C by the costMult facter T. This only occurs if ** compiled with -DSQLITE_ENABLE_COSTMULT */ @@ -2223,10 +2308,12 @@ int opMask; /* Valid operators for constraints */ WhereScan scan; /* Iterator for WHERE terms */ Bitmask saved_prereq; /* Original value of pNew->prereq */ u16 saved_nLTerm; /* Original value of pNew->nLTerm */ u16 saved_nEq; /* Original value of pNew->u.btree.nEq */ + u16 saved_nBtm; /* Original value of pNew->u.btree.nBtm */ + u16 saved_nTop; /* Original value of pNew->u.btree.nTop */ u16 saved_nSkip; /* Original value of pNew->nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ int rc = SQLITE_OK; /* Return code */ LogEst rSize; /* Number of rows in the table */ @@ -2239,17 +2326,20 @@ assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 ); if( pNew->wsFlags & WHERE_BTM_LIMIT ){ opMask = WO_LT|WO_LE; }else{ + assert( pNew->u.btree.nBtm==0 ); opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEqnColumn ); saved_nEq = pNew->u.btree.nEq; + saved_nBtm = pNew->u.btree.nBtm; + saved_nTop = pNew->u.btree.nTop; saved_nSkip = pNew->nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; @@ -2289,10 +2379,12 @@ continue; } pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; + pNew->u.btree.nBtm = saved_nBtm; + pNew->u.btree.nTop = saved_nTop; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf; @@ -2305,18 +2397,27 @@ if( eOp & WO_IN ){ Expr *pExpr = pTerm->pExpr; pNew->wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": TUNING: the SELECT returns 25 rows */ + int i; nIn = 46; assert( 46==sqlite3LogEst(25) ); + + /* The expression may actually be of the form (x, y) IN (SELECT...). + ** In this case there is a separate term for each of (x) and (y). + ** However, the nIn multiplier should only be applied once, not once + ** for each such term. The following loop checks that pTerm is the + ** first such term in use, and sets nIn back to 0 if it is not. */ + for(i=0; inLTerm-1; i++){ + if( pNew->aLTerm[i]->pExpr==pExpr ) nIn = 0; + } }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nIn = sqlite3LogEst(pExpr->x.pList->nExpr); + assert( nIn>0 ); /* RHS always has 2 or more terms... The parser + ** changes "x IN (?)" into "x=?". */ } - assert( nIn>0 ); /* RHS always has 2 or more terms... The parser - ** changes "x IN (?)" into "x=?". */ - }else if( eOp & (WO_EQ|WO_IS) ){ int iCol = pProbe->aiColumn[saved_nEq]; pNew->wsFlags |= WHERE_COLUMN_EQ; assert( saved_nEq==pNew->u.btree.nEq ); if( iCol==XN_ROWID @@ -2332,10 +2433,13 @@ pNew->wsFlags |= WHERE_COLUMN_NULL; }else if( eOp & (WO_GT|WO_GE) ){ testcase( eOp & WO_GT ); testcase( eOp & WO_GE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; + pNew->u.btree.nBtm = whereRangeVectorLen( + pParse, pSrc->iCursor, pProbe, saved_nEq, pTerm + ); pBtm = pTerm; pTop = 0; if( pTerm->wtFlags & TERM_LIKEOPT ){ /* Range contraints that come from the LIKE optimization are ** always used in pairs. */ @@ -2344,16 +2448,20 @@ assert( pTop->wtFlags & TERM_LIKEOPT ); assert( pTop->eOperator==WO_LT ); if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTop; pNew->wsFlags |= WHERE_TOP_LIMIT; + pNew->u.btree.nTop = 1; } }else{ assert( eOp & (WO_LT|WO_LE) ); testcase( eOp & WO_LT ); testcase( eOp & WO_LE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT; + pNew->u.btree.nTop = whereRangeVectorLen( + pParse, pSrc->iCursor, pProbe, saved_nEq, pTerm + ); pTop = pTerm; pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ? pNew->aLTerm[pNew->nLTerm-2] : 0; } @@ -2449,10 +2557,12 @@ pBuilder->nRecValid = nRecValid; #endif } pNew->prereq = saved_prereq; pNew->u.btree.nEq = saved_nEq; + pNew->u.btree.nBtm = saved_nBtm; + pNew->u.btree.nTop = saved_nTop; pNew->nSkip = saved_nSkip; pNew->wsFlags = saved_wsFlags; pNew->nOut = saved_nOut; pNew->nLTerm = saved_nLTerm; @@ -2570,11 +2680,11 @@ return 0; } /* ** Add all WhereLoop objects for a single table of the join where the table -** is idenfied by pBuilder->pNew->iTab. That table is guaranteed to be +** is identified by pBuilder->pNew->iTab. That table is guaranteed to be ** a b-tree table, not a virtual table. ** ** The costs (WhereLoop.rRun) of the b-tree loops added by this function ** are calculated as follows: ** @@ -2724,10 +2834,12 @@ testcase( pNew->iTab!=pSrc->iCursor ); /* See ticket [98d973b8f5] */ continue; /* Partial index inappropriate for this query */ } rSize = pProbe->aiRowLogEst[0]; pNew->u.btree.nEq = 0; + pNew->u.btree.nBtm = 0; + pNew->u.btree.nTop = 0; pNew->nSkip = 0; pNew->nLTerm = 0; pNew->iSortIdx = 0; pNew->rSetup = 0; pNew->prereq = mPrereq; @@ -2852,10 +2964,11 @@ WhereLoopBuilder *pBuilder, Bitmask mPrereq, /* Mask of tables that must be used. */ Bitmask mUsable, /* Mask of usable tables */ u16 mExclude, /* Exclude terms using these operators */ sqlite3_index_info *pIdxInfo, /* Populated object for xBestIndex */ + u16 mNoOmit, /* Do not omit these constraints */ int *pbIn /* OUT: True if plan uses an IN(...) op */ ){ WhereClause *pWC = pBuilder->pWC; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage; @@ -2940,10 +3053,11 @@ pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE; *pbIn = 1; assert( (mExclude & WO_IN)==0 ); } } } + pNew->u.vtab.omitMask &= ~mNoOmit; pNew->nLTerm = mxTerm+1; assert( pNew->nLTerm<=pNew->nLSlot ); pNew->u.vtab.idxNum = pIdxInfo->idxNum; pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; @@ -3013,19 +3127,21 @@ sqlite3_index_info *p; /* Object to pass to xBestIndex() */ int nConstraint; /* Number of constraints in p */ int bIn; /* True if plan uses IN(...) operator */ WhereLoop *pNew; Bitmask mBest; /* Tables used by best possible plan */ + u16 mNoOmit; assert( (mPrereq & mUnusable)==0 ); pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; assert( IsVirtual(pSrc->pTab) ); - p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc, pBuilder->pOrderBy); + p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc, pBuilder->pOrderBy, + &mNoOmit); if( p==0 ) return SQLITE_NOMEM_BKPT; pNew->rSetup = 0; pNew->wsFlags = WHERE_VIRTUALTABLE; pNew->nLTerm = 0; pNew->u.vtab.needFree = 0; @@ -3035,11 +3151,11 @@ return SQLITE_NOMEM_BKPT; } /* First call xBestIndex() with all constraints usable. */ WHERETRACE(0x40, (" VirtualOne: all usable\n")); - rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, &bIn); + rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn); /* If the call to xBestIndex() with all terms enabled produced a plan ** that does not require any source tables (IOW: a plan with mBest==0), ** then there is no point in making any further calls to xBestIndex() ** since they will all return the same result (if the xBestIndex() @@ -3052,11 +3168,12 @@ /* If the plan produced by the earlier call uses an IN(...) term, call ** xBestIndex again, this time with IN(...) terms disabled. */ if( bIn ){ WHERETRACE(0x40, (" VirtualOne: all usable w/o IN\n")); - rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, WO_IN, p, &bIn); + rc = whereLoopAddVirtualOne( + pBuilder, mPrereq, ALLBITS, WO_IN, p, mNoOmit, &bIn); assert( bIn==0 ); mBestNoIn = pNew->prereq & ~mPrereq; if( mBestNoIn==0 ){ seenZero = 1; seenZeroNoIN = 1; @@ -3078,11 +3195,12 @@ mPrev = mNext; if( mNext==ALLBITS ) break; if( mNext==mBest || mNext==mBestNoIn ) continue; WHERETRACE(0x40, (" VirtualOne: mPrev=%04llx mNext=%04llx\n", (sqlite3_uint64)mPrev, (sqlite3_uint64)mNext)); - rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mNext|mPrereq, 0, p, &bIn); + rc = whereLoopAddVirtualOne( + pBuilder, mPrereq, mNext|mPrereq, 0, p, mNoOmit, &bIn); if( pNew->prereq==mPrereq ){ seenZero = 1; if( bIn==0 ) seenZeroNoIN = 1; } } @@ -3090,20 +3208,22 @@ /* If the calls to xBestIndex() in the above loop did not find a plan ** that requires no source tables at all (i.e. one guaranteed to be ** usable), make a call here with all source tables disabled */ if( rc==SQLITE_OK && seenZero==0 ){ WHERETRACE(0x40, (" VirtualOne: all disabled\n")); - rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mPrereq, 0, p, &bIn); + rc = whereLoopAddVirtualOne( + pBuilder, mPrereq, mPrereq, 0, p, mNoOmit, &bIn); if( bIn==0 ) seenZeroNoIN = 1; } /* If the calls to xBestIndex() have so far failed to find a plan ** that requires no source tables at all and does not use an IN(...) ** operator, make a final call to obtain one here. */ if( rc==SQLITE_OK && seenZeroNoIN==0 ){ WHERETRACE(0x40, (" VirtualOne: all disabled and w/o IN\n")); - rc = whereLoopAddVirtualOne(pBuilder, mPrereq, mPrereq, WO_IN, p, &bIn); + rc = whereLoopAddVirtualOne( + pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn); } } if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr); sqlite3DbFree(pParse->db, p); @@ -3443,24 +3563,46 @@ ** that are not constrained by == or IN. */ rev = revSet = 0; distinctColumns = 0; for(j=0; ju.btree.nEq - && pLoop->nSkip==0 - && ((i = pLoop->aLTerm[j]->eOperator) & eqOpMask)!=0 - ){ - if( i & WO_ISNULL ){ - testcase( isOrderDistinct ); - isOrderDistinct = 0; - } - continue; + u8 bOnce = 1; /* True to run the ORDER BY search loop */ + + assert( j>=pLoop->u.btree.nEq + || (pLoop->aLTerm[j]==0)==(jnSkip) + ); + if( ju.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( ALWAYS(eOp & WO_IN) ){ + /* ALWAYS() justification: eOp is an equality operator due to the + ** ju.btree.nEq constraint above. Any equality other + ** than WO_IN is captured by the previous "if". So this one + ** always has to be WO_IN. */ + Expr *pX = pLoop->aLTerm[j]->pExpr; + for(i=j+1; iu.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. */ @@ -3485,11 +3627,10 @@ } /* Find the ORDER BY term that corresponds to the j-th column ** of the index and mark that ORDER BY term off */ - bOnce = 1; isMatch = 0; for(i=0; bOnce && ia[i].pExpr); testcase( wctrlFlags & WHERE_GROUPBY ); @@ -4679,14 +4820,16 @@ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->addrNxt); for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->addrInTop+1); - sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop); - VdbeCoverage(v); - VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen); - VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen); + if( pIn->eEndLoopOp!=OP_Noop ){ + sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop); + VdbeCoverage(v); + VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen); + VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen); + } sqlite3VdbeJumpHere(v, pIn->addrInTop-1); } } sqlite3VdbeResolveLabel(v, pLevel->addrBrk); if( pLevel->addrSkip ){ Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -120,10 +120,12 @@ LogEst rRun; /* Cost of running each loop */ LogEst nOut; /* Estimated number of output rows */ union { struct { /* Information for internal btree tables */ u16 nEq; /* Number of equality constraints */ + u16 nBtm; /* Size of BTM vector */ + u16 nTop; /* Size of TOP vector */ Index *pIndex; /* Index used, or NULL */ } btree; struct { /* Information for virtual tables */ int idxNum; /* Index number */ u8 needFree; /* True if sqlite3_free(idxStr) is needed */ @@ -244,10 +246,11 @@ */ struct WhereTerm { Expr *pExpr; /* Pointer to the subexpression that is this term */ int iParent; /* Disable pWC->a[iParent] when this term disabled */ int leftCursor; /* Cursor number of X in "X " */ + int iField; /* Field in (?,?,?) IN (SELECT...) vector */ union { int leftColumn; /* Column number of X in "X " */ WhereOrInfo *pOrInfo; /* Extra information if (eOperator & WO_OR)!=0 */ WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */ } u; Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -19,10 +19,21 @@ */ #include "sqliteInt.h" #include "whereInt.h" #ifndef SQLITE_OMIT_EXPLAIN + +/* +** Return the name of the i-th column of the pIdx index. +*/ +static const char *explainIndexColumnName(Index *pIdx, int i){ + i = pIdx->aiColumn[i]; + if( i==XN_EXPR ) return ""; + if( i==XN_ROWID ) return "rowid"; + return pIdx->pTable->aCol[i].zName; +} + /* ** This routine is a helper for explainIndexRange() below ** ** pStr holds the text of an expression that we are building up one term ** at a time. This routine adds a new term to the end of the expression. @@ -29,28 +40,36 @@ ** Terms are separated by AND so add the "AND" text for second and subsequent ** terms only. */ static void explainAppendTerm( StrAccum *pStr, /* The text expression being built */ - int iTerm, /* Index of this term. First is zero */ - const char *zColumn, /* Name of the column */ + Index *pIdx, /* Index to read column names from */ + int nTerm, /* Number of terms */ + int iTerm, /* Zero-based index of first term. */ + int bAnd, /* Non-zero to append " AND " */ const char *zOp /* Name of the operator */ ){ - if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5); - sqlite3StrAccumAppendAll(pStr, zColumn); + int i; + + assert( nTerm>=1 ); + if( bAnd ) sqlite3StrAccumAppend(pStr, " AND ", 5); + + if( nTerm>1 ) sqlite3StrAccumAppend(pStr, "(", 1); + for(i=0; i1 ) sqlite3StrAccumAppend(pStr, ")", 1); + sqlite3StrAccumAppend(pStr, zOp, 1); - sqlite3StrAccumAppend(pStr, "?", 1); -} - -/* -** Return the name of the i-th column of the pIdx index. -*/ -static const char *explainIndexColumnName(Index *pIdx, int i){ - i = pIdx->aiColumn[i]; - if( i==XN_EXPR ) return ""; - if( i==XN_ROWID ) return "rowid"; - return pIdx->pTable->aCol[i].zName; + + if( nTerm>1 ) sqlite3StrAccumAppend(pStr, "(", 1); + for(i=0; i1 ) sqlite3StrAccumAppend(pStr, ")", 1); } /* ** Argument pLevel describes a strategy for scanning table pTab. This ** function appends text to pStr that describes the subset of table @@ -79,16 +98,15 @@ sqlite3XPrintf(pStr, i>=nSkip ? "%s=?" : "ANY(%s)", z); } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ - const char *z = explainIndexColumnName(pIndex, i); - explainAppendTerm(pStr, i++, z, ">"); + explainAppendTerm(pStr, pIndex, pLoop->u.btree.nBtm, j, i, ">"); + i = 1; } if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ - const char *z = explainIndexColumnName(pIndex, j); - explainAppendTerm(pStr, i, z, "<"); + explainAppendTerm(pStr, pIndex, pLoop->u.btree.nTop, j, i, "<"); } sqlite3StrAccumAppend(pStr, ")", 1); } /* @@ -274,11 +292,11 @@ ** a conditional such that is only evaluated on the second pass of a ** LIKE-optimization loop, when scanning BLOBs instead of strings. */ static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){ int nLoop = 0; - while( pTerm + while( ALWAYS(pTerm!=0) && (pTerm->wtFlags & TERM_CODED)==0 && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin)) && (pLevel->notReady & pTerm->prereqAll)==0 ){ if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){ @@ -330,20 +348,50 @@ sqlite3VdbeAddOp4(v, OP_Affinity, base, n, 0, zAff, n); sqlite3ExprCacheAffinityChange(pParse, base, n); } } +/* +** Expression pRight, which is the RHS of a comparison operation, is +** either a vector of n elements or, if n==1, a scalar expression. +** Before the comparison operation, affinity zAff is to be applied +** to the pRight values. This function modifies characters within the +** affinity string to SQLITE_AFF_BLOB if either: +** +** * the comparison will be performed with no affinity, or +** * the affinity change in zAff is guaranteed not to change the value. +*/ +static void updateRangeAffinityStr( + Parse *pParse, /* Parse context */ + Expr *pRight, /* RHS of comparison */ + int n, /* Number of vector elements in comparison */ + char *zAff /* Affinity string to modify */ +){ + int i; + for(i=0; ipExpr; 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 eType = IN_INDEX_NOOP; int iTab; struct InLoop *pIn; WhereLoop *pLoop = pLevel->pWLoop; + int i; + int nEq = 0; + int *aiMap = 0; if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 && pLoop->u.btree.pIndex!=0 && pLoop->u.btree.pIndex->aSortOrder[iEq] ){ @@ -377,42 +429,128 @@ testcase( bRev ); bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; - eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0); + + for(i=0; iaLTerm[i] && pLoop->aLTerm[i]->pExpr==pX ){ + disableTerm(pLevel, pTerm); + return iTarget; + } + } + for(i=iEq;inLTerm; i++){ + if( ALWAYS(pLoop->aLTerm[i]) && pLoop->aLTerm[i]->pExpr==pX ) nEq++; + } + + if( (pX->flags & EP_xIsSelect)==0 || pX->x.pSelect->pEList->nExpr==1 ){ + eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0); + }else{ + Select *pSelect = pX->x.pSelect; + sqlite3 *db = pParse->db; + ExprList *pOrigRhs = pSelect->pEList; + ExprList *pOrigLhs = pX->pLeft->x.pList; + ExprList *pRhs = 0; /* New Select.pEList for RHS */ + ExprList *pLhs = 0; /* New pX->pLeft vector */ + + for(i=iEq;inLTerm; i++){ + if( pLoop->aLTerm[i]->pExpr==pX ){ + int iField = pLoop->aLTerm[i]->iField - 1; + Expr *pNewRhs = sqlite3ExprDup(db, pOrigRhs->a[iField].pExpr, 0); + Expr *pNewLhs = sqlite3ExprDup(db, pOrigLhs->a[iField].pExpr, 0); + + pRhs = sqlite3ExprListAppend(pParse, pRhs, pNewRhs); + pLhs = sqlite3ExprListAppend(pParse, pLhs, pNewLhs); + } + } + if( !db->mallocFailed ){ + Expr *pLeft = pX->pLeft; + + if( pSelect->pOrderBy ){ + /* If the SELECT statement has an ORDER BY clause, zero the + ** iOrderByCol variables. These are set to non-zero when an + ** ORDER BY term exactly matches one of the terms of the + ** result-set. Since the result-set of the SELECT statement may + ** have been modified or reordered, these variables are no longer + ** set correctly. Since setting them is just an optimization, + ** it's easiest just to zero them here. */ + ExprList *pOrderBy = pSelect->pOrderBy; + for(i=0; inExpr; i++){ + pOrderBy->a[i].u.x.iOrderByCol = 0; + } + } + + /* Take care here not to generate a TK_VECTOR containing only a + ** single value. Since the parser never creates such a vector, some + ** of the subroutines do not handle this case. */ + if( pLhs->nExpr==1 ){ + pX->pLeft = pLhs->a[0].pExpr; + }else{ + pLeft->x.pList = pLhs; + aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int) * nEq); + testcase( aiMap==0 ); + } + pSelect->pEList = pRhs; + eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap); + testcase( aiMap!=0 && aiMap[0]!=0 ); + pSelect->pEList = pOrigRhs; + pLeft->x.pList = pOrigLhs; + pX->pLeft = pLeft; + } + sqlite3ExprListDelete(pParse->db, pLhs); + sqlite3ExprListDelete(pParse->db, pRhs); + } + if( eType==IN_INDEX_INDEX_DESC ){ testcase( bRev ); bRev = !bRev; } iTab = pX->iTable; sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0); VdbeCoverageIf(v, bRev); VdbeCoverageIf(v, !bRev); assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 ); + pLoop->wsFlags |= WHERE_IN_ABLE; if( pLevel->u.in.nIn==0 ){ pLevel->addrNxt = sqlite3VdbeMakeLabel(v); } - pLevel->u.in.nIn++; + + i = pLevel->u.in.nIn; + pLevel->u.in.nIn += nEq; pLevel->u.in.aInLoop = sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop, sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn); pIn = pLevel->u.in.aInLoop; if( pIn ){ - pIn += pLevel->u.in.nIn - 1; - pIn->iCur = iTab; - if( eType==IN_INDEX_ROWID ){ - pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg); - }else{ - pIn->addrInTop = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg); - } - pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen; - sqlite3VdbeAddOp1(v, OP_IsNull, iReg); VdbeCoverage(v); + int iMap = 0; /* Index in aiMap[] */ + pIn += i; + for(i=iEq;inLTerm; i++){ + int iOut = iReg; + if( pLoop->aLTerm[i]->pExpr==pX ){ + if( eType==IN_INDEX_ROWID ){ + assert( nEq==1 && i==iEq ); + pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg); + }else{ + int iCol = aiMap ? aiMap[iMap++] : 0; + iOut = iReg + i - iEq; + pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut); + } + sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v); + if( i==iEq ){ + pIn->iCur = iTab; + pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen; + }else{ + pIn->eEndLoopOp = OP_Noop; + } + pIn++; + } + } }else{ pLevel->u.in.nIn = 0; } + sqlite3DbFree(pParse->db, aiMap); #endif } disableTerm(pLevel, pTerm); return iReg; } @@ -534,13 +672,19 @@ regBase = r1; }else{ sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j); } } - testcase( pTerm->eOperator & WO_ISNULL ); - testcase( pTerm->eOperator & WO_IN ); - if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ + if( pTerm->eOperator & WO_IN ){ + if( pTerm->pExpr->flags & EP_xIsSelect ){ + /* No affinity ever needs to be (or should be) applied to a value + ** from the RHS of an "? IN (SELECT ...)" expression. The + ** sqlite3FindInIndex() routine has already ensured that the + ** affinity of the comparison has been applied to the value. */ + if( zAff ) zAff[j] = SQLITE_AFF_BLOB; + } + }else if( (pTerm->eOperator & WO_ISNULL)==0 ){ Expr *pRight = pTerm->pExpr->pRight; if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); VdbeCoverage(v); } @@ -858,10 +1002,43 @@ } sqlite3VdbeChangeP4(v, -1, (char*)ai, P4_INTARRAY); } } } + +/* +** If the expression passed as the second argument is a vector, generate +** code to write the first nReg elements of the vector into an array +** of registers starting with iReg. +** +** If the expression is not a vector, then nReg must be passed 1. In +** this case, generate code to evaluate the expression and leave the +** result in register iReg. +*/ +static void codeExprOrVector(Parse *pParse, Expr *p, int iReg, int nReg){ + assert( nReg>0 ); + if( sqlite3ExprIsVector(p) ){ +#ifndef SQLITE_OMIT_SUBQUERY + if( (p->flags & EP_xIsSelect) ){ + Vdbe *v = pParse->pVdbe; + int iSelect = sqlite3CodeSubselect(pParse, p, 0, 0); + sqlite3VdbeAddOp3(v, OP_Copy, iSelect, iReg, nReg-1); + }else +#endif + { + int i; + ExprList *pList = p->x.pList; + assert( nReg<=pList->nExpr ); + for(i=0; ia[i].pExpr, iReg+i); + } + } + }else{ + assert( nReg==1 ); + sqlite3ExprCode(pParse, p, iReg); + } +} /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ @@ -954,11 +1131,12 @@ if( NEVER(pTerm==0) ) continue; if( pTerm->eOperator & WO_IN ){ codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget); addrNotFound = pLevel->addrNxt; }else{ - sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget); + Expr *pRight = pTerm->pExpr->pRight; + codeExprOrVector(pParse, pRight, iTarget, 1); } } sqlite3VdbeAddOp2(v, OP_Integer, pLoop->u.vtab.idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, nConstraint, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg, @@ -1068,10 +1246,11 @@ } codeCursorHint(pTabItem, pWInfo, pLevel, pEnd); if( pStart ){ Expr *pX; /* The expression that defines the start bound */ int r1, rTemp; /* Registers for holding the start boundary */ + int op; /* Cursor seek operation */ /* The following constant maps TK_xx codes into corresponding ** seek opcodes. It depends on a particular ordering of TK_xx */ const u8 aMoveOp[] = { @@ -1087,20 +1266,27 @@ assert( (pStart->wtFlags & TERM_VNULL)==0 ); testcase( pStart->wtFlags & TERM_VIRTUAL ); pX = pStart->pExpr; assert( pX!=0 ); testcase( pStart->leftCursor!=iCur ); /* transitive constraints */ - r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp); - sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1); + if( sqlite3ExprIsVector(pX->pRight) ){ + r1 = rTemp = sqlite3GetTempReg(pParse); + codeExprOrVector(pParse, pX->pRight, r1, 1); + op = aMoveOp[(pX->op - TK_GT) | 0x0001]; + }else{ + r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp); + disableTerm(pLevel, pStart); + op = aMoveOp[(pX->op - TK_GT)]; + } + sqlite3VdbeAddOp3(v, op, iCur, addrBrk, r1); VdbeComment((v, "pk")); VdbeCoverageIf(v, pX->op==TK_GT); VdbeCoverageIf(v, pX->op==TK_LE); VdbeCoverageIf(v, pX->op==TK_LT); VdbeCoverageIf(v, pX->op==TK_GE); sqlite3ExprCacheAffinityChange(pParse, r1, 1); sqlite3ReleaseTempReg(pParse, rTemp); - disableTerm(pLevel, pStart); }else{ sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk); VdbeCoverageIf(v, bRev==0); VdbeCoverageIf(v, bRev!=0); } @@ -1110,17 +1296,21 @@ assert( pX!=0 ); assert( (pEnd->wtFlags & TERM_VNULL)==0 ); testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */ testcase( pEnd->wtFlags & TERM_VIRTUAL ); memEndValue = ++pParse->nMem; - sqlite3ExprCode(pParse, pX->pRight, memEndValue); - if( pX->op==TK_LT || pX->op==TK_GT ){ + codeExprOrVector(pParse, pX->pRight, memEndValue, 1); + if( 0==sqlite3ExprIsVector(pX->pRight) + && (pX->op==TK_LT || pX->op==TK_GT) + ){ testOp = bRev ? OP_Le : OP_Ge; }else{ testOp = bRev ? OP_Lt : OP_Gt; } - disableTerm(pLevel, pEnd); + if( 0==sqlite3ExprIsVector(pX->pRight) ){ + disableTerm(pLevel, pEnd); + } } start = sqlite3VdbeCurrentAddr(v); pLevel->op = bRev ? OP_Prev : OP_Next; pLevel->p1 = iCur; pLevel->p2 = start; @@ -1183,10 +1373,12 @@ OP_IdxGT, /* 1: (end_constraints && !bRev && endEq) */ OP_IdxLE, /* 2: (end_constraints && bRev && !endEq) */ OP_IdxLT, /* 3: (end_constraints && bRev && endEq) */ }; u16 nEq = pLoop->u.btree.nEq; /* Number of == or IN terms */ + u16 nBtm = pLoop->u.btree.nBtm; /* Length of BTM vector */ + u16 nTop = pLoop->u.btree.nTop; /* Length of TOP vector */ int regBase; /* Base register holding constraint values */ WhereTerm *pRangeStart = 0; /* Inequality constraint at range start */ WhereTerm *pRangeEnd = 0; /* Inequality constraint at range end */ int startEq; /* True if range start uses ==, >= or <= */ int endEq; /* True if range end uses ==, >= or <= */ @@ -1195,11 +1387,11 @@ Index *pIdx; /* The index we will be using */ int iIdxCur; /* The VDBE cursor for the index */ int nExtraReg = 0; /* Number of extra registers needed */ int op; /* Instruction opcode */ char *zStartAff; /* Affinity for start of range constraint */ - char cEndAff = 0; /* Affinity for end of range constraint */ + char *zEndAff = 0; /* Affinity for end of range constraint */ u8 bSeekPastNull = 0; /* True to seek past initial nulls */ u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */ pIdx = pLoop->u.btree.pIndex; iIdxCur = pLevel->iIdxCur; @@ -1229,18 +1421,18 @@ ** of the range. */ j = nEq; if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ pRangeStart = pLoop->aLTerm[j++]; - nExtraReg = 1; + nExtraReg = MAX(nExtraReg, pLoop->u.btree.nBtm); /* Like optimization range constraints always occur in pairs */ assert( (pRangeStart->wtFlags & TERM_LIKEOPT)==0 || (pLoop->wsFlags & WHERE_TOP_LIMIT)!=0 ); } if( pLoop->wsFlags & WHERE_TOP_LIMIT ){ pRangeEnd = pLoop->aLTerm[j++]; - nExtraReg = 1; + nExtraReg = MAX(nExtraReg, pLoop->u.btree.nTop); #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS if( (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0 ){ assert( pRangeStart!=0 ); /* LIKE opt constraints */ assert( pRangeStart->wtFlags & TERM_LIKEOPT ); /* occur in pairs */ pLevel->iLikeRepCntr = (u32)++pParse->nMem; @@ -1272,20 +1464,23 @@ if( (nEqnKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC)) || (bRev && pIdx->nKeyCol==nEq) ){ SWAP(WhereTerm *, pRangeEnd, pRangeStart); SWAP(u8, bSeekPastNull, bStopAtNull); + SWAP(u8, nBtm, nTop); } /* Generate code to evaluate all constraint terms using == or IN ** and store the values of those terms in an array of registers ** starting at regBase. */ codeCursorHint(pTabItem, pWInfo, pLevel, pRangeEnd); regBase = codeAllEqualityTerms(pParse,pLevel,bRev,nExtraReg,&zStartAff); assert( zStartAff==0 || sqlite3Strlen30(zStartAff)>=nEq ); - if( zStartAff ) cEndAff = zStartAff[nEq]; + if( zStartAff && nTop ){ + zEndAff = sqlite3DbStrDup(db, &zStartAff[nEq]); + } addrNxt = pLevel->addrNxt; testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 ); testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 ); testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 ); @@ -1296,31 +1491,28 @@ /* Seek the index cursor to the start of the range. */ nConstraint = nEq; if( pRangeStart ){ Expr *pRight = pRangeStart->pExpr->pRight; - sqlite3ExprCode(pParse, pRight, regBase+nEq); + codeExprOrVector(pParse, pRight, regBase+nEq, nBtm); whereLikeOptimizationStringFixup(v, pLevel, pRangeStart); if( (pRangeStart->wtFlags & TERM_VNULL)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt); VdbeCoverage(v); } if( zStartAff ){ - if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_BLOB){ - /* Since the comparison is to be performed with no conversions - ** applied to the operands, set the affinity to apply to pRight to - ** SQLITE_AFF_BLOB. */ - zStartAff[nEq] = SQLITE_AFF_BLOB; - } - if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){ - zStartAff[nEq] = SQLITE_AFF_BLOB; - } - } - nConstraint++; - testcase( pRangeStart->wtFlags & TERM_VIRTUAL ); + updateRangeAffinityStr(pParse, pRight, nBtm, &zStartAff[nEq]); + } + nConstraint += nBtm; + testcase( pRangeStart->wtFlags & TERM_VIRTUAL ); + if( sqlite3ExprIsVector(pRight)==0 ){ + disableTerm(pLevel, pRangeStart); + }else{ + startEq = 1; + } bSeekPastNull = 0; }else if( bSeekPastNull ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq); nConstraint++; startEq = 0; @@ -1349,31 +1541,39 @@ */ nConstraint = nEq; if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); - sqlite3ExprCode(pParse, pRight, regBase+nEq); + codeExprOrVector(pParse, pRight, regBase+nEq, nTop); whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd); if( (pRangeEnd->wtFlags & TERM_VNULL)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt); VdbeCoverage(v); } - if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_BLOB - && !sqlite3ExprNeedsNoAffinityChange(pRight, cEndAff) - ){ - codeApplyAffinity(pParse, regBase+nEq, 1, &cEndAff); + if( zEndAff ){ + updateRangeAffinityStr(pParse, pRight, nTop, zEndAff); + codeApplyAffinity(pParse, regBase+nEq, nTop, zEndAff); + }else{ + assert( pParse->db->mallocFailed ); } - nConstraint++; + nConstraint += nTop; testcase( pRangeEnd->wtFlags & TERM_VIRTUAL ); + + if( sqlite3ExprIsVector(pRight)==0 ){ + disableTerm(pLevel, pRangeEnd); + }else{ + endEq = 1; + } }else if( bStopAtNull ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq); endEq = 0; nConstraint++; } sqlite3DbFree(db, zStartAff); + sqlite3DbFree(db, zEndAff); /* Top of the loop body */ pLevel->p2 = sqlite3VdbeCurrentAddr(v); /* Check if the index cursor is past the end of the range. */ @@ -1385,12 +1585,10 @@ testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT ); testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE ); } /* Seek the table cursor, if required */ - disableTerm(pLevel, pRangeStart); - disableTerm(pLevel, pRangeEnd); 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; @@ -1410,13 +1608,11 @@ } sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont, iRowidReg, pPk->nKeyCol); VdbeCoverage(v); } - /* Record the instruction used to terminate the loop. Disable - ** WHERE clause terms made redundant by the index range scan. - */ + /* Record the instruction used to terminate the loop. */ if( pLoop->wsFlags & WHERE_ONEROW ){ pLevel->op = OP_Noop; }else if( bRev ){ pLevel->op = OP_Prev; }else{ @@ -1489,11 +1685,11 @@ 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; Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -93,11 +93,11 @@ } /* ** Return TRUE if the given operator is one of the operators that is ** allowed for an indexable WHERE clause term. The allowed operators are -** "=", "<", ">", "<=", ">=", "IN", and "IS NULL" +** "=", "<", ">", "<=", ">=", "IN", "IS", and "IS NULL" */ static int allowedOp(int op){ assert( TK_GT>TK_EQ && TK_GTTK_EQ && TK_LTTK_EQ && TK_LE, <, >= or <=), perform the processing + ** on the first element of the vector. */ + assert( TK_GT+1==TK_LE && TK_GT+2==TK_LT && TK_GT+3==TK_GE ); + assert( TK_ISop==TK_VECTOR && (op>=TK_GT && ALWAYS(op<=TK_GE)) ){ + pExpr = pExpr->x.pList->a[0].pExpr; + } + if( pExpr->op==TK_COLUMN ){ *piCur = pExpr->iTable; *piColumn = pExpr->iColumn; return 1; } @@ -849,14 +862,14 @@ for(i=0; mPrereq>1; i++, mPrereq>>=1){} iCur = pFrom->a[i].iCursor; for(pIdx=pFrom->a[i].pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->aColExpr==0 ) continue; for(i=0; inKeyCol; i++){ - if( pIdx->aiColumn[i]!=(-2) ) continue; + if( pIdx->aiColumn[i]!=XN_EXPR ) continue; if( sqlite3ExprCompare(pExpr, pIdx->aColExpr->a[i].pExpr, iCur)==0 ){ *piCur = iCur; - *piColumn = -2; + *piColumn = XN_EXPR; return 1; } } } return 0; @@ -909,10 +922,11 @@ assert( pExpr->op!=TK_AS && pExpr->op!=TK_COLLATE ); prereqLeft = sqlite3WhereExprUsage(pMaskSet, pExpr->pLeft); op = pExpr->op; if( op==TK_IN ){ assert( pExpr->pRight==0 ); + if( sqlite3ExprCheckIN(pParse, pExpr) ) return; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ pTerm->prereqRight = exprSelectUsage(pMaskSet, pExpr->x.pSelect); }else{ pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList); } @@ -935,22 +949,30 @@ 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( exprMightBeIndexed(pSrc, prereqLeft, pLeft, &iCur, &iColumn) ){ + + 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, pTerm->prereqRight, pRight, &iCur, &iColumn) + && 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); @@ -1149,10 +1171,63 @@ pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ + + /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create + ** new terms for each component comparison - "a = ?" and "b = ?". The + ** new terms completely replace the original vector comparison, which is + ** no longer used. + ** + ** This is only required if at least one side of the comparison operation + ** is not a sub-select. */ + if( pWC->op==TK_AND + && (pExpr->op==TK_EQ || pExpr->op==TK_IS) + && sqlite3ExprIsVector(pExpr->pLeft) + && ( (pExpr->pLeft->flags & EP_xIsSelect)==0 + || (pExpr->pRight->flags & EP_xIsSelect)==0 + )){ + int nLeft = sqlite3ExprVectorSize(pExpr->pLeft); + int i; + assert( nLeft==sqlite3ExprVectorSize(pExpr->pRight) ); + for(i=0; ipLeft, i); + Expr *pRight = sqlite3ExprForVectorField(pParse, pExpr->pRight, i); + + pNew = sqlite3PExpr(pParse, pExpr->op, pLeft, pRight, 0); + idxNew = whereClauseInsert(pWC, pNew, TERM_DYNAMIC); + exprAnalyze(pSrc, pWC, idxNew); + } + pTerm = &pWC->a[idxTerm]; + pTerm->wtFlags = TERM_CODED|TERM_VIRTUAL; /* Disable the original */ + pTerm->eOperator = 0; + } + + /* 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; ipLeft); i++){ + int idxNew; + idxNew = whereClauseInsert(pWC, pExpr, TERM_VIRTUAL); + pWC->a[idxNew].iField = i+1; + exprAnalyze(pSrc, pWC, idxNew); + markTermAsChild(pWC, idxNew, idxTerm); + } + } #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 /* When sqlite_stat3 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a Index: test/e_expr.test ================================================================== --- test/e_expr.test +++ test/e_expr.test @@ -845,10 +845,13 @@ 1 10 "x() >= 5 AND x() <= 15" 1 2 2 10 "x() BETWEEN 5 AND 15" 1 1 3 5 "x() >= 5 AND x() <= 5" 1 2 4 5 "x() BETWEEN 5 AND 5" 1 1 + + 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2 + 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1 } { do_test e_expr-13.1.$tn { set ::xcount 0 set a [execsql "SELECT $expr"] list $::xcount $a @@ -1807,20 +1810,20 @@ # return a result set with a single column. # # The following block tests that errors are returned in a bunch of cases # where a subquery returns more than one column. # -set M {only a single result allowed for a SELECT that is part of an expression} +set M {/1 {sub-select returns [23] columns - expected 1}/} foreach {tn sql} { 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } 3 { SELECT (SELECT 1, 2) } 4 { SELECT (SELECT NULL, NULL, NULL) } 5 { SELECT (SELECT * FROM t2) } 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } } { - do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M] + do_catchsql_test e_expr-35.2.$tn $sql $M } # EVIDENCE-OF: R-35764-28041 The result of the expression is the value # of the only column in the first row returned by the SELECT statement. # Index: test/in.test ================================================================== --- test/in.test +++ test/in.test @@ -312,11 +312,11 @@ } {64 256 world} do_test in-9.4 { catchsql { SELECT b FROM t1 WHERE a NOT IN tb; } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} # IN clauses in CHECK constraints. Ticket #1645 # do_test in-10.1 { execsql { @@ -389,32 +389,32 @@ catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2 ); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-12.3 { catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 UNION SELECT a, b FROM t2 ); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-12.4 { catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2 ); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-12.5 { catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2 ); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-12.6 { catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 ); @@ -476,11 +476,11 @@ catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2 ); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-12.15 { catchsql { SELECT * FROM t2 WHERE a IN ( SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 ); @@ -627,15 +627,16 @@ CREATE INDEX i5 ON b(id); SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); } } {} +breakpoint do_test in-13.15 { catchsql { SELECT 0 WHERE (SELECT 0,0) OR (0 IN (1,2)); } -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {sub-select returns 2 columns - expected 1}} do_test in-13.X { db nullvalue "" } {} ADDED test/rowvalue.test Index: test/rowvalue.test ================================================================== --- /dev/null +++ test/rowvalue.test @@ -0,0 +1,259 @@ +# 2016 June 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the SELECT statement. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue + +do_execsql_test 0.0 { + CREATE TABLE one(o); + INSERT INTO one VALUES(1); +} + +foreach {tn v1 v2 eq ne is isnot} { + 1 "1, 2, 3" "1, 2, 3" 1 0 1 0 + 2 "1, 0, 3" "1, 2, 3" 0 1 0 1 + 3 "1, 2, NULL" "1, 2, 3" {} {} 0 1 + 4 "1, 2, NULL" "1, 2, NULL" {} {} 1 0 + 5 "NULL, NULL, NULL" "NULL, NULL, NULL" {} {} 1 0 + + 6 "1, NULL, 1" "1, 1, 1" {} {} 0 1 + 7 "1, NULL, 1" "1, 1, 2" 0 1 0 1 +} { + do_execsql_test 1.$tn.eq "SELECT ($v1) == ($v2)" [list $eq] + do_execsql_test 1.$tn.ne "SELECT ($v1) != ($v2)" [list $ne] + + do_execsql_test 1.$tn.is "SELECT ($v1) IS ($v2)" [list $is] + do_execsql_test 1.$tn.isnot "SELECT ($v1) IS NOT ($v2)" [list $isnot] + + do_execsql_test 1.$tn.2.eq "SELECT (SELECT $v1) == (SELECT $v2)" [list $eq] + do_execsql_test 1.$tn.2.ne "SELECT (SELECT $v1) != (SELECT $v2)" [list $ne] +} + +foreach {tn v1 v2 lt gt le ge} { + 1 "(1, 1, 3)" "(1, 2, 3)" 1 0 1 0 + 2 "(1, 2, 3)" "(1, 2, 3)" 0 0 1 1 + 3 "(1, 3, 3)" "(1, 2, 3)" 0 1 0 1 + + 4 "(1, NULL, 3)" "(1, 2, 3)" {} {} {} {} + 5 "(1, 3, 3)" "(1, NULL, 3)" {} {} {} {} + 6 "(1, NULL, 3)" "(1, NULL, 3)" {} {} {} {} +} { + foreach {tn2 expr res} [list \ + 2.$tn.lt "$v1 < $v2" $lt \ + 2.$tn.gt "$v1 > $v2" $gt \ + 2.$tn.le "$v1 <= $v2" $le \ + 2.$tn.ge "$v1 >= $v2" $ge \ + ] { + do_execsql_test $tn2 "SELECT $expr" [list $res] + + set map(0) [list] + set map() [list] + set map(1) [list 1] + do_execsql_test $tn2.where1 "SELECT * FROM one WHERE $expr" $map($res) + + set map(0) [list 1] + set map() [list] + set map(1) [list] + do_execsql_test $tn2.where2 "SELECT * FROM one WHERE NOT $expr" $map($res) + } +} + +do_execsql_test 3.0 { + CREATE TABLE t1(x, y); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(2, 3); + INSERT INTO t1 VALUES(2, 4); + INSERT INTO t1 VALUES(3, 5); + INSERT INTO t1 VALUES(3, 6); +} + +foreach {tn r order} { + 1 "(1, 1)" "ORDER BY y" + 2 "(1, 1)" "ORDER BY x, y" + 3 "(1, 2)" "ORDER BY x, y DESC" + 4 "(3, 6)" "ORDER BY x DESC, y DESC" + 5 "((3, 5))" "ORDER BY x DESC, y" + 6 "(SELECT 3, 5)" "ORDER BY x DESC, y" +} { + do_execsql_test 3.$tn.1 "SELECT $r == (SELECT x,y FROM t1 $order)" 1 + do_execsql_test 3.$tn.2 "SELECT $r == (SELECT * FROM t1 $order)" 1 + + do_execsql_test 3.$tn.3 " + SELECT (SELECT * FROM t1 $order) == (SELECT * FROM t1 $order) + " 1 + do_execsql_test 3.$tn.4 " + SELECT (SELECT 0, 0) == (SELECT * FROM t1 $order) + " 0 +} + +foreach {tn expr res} { + 1 {(2, 2) BETWEEN (2, 2) AND (3, 3)} 1 + 2 {(2, 2) BETWEEN (2, NULL) AND (3, 3)} {} + 3 {(2, 2) BETWEEN (3, NULL) AND (3, 3)} 0 +} { + do_execsql_test 4.$tn "SELECT $expr" [list $res] +} + +foreach {tn expr res} { + 1 {(2, 4) IN (SELECT * FROM t1)} 1 + 2 {(3, 4) IN (SELECT * FROM t1)} 0 + + 3 {(NULL, 4) IN (SELECT * FROM t1)} {} + 4 {(NULL, 0) IN (SELECT * FROM t1)} 0 + + 5 {(NULL, 4) NOT IN (SELECT * FROM t1)} {} + 6 {(NULL, 0) NOT IN (SELECT * FROM t1)} 1 +} { + do_execsql_test 5.$tn "SELECT $expr" [list $res] +} + +do_execsql_test 6.0 { + CREATE TABLE hh(a, b, c); + INSERT INTO hh VALUES('abc', 1, 'i'); + INSERT INTO hh VALUES('ABC', 1, 'ii'); + INSERT INTO hh VALUES('def', 2, 'iii'); + INSERT INTO hh VALUES('DEF', 2, 'iv'); + INSERT INTO hh VALUES('GHI', 3, 'v'); + INSERT INTO hh VALUES('ghi', 3, 'vi'); + + CREATE INDEX hh_ab ON hh(a, b); +} + +do_execsql_test 6.1 { + SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1); +} {i} +do_execsql_test 6.2 { + SELECT c FROM hh WHERE (a, b) = (SELECT 'abc' COLLATE nocase, 1); +} {i} +do_execsql_test 6.3 { + SELECT c FROM hh WHERE a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); +} {i} +do_execsql_test 6.4 { + SELECT c FROM hh WHERE +a = (SELECT 'abc' COLLATE nocase) AND b = (SELECT 1); +} {i} +do_execsql_test 6.5 { + SELECT c FROM hh WHERE a = (SELECT 'abc') COLLATE nocase AND b = (SELECT 1); +} {i ii} +do_catchsql_test 6.6 { + SELECT c FROM hh WHERE (a, b) = (SELECT 'abc', 1) COLLATE nocase; +} {1 {row value misused}} +do_catchsql_test 6.7 { + SELECT c FROM hh WHERE (a, b) = 1; +} {1 {row value misused}} +do_execsql_test 6.8 { + SELECT c FROM hh WHERE (a COLLATE nocase, b) = (SELECT 'def', 2); +} {iii iv} +do_execsql_test 6.9 { + SELECT c FROM hh WHERE (a COLLATE nocase, b) IS NOT (SELECT 'def', 2); +} {i ii v vi} +do_execsql_test 6.10 { + SELECT c FROM hh WHERE (b, a) = (SELECT 2, 'def'); +} {iii} + +do_execsql_test 7.0 { + CREATE TABLE xy(i INTEGER PRIMARY KEY, j, k); + INSERT INTO xy VALUES(1, 1, 1); + INSERT INTO xy VALUES(2, 2, 2); + INSERT INTO xy VALUES(3, 3, 3); + INSERT INTO xy VALUES(4, 4, 4); +} + + +foreach {tn sql res eqp} { + 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} + "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}" + + 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2} + "0 0 0 {SCAN TABLE xy}" + + 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2} + "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid (2, 1)" {2 2 2 3 3 3 4 4 4} + "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" + + 5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4} + "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" + +} { + do_eqp_test 7.$tn.1 $sql $eqp + do_execsql_test 7.$tn.2 $sql $res +} + +do_execsql_test 8.0 { + CREATE TABLE j1(a); +} +do_execsql_test 8.1 { + SELECT * FROM j1 WHERE (select min(a) FROM j1) IN (?, ?, ?) +} + +do_execsql_test 9.0 { + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); + INSERT INTO t2 VALUES(1, 1, 1); + INSERT INTO t2 VALUES(2, 2, 2); + INSERT INTO t2 VALUES(3, 3, 3); + INSERT INTO t2 VALUES(4, 4, 4); + INSERT INTO t2 VALUES(5, 5, 5); +} + +foreach {tn q res} { + 1 "(a, b) > (2, 1)" {2 3 4 5} + 2 "(a, b) > (2, 2)" {3 4 5} + 3 "(a, b) < (4, 5)" {1 2 3 4} + 4 "(a, b) < (4, 3)" {1 2 3} +} { + do_execsql_test 9.$tn "SELECT c FROM t2 WHERE $q" $res +} + +do_execsql_test 10.0 { + CREATE TABLE dual(dummy); INSERT INTO dual(dummy) VALUES('X'); + CREATE TABLE t3(a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,f TEXT); + CREATE INDEX t3x ON t3(b,c,d,e,f); + + SELECT a FROM t3 + WHERE (c,d) IN (SELECT 'c','d' FROM dual) + AND (a,b,e) IN (SELECT 'a','b','d' FROM dual); +} + +do_catchsql_test 11.1 { + CREATE TABLE t11(a); + SELECT * FROM t11 WHERE (a,a)<=1; +} {1 {row value misused}} +do_catchsql_test 11.2 { + SELECT * FROM t11 WHERE (a,a)<1; +} {1 {row value misused}} +do_catchsql_test 11.3 { + SELECT * FROM t11 WHERE (a,a)>=1; +} {1 {row value misused}} +do_catchsql_test 11.4 { + SELECT * FROM t11 WHERE (a,a)>1; +} {1 {row value misused}} +do_catchsql_test 11.5 { + SELECT * FROM t11 WHERE (a,a)==1; +} {1 {row value misused}} +do_catchsql_test 11.6 { + SELECT * FROM t11 WHERE (a,a)<>1; +} {1 {row value misused}} +do_catchsql_test 11.7 { + SELECT * FROM t11 WHERE (a,a) IS 1; +} {1 {row value misused}} +do_catchsql_test 11.8 { + SELECT * FROM t11 WHERE (a,a) IS NOT 1; +} {1 {row value misused}} + + +finish_test ADDED test/rowvalue2.test Index: test/rowvalue2.test ================================================================== --- /dev/null +++ test/rowvalue2.test @@ -0,0 +1,279 @@ +# 2016 June 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the SELECT statement. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue2 + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES(0, 0, 0); + INSERT INTO t1 VALUES(0, 1, 1); + INSERT INTO t1 VALUES(1, 0, 2); + INSERT INTO t1 VALUES(1, 1, 3); + + CREATE INDEX i1 ON t1(a, b); +} + +do_execsql_test 1.1.1 { SELECT c FROM t1 WHERE (a, b) >= (1, 0) } {2 3} +do_execsql_test 1.1.2 { SELECT c FROM t1 WHERE (a, b) > (1, 0) } {3} + +#------------------------------------------------------------------------- + +do_execsql_test 2.0.1 { + CREATE TABLE t2(a INTEGER, b INTEGER, c INTEGER, d INTEGER); + CREATE INDEX i2 ON t2(a, b, c); +} +do_test 2.0.2 { + foreach a {0 1 2 3} { + foreach b {0 1 2 3} { + foreach c {0 1 2 3} { + execsql { INSERT INTO t2 VALUES($a, $b, $c, $c + $b*4 + $a*16); } + }}} +} {} + +do_execsql_test 2.1 { + SELECT d FROM t2 WHERE (a, b) > (2, 2); +} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>2) }] + +do_execsql_test 2.2 { + SELECT d FROM t2 WHERE (a, b) >= (2, 2); +} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>=2) }] + +do_execsql_test 2.3 { + SELECT d FROM t2 WHERE a=1 AND (b, c) >= (1, 2); +} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>=2)) }] + +do_execsql_test 2.4 { + SELECT d FROM t2 WHERE a=1 AND (b, c) > (1, 2); +} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>2)) }] + +#------------------------------------------------------------------------- + +set words { +airfare airfield airfields airflow airfoil +airfoils airframe airframes airily airing +airings airless airlift airlifts airline +airliner airlines airlock airlocks airmail +airmails airman airmen airplane airplanes + +arraignment arraignments arraigns arrange arranged +arrangement arrangements arranger arrangers arranges +arranging arrant array arrayed arrays +arrears arrest arrested arrester arresters +arresting arrestingly arrestor arrestors arrests + +edifices edit edited editing edition +editions editor editorial editorially editorials +editors edits educable educate educated +educates educating education educational educationally +educations educator educators eel eelgrass +} + +do_test 3.0 { + execsql { CREATE TABLE t3(a, b, c, w); } + foreach w $words { + set a [string range $w 0 2] + set b [string range $w 3 5] + set c [string range $w 6 end] + execsql { INSERT INTO t3 VALUES($a, $b, $c, $w) } + } +} {} + + +foreach {tn idx} { + IDX1 {} + IDX2 { CREATE INDEX i3 ON t3(a, b, c); } + IDX3 { CREATE INDEX i3 ON t3(a, b); } + IDX4 { CREATE INDEX i3 ON t3(a); } +} { + execsql { DROP INDEX IF EXISTS i3 } + execsql $idx + + foreach w $words { + set a [string range $w 0 2] + set b [string range $w 3 5] + set c [string range $w 6 end] + + foreach op [list > >= < <= == IS] { + do_execsql_test 3.1.$tn.$w.$op [subst -novar { + SELECT rowid FROM t3 WHERE (a, b, c) [set op] ($a, $b, $c) + ORDER BY +rowid + }] [db eval [subst -novar { + SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid + }]] + + do_execsql_test 3.1.$tn.$w.$op.subselect [subst -novar { + SELECT rowid FROM t3 WHERE (a, b, c) [set op] ( + SELECT a, b, c FROM t3 WHERE w = $w + ) + ORDER BY +rowid + }] [db eval [subst -novar { + SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid + }]] + } + + } +} + +#------------------------------------------------------------------------- +# + +do_execsql_test 4.0 { + CREATE TABLE t4(a, b, c); + INSERT INTO t4 VALUES(NULL, NULL, NULL); + INSERT INTO t4 VALUES(NULL, NULL, 0); + INSERT INTO t4 VALUES(NULL, NULL, 1); + INSERT INTO t4 VALUES(NULL, 0, NULL); + INSERT INTO t4 VALUES(NULL, 0, 0); + INSERT INTO t4 VALUES(NULL, 0, 1); + INSERT INTO t4 VALUES(NULL, 1, NULL); + INSERT INTO t4 VALUES(NULL, 1, 0); + INSERT INTO t4 VALUES(NULL, 1, 1); + + INSERT INTO t4 VALUES( 0, NULL, NULL); + INSERT INTO t4 VALUES( 0, NULL, 0); + INSERT INTO t4 VALUES( 0, NULL, 1); + INSERT INTO t4 VALUES( 0, 0, NULL); + INSERT INTO t4 VALUES( 0, 0, 0); + INSERT INTO t4 VALUES( 0, 0, 1); + INSERT INTO t4 VALUES( 0, 1, NULL); + INSERT INTO t4 VALUES( 0, 1, 0); + INSERT INTO t4 VALUES( 0, 1, 1); + + INSERT INTO t4 VALUES( 1, NULL, NULL); + INSERT INTO t4 VALUES( 1, NULL, 0); + INSERT INTO t4 VALUES( 1, NULL, 1); + INSERT INTO t4 VALUES( 1, 0, NULL); + INSERT INTO t4 VALUES( 1, 0, 0); + INSERT INTO t4 VALUES( 1, 0, 1); + INSERT INTO t4 VALUES( 1, 1, NULL); + INSERT INTO t4 VALUES( 1, 1, 0); + INSERT INTO t4 VALUES( 1, 1, 1); +} + +proc make_expr1 {cList vList op} { + return "([join $cList ,]) $op ([join $vList ,])" +} + +proc make_expr3 {cList vList op} { + set n [llength $cList] + + set aList [list] + foreach c [lrange $cList 0 end-1] v [lrange $vList 0 end-1] { + lappend aList "$c == $v" + } + lappend aList "[lindex $cList end] $op [lindex $vList end]" + + return "([join $aList { AND }])" +} + +proc make_expr2 {cList vList op} { + set ret "" + + switch -- $op { + == - IS { + set aList [list] + foreach c $cList v $vList { lappend aList "($c $op $v)" } + set ret [join $aList " AND "] + } + + < - > { + set oList [list] + for {set i 0} {$i < [llength $cList]} {incr i} { + lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $op] + } + set ret [join $oList " OR "] + } + + <= - >= { + set o2 [string range $op 0 0] + set oList [list] + for {set i 0} {$i < [llength $cList]-1} {incr i} { + lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $o2] + } + lappend oList [make_expr3 $cList $vList $op] + set ret [join $oList " OR "] + } + + + default { + error "Unknown op: $op" + } + } + + set ret +} + +foreach {tn idx} { + IDX1 {} + IDX2 { CREATE INDEX i4 ON t4(a, b, c); } + IDX3 { CREATE INDEX i4 ON t4(a, b); } + IDX4 { CREATE INDEX i4 ON t4(a); } +} { + execsql { DROP INDEX IF EXISTS i4 } + execsql $idx + + foreach {tn2 vector} { + 1 {0 0 0} + 2 {1 1 1} + 3 {0 0 NULL} + 4 {0 NULL 0} + 5 {NULL 0 0} + 6 {1 1 NULL} + 7 {1 NULL 1} + 8 {NULL 1 1} + } { + foreach op { IS == < <= > >= } { + set e1 [make_expr1 {a b c} $vector $op] + set e2 [make_expr2 {a b c} $vector $op] + + do_execsql_test 4.$tn.$tn2.$op \ + "SELECT rowid FROM t4 WHERE $e2 ORDER BY +rowid" [ + db eval "SELECT rowid FROM t4 WHERE $e1 ORDER BY +rowid" + ] + } + } +} + +do_execsql_test 5.0 { + CREATE TABLE r1(a TEXT, iB TEXT); + CREATE TABLE r2(x TEXT, zY INTEGER); + CREATE INDEX r1ab ON r1(a, iB); + + INSERT INTO r1 VALUES(35, 35); + INSERT INTO r2 VALUES(35, 36); + INSERT INTO r2 VALUES(35, 4); + INSERT INTO r2 VALUES(35, 35); +} {} + +foreach {tn lhs rhs} { + 1 {x +zY} {a iB} + 2 {x zY} {a iB} + 3 {x zY} {a +iB} + 4 {+x zY} {a iB} + 5 {x zY} {+a iB} +} { + foreach op { IS == < <= > >= } { + set e1 [make_expr1 $lhs $rhs $op] + set e2 [make_expr2 $lhs $rhs $op] + do_execsql_test 5.$tn.$op \ + "SELECT * FROM r1, r2 WHERE $e2 ORDER BY iB" [db eval \ + "SELECT * FROM r1, r2 WHERE $e1 ORDER BY iB" + ] + } +} + + +finish_test ADDED test/rowvalue3.test Index: test/rowvalue3.test ================================================================== --- /dev/null +++ test/rowvalue3.test @@ -0,0 +1,206 @@ +# 2016 June 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing "(...) IN (SELECT ...)" expressions +# 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); +} + +foreach {tn sql res} { + 1 "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)" 1 + 2 "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)" {} + 3 "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)" {} + 4 "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)" 1 + 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 { + CREATE TABLE z1(x, y, z); + CREATE TABLE kk(a, b); + + INSERT INTO z1 VALUES('a', 'b', 'c'); + INSERT INTO z1 VALUES('d', 'e', 'f'); + INSERT INTO z1 VALUES('g', 'h', 'i'); + + -- INSERT INTO kk VALUES('y', 'y'); + 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} + + do_execsql_test 2.$tn.2 { + SELECT * FROM z1 WHERE (x,y) IN (SELECT a, b FROM kk) + } {d e f} + + do_execsql_test 2.$tn.3 { + SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b FROM kk) + } {d e f} + + do_execsql_test 2.$tn.4 { + SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b||'x' FROM kk) + } {} + + 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 ADDED test/rowvalue4.test Index: test/rowvalue4.test ================================================================== --- /dev/null +++ test/rowvalue4.test @@ -0,0 +1,313 @@ +# 2016 July 29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is syntax errors involving row-value constructors +# and sub-selects that return multiple arguments. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue4 + +#------------------------------------------------------------------------- +# Test some error conditions: +# +# * row values used where they are not supported, +# * row values or sub-selects that contain/return the wrong number +# of elements. +# +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c); + CREATE INDEX t1bac ON t1(b, a, c); +} + +foreach {tn e} { + 1 "(1, 2, 3)" + 2 "1 + (1, 2)" + 3 "(1,2,3) == (1, 2)" +} { + do_catchsql_test 1.$tn "SELECT $e" {1 {row value misused}} +} + +foreach {tn s error} { + 1 "SELECT * FROM t1 WHERE a = (1, 2)" {row value misused} + 2 "SELECT * FROM t1 WHERE b = (1, 2)" {row value misused} + 3 "SELECT * FROM t1 WHERE NOT (b = (1, 2))" {row value misused} + 4 "SELECT * FROM t1 LIMIT (1, 2)" {row value misused} + 5 "SELECT (a, b) IN (SELECT * FROM t1) FROM t1" + {sub-select returns 3 columns - expected 2} + + 6 "SELECT * FROM t1 WHERE (a, b) IN (SELECT * FROM t1)" + {sub-select returns 3 columns - expected 2} + 7 "SELECT * FROM t1 WHERE (c, c) <= 1" {row value misused} + 8 "SELECT * FROM t1 WHERE (b, b) <= 1" {row value misused} +} { + do_catchsql_test 2.$tn "$s" [list 1 $error] +} + +#------------------------------------------------------------------------- +do_execsql_test 2.0 { + CREATE TABLE t2(a, b, c, d); + INSERT INTO t2 VALUES(1, 1, 1, 1); + INSERT INTO t2 VALUES(1, 1, 2, 2); + INSERT INTO t2 VALUES(1, 1, 3, 3); + INSERT INTO t2 VALUES(1, 2, 1, 4); + INSERT INTO t2 VALUES(1, 2, 2, 5); + INSERT INTO t2 VALUES(1, 2, 3, 6); + INSERT INTO t2 VALUES(1, 3, 1, 7); + INSERT INTO t2 VALUES(1, 3, 2, 8); + INSERT INTO t2 VALUES(1, 3, 3, 9); + + INSERT INTO t2 VALUES(2, 1, 1, 10); + INSERT INTO t2 VALUES(2, 1, 2, 11); + INSERT INTO t2 VALUES(2, 1, 3, 12); + INSERT INTO t2 VALUES(2, 2, 1, 13); + INSERT INTO t2 VALUES(2, 2, 2, 14); + INSERT INTO t2 VALUES(2, 2, 3, 15); + INSERT INTO t2 VALUES(2, 3, 1, 16); + INSERT INTO t2 VALUES(2, 3, 2, 17); + INSERT INTO t2 VALUES(2, 3, 3, 18); + + INSERT INTO t2 VALUES(3, 1, 1, 19); + INSERT INTO t2 VALUES(3, 1, 2, 20); + INSERT INTO t2 VALUES(3, 1, 3, 21); + INSERT INTO t2 VALUES(3, 2, 1, 22); + INSERT INTO t2 VALUES(3, 2, 2, 23); + INSERT INTO t2 VALUES(3, 2, 3, 24); + INSERT INTO t2 VALUES(3, 3, 1, 25); + INSERT INTO t2 VALUES(3, 3, 2, 26); + INSERT INTO t2 VALUES(3, 3, 3, 27); +} + +foreach {nm idx} { + idx1 {} + idx2 { CREATE INDEX t2abc ON t2(a, b, c); } + idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); } + idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); } + idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); } + idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); } + idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) } + idx8 { CREATE INDEX t2abc ON t2(c, b, a); } + idx9 { CREATE INDEX t2d ON t2(d); } + idx10 { CREATE INDEX t2abc ON t2(a DESC, b, c DESC); } +} { + drop_all_indexes + execsql $idx + + foreach {tn where res} { + 1 "(a, b, c) < (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13} + 2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14} + 3 "(a, b, c) > (2, 2, 2)" {15 16 17 18 19 20 21 22 23 24 25 26 27} + 4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27} + 5 "(a, b, c) >= (2, 2, NULL)" {16 17 18 19 20 21 22 23 24 25 26 27} + 6 "(a, b, c) <= (2, 2, NULL)" {1 2 3 4 5 6 7 8 9 10 11 12} + 7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27} + 8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9} + + 9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)" + {1 2 3 4 5 6 7 8 9 10 11 12 13} + + 10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14 + + 11 "a = 2 AND (b, c) > (2, 2)" {15 16 17 18} + 12 "a = 2 AND (b, c) < (3, 3) AND (b, c) > (1, 1)" {11 12 13 14 15 16 17} + } { + set result [db eval "SELECT d FROM t2 WHERE $where"] + do_test 2.1.$nm.$tn { lsort -integer $result } $res + } + + foreach {tn e res} { + 1 "(2, 1) IN (SELECT a, b FROM t2)" 1 + 2 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d)" 1 + 3 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 9)" 0 + 4 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 10)" 1 + + 5 "(3, 3) = (SELECT a, b FROM t2 ORDER BY d DESC LIMIT 1)" 1 + 6 "(3, 3) = (SELECT a, b FROM t2 ORDER BY d ASC LIMIT 1)" 0 + 7 "(1, NULL) = (SELECT a, b FROM t2 ORDER BY d ASC LIMIT 1)" {{}} + + 8 "(3, 1) = (SELECT b, c FROM t2 ORDER BY d DESC LIMIT 1 OFFSET 2)" 1 + 9 "(3, 1) = (SELECT b, c FROM t2 ORDER BY d ASC LIMIT 1 OFFSET 2)" 0 + 10 "(1, NULL) = (SELECT b, c FROM t2 ORDER BY d ASC LIMIT 1 OFFSET 2)" {{}} + + 11 "(3, 3) = (SELECT max(a), max(b) FROM t2)" 1 + 12 "(3, 1) = (SELECT max(a), min(b) FROM t2)" 1 + 13 "(NULL, NULL) = (SELECT max(a), min(b) FROM t2)" {{}} + + 14 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 5 OFFSET 11)" 1 + 15 "(2, 1) IN (SELECT a, b FROM t2 ORDER BY d LIMIT 5 OFFSET 12)" 0 + } { + do_execsql_test 2.2.$nm.$tn "SELECT $e" $res + } +} + +ifcapable stat4 { + do_execsql_test 3.0 { + CREATE TABLE c1(a, b, c, d); + INSERT INTO c1(a, b) VALUES(1, 'a'); + INSERT INTO c1(a, b) VALUES(1, 'b'); + INSERT INTO c1(a, b) VALUES(1, 'c'); + INSERT INTO c1(a, b) VALUES(1, 'd'); + INSERT INTO c1(a, b) VALUES(1, 'e'); + INSERT INTO c1(a, b) VALUES(1, 'f'); + INSERT INTO c1(a, b) VALUES(1, 'g'); + INSERT INTO c1(a, b) VALUES(1, 'h'); + INSERT INTO c1(a, b) VALUES(1, 'i'); + INSERT INTO c1(a, b) VALUES(1, 'j'); + INSERT INTO c1(a, b) VALUES(1, 'k'); + INSERT INTO c1(a, b) VALUES(1, 'l'); + INSERT INTO c1(a, b) VALUES(1, 'm'); + INSERT INTO c1(a, b) VALUES(1, 'n'); + INSERT INTO c1(a, b) VALUES(1, 'o'); + INSERT INTO c1(a, b) VALUES(1, 'p'); + INSERT INTO c1(a, b) VALUES(2, 'a'); + INSERT INTO c1(a, b) VALUES(2, 'b'); + INSERT INTO c1(a, b) VALUES(2, 'c'); + INSERT INTO c1(a, b) VALUES(2, 'd'); + INSERT INTO c1(a, b) VALUES(2, 'e'); + INSERT INTO c1(a, b) VALUES(2, 'f'); + INSERT INTO c1(a, b) VALUES(2, 'g'); + INSERT INTO c1(a, b) VALUES(2, 'h'); + + INSERT INTO c1(c, d) SELECT a, b FROM c1; + + CREATE INDEX c1ab ON c1(a, b); + CREATE INDEX c1cd ON c1(c, d); + ANALYZE; + } + + do_eqp_test 3.1.1 { SELECT * FROM c1 WHERE a=1 AND c=2 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)} + } + do_eqp_test 3.1.2 { SELECT * FROM c1 WHERE a=1 AND b>'d' AND c=2 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)} + } + do_eqp_test 3.1.3 { SELECT * FROM c1 WHERE a=1 AND b>'l' AND c=2 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=? AND b>?)} + } + + do_eqp_test 3.2.1 { SELECT * FROM c1 WHERE a=1 AND c>1 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c>?)} + } + do_eqp_test 3.2.2 { SELECT * FROM c1 WHERE a=1 AND c>0 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + } + do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + } + do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + } + do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))} + } + do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } { + 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + } +} + +#------------------------------------------------------------------------ + +do_execsql_test 5.0 { + CREATE TABLE d1(x, y); + CREATE TABLE d2(a, b, c); + CREATE INDEX d2ab ON d2(a, b); + CREATE INDEX d2c ON d2(c); + + WITH i(i) AS ( + VALUES(1) UNION ALL SELECT i+1 FROM i WHERE i<1000 + ) + INSERT INTO d2 SELECT i/3, i%3, i/3 FROM i; + ANALYZE; +} + +do_eqp_test 5.1 { + SELECT * FROM d2 WHERE + (a, b) IN (SELECT x, y FROM d1) AND + (c) IN (SELECT y FROM d1) +} { + 0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)} + 0 0 0 {EXECUTE LIST SUBQUERY 1} + 1 0 0 {SCAN TABLE d1} + 0 0 0 {EXECUTE LIST SUBQUERY 2} + 2 0 0 {SCAN TABLE d1} +} + +do_execsql_test 6.0 { + CREATE TABLE e1(a, b, c, d, e); + CREATE INDEX e1ab ON e1(a, b); + CREATE INDEX e1cde ON e1(c, d, e); +} + +do_eqp_test 6.1 { + SELECT * FROM e1 WHERE (a, b) > (?, ?) +} { + 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))} +} +do_eqp_test 6.2 { + SELECT * FROM e1 WHERE (a, b) < (?, ?) +} { + 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))} +} +do_eqp_test 6.3 { + SELECT * FROM e1 WHERE c = ? AND (d, e) > (?, ?) +} { + 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))} +} +do_eqp_test 6.4 { + SELECT * FROM e1 WHERE c = ? AND (d, e) < (?, ?) +} { + 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))} +} + +do_eqp_test 6.5 { + SELECT * FROM e1 WHERE (d, e) BETWEEN (?, ?) AND (?, ?) AND c = ? +} { + 0 0 0 + {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))} +} + +#------------------------------------------------------------------------- + +do_execsql_test 7.1 { + CREATE TABLE f1(a, b, c); + CREATE INDEX f1ab ON f1(a, b); +} + +do_catchsql_test 7.2 { + SELECT (a COLLATE nocase, b) IN (SELECT a, b FROM f1) FROM f1; +} {0 {}} + +do_catchsql_test 7.3 { + SELECT (a COLLATE nose, b) IN (SELECT a, b FROM f1) FROM f1; +} {1 {no such collation sequence: nose}} + +do_catchsql_test 7.4 { + SELECT * FROM f1 WHERE (?, ? COLLATE nose) > (a, b); +} {1 {no such collation sequence: nose}} + +#------------------------------------------------------------------------- +drop_all_tables +do_execsql_test 8.1 { + CREATE TABLE c1(x, y); + CREATE TABLE c2(a, b, c); + CREATE INDEX c2ab ON c2(a, b); + CREATE INDEX c2c ON c2(c); + + CREATE TABLE c3(d); +} +do_catchsql_test 8.2 { + SELECT * FROM c2 CROSS JOIN c3 WHERE + ( (a, b) == (SELECT x, y FROM c1) AND c3.d = c ) OR + ( c == (SELECT x, y FROM c1) AND c3.d = c ) +} {1 {row value misused}} + +finish_test ADDED test/rowvalue5.test Index: test/rowvalue5.test ================================================================== --- /dev/null +++ test/rowvalue5.test @@ -0,0 +1,110 @@ +# 2016 July 29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is syntax errors involving row-values and +# virtual tables. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue5 + +proc vtab_command {method args} { + switch -- $method { + xConnect { + return "CREATE TABLE t1(a, b, c, d, expr)" + } + + xBestIndex { + set COL(0) a + set COL(1) b + set COL(2) c + set COL(3) d + set COL(4) expr + + set OP(eq) = + set OP(ne) != + set OP(gt) > + set OP(le) <= + set OP(lt) < + set OP(ge) >= + set OP(match) MATCH + set OP(like) LIKE + set OP(glob) GLOB + set OP(regexp) REGEXP + + set clist [lindex $args 0] + set ret [list] + set elist [list] + set i 0 + foreach c $clist { + array set C $c + if {$C(usable)} { + lappend ret omit $i + lappend elist "$COL($C(column)) $OP($C(op)) %$i%" + } + incr i + } + + lappend ret idxstr [join $elist " AND "] + #puts "xBestIndex: $ret" + return $ret + } + + xFilter { + foreach {idxnum idxstr arglist} $args {} + set i 0 + set ee $idxstr + foreach a $arglist { + if {[string is double $a]==0} { + set a "'[string map {' ''} $a]'" + } + set ee [string map [list "%$i%" $a] $ee] + incr i + } + set ee [string map [list "'" "''"] $ee] + + set ret [list sql "SELECT 1, 'a', 'b', 'c', 'd', '$ee'"] + #puts "xFilter: $ret" + return $ret + } + } + + return {} +} + +register_tcl_module db +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); +} {} + + +foreach {tn where res} { + 1 "1" {{}} + 2 "a=1" {{a = 1}} + 3 "a=1 AND 4 = b" {{a = 1 AND b = 4}} + 4 "c>'hello'" {{c > 'hello'}} + 5 "c<='hel''lo'" {{c <= 'hel''lo'}} + 6 "(a, b) = (SELECT 9, 10)" {{a = 9 AND b = 10}} + 7 "(+a, b) = (SELECT 'a', 'b')" {{b = 'b'}} + 8 "(a, +b) = (SELECT 'a', 'b')" {{a = 'a'}} + 11 "(+a, b) IN (SELECT 'a', 'b')" {{b = 'b'}} + 12 "(a, +b) IN (SELECT 'a', 'b')" {{a = 'a'}} + + 13 "(a, b) < ('d', 'e')" {{a <= 'd'}} + 14 "(a, b) < ('a', 'c')" {{a <= 'a'}} + 15 "(a, b) <= ('a', 'b')" {{a <= 'a'}} + 16 "(a, b) < ('a', 'b')" {} +} { + do_execsql_test 1.$tn "SELECT expr FROM x1 WHERE $where" $res +} + +finish_test ADDED test/rowvalue6.test Index: test/rowvalue6.test ================================================================== --- /dev/null +++ test/rowvalue6.test @@ -0,0 +1,36 @@ +# 2016-08-18 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# The focus of this file is handling of NULL values in row-value IN +# expressions. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue6 + +do_execsql_test 1.1 { + CREATE TABLE t1(a,b,c); + CREATE INDEX t1x1 ON t1(a,b); + INSERT INTO t1 VALUES(1,NULL,200); + + CREATE TABLE t2(x,y,z); + INSERT INTO t2 VALUES(1,NULL,55); + + SELECT c FROM t1 WHERE (a,b) IN (SELECT x,y FROM t2 WHERE z==55); +} {} +do_execsql_test 1.2 { + INSERT INTO t1 VALUES(2,3,400); + INSERT INTO t2 VALUES(2,3,55); + + SELECT c FROM t1 WHERE (a,b) IN (SELECT x,y FROM t2 WHERE z==55); +} {400} + +finish_test ADDED test/rowvalue7.test Index: test/rowvalue7.test ================================================================== --- /dev/null +++ test/rowvalue7.test @@ -0,0 +1,58 @@ +# 2016-08-18 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# The focus of this file is vector assignments in the SET clause of +# an UPDATE statement. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue7 + +do_execsql_test 1.1 { + CREATE TABLE t1(a,b,c,d); + CREATE INDEX t1x ON t1(a,b); + INSERT INTO t1(a,b,c,d) VALUES(1,2,0,0),(3,4,0,0),(5,6,0,0); + CREATE TABLE t2(w,x,y,z); + CREATE INDEX t2x ON t2(w,x); + INSERT INTO t2(w,x,y,z) VALUES(1,2,11,22),(8,9,88,99),(3,5,33,55),(5,6,55,66); + + SELECT *,'|' FROM t1 ORDER BY a; +} {1 2 0 0 | 3 4 0 0 | 5 6 0 0 |} + +do_execsql_test 1.2 { + UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE (w,x)=(a,b)); + SELECT *,'|' FROM t1 ORDER BY a; +} {1 2 11 22 | 3 4 {} {} | 5 6 55 66 |} + +do_execsql_test 1.3 { + UPDATE t1 SET (c,d) = (SELECT y,z FROM t2 WHERE w=a); + SELECT *,'|' FROM t1 ORDER BY a; +} {1 2 11 22 | 3 4 33 55 | 5 6 55 66 |} + +do_execsql_test 1.4 { + UPDATE t1 SET (c) = 99 WHERE a=3; + SELECT *,'|' FROM t1 ORDER BY a; +} {1 2 11 22 | 3 4 99 55 | 5 6 55 66 |} + +do_execsql_test 1.5 { + UPDATE t1 SET b = 8, (c,d) = (SELECT 123,456) WHERE a=3; + SELECT *,'|' FROM t1 ORDER BY a; +} {1 2 11 22 | 3 8 123 456 | 5 6 55 66 |} + +do_catchsql_test 2.1 { + UPDATE t1 SET (c,d) = (SELECT x,y,z FROM t2 WHERE w=a); +} {1 {2 columns assigned 3 values}} + +do_catchsql_test 2.2 { + UPDATE t1 SET (b,c,d) = (SELECT x,y FROM t2 WHERE w=a); +} {1 {3 columns assigned 2 values}} + +finish_test ADDED test/rowvalue8.test Index: test/rowvalue8.test ================================================================== --- /dev/null +++ test/rowvalue8.test @@ -0,0 +1,59 @@ +# 2016-08-22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# Use of row values in CASE statements. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue8 + +do_execsql_test 1.1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); + INSERT INTO t1(a,b,c,d) VALUES + (1,1,2,3), + (2,2,3,4), + (3,1,2,4), + (4,2,3,5), + (5,3,4,6), + (6,4,5,9); + SELECT a, CASE (b,c) WHEN (1,2) THEN 'aleph' + WHEN (2,3) THEN 'bet' + WHEN (3,4) THEN 'gimel' + ELSE '-' END, + '|' + FROM t1 + ORDER BY a; +} {1 aleph | 2 bet | 3 aleph | 4 bet | 5 gimel | 6 - |} +do_execsql_test 1.2 { + SELECT a, CASE (b,c,d) WHEN (1,2,3) THEN 'aleph' + WHEN (2,3,4) THEN 'bet' + WHEN (3,4,6) THEN 'gimel' + ELSE '-' END, + '|' + FROM t1 + ORDER BY a; +} {1 aleph | 2 bet | 3 - | 4 - | 5 gimel | 6 - |} + +do_execsql_test 2.1 { + CREATE TABLE t2(x INTEGER PRIMARY KEY, y); + INSERT INTO t2(x,y) VALUES(1,6),(2,5),(3,4),(4,3),(5,2),(6,1); + SELECT x, CASE (SELECT b,c FROM t1 WHERE a=y) + WHEN (1,2) THEN 'aleph' + WHEN (2,3) THEN 'bet' + WHEN (3,4) THEN 'gimel' + ELSE '-' END, + '|' + FROM t2 + ORDER BY +x; +} {1 - | 2 gimel | 3 bet | 4 aleph | 5 bet | 6 aleph |} + + +finish_test ADDED test/rowvalue9.test Index: test/rowvalue9.test ================================================================== --- /dev/null +++ test/rowvalue9.test @@ -0,0 +1,302 @@ +# 2016 September 3 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing SQL statements that use row value +# constructors. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rowvalue9 + +# Tests: +# +# 1.*: Test that affinities are handled correctly by various row-value +# operations without indexes. +# +# 2.*: Test an affinity bug that came up during testing. +# +# 3.*: Test a row-value version of the bug tested by 2.*. +# +# 4.*: Test that affinities are handled correctly by various row-value +# operations with assorted indexes. +# + +do_execsql_test 1.0.1 { + CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b)); + + INSERT INTO a1 (rowid, c, b, a) VALUES(3, '0x03', 1, 1); + INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2); + INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3); + INSERT INTO a1 (rowid, c, b, a) VALUES(92, '0x5C', 4, 4); + + CREATE TABLE a2(x BLOB, y BLOB); + INSERT INTO a2(x, y) VALUES(1, 1); + INSERT INTO a2(x, y) VALUES(2, '2'); + INSERT INTO a2(x, y) VALUES('3', 3); + INSERT INTO a2(x, y) VALUES('4', '4'); +} + +do_execsql_test 1.0.2 { + SELECT x, typeof(x), y, typeof(y) FROM a2 ORDER BY rowid +} { + 1 integer 1 integer + 2 integer 2 text + 3 text 3 integer + 4 text 4 text +} + +do_execsql_test 1.1.1 { + SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a2 +} {{} {} 15 92} +do_execsql_test 1.1.2 { + SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a2 +} {{} {} 15 92} + +do_execsql_test 1.2.3 { + SELECT a1.rowid FROM a1, a2 WHERE a=x AND b=y; +} {15 92} +do_execsql_test 1.2.4 { + SELECT a1.rowid FROM a1, a2 WHERE (a, b) = (x, y) +} {15 92} + + +do_execsql_test 1.3.1 { + SELECT a1.rowid FROM a1, a2 WHERE coalesce(NULL,x)=a AND coalesce(NULL,y)=b +} {3 14 15 92} +do_execsql_test 1.3.2 { + SELECT a1.rowid FROM a1, a2 + WHERE (coalesce(NULL,x), coalesce(NULL,y)) = (a, b) +} {3 14 15 92} + +do_execsql_test 1.4.1 { + SELECT a1.rowid FROM a1, a2 WHERE +x=a AND +y=b +} {3 14 15 92} +do_execsql_test 1.4.2 { + SELECT a1.rowid FROM a1, a2 WHERE (+x, +y) = (a, b) +} {3 14 15 92} + +do_execsql_test 1.5.1 { + SELECT (SELECT rowid FROM a1 WHERE a=+x AND b=+y) FROM a2 +} {3 14 15 92} +do_execsql_test 1.5.2 { + SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a2 +} {3 14 15 92} +do_execsql_test 1.5.3 { + SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2 +} {3 14 15 92} + +do_execsql_test 1.6.1 { + SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2) +} {15 92} +do_execsql_test 1.6.2 { + SELECT a1.rowid FROM a1, a2 WHERE EXISTS ( + SELECT 1 FROM a1 WHERE a=x AND b=y + ) +} {3 14 15 92 3 14 15 92} + +# Test that [199df416] is fixed. +# +do_execsql_test 2.1 { + CREATE TABLE b1(a TEXT); + CREATE TABLE b2(x BLOB); + INSERT INTO b1 VALUES(1); + INSERT INTO b2 VALUES(1); +} +do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {} +do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} +do_execsql_test 2.4 { CREATE UNIQUE INDEX b1a ON b1(a); } +do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {} + +# Test that a multi-column version of the query that revealed problem +# [199df416] also works. +# +do_execsql_test 3.1 { + CREATE TABLE c1(a INTEGER, b TEXT); + INSERT INTO c1 VALUES(1, 1); + CREATE TABLE c2(x BLOB, y BLOB); + INSERT INTO c2 VALUES(1, 1); +} +do_execsql_test 3.2 { + SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) +} {} +do_execsql_test 3.3 { + CREATE UNIQUE INDEX c1ab ON c1(a, b); + SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2) +} {} +do_execsql_test 3.4 { + SELECT * FROM c1 WHERE (a, +b) IN (SELECT x, y FROM c2) +} {} + +do_execsql_test 3.5 { + SELECT c1.rowid FROM c1 WHERE b = (SELECT y FROM c2); +} {} +do_execsql_test 3.6 { + SELECT c1.rowid FROM c1 WHERE (a, b) = (SELECT x, y FROM c2); +} {} + + +#------------------------------------------------------------------------- +# +do_execsql_test 4.0 { + CREATE TABLE d1(a TEXT, b INTEGER, c NUMERIC); + CREATE TABLE d2(x BLOB, y BLOB); + + INSERT INTO d1 VALUES(1, 1, 1); + INSERT INTO d1 VALUES(2, 2, 2); + INSERT INTO d1 VALUES(3, 3, 3); + INSERT INTO d1 VALUES(4, 4, 4); + + INSERT INTO d2 VALUES (1, 1); + INSERT INTO d2 VALUES (2, '2'); + INSERT INTO d2 VALUES ('3', 3); + INSERT INTO d2 VALUES ('4', '4'); +} + +foreach {tn idx} { + 1 {} + 2 { CREATE INDEX idx ON d1(a) } + 3 { CREATE INDEX idx ON d1(a, c) } + 4 { CREATE INDEX idx ON d1(c) } + 5 { CREATE INDEX idx ON d1(c, a) } + + 6 { + CREATE INDEX idx ON d1(c, a) ; + CREATE INDEX idx1 ON d2(x, y); + } + + 7 { + CREATE INDEX idx ON d1(c, a) ; + CREATE UNIQUE INDEX idx2 ON d2(x, y) ; + } + + 8 { + CREATE INDEX idx ON d1(c) ; + CREATE UNIQUE INDEX idx2 ON d2(x); + } + +} { + execsql { DROP INDEX IF EXISTS idx } + execsql { DROP INDEX IF EXISTS idx2 } + execsql { DROP INDEX IF EXISTS idx3 } + execsql $idx + + do_execsql_test 4.$tn.1 { + SELECT rowid FROM d1 WHERE (a, c) IN (SELECT x, y FROM d2); + } {3 4} + + do_execsql_test 4.$tn.2 { + SELECT rowid FROM d1 WHERE (c, a) IN (SELECT x, y FROM d2); + } {2 4} + + do_execsql_test 4.$tn.3 { + SELECT rowid FROM d1 WHERE (+c, a) IN (SELECT x, y FROM d2); + } {2} + + do_execsql_test 4.$tn.4 { + SELECT rowid FROM d1 WHERE (c, a) = ( + SELECT x, y FROM d2 WHERE d2.rowid=d1.rowid + ); + } {2 4} + + do_execsql_test 4.$tn.5 { + SELECT d1.rowid FROM d1, d2 WHERE a = y; + } {2 4} + + do_execsql_test 4.$tn.6 { + SELECT d1.rowid FROM d1 WHERE a = ( + SELECT y FROM d2 where d2.rowid=d1.rowid + ); + } {2 4} +} + +do_execsql_test 5.0 { + CREATE TABLE e1(a TEXT, c NUMERIC); + CREATE TABLE e2(x BLOB, y BLOB); + + INSERT INTO e1 VALUES(2, 2); + + INSERT INTO e2 VALUES ('2', 2); + INSERT INTO e2 VALUES ('2', '2'); + INSERT INTO e2 VALUES ('2', '2.0'); + + CREATE INDEX e1c ON e1(c); +} + +do_execsql_test 5.1 { + SELECT rowid FROM e1 WHERE (a, c) IN (SELECT x, y FROM e2); +} {1} +do_execsql_test 5.2 { + SELECT rowid FROM e2 WHERE rowid IN (SELECT +c FROM e1); +} {2} +do_execsql_test 5.3 { + SELECT rowid FROM e2 WHERE rowid IN (SELECT 0+c FROM e1); +} {2} + +#------------------------------------------------------------------------- +# +do_execsql_test 6.0 { + CREATE TABLE f1(a, b); + CREATE TABLE f2(c, d); + CREATE TABLE f3(e, f); +} + +do_execsql_test 6.1 { + SELECT * FROM f3 WHERE (e, f) IN ( + SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2 + ); +} +do_execsql_test 6.2 { + CREATE INDEX f3e ON f3(e); + SELECT * FROM f3 WHERE (e, f) IN ( + SELECT a, b FROM f1 UNION ALL SELECT c, d FROM f2 + ); +} + + +#------------------------------------------------------------------------- +# +do_execsql_test 7.0 { + CREATE TABLE g1(a, b); + INSERT INTO g1 VALUES + (1, 1), (1, 2), (1, 3), (1, 'i'), (1, 'j'), + (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), + (1, 4), (1, 5); + + CREATE TABLE g2(x, y); + CREATE INDEX g2x ON g2(x); + + INSERT INTO g2 VALUES(1, 4); + INSERT INTO g2 VALUES(1, 5); +} + +do_execsql_test 7.1 { + SELECT * FROM g2 WHERE (x, y) IN ( + SELECT a, b FROM g1 ORDER BY +a, +b LIMIT 10 + ); +} { 1 4 1 5 } + +do_execsql_test 7.2 { + SELECT * FROM g2 WHERE (x, y) IN ( + SELECT a, b FROM g1 ORDER BY a, b LIMIT 10 + ); +} { 1 4 1 5 } + +do_execsql_test 7.3 { + SELECT * FROM g2 WHERE (x, y) IN ( + SELECT a, b FROM g1 ORDER BY 1, 2 LIMIT 10 + ); +} { 1 4 1 5 } + + +finish_test + ADDED test/rowvaluefault.test Index: test/rowvaluefault.test ================================================================== --- /dev/null +++ test/rowvaluefault.test @@ -0,0 +1,72 @@ +# 2016 June 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/malloc_common.tcl +set ::testprefix rowvaluefault + +do_execsql_test 1.0 { + CREATE TABLE xyz(one, two, thr, fou); + INSERT INTO xyz VALUES('A', 'A', 'A', 1); + INSERT INTO xyz VALUES('B', 'B', 'B', 2); + INSERT INTO xyz VALUES('C', 'C', 'C', 3); + INSERT INTO xyz VALUES('D', 'D', 'D', 4); + + CREATE UNIQUE INDEX xyz_one_two ON xyz(one, two); +} + +do_faultsim_test 1 -faults oom* -body { + execsql { SELECT fou FROM xyz WHERE (one, two, thr) = ('B', 'B', 'B') } +} -test { + faultsim_test_result {0 2} +} + +do_faultsim_test 2 -faults oom* -body { + execsql { SELECT fou FROM xyz WHERE (two, thr) IS ('C', 'C') } +} -test { + faultsim_test_result {0 3} +} + +do_faultsim_test 3 -faults oom* -body { + execsql { SELECT fou FROM xyz WHERE (one, two, thr) > ('B', 'B', 'B') } +} -test { + faultsim_test_result {0 {3 4}} +} + +do_faultsim_test 4 -faults oom* -body { + execsql { SELECT fou FROM xyz WHERE (one, two) IN (SELECT one, two FROM xyz) } +} -test { + faultsim_test_result {0 {1 2 3 4}} +} + +do_faultsim_test 5 -faults oom* -body { + execsql { + SELECT fou FROM xyz + WHERE (one, two, thr) IN (SELECT one, two, thr FROM xyz) + } +} -test { + faultsim_test_result {0 {1 2 3 4}} +} + +do_faultsim_test 6 -faults oom* -body { + execsql { + SELECT fou FROM xyz + WHERE (one, two, thr) BETWEEN ('B', 'B', 'B') AND ('C', 'C', 'C') } +} -test { + faultsim_test_result {0 {2 3}} +} + +finish_test + Index: test/select7.test ================================================================== --- test/select7.test +++ test/select7.test @@ -112,30 +112,26 @@ do_test select7-5.1 { catchsql { CREATE TABLE t2(a,b); SELECT 5 IN (SELECT a,b FROM t2); } - } [list 1 \ - {only a single result allowed for a SELECT that is part of an expression}] + } {1 {sub-select returns 2 columns - expected 1}} do_test select7-5.2 { catchsql { SELECT 5 IN (SELECT * FROM t2); } - } [list 1 \ - {only a single result allowed for a SELECT that is part of an expression}] + } {1 {sub-select returns 2 columns - expected 1}} do_test select7-5.3 { catchsql { SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2); } - } [list 1 \ - {only a single result allowed for a SELECT that is part of an expression}] + } {1 {sub-select returns 2 columns - expected 1}} do_test select7-5.4 { catchsql { SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2); } - } [list 1 \ - {only a single result allowed for a SELECT that is part of an expression}] + } {1 {sub-select returns 2 columns - expected 1}} } # Verify that an error occurs if you have too many terms on a # compound select statement. # Index: test/subselect.test ================================================================== --- test/subselect.test +++ test/subselect.test @@ -38,11 +38,11 @@ # Try a select with more than one result column. # do_test subselect-1.2 { set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg] lappend v $msg -} {1 {only a single result allowed for a SELECT that is part of an expression}} +} {1 {row value misused}} # A subselect without an aggregate. # do_test subselect-1.3a { execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)} Index: test/tester.tcl ================================================================== --- test/tester.tcl +++ test/tester.tcl @@ -23,10 +23,11 @@ # test_pwd # get_pwd # copy_file FROM TO # delete_file FILENAME # drop_all_tables ?DB? +# drop_all_indexes ?DB? # forcecopy FROM TO # forcedelete FILENAME # # Test the capability of the SQLite version built into the interpreter to # determine if a specific test can be run: @@ -1287,13 +1288,13 @@ set G "" set B "" set D "" } foreach opcode { - Seek SeekGe SeekGt SeekLe SeekLt NotFound Last Rewind + Seek SeekGE SeekGT SeekLE SeekLT NotFound Last Rewind NoConflict Next Prev VNext VPrev VFilter - SorterSort SorterNext + SorterSort SorterNext NextIfOpen } { set color($opcode) $B } foreach opcode {ResultRow} { set color($opcode) $G @@ -1309,14 +1310,20 @@ if {$opcode == "Goto" && ($bSeenGoto==0 || ($p2 > $addr+10))} { set linebreak($p2) 1 set bSeenGoto 1 } + + if {$opcode=="Once"} { + for {set i $addr} {$i<$p2} {incr i} { + set star($i) $addr + } + } if {$opcode=="Next" || $opcode=="Prev" || $opcode=="VNext" || $opcode=="VPrev" - || $opcode=="SorterNext" + || $opcode=="SorterNext" || $opcode=="NextIfOpen" } { for {set i $p2} {$i<$addr} {incr i} { incr x($i) 2 } } @@ -1335,10 +1342,16 @@ $db eval "explain $sql" {} { if {[info exists linebreak($addr)]} { output2 "" } set I [string repeat " " $x($addr)] + + if {[info exists star($addr)]} { + set ii [expr $x($star($addr))] + append I " " + set I [string replace $I $ii $ii *] + } set col "" catch { set col $color($opcode) } output2 [format {%-4d %s%s%-12.12s%s %-6d %-6d %-6d % -17s %s %s} \ @@ -1936,10 +1949,20 @@ } ifcapable trigger&&foreignkey { $db eval "PRAGMA foreign_keys = $pk" } } + +# Drop all auxiliary indexes from the main database opened by handle [db]. +# +proc drop_all_indexes {{db db}} { + set L [$db eval { + SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%' + }] + foreach idx $L { $db eval "DROP INDEX $idx" } +} + #------------------------------------------------------------------------- # If a test script is executed with global variable $::G(perm:name) set to # "wal", then the tests are run in WAL mode. Otherwise, they should be run # in rollback mode. The following Tcl procs are used to make this less Index: test/types2.test ================================================================== --- test/types2.test +++ test/types2.test @@ -331,10 +331,10 @@ test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8} test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7} test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7} test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12} test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12} - test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11} + test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {} test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10} } finish_test Index: tool/addopcodes.tcl ================================================================== --- tool/addopcodes.tcl +++ tool/addopcodes.tcl @@ -35,10 +35,12 @@ AGG_FUNCTION AGG_COLUMN UMINUS UPLUS REGISTER + VECTOR + SELECT_COLUMN ASTERISK SPAN SPACE ILLEGAL }