SQLite

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7404ea83168e6c739ebe8fc5d65bbf0265432ccb35b3418bb0381d74362f7527
User & Date: drh 2018-08-09 18:36:54.837
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
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
3363
3364
3365
3366
3367
3368
3369






3370













3371
3372
3373
3374
3375
3376
3377

        return target;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      int iTab = pExpr->iTable;
      if( ExprHasProperty(pExpr, EP_FixedCol) ){






        return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);













      }
      if( iTab<0 ){
        if( pParse->iSelfTab<0 ){
          /* Generating CHECK constraints or inserting into partial index */
          return pExpr->iColumn - pParse->iSelfTab;
        }else{
          /* Coding an expression that is part of an index where column names








>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>








3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396

        return target;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      int iTab = pExpr->iTable;
      if( ExprHasProperty(pExpr, EP_FixedCol) ){
        /* This COLUMN expression is really a constant due to WHERE clause
        ** constraints, and that constant is coded by the pExpr->pLeft
        ** expresssion.  However, make sure the constant has the correct
        ** datatype by applying the Affinity of the table column to the
        ** constant.
        */
        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
        int aff = sqlite3TableColumnAffinity(pExpr->pTab, pExpr->iColumn);
        if( aff!=SQLITE_AFF_BLOB ){
          static const char zAff[] = "B\000C\000D\000E";
          assert( SQLITE_AFF_BLOB=='A' );
          assert( SQLITE_AFF_TEXT=='B' );
          if( iReg!=target ){
            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
            iReg = target;
          }
          sqlite3VdbeAddOp4(v, OP_Affinity, iReg, 1, 0,
                            &zAff[(aff-'B')*2], P4_STATIC);
        }
        return iReg;
      }
      if( iTab<0 ){
        if( pParse->iSelfTab<0 ){
          /* Generating CHECK constraints or inserting into partial index */
          return pExpr->iColumn - pParse->iSelfTab;
        }else{
          /* Coding an expression that is part of an index where column names
︙︙
Changes to test/whereL.test.
︙︙
62
63
64
65
66
67
68












































69
70


# If the constants are blindly propagated, as shown in the following
# query, the wrong answer results:
#
do_execsql_test 201 {
  SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
} {}













































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114


# If the constants are blindly propagated, as shown in the following
# query, the wrong answer results:
#
do_execsql_test 201 {
  SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
} {}

# Constant propagation caused an incorrect answer in the following
# query.  (Reported by Bentley system on 2018-08-09.)
#
do_execsql_test 300 {
  CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT);
  CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER);
  CREATE TABLE C(
    id INTEGER PRIMARY KEY,
    xx INTEGER NOT NULL,
    yy INTEGER,
    zz INTEGER
  );
  CREATE UNIQUE INDEX x2 ON C(yy);
  CREATE UNIQUE INDEX x4 ON C(yy, zz);
  INSERT INTO A(id) VALUES(1);
  INSERT INTO B(id) VALUES(2);
  INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2);
  SELECT 1
    FROM A,
         (SELECT id,xx,yy,zz FROM C) subq,
         B
   WHERE A.id='1'
     AND A.id=subq.yy
     AND B.id=subq.zz;
} {1}  
do_execsql_test 301 {
  SELECT 1
    FROM A,
         (SELECT id,xx,yy,zz FROM C) subq,
         B
   WHERE A.id=1
     AND A.id=subq.yy
     AND B.id=subq.zz;
} {1}  
do_execsql_test 302 {
  SELECT 1
    FROM A,
         (SELECT id,yy,zz FROM C) subq,
         B
   WHERE A.id='1'
     AND A.id=subq.yy
     AND B.id=subq.zz;
} {1}  

finish_test