/ Check-in [36997c4ade]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Ensure the columns of views and sub-queries maintain their implicit collation sequences when the "push-down" optimization is applied. Fix for [18458b1a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 36997c4ade2ef3a274cd1ac52b44118fb3a05325adb650b7b338ecb43d060575
User & Date: dan 2019-09-10 15:33:52
Context
2019-09-10
17:51
Enhance treeview to show SOFT-COLLATE for TK_COLLATE operators that omit the EP_Collate flag. check-in: a97804620a user: drh tags: trunk
15:33
Ensure the columns of views and sub-queries maintain their implicit collation sequences when the "push-down" optimization is applied. Fix for [18458b1a]. check-in: 36997c4ade user: dan tags: trunk
2019-09-09
20:17
Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Fix for [a7debbe0]. check-in: b9ec72203c user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/select.c.

  3414   3414   */
  3415   3415   typedef struct SubstContext {
  3416   3416     Parse *pParse;            /* The parsing context */
  3417   3417     int iTable;               /* Replace references to this table */
  3418   3418     int iNewTable;            /* New table number */
  3419   3419     int isLeftJoin;           /* Add TK_IF_NULL_ROW opcodes on each replacement */
  3420   3420     ExprList *pEList;         /* Replacement expressions */
  3421         -  int bFlattener;           /* True for query-flattener, false otherwise */
  3422   3421   } SubstContext;
  3423   3422   
  3424   3423   /* Forward Declarations */
  3425   3424   static void substExprList(SubstContext*, ExprList*);
  3426   3425   static void substSelect(SubstContext*, Select*, int);
  3427   3426   
  3428   3427   /*
................................................................................
  3476   3475           if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
  3477   3476             pNew->iRightJoinTable = pExpr->iRightJoinTable;
  3478   3477             ExprSetProperty(pNew, EP_FromJoin);
  3479   3478           }
  3480   3479           sqlite3ExprDelete(db, pExpr);
  3481   3480           pExpr = pNew;
  3482   3481   
  3483         -        /* If this call is part of query-flattening, ensure that the
  3484         -        ** new expression has an implicit collation sequence. */
  3485         -        if( pSubst->bFlattener && pExpr ){
         3482  +        /* Ensure that the expression now has an implicit collation sequence,
         3483  +        ** just as it did when it was a column of a view or sub-query. */
         3484  +        if( pExpr ){
  3486   3485             if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){
  3487   3486               CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr);
  3488   3487               pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, 
  3489   3488                   (pColl ? pColl->zName : "BINARY")
  3490   3489               );
  3491   3490             }
  3492   3491             ExprClearProperty(pExpr, EP_Collate);
................................................................................
  4052   4051       if( db->mallocFailed==0 ){
  4053   4052         SubstContext x;
  4054   4053         x.pParse = pParse;
  4055   4054         x.iTable = iParent;
  4056   4055         x.iNewTable = iNewParent;
  4057   4056         x.isLeftJoin = isLeftJoin;
  4058   4057         x.pEList = pSub->pEList;
  4059         -      x.bFlattener = 1;
  4060   4058         substSelect(&x, pParent, 0);
  4061   4059       }
  4062   4060     
  4063   4061       /* The flattened query is a compound if either the inner or the
  4064   4062       ** outer query is a compound. */
  4065   4063       pParent->selFlags |= pSub->selFlags & SF_Compound;
  4066   4064       assert( (pSub->selFlags & SF_Distinct)==0 ); /* restriction (17b) */
................................................................................
  4378   4376         pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
  4379   4377         unsetJoinExpr(pNew, -1);
  4380   4378         x.pParse = pParse;
  4381   4379         x.iTable = iCursor;
  4382   4380         x.iNewTable = iCursor;
  4383   4381         x.isLeftJoin = 0;
  4384   4382         x.pEList = pSubq->pEList;
  4385         -      x.bFlattener = 0;
  4386   4383         pNew = substExpr(&x, pNew);
  4387   4384         if( pSubq->selFlags & SF_Aggregate ){
  4388   4385           pSubq->pHaving = sqlite3ExprAnd(pParse, pSubq->pHaving, pNew);
  4389   4386         }else{
  4390   4387           pSubq->pWhere = sqlite3ExprAnd(pParse, pSubq->pWhere, pNew);
  4391   4388         }
  4392   4389         pSubq = pSubq->pPrior;

Changes to src/test1.c.

  7196   7196       { "distinct-opt",        SQLITE_DistinctOpt    },
  7197   7197       { "cover-idx-scan",      SQLITE_CoverIdxScan   },
  7198   7198       { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
  7199   7199       { "transitive",          SQLITE_Transitive     },
  7200   7200       { "omit-noop-join",      SQLITE_OmitNoopJoin   },
  7201   7201       { "stat4",               SQLITE_Stat4          },
  7202   7202       { "skip-scan",           SQLITE_SkipScan       },
         7203  +    { "push-down",           SQLITE_PushDown       },
  7203   7204     };
  7204   7205   
  7205   7206     if( objc!=4 ){
  7206   7207       Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
  7207   7208       return TCL_ERROR;
  7208   7209     }
  7209   7210     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Added test/tkt-18458b1a.test.

            1  +# 2019 September 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. In particular,
           12  +# that problems related to ticket [18458b1a] have been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix tkt-18458b1a
           18  +
           19  +foreach tn {1 2} {
           20  +  reset_db
           21  +  if {$tn==1} {
           22  +    # Disable the flattener and push-down optimizations
           23  +    optimization_control db query-flattener 0
           24  +    optimization_control db push-down 0
           25  +  } else {
           26  +    # Enable them
           27  +    optimization_control db query-flattener 1
           28  +    optimization_control db push-down 1
           29  +  }
           30  +
           31  +  db cache size 0
           32  +
           33  +  do_execsql_test $tn.1.1 {
           34  +    CREATE TABLE t0(c0 COLLATE NOCASE);
           35  +    INSERT INTO t0(c0) VALUES ('B');
           36  +    CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;
           37  +  } 
           38  +
           39  +  do_execsql_test $tn.1.2 {
           40  +    SELECT count(*) FROM v0 WHERE c1 >= c0;
           41  +  } 1
           42  +
           43  +  do_execsql_test $tn.1.3 {
           44  +    SELECT count(*) FROM v0 WHERE NOT NOT (c1 >= c0);
           45  +  } 1
           46  +
           47  +  do_execsql_test $tn.1.4 {
           48  +    SELECT count(*) FROM v0 WHERE ((c1 >= c0) OR 0+0);
           49  +  } 1
           50  +}
           51  +
           52  +finish_test
           53  +