/ Check-in [1863b7bf12]
Login

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

Overview
Comment: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. Possible fix for [a7debbe0].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-a7debbe0.
Files: files | file ages | folders
SHA3-256: 1863b7bf12521bdd2b51c5b8d3a4634bff3e15d3713e0b5343952df7da02f794
User & Date: dan 2019-09-09 19:49:42
Context
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
19:49
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. Possible fix for [a7debbe0]. Closed-Leaf check-in: 1863b7bf12 user: dan tags: tkt-a7debbe0.
2019-09-04
06:56
Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. check-in: cb3e2be674 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace 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 */
  3421   3422   } SubstContext;
  3422   3423   
  3423   3424   /* Forward Declarations */
  3424   3425   static void substExprList(SubstContext*, ExprList*);
  3425   3426   static void substSelect(SubstContext*, Select*, int);
  3426   3427   
  3427   3428   /*
................................................................................
  3474   3475           }
  3475   3476           if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
  3476   3477             pNew->iRightJoinTable = pExpr->iRightJoinTable;
  3477   3478             ExprSetProperty(pNew, EP_FromJoin);
  3478   3479           }
  3479   3480           sqlite3ExprDelete(db, pExpr);
  3480   3481           pExpr = pNew;
         3482  +
         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 ){
         3486  +          if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){
         3487  +            CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr);
         3488  +            pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, 
         3489  +                (pColl ? pColl->zName : "BINARY")
         3490  +            );
         3491  +          }
         3492  +          ExprClearProperty(pExpr, EP_Collate);
         3493  +        }
  3481   3494         }
  3482   3495       }
  3483   3496     }else{
  3484   3497       if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){
  3485   3498         pExpr->iTable = pSubst->iNewTable;
  3486   3499       }
  3487   3500       pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
................................................................................
  4039   4052       if( db->mallocFailed==0 ){
  4040   4053         SubstContext x;
  4041   4054         x.pParse = pParse;
  4042   4055         x.iTable = iParent;
  4043   4056         x.iNewTable = iNewParent;
  4044   4057         x.isLeftJoin = isLeftJoin;
  4045   4058         x.pEList = pSub->pEList;
         4059  +      x.bFlattener = 1;
  4046   4060         substSelect(&x, pParent, 0);
  4047   4061       }
  4048   4062     
  4049   4063       /* The flattened query is a compound if either the inner or the
  4050   4064       ** outer query is a compound. */
  4051   4065       pParent->selFlags |= pSub->selFlags & SF_Compound;
  4052   4066       assert( (pSub->selFlags & SF_Distinct)==0 ); /* restriction (17b) */
................................................................................
  4364   4378         pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
  4365   4379         unsetJoinExpr(pNew, -1);
  4366   4380         x.pParse = pParse;
  4367   4381         x.iTable = iCursor;
  4368   4382         x.iNewTable = iCursor;
  4369   4383         x.isLeftJoin = 0;
  4370   4384         x.pEList = pSubq->pEList;
         4385  +      x.bFlattener = 0;
  4371   4386         pNew = substExpr(&x, pNew);
  4372   4387         if( pSubq->selFlags & SF_Aggregate ){
  4373   4388           pSubq->pHaving = sqlite3ExprAnd(pParse, pSubq->pHaving, pNew);
  4374   4389         }else{
  4375   4390           pSubq->pWhere = sqlite3ExprAnd(pParse, pSubq->pWhere, pNew);
  4376   4391         }
  4377   4392         pSubq = pSubq->pPrior;

