Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3416,11 +3416,10 @@ Parse *pParse; /* The parsing context */ int iTable; /* Replace references to this table */ int iNewTable; /* New table number */ int isLeftJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */ ExprList *pEList; /* Replacement expressions */ - int bFlattener; /* True for query-flattener, false otherwise */ } SubstContext; /* Forward Declarations */ static void substExprList(SubstContext*, ExprList*); static void substSelect(SubstContext*, Select*, int); @@ -3478,13 +3477,13 @@ ExprSetProperty(pNew, EP_FromJoin); } sqlite3ExprDelete(db, pExpr); pExpr = pNew; - /* If this call is part of query-flattening, ensure that the - ** new expression has an implicit collation sequence. */ - if( pSubst->bFlattener && pExpr ){ + /* Ensure that the expression now has an implicit collation sequence, + ** just as it did when it was a column of a view or sub-query. */ + if( pExpr ){ if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){ CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr); pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, (pColl ? pColl->zName : "BINARY") ); @@ -4054,11 +4053,10 @@ x.pParse = pParse; x.iTable = iParent; x.iNewTable = iNewParent; x.isLeftJoin = isLeftJoin; x.pEList = pSub->pEList; - x.bFlattener = 1; substSelect(&x, pParent, 0); } /* The flattened query is a compound if either the inner or the ** outer query is a compound. */ @@ -4380,11 +4378,10 @@ x.pParse = pParse; x.iTable = iCursor; x.iNewTable = iCursor; x.isLeftJoin = 0; x.pEList = pSubq->pEList; - x.bFlattener = 0; pNew = substExpr(&x, pNew); if( pSubq->selFlags & SF_Aggregate ){ pSubq->pHaving = sqlite3ExprAnd(pParse, pSubq->pHaving, pNew); }else{ pSubq->pWhere = sqlite3ExprAnd(pParse, pSubq->pWhere, pNew); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -7198,10 +7198,11 @@ { "order-by-idx-join", SQLITE_OrderByIdxJoin }, { "transitive", SQLITE_Transitive }, { "omit-noop-join", SQLITE_OmitNoopJoin }, { "stat4", SQLITE_Stat4 }, { "skip-scan", SQLITE_SkipScan }, + { "push-down", SQLITE_PushDown }, }; if( objc!=4 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); return TCL_ERROR; ADDED test/tkt-18458b1a.test Index: test/tkt-18458b1a.test ================================================================== --- /dev/null +++ test/tkt-18458b1a.test @@ -0,0 +1,53 @@ +# 2019 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. In particular, +# that problems related to ticket [18458b1a] have been fixed. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix tkt-18458b1a + +foreach tn {1 2} { + reset_db + if {$tn==1} { + # Disable the flattener and push-down optimizations + optimization_control db query-flattener 0 + optimization_control db push-down 0 + } else { + # Enable them + optimization_control db query-flattener 1 + optimization_control db push-down 1 + } + + db cache size 0 + + do_execsql_test $tn.1.1 { + CREATE TABLE t0(c0 COLLATE NOCASE); + INSERT INTO t0(c0) VALUES ('B'); + CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0; + } + + do_execsql_test $tn.1.2 { + SELECT count(*) FROM v0 WHERE c1 >= c0; + } 1 + + do_execsql_test $tn.1.3 { + SELECT count(*) FROM v0 WHERE NOT NOT (c1 >= c0); + } 1 + + do_execsql_test $tn.1.4 { + SELECT count(*) FROM v0 WHERE ((c1 >= c0) OR 0+0); + } 1 +} + +finish_test +