/ Check-in [da81725c]
Login

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

Overview
Comment:Clarify the documentation on how comparisons occur in an IN operator. Fix the comparison operators when both sides of an IN operator are expressions (ticket #2248). Changes to main.mk for adding FTS2 into the standard build also got mixed in with this check-in by mistake. (CVS 3656)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: da81725ca1cd894b3f2d734767e10cc0dc329566
User & Date: drh 2007-02-23 03:00:45
Context
2007-02-23
14:20
Clarifications to the datatype3.html document. (CVS 3657) check-in: 4692a85e user: drh tags: trunk
03:00
Clarify the documentation on how comparisons occur in an IN operator. Fix the comparison operators when both sides of an IN operator are expressions (ticket #2248). Changes to main.mk for adding FTS2 into the standard build also got mixed in with this check-in by mistake. (CVS 3656) check-in: da81725c user: drh tags: trunk
00:14
Fix typos in test naming (was using 'e' instead of 'f'). (CVS 3655) check-in: 16cb00ad user: shess tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

    97     97     $(TOP)/src/pragma.c \
    98     98     $(TOP)/src/prepare.c \
    99     99     $(TOP)/src/printf.c \
   100    100     $(TOP)/src/random.c \
   101    101     $(TOP)/src/select.c \
   102    102     $(TOP)/src/shell.c \
   103    103     $(TOP)/src/sqlite.h.in \
          104  +  $(TOP)/src/sqlite3ext.h \
   104    105     $(TOP)/src/sqliteInt.h \
   105    106     $(TOP)/src/table.c \
   106    107     $(TOP)/src/tclsqlite.c \
   107    108     $(TOP)/src/tokenize.c \
   108    109     $(TOP)/src/trigger.c \
   109    110     $(TOP)/src/utf.c \
   110    111     $(TOP)/src/update.c \
................................................................................
   126    127     $(TOP)/ext/fts1/fts1.c \
   127    128     $(TOP)/ext/fts1/fts1.h \
   128    129     $(TOP)/ext/fts1/fts1_hash.c \
   129    130     $(TOP)/ext/fts1/fts1_hash.h \
   130    131     $(TOP)/ext/fts1/fts1_porter.c \
   131    132     $(TOP)/ext/fts1/fts1_tokenizer.h \
   132    133     $(TOP)/ext/fts1/fts1_tokenizer1.c
          134  +SRC += \
          135  +  $(TOP)/ext/fts2/fts2.c \
          136  +  $(TOP)/ext/fts2/fts2.h \
          137  +  $(TOP)/ext/fts2/fts2_hash.c \
          138  +  $(TOP)/ext/fts2/fts2_hash.h \
          139  +  $(TOP)/ext/fts2/fts2_porter.c \
          140  +  $(TOP)/ext/fts2/fts2_tokenizer.h \
          141  +  $(TOP)/ext/fts2/fts2_tokenizer1.c
          142  +
          143  +# Generated source code files
          144  +#
          145  +SRC += \
          146  +  keywordhash.h \
          147  +  opcodes.c \
          148  +  opcodes.h \
          149  +  parse.c \
          150  +  parse.h \
          151  +  sqlite3.h
   133    152   
   134    153   
   135    154   # Source code to the test files.
   136    155   #
   137    156   TESTSRC = \
   138    157     $(TOP)/src/btree.c \
   139    158     $(TOP)/src/date.c \
................................................................................
   178    197      $(TOP)/src/sqlite3ext.h \
   179    198      $(TOP)/src/sqliteInt.h  \
   180    199      $(TOP)/src/vdbe.h \
   181    200      parse.h
   182    201   
   183    202   # Header files used by extensions
   184    203   #
   185         -HDR += \
          204  +EXTHDR += \
   186    205     $(TOP)/ext/fts1/fts1.h \
   187    206     $(TOP)/ext/fts1/fts1_hash.h \
   188    207     $(TOP)/ext/fts1/fts1_tokenizer.h
          208  +EXTHDR += \
          209  +  $(TOP)/ext/fts2/fts2.h \
          210  +  $(TOP)/ext/fts2/fts2_hash.h \
          211  +  $(TOP)/ext/fts2/fts2_tokenizer.h
   189    212   
   190    213   
   191    214   # Header files used by the VDBE submodule
   192    215   #
   193    216   VDBEHDR = \
   194         -   $(HDR) \
   195    217      $(TOP)/src/vdbeInt.h
   196    218   
   197    219   # This is the default Makefile target.  The objects listed here
   198    220   # are what get build when you type just "make" with no arguments.
   199    221   #
   200    222   all:	sqlite3.h libsqlite3.a sqlite3$(EXE)
   201    223   
................................................................................
   218    240   
   219    241   # This target creates a directory named "tsrc" and fills it with
   220    242   # copies of all of the C source code and header files needed to
   221    243   # build on the target system.  Some of the C source code and header
   222    244   # files are automatically generated.  This target takes care of
   223    245   # all that automatic generation.
   224    246   #
   225         -target_source:	$(SRC) $(VDBEHDR) opcodes.c keywordhash.h
          247  +target_source:	$(SRC)
   226    248   	rm -rf tsrc
   227    249   	mkdir tsrc
   228         -	cp $(SRC) $(VDBEHDR) tsrc
          250  +	cp $(SRC) tsrc
   229    251   	rm tsrc/sqlite.h.in tsrc/parse.y
   230         -	cp parse.c opcodes.c keywordhash.h tsrc
   231    252   
   232    253   # Rules to build the LEMON compiler generator
   233    254   #
   234    255   lemon:	$(TOP)/tool/lemon.c $(TOP)/tool/lempar.c
   235    256   	$(BCC) -o lemon $(TOP)/tool/lemon.c
   236    257   	cp $(TOP)/tool/lempar.c .
   237    258   
................................................................................
   367    388   
   368    389   util.o:	$(TOP)/src/util.c $(HDR)
   369    390   	$(TCCX) -c $(TOP)/src/util.c
   370    391   
   371    392   vacuum.o:	$(TOP)/src/vacuum.c $(HDR)
   372    393   	$(TCCX) -c $(TOP)/src/vacuum.c
   373    394   
   374         -vdbe.o:	$(TOP)/src/vdbe.c $(VDBEHDR)
          395  +vdbe.o:	$(TOP)/src/vdbe.c $(VDBEHDR) $(HDR)
   375    396   	$(TCCX) -c $(TOP)/src/vdbe.c
   376    397   
   377         -vdbeapi.o:	$(TOP)/src/vdbeapi.c $(VDBEHDR)
          398  +vdbeapi.o:	$(TOP)/src/vdbeapi.c $(VDBEHDR) $(HDR)
   378    399   	$(TCCX) -c $(TOP)/src/vdbeapi.c
   379    400   
   380         -vdbeaux.o:	$(TOP)/src/vdbeaux.c $(VDBEHDR)
          401  +vdbeaux.o:	$(TOP)/src/vdbeaux.c $(VDBEHDR) $(HDR)
   381    402   	$(TCCX) -c $(TOP)/src/vdbeaux.c
   382    403   
   383         -vdbefifo.o:	$(TOP)/src/vdbefifo.c $(VDBEHDR)
          404  +vdbefifo.o:	$(TOP)/src/vdbefifo.c $(VDBEHDR) $(HDR)
   384    405   	$(TCCX) -c $(TOP)/src/vdbefifo.c
   385    406   
   386         -vdbemem.o:	$(TOP)/src/vdbemem.c $(VDBEHDR)
          407  +vdbemem.o:	$(TOP)/src/vdbemem.c $(VDBEHDR) $(HDR)
   387    408   	$(TCCX) -c $(TOP)/src/vdbemem.c
   388    409   
   389         -vtab.o:	$(TOP)/src/vtab.c $(VDBEHDR)
          410  +vtab.o:	$(TOP)/src/vtab.c $(VDBEHDR) $(HDR)
   390    411   	$(TCCX) -c $(TOP)/src/vtab.c
   391    412   
   392    413   where.o:	$(TOP)/src/where.c $(HDR)
   393    414   	$(TCCX) -c $(TOP)/src/where.c
   394    415   
   395    416   # Rules for building test programs and for running tests
   396    417   #

Changes to src/expr.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 routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.276 2007/02/14 09:19:36 danielk1977 Exp $
           15  +** $Id: expr.c,v 1.277 2007/02/23 03:00:45 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
   127    127     if( pExpr->pRight ){
   128    128       aff = sqlite3CompareAffinity(pExpr->pRight, aff);
   129    129     }
   130    130     else if( pExpr->pSelect ){
   131    131       aff = sqlite3CompareAffinity(pExpr->pSelect->pEList->a[0].pExpr, aff);
   132    132     }
   133    133     else if( !aff ){
   134         -    aff = SQLITE_AFF_NUMERIC;
          134  +    aff = SQLITE_AFF_NONE;
   135    135     }
   136    136     return aff;
   137    137   }
   138    138   
   139    139   /*
   140    140   ** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
   141    141   ** idx_affinity is the affinity of an indexed column. Return true

Changes to test/types2.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library. The focus
    12     12   # of this file is testing the interaction of manifest types, type affinity
    13     13   # and comparison expressions.
    14     14   #
    15         -# $Id: types2.test,v 1.6 2006/05/23 23:22:29 drh Exp $
           15  +# $Id: types2.test,v 1.7 2007/02/23 03:00:45 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Tests in this file are organized roughly as follows:
    21     21   #
    22     22   # types2-1.*: The '=' operator in the absence of an index.
................................................................................
   200    200     # types2-5.* - The 'IN (x, y....)' operator with no index.
   201    201     # 
   202    202     # Compare literals against literals (no affinity applied)
   203    203     test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
   204    204     test_bool types2-5.2 {} {10 IN ('10.0', 20)} 0
   205    205     test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 0
   206    206     test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
   207         -  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1
          207  +  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 0
   208    208     
   209    209     # Compare literals against a column with TEXT affinity
   210    210     test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
   211    211     test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
   212    212     test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
   213    213     test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
   214    214     test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1
................................................................................
   225    225     test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
   226    226     test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
   227    227     test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
   228    228     test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
   229    229     test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
   230    230     test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
   231    231     test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1
          232  +
          233  +  # Ticket #2248:  Comparisons of strings literals that look like
          234  +  # numbers.
          235  +  test_bool types2-5.24 {} {'1' IN ('1')} 1
          236  +  test_bool types2-5.25 {} {'2' IN (2)} 0
          237  +  test_bool types2-5.26 {} {3 IN ('3')} 0
          238  +  test_bool types2-5.27 {} {4 IN (4)} 1
          239  +
          240  +  # The affinity of columns on the right side of IN(...) is ignored.
          241  +  # All values in the expression list are treated as ordinary expressions,
          242  +  # even if they are columns with affinity.
          243  +  test_bool types2-5.30 {t1='10'} {10 IN (5,t1,'abc')} 0
          244  +  test_bool types2-5.31 {t1='10'} {10 IN ('abc',t1,5)} 0
          245  +  test_bool types2-5.32 {t1='010'} {10 IN (5,t1,'abc')} 0
          246  +  test_bool types2-5.33 {t1='010'} {10 IN ('abc',t1,5)} 0
          247  +  test_bool types2-5.34 {t1='10'} {'10' IN (5,t1,'abc')} 1
          248  +  test_bool types2-5.35 {t1='10'} {'10' IN ('abc',t1,5)} 1
          249  +  test_bool types2-5.36 {t1='010'} {'10' IN (5,t1,'abc')} 0
          250  +  test_bool types2-5.37 {t1='010'} {'10' IN ('abc',t1,5)} 0
          251  +  
          252  +  # Columns on both the left and right of IN(...).  Only the column
          253  +  # on the left matters.  The all values on the right are treated like
          254  +  # expressions.
          255  +  test_bool types2-5.40 {t1='10',n1=10} {t1 IN (5,n1,11)} 1
          256  +  test_bool types2-5.41 {t1='010',n1=10} {t1 IN (5,n1,11)} 0
          257  +  test_bool types2-5.42 {t1='10',n1=10} {n1 IN (5,t1,11)} 1
          258  +  test_bool types2-5.43 {t1='010',n1=10} {n1 IN (5,t1,11)} 1
   232    259   }
   233    260   
   234    261   # Tests named types2-6.* use the same infrastructure as the types2-2.*
   235    262   # tests. The contents of the vals array is repeated here for easy 
   236    263   # reference.
   237    264   # 
   238    265   # set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']

Changes to www/datatype3.tcl.

     1         -set rcsid {$Id: datatype3.tcl,v 1.14 2006/05/23 23:22:29 drh Exp $}
            1  +set rcsid {$Id: datatype3.tcl,v 1.15 2007/02/23 03:00:46 drh Exp $}
     2      2   source common.tcl
     3      3   header {Datatypes In SQLite Version 3}
     4      4   puts {
     5      5   <h2>Datatypes In SQLite Version 3</h2>
     6      6   
     7      7   <h3>1. Storage Classes</h3>
     8      8   
................................................................................
   221    221   
   222    222   <P>Expressions of the type "a IN (SELECT b ....)" are handled by the three
   223    223   rules enumerated above for binary comparisons (e.g. in a
   224    224   similar manner to "a = b"). For example if 'b' is a column value
   225    225   and 'a' is an expression, then the affinity of 'b' is applied to 'a'
   226    226   before any comparisons take place.</P>
   227    227   
   228         -<P>SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = z OR
   229         -a = y OR a = z".
          228  +<P>SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = +x OR
          229  +a = +y OR a = +z".  The values to the right of the IN operator (the "x", "y",
          230  +and "z" values in this example) are considered to be expressions, even if they
          231  +happen to be column values.  If the value of the left of the IN operator is
          232  +a column, then the affinity of that column is used.  If the value is an
          233  +expression then no conversions occur.
   230    234   </P>
   231    235   
   232    236   <h4>3.1 Comparison Example</h4>
   233    237   
   234    238   <blockquote>
   235    239   <PRE>
   236    240   CREATE TABLE t1(