Added test/tkt-a7debbe0.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 a7debbe0ad1 have been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix tkt-a7debbe0
           18  +
           19  +foreach tn {1 2} {
           20  +  reset_db
           21  +  if {$tn==1} {
           22  +    # Disable the flattener
           23  +    optimization_control db query-flattener 0
           24  +  } else {
           25  +    # Enable the flattener
           26  +    optimization_control db query-flattener 1
           27  +  }
           28  +
           29  +  do_execsql_test $tn.1.0 {
           30  +    CREATE TABLE t0(xyz INTEGER);
           31  +    INSERT INTO t0(xyz) VALUES(456);
           32  +    CREATE VIEW v2(a, B) AS 
           33  +        SELECT 'a', 'B' COLLATE NOCASE FROM t0;
           34  +    CREATE TABLE t2(a, B COLLATE NOCASE);
           35  +    INSERT INTO t2 VALUES('a', 'B');
           36  +    CREATE VIEW v3(a, B) AS
           37  +        SELECT 'a' COLLATE BINARY, 'B' COLLATE NOCASE FROM t0;
           38  +
           39  +    CREATE VIEW v4(a, B) AS
           40  +        SELECT 'a', +CAST('B' COLLATE NOCASE AS TEXT) FROM t0;
           41  +
           42  +    CREATE VIEW v5(a, B) AS
           43  +        SELECT 'a', ('B' COLLATE NOCASE) || '' FROM t0;
           44  +  }
           45  +
           46  +  # Table t2 and views v2 through v5 should all be equivalent.
           47  +  do_execsql_test $tn.1.1.1 { SELECT a   >= B FROM t2;         } 1
           48  +  do_execsql_test $tn.1.1.2 { SELECT 'a' >= 'B' COLLATE NOCASE } 0
           49  +  do_execsql_test $tn.1.1.3 { SELECT a   >= B FROM v2          } 1
           50  +  do_execsql_test $tn.1.1.4 { SELECT a   >= B FROM v3          } 1
           51  +  do_execsql_test $tn.1.1.5 { SELECT a   >= B FROM v4          } 1
           52  +  do_execsql_test $tn.1.1.6 { SELECT a   >= B FROM v5          } 1
           53  +
           54  +  do_execsql_test $tn.1.2.1 { SELECT B   < a FROM t2           } 0
           55  +  do_execsql_test $tn.1.2.2 { SELECT 'B' COLLATE NOCASE < 'a'  } 0
           56  +  do_execsql_test $tn.1.2.3 { SELECT B   < a FROM v2           } 0
           57  +  do_execsql_test $tn.1.2.4 { SELECT B   < a FROM v3           } 0
           58  +  do_execsql_test $tn.1.2.5 { SELECT a  < B FROM v4           } 0
           59  +  do_execsql_test $tn.1.2.6 { SELECT a  < B FROM v5           } 0
           60  +
           61  +  #-------------------------------------------------------------------------
           62  +  do_execsql_test 2.0 {
           63  +    CREATE TABLE t5(a, b COLLATE NOCASE);
           64  +    INSERT INTO t5 VALUES(1, 'XYZ');
           65  +  }
           66  +
           67  +  # Result should be 0, as column "xyz" from the sub-query has implicit
           68  +  # collation sequence BINARY.
           69  +  do_execsql_test 2.1 {
           70  +    SELECT xyz==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
           71  +  } {0}
           72  +
           73  +  # Result should be 1, as literal 'xyz' has no collation sequence, so
           74  +  # the comparison uses the implicit collation sequence of the RHS - NOCASE.
           75  +  do_execsql_test 2.2 {
           76  +    SELECT 'xyz'==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5;
           77  +  } {1}
           78  +
           79  +  #-----------------------------------------------------------------------
           80  +  # The test case submitted with the ticket.
           81  +  #
           82  +  do_execsql_test 3.0 {
           83  +    DROP TABLE t0;
           84  +    DROP VIEW v2;
           85  +
           86  +    CREATE TABLE t0(c0);
           87  +    INSERT INTO t0(c0) VALUES('');
           88  +    CREATE VIEW v2(c0, c1) AS 
           89  +        SELECT 'B' COLLATE NOCASE, 'a' FROM t0 ORDER BY t0.c0;
           90  +    SELECT SUM(count) FROM (
           91  +      SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2
           92  +    );
           93  +  } 1
           94  +
           95  +  # The result is 1, as the collation used is the implicit collation sequence
           96  +  # of v2.c1 - BINARY.
           97  +  do_execsql_test 3.1 {
           98  +    SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2;
           99  +  } 1
          100  +}
          101  +
          102  +finish_test
          103  +