Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -6245,19 +6245,24 @@ ** These macros defined the allowed values for the ** [sqlite3_index_info].aConstraint[].op field. Each value represents ** an operator that is part of a constraint term in the wHERE clause of ** a query that uses a [virtual table]. */ -#define SQLITE_INDEX_CONSTRAINT_EQ 2 -#define SQLITE_INDEX_CONSTRAINT_GT 4 -#define SQLITE_INDEX_CONSTRAINT_LE 8 -#define SQLITE_INDEX_CONSTRAINT_LT 16 -#define SQLITE_INDEX_CONSTRAINT_GE 32 -#define SQLITE_INDEX_CONSTRAINT_MATCH 64 -#define SQLITE_INDEX_CONSTRAINT_LIKE 65 -#define SQLITE_INDEX_CONSTRAINT_GLOB 66 -#define SQLITE_INDEX_CONSTRAINT_REGEXP 67 +#define SQLITE_INDEX_CONSTRAINT_EQ 2 +#define SQLITE_INDEX_CONSTRAINT_GT 4 +#define SQLITE_INDEX_CONSTRAINT_LE 8 +#define SQLITE_INDEX_CONSTRAINT_LT 16 +#define SQLITE_INDEX_CONSTRAINT_GE 32 +#define SQLITE_INDEX_CONSTRAINT_MATCH 64 +#define SQLITE_INDEX_CONSTRAINT_LIKE 65 +#define SQLITE_INDEX_CONSTRAINT_GLOB 66 +#define SQLITE_INDEX_CONSTRAINT_REGEXP 67 +#define SQLITE_INDEX_CONSTRAINT_NE 68 +#define SQLITE_INDEX_CONSTRAINT_ISNOT 69 +#define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70 +#define SQLITE_INDEX_CONSTRAINT_ISNULL 71 +#define SQLITE_INDEX_CONSTRAINT_IS 72 /* ** CAPI3REF: Register A Virtual Table Implementation ** METHOD: sqlite3 ** Index: src/test8.c ================================================================== --- src/test8.c +++ src/test8.c @@ -895,21 +895,22 @@ case SQLITE_INDEX_CONSTRAINT_GLOB: zOp = "glob"; break; case SQLITE_INDEX_CONSTRAINT_REGEXP: zOp = "regexp"; break; } - if( zOp[0]=='L' ){ - zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", - zSep, zNewCol); - } else { - zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp); - } - string_concat(&zQuery, zNew, 1, &rc); - - zSep = "AND"; - pUsage->argvIndex = ++nArg; - pUsage->omit = 1; + if( zOp ){ + if( zOp[0]=='L' ){ + zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", + zSep, zNewCol); + } else { + zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp); + } + string_concat(&zQuery, zNew, 1, &rc); + zSep = "AND"; + pUsage->argvIndex = ++nArg; + pUsage->omit = 1; + } } } /* If there is only one term in the ORDER BY clause, and it is ** on a column that this virtual table has an index for, then consume Index: src/test_bestindex.c ================================================================== --- src/test_bestindex.c +++ src/test_bestindex.c @@ -412,10 +412,20 @@ zOp = "like"; break; case SQLITE_INDEX_CONSTRAINT_GLOB: zOp = "glob"; break; case SQLITE_INDEX_CONSTRAINT_REGEXP: zOp = "regexp"; break; + case SQLITE_INDEX_CONSTRAINT_NE: + zOp = "ne"; break; + case SQLITE_INDEX_CONSTRAINT_ISNOT: + zOp = "isnot"; break; + case SQLITE_INDEX_CONSTRAINT_ISNOTNULL: + zOp = "isnotnull"; break; + case SQLITE_INDEX_CONSTRAINT_ISNULL: + zOp = "isnull"; break; + case SQLITE_INDEX_CONSTRAINT_IS: + zOp = "is"; break; } Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1)); Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1)); Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1)); Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -866,11 +866,11 @@ assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; + if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; assert( pTerm->u.leftColumn>=(-1) ); nTerm++; } @@ -914,46 +914,52 @@ *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy; *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage = pUsage; for(i=j=0, pTerm=pWC->a; inTerm; i++, pTerm++){ - u8 op; + u16 op; if( pTerm->leftCursor != pSrc->iCursor ) continue; if( pTerm->prereqRight & mUnusable ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; + if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; - op = (u8)pTerm->eOperator & WO_ALL; + op = pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; - if( op==WO_MATCH ){ - op = pTerm->eMatchOp; - } - pIdxCons[j].op = op; - /* The direct assignment in the previous line is possible only because - ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The - ** following asserts verify this fact. */ - assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); - assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); - 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; + if( op==WO_AUX ){ + pIdxCons[j].op = pTerm->eMatchOp; + }else if( op & (WO_ISNULL|WO_IS) ){ + if( op==WO_ISNULL ){ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL; + }else{ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS; + } + }else{ + pIdxCons[j].op = (u8)op; + /* The direct assignment in the previous line is possible only because + ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The + ** following asserts verify this fact. */ + assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); + assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); + assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); + assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); + assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); + assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) ); + + 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; i0 ); - if( sqlite3ExprIsVector(p) ){ + if( p && 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); Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -310,52 +310,88 @@ #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ #ifndef SQLITE_OMIT_VIRTUALTABLE /* -** Check to see if the given expression is of the form -** -** column OP expr -** -** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a -** column of a virtual table. -** -** If it is then return TRUE. If not, return FALSE. +** Check to see if the pExpr expression is a form that needs to be passed +** to the xBestIndex method of virtual tables. Forms of interest include: +** +** Expression Virtual Table Operator +** ----------------------- --------------------------------- +** 1. column MATCH expr SQLITE_INDEX_CONSTRAINT_MATCH +** 2. column GLOB expr SQLITE_INDEX_CONSTRAINT_GLOB +** 3. column LIKE expr SQLITE_INDEX_CONSTRAINT_LIKE +** 4. column REGEXP expr SQLITE_INDEX_CONSTRAINT_REGEXP +** 5. column != expr SQLITE_INDEX_CONSTRAINT_NE +** 6. expr != column SQLITE_INDEX_CONSTRAINT_NE +** 7. column IS NOT expr SQLITE_INDEX_CONSTRAINT_ISNOT +** 8. expr IS NOT column SQLITE_INDEX_CONSTRAINT_ISNOT +** 9. column IS NOT NULL SQLITE_INDEX_CONSTRAINT_ISNOTNULL +** +** In every case, "column" must be a column of a virtual table. If there +** is a match, set *ppLeft to the "column" expression, set *ppRight to the +** "expr" expression (even though in forms (6) and (8) the column is on the +** right and the expression is on the left). Also set *peOp2 to the +** appropriate virtual table operator. The return value is 1 or 2 if there +** is a match. The usual return is 1, but if the RHS is also a column +** of virtual table in forms (5) or (7) then return 2. +** +** If the expression matches none of the patterns above, return 0. */ -static int isMatchOfColumn( +static int isAuxiliaryVtabOperator( Expr *pExpr, /* Test this expression */ - unsigned char *peOp2 /* OUT: 0 for MATCH, or else an op2 value */ + unsigned char *peOp2, /* OUT: 0 for MATCH, or else an op2 value */ + Expr **ppLeft, /* Column expression to left of MATCH/op2 */ + Expr **ppRight /* Expression to left of MATCH/op2 */ ){ - static const struct Op2 { - const char *zOp; - unsigned char eOp2; - } aOp[] = { - { "match", SQLITE_INDEX_CONSTRAINT_MATCH }, - { "glob", SQLITE_INDEX_CONSTRAINT_GLOB }, - { "like", SQLITE_INDEX_CONSTRAINT_LIKE }, - { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP } - }; - ExprList *pList; - Expr *pCol; /* Column reference */ - int i; - - if( pExpr->op!=TK_FUNCTION ){ - return 0; - } - pList = pExpr->x.pList; - if( pList==0 || pList->nExpr!=2 ){ - return 0; - } - pCol = pList->a[1].pExpr; - if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){ - return 0; - } - for(i=0; iu.zToken, aOp[i].zOp)==0 ){ - *peOp2 = aOp[i].eOp2; - return 1; - } + if( pExpr->op==TK_FUNCTION ){ + static const struct Op2 { + const char *zOp; + unsigned char eOp2; + } aOp[] = { + { "match", SQLITE_INDEX_CONSTRAINT_MATCH }, + { "glob", SQLITE_INDEX_CONSTRAINT_GLOB }, + { "like", SQLITE_INDEX_CONSTRAINT_LIKE }, + { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP } + }; + ExprList *pList; + Expr *pCol; /* Column reference */ + int i; + + pList = pExpr->x.pList; + if( pList==0 || pList->nExpr!=2 ){ + return 0; + } + pCol = pList->a[1].pExpr; + if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){ + return 0; + } + for(i=0; iu.zToken, aOp[i].zOp)==0 ){ + *peOp2 = aOp[i].eOp2; + *ppRight = pList->a[0].pExpr; + *ppLeft = pCol; + return 1; + } + } + }else if( pExpr->op==TK_NE || pExpr->op==TK_ISNOT || pExpr->op==TK_NOTNULL ){ + int res = 0; + Expr *pLeft = pExpr->pLeft; + Expr *pRight = pExpr->pRight; + if( pLeft->op==TK_COLUMN && IsVirtual(pLeft->pTab) ){ + res++; + } + if( pRight && pRight->op==TK_COLUMN && IsVirtual(pRight->pTab) ){ + res++; + SWAP(Expr*, pLeft, pRight); + } + *ppLeft = pLeft; + *ppRight = pRight; + if( pExpr->op==TK_NE ) *peOp2 = SQLITE_INDEX_CONSTRAINT_NE; + if( pExpr->op==TK_ISNOT ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOT; + if( pExpr->op==TK_NOTNULL ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOTNULL; + return res; } return 0; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ @@ -602,11 +638,11 @@ pAndWC->pOuter = pWC; if( !db->mallocFailed ){ for(j=0, pAndTerm=pAndWC->a; jnTerm; j++, pAndTerm++){ assert( pAndTerm->pExpr ); if( allowedOp(pAndTerm->pExpr->op) - || pAndTerm->eOperator==WO_MATCH + || pAndTerm->eOperator==WO_AUX ){ b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor); } } } @@ -1184,45 +1220,50 @@ } } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ #ifndef SQLITE_OMIT_VIRTUALTABLE - /* Add a WO_MATCH auxiliary term to the constraint set if the - ** current expression is of the form: column MATCH expr. + /* Add a WO_AUX auxiliary term to the constraint set if the + ** current expression is of the form "column OP expr" where OP + ** is an operator that gets passed into virtual tables but which is + ** not normally optimized for ordinary tables. In other words, OP + ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ - if( pWC->op==TK_AND && isMatchOfColumn(pExpr, &eOp2) ){ - int idxNew; + if( pWC->op==TK_AND ){ Expr *pRight, *pLeft; - WhereTerm *pNewTerm; - Bitmask prereqColumn, prereqExpr; - - pRight = pExpr->x.pList->a[0].pExpr; - pLeft = pExpr->x.pList->a[1].pExpr; - prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); - prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); - if( (prereqExpr & prereqColumn)==0 ){ - Expr *pNewExpr; - pNewExpr = sqlite3PExpr(pParse, TK_MATCH, - 0, sqlite3ExprDup(db, pRight, 0)); - if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){ - ExprSetProperty(pNewExpr, EP_FromJoin); - } - idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); - testcase( idxNew==0 ); - pNewTerm = &pWC->a[idxNew]; - pNewTerm->prereqRight = prereqExpr; - pNewTerm->leftCursor = pLeft->iTable; - pNewTerm->u.leftColumn = pLeft->iColumn; - pNewTerm->eOperator = WO_MATCH; - pNewTerm->eMatchOp = eOp2; - markTermAsChild(pWC, idxNew, idxTerm); - pTerm = &pWC->a[idxTerm]; - pTerm->wtFlags |= TERM_COPIED; - pNewTerm->prereqAll = pTerm->prereqAll; + int res = isAuxiliaryVtabOperator(pExpr, &eOp2, &pLeft, &pRight); + while( res-- > 0 ){ + int idxNew; + WhereTerm *pNewTerm; + Bitmask prereqColumn, prereqExpr; + + prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); + prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); + if( (prereqExpr & prereqColumn)==0 ){ + Expr *pNewExpr; + pNewExpr = sqlite3PExpr(pParse, TK_MATCH, + 0, sqlite3ExprDup(db, pRight, 0)); + if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){ + ExprSetProperty(pNewExpr, EP_FromJoin); + } + idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); + testcase( idxNew==0 ); + pNewTerm = &pWC->a[idxNew]; + pNewTerm->prereqRight = prereqExpr; + pNewTerm->leftCursor = pLeft->iTable; + pNewTerm->u.leftColumn = pLeft->iColumn; + pNewTerm->eOperator = WO_AUX; + pNewTerm->eMatchOp = eOp2; + markTermAsChild(pWC, idxNew, idxTerm); + pTerm = &pWC->a[idxTerm]; + pTerm->wtFlags |= TERM_COPIED; + pNewTerm->prereqAll = pTerm->prereqAll; + } + SWAP(Expr*, pLeft, pRight); } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create ADDED test/bestindex5.test Index: test/bestindex5.test ================================================================== --- /dev/null +++ test/bestindex5.test @@ -0,0 +1,250 @@ +# 2017 September 10 +# +# 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. +# +#*********************************************************************** +# Test the virtual table interface. In particular the xBestIndex +# method. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix bestindex4 + +ifcapable !vtab { + finish_test + return +} + +#------------------------------------------------------------------------- +# Virtual table callback for a virtual table named $tbl. +# +proc vtab_cmd {method args} { + + set binops(ne) != + set binops(eq) = + set binops(isnot) "IS NOT" + set binops(is) "IS" + + set unops(isnotnull) "IS NOT NULL" + set unops(isnull) "IS NULL" + + set cols(0) a + set cols(1) b + set cols(2) c + + switch -- $method { + xConnect { + return "CREATE TABLE t1(a, b, c)" + } + + xBestIndex { + foreach {clist orderby mask} $args {} + + set cost 1000000.0 + set ret [list] + set str [list] + + set v 0 + for {set i 0} {$i < [llength $clist]} {incr i} { + array unset C + array set C [lindex $clist $i] + if {$C(usable)} { + if {[info exists binops($C(op))]} { + lappend ret omit $i + lappend str "$cols($C(column)) $binops($C(op)) %$v%" + incr v + set cost [expr $cost / 2] + } + if {[info exists unops($C(op))]} { + lappend ret omit $i + lappend str "$cols($C(column)) $unops($C(op))" + incr v + set cost [expr $cost / 2] + } + } + } + + lappend ret idxstr [join $str " AND "] + lappend ret cost $cost + return $ret + } + + xFilter { + set q [lindex $args 1] + set a [lindex $args 2] + for {set v 0} {$v < [llength $a]} {incr v} { + set val [lindex $a $v] + set q [string map [list %$v% '$val'] $q] + } + if {$q==""} { set q 1 } + lappend ::xFilterQueries "WHERE $q" + return [list sql "SELECT rowid, * FROM t1x WHERE $q"] + } + } + return "" +} + +proc vtab_simple {method args} { + switch -- $method { + xConnect { + return "CREATE TABLE t2(x)" + } + xBestIndex { + return [list cost 999999.0] + } + xFilter { + return [list sql "SELECT rowid, * FROM t2x"] + } + } + return "" +} + +register_tcl_module db + +proc do_vtab_query_test {tn query result} { + set ::xFilterQueries [list] + uplevel [list + do_test $tn [string map [list %QUERY% $query] { + set r [execsql {%QUERY%}] + set r [concat $::xFilterQueries $r] + set r + }] [list {*}$result] + ] +} + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd'); + CREATE TABLE t1x(a INTEGER, b TEXT, c REAL); + INSERT INTO t1x VALUES(1, 2, 3); + INSERT INTO t1x VALUES(4, 5, 6); + INSERT INTO t1x VALUES(7, 8, 9); + + CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple'); + CREATE TABLE t2x(x INTEGER); + INSERT INTO t2x VALUES(1); +} + +do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } { + "WHERE a != 'hello'" + 1 2 3.0 4 5 6.0 7 8 9.0 +} + +do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } { + "WHERE b != '8'" + 1 2 3.0 4 5 6.0 +} +do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } { + "WHERE b != '8'" + 1 2 3.0 4 5 6.0 +} + +do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } { + "WHERE c IS NOT '3'" + 4 5 6.0 7 8 9.0 +} +do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } { + "WHERE c IS NOT '3'" + 4 5 6.0 7 8 9.0 +} + +do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } { + "WHERE a != '1'" + 4 5 6.0 1 7 8 9.0 1 +} +do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } { + "WHERE a != '1'" + 4 5 6.0 1 7 8 9.0 1 +} + +do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } { + "WHERE a IS NOT NULL" + 1 2 3.0 4 5 6.0 7 8 9.0 +} +do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } { + "WHERE a IS NOT ''" + 1 2 3.0 4 5 6.0 7 8 9.0 +} + +do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } { + "WHERE a IS NULL" +} + +do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } { + "WHERE a IS ''" +} + +do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } { + "WHERE a IS '1' AND b IS '2'" + 1 2 3.0 +} +do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } { + {WHERE b IS '5' AND a IS '4'} + 4 5 6.0 +} + +#--------------------------------------------------------------------- +do_execsql_test 2.0.0 { + DELETE FROM t1x; + INSERT INTO t1x VALUES('a', 'b', 'c'); +} +do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c} +do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {} + +do_execsql_test 2.1.0 { + DELETE FROM t1x; + INSERT INTO t1x VALUES(7, 8, 9); +} +do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0} +do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {} +do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' } +do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' } + + +do_execsql_test 2.2.1 { + CREATE TABLE t3(a INTEGER, b TEXT); + INSERT INTO t3 VALUES(45, 46); +} +do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); } +do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); } +do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46} +do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46} + +#--------------------------------------------------------------------- +# Test the != operator on a virtual table with column affinities. +# +proc vtab_simple_integer {method args} { + switch -- $method { + xConnect { + return "CREATE TABLE t4(x INTEGER)" + } + xBestIndex { + return [list cost 999999.0] + } + xFilter { + return [list sql "SELECT rowid, * FROM t4x"] + } + } + return "" +} + +do_execsql_test 3.0 { + CREATE TABLE t4x(a INTEGER); + INSERT INTO t4x VALUES(245); + CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer'); +} +do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245} +do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245} +do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {} +do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {} + +do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {} + + +finish_test +