SQLite

Check-in [e0d909c740]
Login

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

Overview
Comment:When the affinity of a table column is INT or REAL, make the affinity of corresponding index columns NUMERIC. This increases the precision of index lookups for large numbers so that it matches the precision of ordinary comparison operators. Ticket [40812aea1fde9594]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e0d909c740b774d8a46731696e33342be83206cc4a95d07f42fdb3d8cc2d7a8e
User & Date: drh 2019-08-30 23:56:34.003
Context
2019-08-31
01:33
Remove some affinity tests that became unreachable due to the prior change. (check-in: e2db1123fa user: drh tags: trunk)
2019-08-30
23:56
When the affinity of a table column is INT or REAL, make the affinity of corresponding index columns NUMERIC. This increases the precision of index lookups for large numbers so that it matches the precision of ordinary comparison operators. Ticket [40812aea1fde9594] (check-in: e0d909c740 user: drh tags: trunk)
23:15
Make sure OP_RealAffinity has been applied to all columns of type REAL in the excluded.* pseudo-table of an UPSERT. Ticket [5a3dba8104421320] (check-in: 67381daded user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
︙︙
95
96
97
98
99
100
101

102
103
104
105
106
107
108

        aff = SQLITE_AFF_INTEGER;
      }else{
        assert( x==XN_EXPR );
        assert( pIdx->aColExpr!=0 );
        aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
      }
      if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;

      pIdx->zColAff[n] = aff;
    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}








>








95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

        aff = SQLITE_AFF_INTEGER;
      }else{
        assert( x==XN_EXPR );
        assert( pIdx->aColExpr!=0 );
        aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
      }
      if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;
      if( aff>SQLITE_AFF_NUMERIC) aff = SQLITE_AFF_NUMERIC;
      pIdx->zColAff[n] = aff;
    }
    pIdx->zColAff[n] = 0;
  }
 
  return pIdx->zColAff;
}
︙︙
Changes to src/wherecode.c.
︙︙
751
752
753
754
755
756
757

758
759
760
761
762
763
764

      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
          zAff[j] = SQLITE_AFF_BLOB;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
          zAff[j] = SQLITE_AFF_BLOB;
        }

      }
    }
  }
  *pzAff = zAff;
  return regBase;
}









>








751
752
753
754
755
756
757
758
759
760
761
762
763
764
765

      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
          zAff[j] = SQLITE_AFF_BLOB;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
          zAff[j] = SQLITE_AFF_BLOB;
        }
        if( zAff[j]==SQLITE_AFF_REAL ) zAff[j] = SQLITE_AFF_NUMERIC;
      }
    }
  }
  *pzAff = zAff;
  return regBase;
}
︙︙
Changes to test/affinity2.test.
︙︙
112
113
114
115
116
117
118











119
120

do_execsql_test 506 {
  SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 507 {
  SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;
} {-1 {}}
 












finish_test








>
>
>
>
>
>
>
>
>
>
>



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

do_execsql_test 506 {
  SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;
} {0 '-1' 1}
do_execsql_test 507 {
  SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;
} {-1 {}}
 
# 2019-08-30 ticket https://www.sqlite.org/src/info/40812aea1fde9594
#
do_execsql_test 600 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 REAL UNIQUE);
  INSERT INTO t0(c0) VALUES (3175546974276630385);
  SELECT 3175546974276630385 < c0 FROM t0;
} {1}
do_execsql_test 601 {
  SELECT 1 FROM t0 WHERE 3175546974276630385 < c0;
} {1}

finish_test