/ Check-in [7404ea8316]
Login

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

Overview
Comment:When a column must be a constant due to WHERE clause and the value of that column is being coded as a constant, make sure the affinity is correct.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7404ea83168e6c739ebe8fc5d65bbf0265432ccb35b3418bb0381d74362f7527
User & Date: drh 2018-08-09 18:36:54
Context
2018-08-09
21:45
Fix the isLikeOrGlob() routine in the WHERE clause processing logic so that it avoids signed/unsigned character comparisons, as that can lead to an incorrect answer if the ESCAPE clause is an invalid UTF8 string. Problem found by OSSFuzz. check-in: 4195a3f8b5 user: drh tags: trunk
20:47
Experimental implementation of ALTER TABLE ... RENAME COLUMN. Still buggy. check-in: fa0fc01eb4 user: dan tags: alter-table-rename-column
18:36
When a column must be a constant due to WHERE clause and the value of that column is being coded as a constant, make sure the affinity is correct. check-in: 7404ea8316 user: drh tags: trunk
2018-08-08
20:46
Minor style improvements. check-in: 60bbca2b9a user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

  3363   3363           return target;
  3364   3364         }
  3365   3365         /* Otherwise, fall thru into the TK_COLUMN case */
  3366   3366       }
  3367   3367       case TK_COLUMN: {
  3368   3368         int iTab = pExpr->iTable;
  3369   3369         if( ExprHasProperty(pExpr, EP_FixedCol) ){
  3370         -        return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
         3370  +        /* This COLUMN expression is really a constant due to WHERE clause
         3371  +        ** constraints, and that constant is coded by the pExpr->pLeft
         3372  +        ** expresssion.  However, make sure the constant has the correct
         3373  +        ** datatype by applying the Affinity of the table column to the
         3374  +        ** constant.
         3375  +        */
         3376  +        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
         3377  +        int aff = sqlite3TableColumnAffinity(pExpr->pTab, pExpr->iColumn);
         3378  +        if( aff!=SQLITE_AFF_BLOB ){
         3379  +          static const char zAff[] = "B\000C\000D\000E";
         3380  +          assert( SQLITE_AFF_BLOB=='A' );
         3381  +          assert( SQLITE_AFF_TEXT=='B' );
         3382  +          if( iReg!=target ){
         3383  +            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
         3384  +            iReg = target;
         3385  +          }
         3386  +          sqlite3VdbeAddOp4(v, OP_Affinity, iReg, 1, 0,
         3387  +                            &zAff[(aff-'B')*2], P4_STATIC);
         3388  +        }
         3389  +        return iReg;
  3371   3390         }
  3372   3391         if( iTab<0 ){
  3373   3392           if( pParse->iSelfTab<0 ){
  3374   3393             /* Generating CHECK constraints or inserting into partial index */
  3375   3394             return pExpr->iColumn - pParse->iSelfTab;
  3376   3395           }else{
  3377   3396             /* Coding an expression that is part of an index where column names

Changes to test/whereL.test.

    62     62   
    63     63   # If the constants are blindly propagated, as shown in the following
    64     64   # query, the wrong answer results:
    65     65   #
    66     66   do_execsql_test 201 {
    67     67     SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
    68     68   } {}
           69  +
           70  +# Constant propagation caused an incorrect answer in the following
           71  +# query.  (Reported by Bentley system on 2018-08-09.)
           72  +#
           73  +do_execsql_test 300 {
           74  +  CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT);
           75  +  CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER);
           76  +  CREATE TABLE C(
           77  +    id INTEGER PRIMARY KEY,
           78  +    xx INTEGER NOT NULL,
           79  +    yy INTEGER,
           80  +    zz INTEGER
           81  +  );
           82  +  CREATE UNIQUE INDEX x2 ON C(yy);
           83  +  CREATE UNIQUE INDEX x4 ON C(yy, zz);
           84  +  INSERT INTO A(id) VALUES(1);
           85  +  INSERT INTO B(id) VALUES(2);
           86  +  INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2);
           87  +  SELECT 1
           88  +    FROM A,
           89  +         (SELECT id,xx,yy,zz FROM C) subq,
           90  +         B
           91  +   WHERE A.id='1'
           92  +     AND A.id=subq.yy
           93  +     AND B.id=subq.zz;
           94  +} {1}  
           95  +do_execsql_test 301 {
           96  +  SELECT 1
           97  +    FROM A,
           98  +         (SELECT id,xx,yy,zz FROM C) subq,
           99  +         B
          100  +   WHERE A.id=1
          101  +     AND A.id=subq.yy
          102  +     AND B.id=subq.zz;
          103  +} {1}  
          104  +do_execsql_test 302 {
          105  +  SELECT 1
          106  +    FROM A,
          107  +         (SELECT id,yy,zz FROM C) subq,
          108  +         B
          109  +   WHERE A.id='1'
          110  +     AND A.id=subq.yy
          111  +     AND B.id=subq.zz;
          112  +} {1}  
    69    113   
    70    114   finish_test