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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6e5a49762166a942e1b2c3beae8a30c0 |
User & Date: | drh 2006-05-23 23:22:29.000 |
Context
2006-05-23
| ||
23:25 | Additional tests for ticket #1821. (CVS 3189) (check-in: b93e3fb02a user: drh tags: trunk) | |
23:22 | Fix type coercion rules for the IN operator. Ticket #1821. (CVS 3188) (check-in: 6e5a497621 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: 9d7297b9ef user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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. ** |
︙ | ︙ | |||
1363 1364 1365 1366 1367 1368 1369 | ** a column, use numeric affinity. */ int i; ExprList *pList = pExpr->pList; struct ExprList_item *pItem; if( !affinity ){ | | | 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 | ** 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 | ** 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. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** 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 |
︙ | ︙ | |||
207 208 209 210 211 212 213 | /* 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. | | | 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | /* 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # 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. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # 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 { |
︙ | ︙ | |||
318 319 320 321 322 323 324 | } {111} do_test in-10.2 { catchsql { INSERT INTO t5 VALUES(4); } } {1 {constraint failed}} | > | > > > > > > > > > > > > > > > > > > > > > > > | 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 | } {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 | # 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. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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. |
︙ | ︙ | |||
195 196 197 198 199 200 201 | 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. # | | | | | 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 | 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 | 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> |
︙ | ︙ | |||
83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | <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> <P>A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No 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> | > > > > > > > | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | <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> <P>A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No 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> |
︙ | ︙ | |||
125 126 127 128 129 130 131 132 133 134 135 136 137 138 | column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.</P> <LI><P>If the datatype for a column contains the string "BLOB" 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 <table> AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.</P> | > > > > | 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.</P> <LI><P>If the datatype for a column contains the string "BLOB" 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 "REAL", "FLOA", or "DOUB" 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 <table> AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.</P> |
︙ | ︙ | |||
192 193 194 195 196 197 198 | 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> | | | 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 | 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 >= b |
︙ | ︙ |