/ Check-in [a2e55a38]
Login

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

Overview
Comment:Fix incorrect type conversions when copying from a table into itself. Ticket #1536. (CVS 2773)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a2e55a3828d74d78b0b76f956a537a0d906f3191
User & Date: drh 2005-11-24 13:15:33
Context
2005-11-24
14:24
Fix a comment in btree.c. No code changes. (CVS 2774) check-in: 0d8bd133 user: drh tags: trunk
13:15
Fix incorrect type conversions when copying from a table into itself. Ticket #1536. (CVS 2773) check-in: a2e55a38 user: drh tags: trunk
2005-11-21
12:48
Less verbose output when EXPLAIN QUERY PLAN identifies use of the primary key. (CVS 2772) check-in: 5045f099 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.148 2005/11/14 22:29:05 drh Exp $
           15  +** $Id: insert.c,v 1.149 2005/11/24 13:15:33 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
    23     23   **
    24     24   **  Character      Column affinity
    25     25   **  ------------------------------
    26         -**  'n'            NUMERIC
    27         -**  't'            TEXT
    28         -**  'o'            NONE
           26  +**  'a'            TEXT
           27  +**  'b'            NONE
           28  +**  'c'            NUMERIC
           29  +**  'd'            INTEGER
           30  +**  'e'            REAL
    29     31   */
    30     32   void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
    31     33     if( !pIdx->zColAff ){
    32     34       /* The first time a column affinity string for a particular index is
    33     35       ** required, it is allocated and populated here. It is then stored as
    34     36       ** a member of the Index structure for subsequent use.
    35     37       **
................................................................................
    56     58   ** Set P3 of the most recently inserted opcode to a column affinity
    57     59   ** string for table pTab. A column affinity string has one character
    58     60   ** for each column indexed by the index, according to the affinity of the
    59     61   ** column:
    60     62   **
    61     63   **  Character      Column affinity
    62     64   **  ------------------------------
    63         -**  'n'            NUMERIC
    64         -**  't'            TEXT
    65         -**  'o'            NONE
           65  +**  'a'            TEXT
           66  +**  'b'            NONE
           67  +**  'c'            NUMERIC
           68  +**  'd'            INTEGER
           69  +**  'e'            REAL
    66     70   */
    67     71   void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){
    68     72     /* The first time a column affinity string for a particular table
    69     73     ** is required, it is allocated and populated here. It is then 
    70     74     ** stored as a member of the Table structure for subsequent use.
    71     75     **
    72     76     ** The column affinity string will eventually be deleted by
................................................................................
   356    360       if( useTempTable ){
   357    361         /* Generate the subroutine that SELECT calls to process each row of
   358    362         ** the result.  Store the result in a temporary table
   359    363         */
   360    364         srcTab = pParse->nTab++;
   361    365         sqlite3VdbeResolveLabel(v, iInsertBlock);
   362    366         sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
   363         -      sqlite3TableAffinityStr(v, pTab);
   364    367         sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0);
   365    368         sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
   366    369         sqlite3VdbeAddOp(v, OP_Insert, srcTab, 0);
   367    370         sqlite3VdbeAddOp(v, OP_Return, 0, 0);
   368    371   
   369    372         /* The following code runs first because the GOTO at the very top
   370    373         ** of the program jumps to it.  Create the temporary table, then jump

Added test/tkt1536.test.

            1  +# 2005 November 24
            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  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests to verify that ticket #1536 is
           14  +# fixed.  
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test tkt1536-1.1 {
           21  +  execsql {
           22  +    CREATE TABLE t1(
           23  +      a INTEGER PRIMARY KEY,
           24  +      b TEXT
           25  +    );
           26  +    INSERT INTO t1 VALUES(1,'01');
           27  +    SELECT typeof(a), typeof(b) FROM t1;
           28  +  }
           29  +} {integer text}
           30  +do_test tkt1536-1.2 {
           31  +  execsql {
           32  +    INSERT INTO t1(b) SELECT b FROM t1;
           33  +    SELECT b FROM t1 WHERE rowid=2;
           34  +  }
           35  +} {01}
           36  + 
           37  +
           38  +finish_test

Changes to www/optoverview.tcl.

     1      1   #
     2      2   # Run this TCL script to generate HTML for the goals.html file.
     3      3   #
     4         -set rcsid {$Id: optoverview.tcl,v 1.4 2005/09/20 01:36:30 drh Exp $}
            4  +set rcsid {$Id: optoverview.tcl,v 1.5 2005/11/24 13:15:34 drh Exp $}
     5      5   source common.tcl
     6      6   header {The SQLite Query Optimizer Overview}
     7      7   
     8      8   proc CODE {text} {
     9      9     puts "<blockquote><pre>"
    10     10     puts $text
    11     11     puts "</pre></blockquote>"
................................................................................
    84     84     The analysis of a term might cause new "virtual" terms to
    85     85     be added to the WHERE clause.  Virtual terms can be used with
    86     86     indices to restrict a search.  But virtual terms never generate code
    87     87     that is tested against input rows.
    88     88   }
    89     89   
    90     90   PARAGRAPH {
    91         -  In order be used by an index, a term must be of one of the following
           91  +  To be usable by an index a term must be of one of the following
    92     92     forms:
    93     93   }
    94     94   SYNTAX {
    95     95     /column/ = /expression/
    96     96     /column/ > /expression/
    97     97     /column/ >= /expression/
    98     98     /column/ < /expression/