/ Check-in [88e2ce91]
Login

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

Overview
Comment:When an index is based on a text representation of a numeric column in the original table, make sure the indexed value uses the canonical text representation of the numeric value in the table. Proposed fix for ticket [343634942dd54ab57b70].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 88e2ce916791d488076584f3795a89eb4277fcb812af9e4c2f383815d55ff6f1
User & Date: drh 2018-02-01 01:13:33
Context
2018-02-01
15:19
Fix a memory leak that could follow an IO error in the zipfile extension. And add other tests. check-in: e6bb7506 user: dan tags: trunk
01:13
When an index is based on a text representation of a numeric column in the original table, make sure the indexed value uses the canonical text representation of the numeric value in the table. Proposed fix for ticket [343634942dd54ab57b70]. check-in: 88e2ce91 user: drh tags: trunk
2018-01-31
20:18
When creating a new archive entry, have zipfile store UTC instead of local time in the legacy MS-DOS format timestamp field. check-in: b730d187 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

   260    260       pRec->u.i = iValue;
   261    261       pRec->flags |= MEM_Int;
   262    262     }else{
   263    263       pRec->u.r = rValue;
   264    264       pRec->flags |= MEM_Real;
   265    265       if( bTryForInt ) sqlite3VdbeIntegerAffinity(pRec);
   266    266     }
          267  +  /* TEXT->NUMERIC is many->one.  Hence, it is important to invalidate the
          268  +  ** string representation after computing a numeric equivalent, because the
          269  +  ** string representation might not be the canonical representation for the
          270  +  ** numeric value.  Ticket [343634942dd54ab57b7024] 2018-01-31. */
          271  +  pRec->flags &= ~MEM_Str;
   267    272   }
   268    273   
   269    274   /*
   270    275   ** Processing is determine by the affinity parameter:
   271    276   **
   272    277   ** SQLITE_AFF_INTEGER:
   273    278   ** SQLITE_AFF_REAL:

Changes to test/indexexpr1.test.

   420    420     DROP TABLE IF EXISTS t1;
   421    421     CREATE TABLE t1(a PRIMARY KEY,b UNIQUE);
   422    422     REPLACE INTO t1 VALUES(2, 1);
   423    423     REPLACE INTO t1 SELECT 6,1;
   424    424     CREATE INDEX t1aa ON t1(a-a);
   425    425     REPLACE INTO t1 SELECT a, randomblob(a) FROM t1
   426    426   } {}
          427  +
          428  +# 2018-01-31 https://www.sqlite.org/src/tktview/343634942dd54ab57b702411
          429  +# When an index on an expression depends on the string representation of
          430  +# a numeric table column, trouble can arise since there are multiple
          431  +# string that can map to the same numeric value.  (Ex: 123, 0123, 000123).
          432  +#
          433  +do_execsql_test indexexpr-1600 {
          434  +  DROP TABLE IF EXISTS t1;
          435  +  CREATE TABLE t1 (a INTEGER, b);
          436  +  CREATE INDEX idx1 ON t1 (lower(a));
          437  +  INSERT INTO t1 VALUES('0001234',3);
          438  +  PRAGMA integrity_check;
          439  +} {ok}
          440  +do_execsql_test indexexpr-1610 {
          441  +  INSERT INTO t1 VALUES('1234',0),('001234',2),('01234',1);
          442  +  SELECT b FROM t1 WHERE lower(a)='1234' ORDER BY +b;
          443  +} {0 1 2 3}
          444  +do_execsql_test indexexpr-1620 {
          445  +  SELECT b FROM t1 WHERE lower(a)='01234' ORDER BY +b;
          446  +} {}
          447  +
   427    448   
   428    449   finish_test