/ Check-in [6e5a4976]
Login

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

Overview
Comment:Fix type coercion rules for the IN operator. Ticket #1821. (CVS 3188)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6e5a49762166a942e1b2c3beae8a30c07187eb10
User & Date: drh 2006-05-23 23:22:29
Context
2006-05-23
23:25
Additional tests for ticket #1821. (CVS 3189) check-in: b93e3fb0 user: drh tags: trunk
23:22
Fix type coercion rules for the IN operator. Ticket #1821. (CVS 3188) check-in: 6e5a4976 user: drh tags: trunk
2006-05-22
22:04
If an sqlite3_mprintf() call uses a disallowed internal-use-only conversion character, then abort the call with a -1 error code. Ticket #1818. (CVS 3187) check-in: 9d7297b9 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.257 2006/03/17 13:56:34 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
        ** a column, use numeric affinity.
        */
        int i;
        ExprList *pList = pExpr->pList;
        struct ExprList_item *pItem;

        if( !affinity ){
          affinity = SQLITE_AFF_NUMERIC;
        }
        keyInfo.aColl[0] = pExpr->pLeft->pColl;

        /* Loop through each expression in <exprlist>. */
        for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
          Expr *pE2 = pItem->pExpr;








|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.258 2006/05/23 23:22:29 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
        ** a column, use numeric affinity.
        */
        int i;
        ExprList *pList = pExpr->pList;
        struct ExprList_item *pItem;

        if( !affinity ){
          affinity = SQLITE_AFF_NONE;
        }
        keyInfo.aColl[0] = pExpr->pLeft->pColl;

        /* Loop through each expression in <exprlist>. */
        for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
          Expr *pE2 = pItem->pExpr;

