/ Check-in [e62eddbb]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix a false-positive in sqlite3ExprNeedsNoAffinityChange(). Ticket [ac184eb571d5e6e0]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e62eddbb048cbc2c15cb8eeb5b7a907e7cb08c21a27ebba96a069f62dbcb0f30
User & Date: drh 2019-08-22 11:11:28
Context
2019-08-22
16:38
Fix the likely(), unlikely(), and likelihood() functions so that they have no affinity, just like any other function. Ticket [7e07a3dbf5a8cd26] check-in: 44578865 user: drh tags: trunk
11:11
Fix a false-positive in sqlite3ExprNeedsNoAffinityChange(). Ticket [ac184eb571d5e6e0] check-in: e62eddbb user: drh tags: trunk
00:53
Fix the OP_SeekGE, OP_SeekGT, OP_SeekLE, and OP_SeekLT opcodes so that they preserve the datatype of the value in the register used as the key. Ticket [d9f584e936c7a8d0] check-in: 81b9f0f5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2155   2155   ** This routine is used to determine if the OP_Affinity operation
  2156   2156   ** can be omitted.  When in doubt return FALSE.  A false negative
  2157   2157   ** is harmless.  A false positive, however, can result in the wrong
  2158   2158   ** answer.
  2159   2159   */
  2160   2160   int sqlite3ExprNeedsNoAffinityChange(const Expr *p, char aff){
  2161   2161     u8 op;
         2162  +  int unaryMinus = 0;
  2162   2163     if( aff==SQLITE_AFF_BLOB ) return 1;
  2163         -  while( p->op==TK_UPLUS || p->op==TK_UMINUS ){ p = p->pLeft; }
         2164  +  while( p->op==TK_UPLUS || p->op==TK_UMINUS ){
         2165  +    if( p->op==TK_UMINUS ) unaryMinus = 1;
         2166  +    p = p->pLeft;
         2167  +  }
  2164   2168     op = p->op;
  2165   2169     if( op==TK_REGISTER ) op = p->op2;
  2166   2170     switch( op ){
  2167   2171       case TK_INTEGER: {
  2168   2172         return aff==SQLITE_AFF_INTEGER || aff==SQLITE_AFF_NUMERIC;
  2169   2173       }
  2170   2174       case TK_FLOAT: {
  2171   2175         return aff==SQLITE_AFF_REAL || aff==SQLITE_AFF_NUMERIC;
  2172   2176       }
  2173   2177       case TK_STRING: {
  2174         -      return aff==SQLITE_AFF_TEXT;
         2178  +      return !unaryMinus && aff==SQLITE_AFF_TEXT;
  2175   2179       }
  2176   2180       case TK_BLOB: {
  2177         -      return 1;
         2181  +      return !unaryMinus;
  2178   2182       }
  2179   2183       case TK_COLUMN: {
  2180   2184         assert( p->iTable>=0 );  /* p cannot be part of a CHECK constraint */
  2181   2185         return p->iColumn<0
  2182   2186             && (aff==SQLITE_AFF_INTEGER || aff==SQLITE_AFF_NUMERIC);
  2183   2187       }
  2184   2188       default: {

Changes to test/affinity2.test.

    80     80     INSERT INTO t3 VALUES(3, 1, 1);
    81     81     INSERT INTO t3 VALUES(4, 1, 0);
    82     82     INSERT INTO t3 VALUES(5, 1, 1);
    83     83   }
    84     84   do_execsql_test 440 {
    85     85     SELECT * FROM t3 WHERE c='0' ORDER BY a;
    86     86   } {2 1 0 4 1 0}
           87  +
           88  +# 2019-08-22 ticket https://sqlite.org/src/info/d99f1ffe836c591ac57f
           89  +# False positive in sqlite3ExprNeedsNoAffinityChange()
           90  +#
           91  +do_execsql_test 500 {
           92  +  DROP TABLE IF EXISTS t0;
           93  +  CREATE TABLE t0(c0 TEXT UNIQUE, c1);
           94  +  INSERT INTO t0(c0) VALUES (-1);
           95  +  SELECT quote(- x'ce'), quote(t0.c0), quote(- x'ce' >= t0.c0) FROM t0;
           96  +} {0 '-1' 1}
           97  +do_execsql_test 501 {
           98  +  SELECT * FROM t0 WHERE - x'ce' >= t0.c0;
           99  +} {-1 {}}
          100  +do_execsql_test 502 {
          101  +  SELECT quote(+-+x'ce'), quote(t0.c0), quote(+-+x'ce' >= t0.c0) FROM t0;
          102  +} {0 '-1' 1}
          103  +do_execsql_test 503 {
          104  +  SELECT * FROM t0 WHERE +-+x'ce' >= t0.c0;
          105  +} {-1 {}}
          106  +do_execsql_test 504 {
          107  +  SELECT quote(- 'ce'), quote(t0.c0), quote(- 'ce' >= t0.c0) FROM t0;
          108  +} {0 '-1' 1}
          109  +do_execsql_test 505 {
          110  +  SELECT * FROM t0 WHERE - 'ce' >= t0.c0;
          111  +} {-1 {}}
          112  +do_execsql_test 506 {
          113  +  SELECT quote(+-+'ce'), quote(t0.c0), quote(+-+'ce' >= t0.c0) FROM t0;
          114  +} {0 '-1' 1}
          115  +do_execsql_test 507 {
          116  +  SELECT * FROM t0 WHERE +-+'ce' >= t0.c0;
          117  +} {-1 {}}
          118  + 
    87    119   
    88    120   finish_test