/ Check-in [0658c16e]
Login

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

Overview
Comment:When computing an expression value for an index-on-expression or a CHECK constraint and the expressions uses a REAL table column, but the value of that column is an integer (in other words, when it is using the store-real-as-integer optimization) be sure to promote the value to real before evaluating the expression. Ticket [57af00b6642ecd68].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0658c16e311393c8a347b1bd41fa5dbfd2e184aa75d84c011aa8dbac79b632e9
User & Date: drh 2019-09-02 00:58:44
Context
2019-09-02
01:25
Fix an obsolete comment that defines the meaning of one of the parameters to the sqlite3FindInIndex() subroutine. No changes to code. check-in: 0c946f08 user: drh tags: trunk
00:58
When computing an expression value for an index-on-expression or a CHECK constraint and the expressions uses a REAL table column, but the value of that column is an integer (in other words, when it is using the store-real-as-integer optimization) be sure to promote the value to real before evaluating the expression. Ticket [57af00b6642ecd68]. check-in: 0658c16e user: drh tags: trunk
2019-09-01
23:36
Remove an obsolete paragraph from the OP_Column documentation. No code changes. check-in: f6d8956c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/expr.c.

3528
3529
3530
3531
3532
3533
3534











3535

3536
3537
3538
3539
3540
3541
3542
                            &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
          ** in the index refer to the table to which the index belongs */
          iTab = pParse->iSelfTab - 1;
        }
      }
      return sqlite3ExprCodeGetColumn(pParse, pExpr->y.pTab,







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







3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
                            &zAff[(aff-'B')*2], P4_STATIC);
        }
        return iReg;
      }
      if( iTab<0 ){
        if( pParse->iSelfTab<0 ){
          /* Generating CHECK constraints or inserting into partial index */
          assert( pExpr->y.pTab!=0 );
          assert( pExpr->iColumn>=XN_ROWID );
          assert( pExpr->iColumn<pExpr->y.pTab->nCol );
          if( pExpr->iColumn>=0
            && pExpr->y.pTab->aCol[pExpr->iColumn].affinity==SQLITE_AFF_REAL
          ){
            sqlite3VdbeAddOp2(v, OP_SCopy, pExpr->iColumn - pParse->iSelfTab,
                              target);
            sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
            return target;
          }else{
            return pExpr->iColumn - pParse->iSelfTab;
          }
        }else{
          /* Coding an expression that is part of an index where column names
          ** in the index refer to the table to which the index belongs */
          iTab = pParse->iSelfTab - 1;
        }
      }
      return sqlite3ExprCodeGetColumn(pParse, pExpr->y.pTab,

Changes to test/check.test.

119
120
121
122
123
124
125





126
127
128
129
130
131
132
...
142
143
144
145
146
147
148

149
150
151







152
153
154
155
156
157
158
...
191
192
193
194
195
196
197

198
199
200
201
202
203
204
  execsql {
    PRAGMA writable_schema = 1;
    CREATE TABLE t2(
      x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
      z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
    );





    PRAGMA writable_schema = 0;
  }
} {}
do_test check-2.2 {
  execsql {
    INSERT INTO t2 VALUES(1,2.2,'three');
    SELECT * FROM t2;
................................................................................
} {1 2.2 three {} {} {}}
do_test check-2.4 {
  catchsql {
    INSERT INTO t2 VALUES(1.1, NULL, NULL);
  }
} {1 {CHECK constraint failed: one}}
do_test check-2.5 {

  catchsql {
    INSERT INTO t2 VALUES(NULL, 5, NULL);
  }







} {1 {CHECK constraint failed: two}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {CHECK constraint failed: three}}

................................................................................
    INSERT INTO t2c VALUES('xyzzy',7,8);
  }
} {1 {CHECK constraint failed: x_two}}
do_test check-2.cleanup {
  execsql {
    DROP TABLE IF EXISTS t2b;
    DROP TABLE IF EXISTS t2c;

  }
} {}

ifcapable subquery {
  do_test check-3.1 {
    catchsql {
      CREATE TABLE t3(







>
>
>
>
>







 







>



>
>
>
>
>
>
>







 







>







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
  execsql {
    PRAGMA writable_schema = 1;
    CREATE TABLE t2(
      x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
      z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
    );
    CREATE TABLE t2n(
      x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
      y NUMERIC CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
      z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
    );
    PRAGMA writable_schema = 0;
  }
} {}
do_test check-2.2 {
  execsql {
    INSERT INTO t2 VALUES(1,2.2,'three');
    SELECT * FROM t2;
................................................................................
} {1 2.2 three {} {} {}}
do_test check-2.4 {
  catchsql {
    INSERT INTO t2 VALUES(1.1, NULL, NULL);
  }
} {1 {CHECK constraint failed: one}}
do_test check-2.5 {
  # The 5 gets automatically promoted to 5.0 because the column type is REAL
  catchsql {
    INSERT INTO t2 VALUES(NULL, 5, NULL);
  }
} {0 {}}
do_test check-2.5b {
  # This time the column type is NUMERIC, so not automatic promption to REAL
  # occurs and the constraint fails.
  catchsql {
    INSERT INTO t2n VALUES(NULL, 5, NULL);
  }
} {1 {CHECK constraint failed: two}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {CHECK constraint failed: three}}

................................................................................
    INSERT INTO t2c VALUES('xyzzy',7,8);
  }
} {1 {CHECK constraint failed: x_two}}
do_test check-2.cleanup {
  execsql {
    DROP TABLE IF EXISTS t2b;
    DROP TABLE IF EXISTS t2c;
    DROP TABLE IF EXISTS t2n;
  }
} {}

ifcapable subquery {
  do_test check-3.1 {
    catchsql {
      CREATE TABLE t3(

Changes to test/indexexpr1.test.

452
453
454
455
456
457
458
459
























460
do_execsql_test indexexpr-1700 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0);
  INSERT INTO t0(c0) VALUES (0);
  CREATE INDEX i0 ON t0(NULL > c0) WHERE (NULL NOT NULL);
  SELECT * FROM t0 WHERE ((NULL IS FALSE) IS FALSE);
} {0}

























finish_test








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

452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
do_execsql_test indexexpr-1700 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0);
  INSERT INTO t0(c0) VALUES (0);
  CREATE INDEX i0 ON t0(NULL > c0) WHERE (NULL NOT NULL);
  SELECT * FROM t0 WHERE ((NULL IS FALSE) IS FALSE);
} {0}

# 2019-09-02 https://www.sqlite.org/src/tktview/57af00b6642ecd6848
# When the expression of an an index-on-expression references a
# table column of type REAL that is actually holding an MEM_IntReal
# value, be sure to use the REAL value and not the INT value when
# computing the expression.
#
do_execsql_test indexexpr-1800 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 REAL, c1 TEXT);
  CREATE INDEX i0 ON t0(+c0, c0);
  INSERT INTO t0(c0) VALUES(0);
  SELECT CAST(+ t0.c0 AS BLOB) LIKE 0 FROM t0; 
} {0}
do_execsql_test indexexpr-1810 {
  SELECT CAST(+ t0.c0 AS BLOB) LIKE '0.0' FROM t0; 
} {1}
do_execsql_test indexexpr-1820 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x REAL);
  CREATE INDEX t1x ON t1(x, +x);
  INSERT INTO t1(x) VALUES(2);
  SELECT +x FROM t1 WHERE x=2;
} {2.0}

finish_test