Changes to src/prepare.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.33 2006/03/13 15:06:07 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** Fill the InitData structure with an error message that indicates
................................................................................
  /* Get the database meta information.
  **
  ** Meta values are as follows:
  **    meta[0]   Schema cookie.  Changes with each schema change.
  **    meta[1]   File format of schema layer.
  **    meta[2]   Size of the page cache.
  **    meta[3]   Use freelist if 0.  Autovacuum if greater than zero.
  **    meta[4]   Db text encoding. 1:UTF-8 3:UTF-16 LE 4:UTF-16 BE
  **    meta[5]   The user cookie. Used by the application.
  **    meta[6]   
  **    meta[7]
  **    meta[8]
  **    meta[9]
  **
  ** Note: The #defined SQLITE_UTF* symbols in sqliteInt.h correspond to







|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.34 2006/05/23 23:22:29 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** Fill the InitData structure with an error message that indicates
................................................................................
  /* Get the database meta information.
  **
  ** Meta values are as follows:
  **    meta[0]   Schema cookie.  Changes with each schema change.
  **    meta[1]   File format of schema layer.
  **    meta[2]   Size of the page cache.
  **    meta[3]   Use freelist if 0.  Autovacuum if greater than zero.
  **    meta[4]   Db text encoding. 1:UTF-8 2:UTF-16LE 3:UTF-16BE
  **    meta[5]   The user cookie. Used by the application.
  **    meta[6]   
  **    meta[7]
  **    meta[8]
  **    meta[9]
  **
  ** Note: The #defined SQLITE_UTF* symbols in sqliteInt.h correspond to

Changes to test/in.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
318
319
320
321
322
323
324

325























326
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.15 2006/01/30 14:36:59 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
} {111}
do_test in-10.2 {
  catchsql {
    INSERT INTO t5 VALUES(4);
  }
} {1 {constraint failed}}


























finish_test







|







 







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.16 2006/05/23 23:22:29 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
} {111}
do_test in-10.2 {
  catchsql {
    INSERT INTO t5 VALUES(4);
  }
} {1 {constraint failed}}

# Ticket #1821
#
# Type affinity applied to the right-hand side of an IN operator.
#
do_test in-11.1 {
  execsql {
    CREATE TABLE t6(a,b NUMERIC);
    INSERT INTO t6 VALUES(1,2);
    INSERT INTO t6 VALUES(2,3);
    SELECT * FROM t6 WHERE b IN (2);
  }
} {1 2}
do_test in-11.2 {
  # The '2' should be coerced into 2 because t6.b is NUMERIC
  execsql {
    SELECT * FROM t6 WHERE b IN ('2');
  }
} {1 2}
do_test in-11.3 {
  # No coercion should occur here because of the unary + before b.
  execsql {
    SELECT * FROM t6 WHERE +b IN ('2');
  }
} {}

finish_test

Changes to test/types2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of this file is testing the interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.5 2005/01/21 03:12:16 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1

ifcapable subquery {
  # types2-5.* - The 'IN (x, y....)' operator with no index.
  # 
  # Compare literals against literals (always a numeric comparison).
  test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
  test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1
  test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1
  test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1
  
  # Compare literals against a column with TEXT affinity
  test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
  test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
  test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0







|







 







|

|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of this file is testing the interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.6 2006/05/23 23:22:29 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1

ifcapable subquery {
  # types2-5.* - The 'IN (x, y....)' operator with no index.
  # 
  # Compare literals against literals (no affinity applied)
  test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
  test_bool types2-5.2 {} {10 IN ('10.0', 20)} 0
  test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 0
  test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1
  
  # Compare literals against a column with TEXT affinity
  test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
  test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
  test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0

Changes to www/datatype3.tcl.

1
2
3
4
5
6
7
8
..
83
84
85
86
87
88
89

90
91
92
93
94
95
96
...
103
104
105
106
107
108
109






110
111
112
113
114
115
116
...
125
126
127
128
129
130
131




132
133
134
135
136
137
138
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
set rcsid {$Id: datatype3.tcl,v 1.13 2006/04/01 14:38:41 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................

<P>Each column in an SQLite 3 database is assigned one of the
following type affinities:</P>
<UL>
	<LI>TEXT</LI>
	<LI>NUMERIC</LI>
	<LI>INTEGER</LI>

	<LI>NONE</LI>
</UL>

<P>A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. If numerical data is inserted into a column with
TEXT affinity it is converted to text form before being stored.</P>

................................................................................
attempt is made to convert NULL or blob values.</P>

<P>A column that uses INTEGER affinity behaves in the same way as a
column with NUMERIC affinity, except that if a real value with no
floating point component (or text value that converts to such) is
inserted it is converted to an integer and stored using the INTEGER
storage class.</P>







<P>A column with affinity NONE does not prefer one storage class over
another.  It makes no attempt to coerce data before
it is inserted.</P>

<h4>2.1 Determination Of Column Affinity</h4>

................................................................................
	column has TEXT affinity. Notice that the type VARCHAR contains the
	string &quot;CHAR&quot; and is thus assigned TEXT affinity.</P>

	<LI><P>If the datatype for a column
         contains the string &quot;BLOB&quot; or if
        no datatype is specified then the column has affinity NONE.</P>





	<LI><P>Otherwise, the affinity is NUMERIC.</P>
</OL>

<P>If a table is created using a "CREATE TABLE &lt;table&gt; AS
SELECT..." statement, then all columns have no datatype specified
and they are given no affinity.</P>

................................................................................
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has
	INTEGER or NUMERIC affinity and the other does not, the NUMERIC
	affinity is applied to any values with storage class TEXT extracted
	from the non-NUMERIC column.</P>

	<LI><P>When the results of two expressions are compared, the no
        conversions occur.  The results are compared as is.  If a string
        is compared to a number, the number will always be less than the
        string.</P>
</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
|







 







>







 







>
>
>
>
>
>







 







>
>
>
>







 







|







1
2
3
4
5
6
7
8
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
set rcsid {$Id: datatype3.tcl,v 1.14 2006/05/23 23:22:29 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................

<P>Each column in an SQLite 3 database is assigned one of the
following type affinities:</P>
<UL>
	<LI>TEXT</LI>
	<LI>NUMERIC</LI>
	<LI>INTEGER</LI>
        <LI>REAL</li>
	<LI>NONE</LI>
</UL>

<P>A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. If numerical data is inserted into a column with
TEXT affinity it is converted to text form before being stored.</P>

................................................................................
attempt is made to convert NULL or blob values.</P>

<P>A column that uses INTEGER affinity behaves in the same way as a
column with NUMERIC affinity, except that if a real value with no
floating point component (or text value that converts to such) is
inserted it is converted to an integer and stored using the INTEGER
storage class.</P>

<P>A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (As an optimization, integer values are stored on
disk as integers in order to take up less space and are only converted
to floating point as the value is read out of the table.)</P>

<P>A column with affinity NONE does not prefer one storage class over
another.  It makes no attempt to coerce data before
it is inserted.</P>

<h4>2.1 Determination Of Column Affinity</h4>

................................................................................
	column has TEXT affinity. Notice that the type VARCHAR contains the
	string &quot;CHAR&quot; and is thus assigned TEXT affinity.</P>

	<LI><P>If the datatype for a column
         contains the string &quot;BLOB&quot; or if
        no datatype is specified then the column has affinity NONE.</P>

        <LI><P>If the datatype for a column
        contains any of the strings &quot;REAL&quot;, &quot;FLOA&quot;,
        or &quot;DOUB&quot; then the column has REAL affinity</P>

	<LI><P>Otherwise, the affinity is NUMERIC.</P>
</OL>

<P>If a table is created using a "CREATE TABLE &lt;table&gt; AS
SELECT..." statement, then all columns have no datatype specified
and they are given no affinity.</P>

................................................................................
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has
	INTEGER or NUMERIC affinity and the other does not, the NUMERIC
	affinity is applied to any values with storage class TEXT extracted
	from the non-NUMERIC column.</P>

	<LI><P>When the results of two expressions are compared, no
        conversions occur.  The results are compared as is.  If a string
        is compared to a number, the number will always be less than the
        string.</P>
</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b