/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

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

Changes to test/check.test.

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

Changes to test/indexexpr1.test.

   452    452   do_execsql_test indexexpr-1700 {
   453    453     DROP TABLE IF EXISTS t0;
   454    454     CREATE TABLE t0(c0);
   455    455     INSERT INTO t0(c0) VALUES (0);
   456    456     CREATE INDEX i0 ON t0(NULL > c0) WHERE (NULL NOT NULL);
   457    457     SELECT * FROM t0 WHERE ((NULL IS FALSE) IS FALSE);
   458    458   } {0}
          459  +
          460  +# 2019-09-02 https://www.sqlite.org/src/tktview/57af00b6642ecd6848
          461  +# When the expression of an an index-on-expression references a
          462  +# table column of type REAL that is actually holding an MEM_IntReal
          463  +# value, be sure to use the REAL value and not the INT value when
          464  +# computing the expression.
          465  +#
          466  +do_execsql_test indexexpr-1800 {
          467  +  DROP TABLE IF EXISTS t0;
          468  +  CREATE TABLE t0(c0 REAL, c1 TEXT);
          469  +  CREATE INDEX i0 ON t0(+c0, c0);
          470  +  INSERT INTO t0(c0) VALUES(0);
          471  +  SELECT CAST(+ t0.c0 AS BLOB) LIKE 0 FROM t0; 
          472  +} {0}
          473  +do_execsql_test indexexpr-1810 {
          474  +  SELECT CAST(+ t0.c0 AS BLOB) LIKE '0.0' FROM t0; 
          475  +} {1}
          476  +do_execsql_test indexexpr-1820 {
          477  +  DROP TABLE IF EXISTS t1;
          478  +  CREATE TABLE t1(x REAL);
          479  +  CREATE INDEX t1x ON t1(x, +x);
          480  +  INSERT INTO t1(x) VALUES(2);
          481  +  SELECT +x FROM t1 WHERE x=2;
          482  +} {2.0}
   459    483   
   460    484   finish_test