/ Check-in [9b64af7b]
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:Back out check-in [0b3174e0b1364c] and replace it with a better fix for \ticket [91e2e8ba6ff2e2] - a fix that does not cause the problem identified by ticket [7ffd1ca1d2ad4ec]. Add new test cases for both tickets.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9b64af7b5201a8700ae9e384b04714ca18df7449
User & Date: drh 2017-01-16 18:10:17
Context
2017-01-17
00:10
Disable intrinsic functions for Windows using Clang, due to reports of linkage errors. This causes a 0.6% performance reduction. We will want to revisit this change in the future. check-in: 7fd560c6 user: drh tags: trunk
2017-01-16
18:10
Back out check-in [0b3174e0b1364c] and replace it with a better fix for \ticket [91e2e8ba6ff2e2] - a fix that does not cause the problem identified by ticket [7ffd1ca1d2ad4ec]. Add new test cases for both tickets. check-in: 9b64af7b user: drh tags: trunk
16:43
Back out check-in [0b3174e0b1364c] and replace it with a better fix for ticket [91e2e8ba6ff2e2] - a fix that does not cause the problem identified by ticket [7ffd1ca1d2ad4ec]. Closed-Leaf check-in: 06136652 user: drh tags: automatic-index-affinity
2017-01-13
18:24
Fix a problem preventing resumption of RBU operations after recovering from a process or system failure that occurs during the incremental-checkpoint phase. check-in: 97914266 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   869    869       ** But we are getting ready to store this value back into an index, where
   870    870       ** it should be converted by to INTEGER again.  So omit the OP_RealAffinity
   871    871       ** opcode if it is present */
   872    872       sqlite3VdbeDeletePriorOpcode(v, OP_RealAffinity);
   873    873     }
   874    874     if( regOut ){
   875    875       sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut);
          876  +    if( pIdx->pTable->pSelect ){
          877  +      const char *zAff = sqlite3IndexAffinityStr(pParse->db, pIdx);
          878  +      sqlite3VdbeChangeP4(v, -1, zAff, P4_TRANSIENT);
          879  +    }
   876    880     }
   877    881     sqlite3ReleaseTempRange(pParse, regBase, nCol);
   878    882     return regBase;
   879    883   }
   880    884   
   881    885   /*
   882    886   ** If a prior call to sqlite3GenerateIndexKey() generated a jump-over label

Changes to src/update.c.

    67     67       VdbeComment((v, "%s.%s", pTab->zName, pCol->zName));
    68     68       assert( i<pTab->nCol );
    69     69       sqlite3ValueFromExpr(sqlite3VdbeDb(v), pCol->pDflt, enc, 
    70     70                            pCol->affinity, &pValue);
    71     71       if( pValue ){
    72     72         sqlite3VdbeAppendP4(v, pValue, P4_MEM);
    73     73       }
           74  +  }
    74     75   #ifndef SQLITE_OMIT_FLOATING_POINT
    75         -    if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
    76         -      sqlite3VdbeAddOp1(v, OP_RealAffinity, iReg);
    77         -    }
           76  +  if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
           77  +    sqlite3VdbeAddOp1(v, OP_RealAffinity, iReg);
           78  +  }
    78     79   #endif
    79         -  }
    80     80   }
    81     81   
    82     82   /*
    83     83   ** Process an UPDATE statement.
    84     84   **
    85     85   **   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
    86     86   **          \_______/ \________/     \______/       \________________/

Added test/affinity3.test.

            1  +# 2017-01-16
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for bugs:
           13  +#
           14  +#    https://www.sqlite.org/src/info/91e2e8ba6ff2e2
           15  +#    https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +# Ticket https://www.sqlite.org/src/info/91e2e8ba6ff2e2 (2011-09-19)
           22  +# Automatic index causes undesired type conversions
           23  +#
           24  +do_execsql_test affinity3-100 {
           25  +  CREATE TABLE customer (id INT PRIMARY KEY);
           26  +  CREATE TABLE apr (id INT PRIMARY KEY, apr REAL);
           27  +  
           28  +  CREATE VIEW v1 AS
           29  +  SELECT c.id, i.apr
           30  +  FROM customer c
           31  +  LEFT JOIN apr i ON i.id=c.id;
           32  +  
           33  +  CREATE VIEW v2 AS
           34  +  SELECT c.id, v1.apr
           35  +  FROM customer c
           36  +  LEFT JOIN v1 ON v1.id=c.id;
           37  +  
           38  +  INSERT INTO customer (id) VALUES (1);
           39  +  INSERT INTO apr (id, apr) VALUES (1, 12);
           40  +  INSERT INTO customer (id) VALUES (2);
           41  +  INSERT INTO apr (id, apr) VALUES (2, 12.01);
           42  +}
           43  +do_execsql_test affinity3-110 {
           44  +  PRAGMA automatic_index=ON;
           45  +  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
           46  +} {1 0.12 real 2 0.1201 real}
           47  +do_execsql_test affinity3-120 {
           48  +  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
           49  +} {1 0.12 real 2 0.1201 real}
           50  +do_execsql_test affinity3-130 {
           51  +  PRAGMA automatic_index=OFF;
           52  +  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
           53  +} {1 0.12 real 2 0.1201 real}
           54  +do_execsql_test affinity3-140 {
           55  +  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
           56  +} {1 0.12 real 2 0.1201 real}
           57  +
           58  +# Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf  (2017-01-16)
           59  +# Incorrect affinity when using automatic indexes 
           60  +#
           61  +do_execsql_test affinity3-200 {
           62  +  CREATE TABLE map_integer (id INT, name);
           63  +  INSERT INTO map_integer VALUES(1,'a');
           64  +  CREATE TABLE map_text (id TEXT, name);
           65  +  INSERT INTO map_text VALUES('4','e');
           66  +  CREATE TABLE data (id TEXT, name);
           67  +  INSERT INTO data VALUES(1,'abc');
           68  +  INSERT INTO data VALUES('4','xyz');
           69  +  CREATE VIEW idmap as
           70  +      SELECT * FROM map_integer
           71  +      UNION SELECT * FROM map_text;
           72  +  CREATE TABLE mzed AS SELECT * FROM idmap;
           73  +}
           74  +
           75  +do_execsql_test affinity3-210 {
           76  +  PRAGMA automatic_index=ON;
           77  +  SELECT * FROM data JOIN idmap USING(id);
           78  +} {1 abc a 4 xyz e}
           79  +do_execsql_test affinity3-220 {
           80  +  SELECT * FROM data JOIN mzed USING(id);
           81  +} {1 abc a 4 xyz e}
           82  +
           83  +do_execsql_test affinity3-250 {
           84  +  PRAGMA automatic_index=OFF;
           85  +  SELECT * FROM data JOIN idmap USING(id);
           86  +} {1 abc a 4 xyz e}
           87  +do_execsql_test affinity3-260 {
           88  +  SELECT * FROM data JOIN mzed USING(id);
           89  +} {1 abc a 4 xyz e}
           90  +
           91  +finish_test