SQLite

Check-in [8411718f0a]
Login

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

Overview
Comment:Fix problems with types and the recognition of BLOB as having no affinity. (CVS 1418)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8411718f0ac17e9c2376fdf8b5fa0cc5fc88be9b
User & Date: drh 2004-05-20 12:41:20.000
Context
2004-05-20
13:54
sqlite3MemCompare now takes a CollSeq* argument. (CVS 1419) (check-in: 5c1e47a252 user: drh tags: trunk)
12:41
Fix problems with types and the recognition of BLOB as having no affinity. (CVS 1418) (check-in: 8411718f0a user: drh tags: trunk)
12:10
Default type affinity is now NUMERIC. The affinity.html file checked into the doc directory. (CVS 1417) (check-in: 948307f07d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.188 2004/05/20 12:10:20 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.189 2004/05/20 12:41:20 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
    p->aCol = aNew;
  }
  pCol = &p->aCol[p->nCol];
  memset(pCol, 0, sizeof(p->aCol[0]));
  pCol->zName = z;
 
  /* If there is no type specified, columns have the default affinity
  ** 'NONE'. If there is a type specified, then sqlite3AddColumnType()
  ** will be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_NONE;
  p->nCol++;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on







|


|







583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
    p->aCol = aNew;
  }
  pCol = &p->aCol[p->nCol];
  memset(pCol, 0, sizeof(p->aCol[0]));
  pCol->zName = z;
 
  /* If there is no type specified, columns have the default affinity
  ** 'NUMERIC'. If there is a type specified, then sqlite3AddColumnType()
  ** will be called next to set pCol->affinity correctly.
  */
  pCol->affinity = SQLITE_AFF_NUMERIC;
  p->nCol++;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "NOT NULL" constraint has
** been seen on a column.  This routine sets the notNull flag on
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.114 2004/05/18 10:06:25 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.115 2004/05/20 12:41:20 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

type ::= .
type ::= typename(X).                    {sqlite3AddColumnType(pParse,&X,&X);}
type ::= typename(X) LP signed RP(Y).    {sqlite3AddColumnType(pParse,&X,&Y);}
type ::= typename(X) LP signed COMMA signed RP(Y).
                                         {sqlite3AddColumnType(pParse,&X,&Y);}
%type typename {Token}
typename(A) ::= ids(X).           {A = X;}
typename(A) ::= typename(X) ids.  {A = X;}
%type signed {int}
signed(A) ::= INTEGER(X).         { A = atoi(X.z); }
signed(A) ::= PLUS INTEGER(X).    { A = atoi(X.z); }
signed(A) ::= MINUS INTEGER(X).   { A = -atoi(X.z); }
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT nm ccons.







|
|







158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

type ::= .
type ::= typename(X).                    {sqlite3AddColumnType(pParse,&X,&X);}
type ::= typename(X) LP signed RP(Y).    {sqlite3AddColumnType(pParse,&X,&Y);}
type ::= typename(X) LP signed COMMA signed RP(Y).
                                         {sqlite3AddColumnType(pParse,&X,&Y);}
%type typename {Token}
typename(A) ::= ids(X).             {A = X;}
typename(A) ::= typename(X) ids(Y). {A.z=X.z; A.n=X.n+Addr(Y.z)-Addr(X.z);}
%type signed {int}
signed(A) ::= INTEGER(X).         { A = atoi(X.z); }
signed(A) ::= PLUS INTEGER(X).    { A = atoi(X.z); }
signed(A) ::= MINUS INTEGER(X).   { A = -atoi(X.z); }
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT nm ccons.
891
892
893
894
895
896
897
898
899
900
database_kw_opt ::= DATABASE.
database_kw_opt ::= .

//////////////////////// DETACH DATABASE name /////////////////////////////////
cmd ::= DETACH database_kw_opt nm(D). {
  sqlite3Detach(pParse, &D);
}










<
<
<
891
892
893
894
895
896
897



database_kw_opt ::= DATABASE.
database_kw_opt ::= .

//////////////////////// DETACH DATABASE name /////////////////////////////////
cmd ::= DETACH database_kw_opt nm(D). {
  sqlite3Detach(pParse, &D);
}



Changes to test/types.test.
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. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.4 2004/05/18 10:06:26 danielk1977 Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage







|







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. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.5 2004/05/20 12:41:20 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

64
65
66
67
68
69
70
# types-3.*: Test that the '=' operator respects manifest types.
#


# Create a table with one column for each type of affinity
do_test types-1.1.0 {
  execsql {
    CREATE TABLE t1(i integer, n numeric, t text, o);
  }
} {}

# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values [list \
  [list 5.0   INTEGER REAL    TEXT REAL] \
  [list 5     INTEGER INTEGER TEXT INTEGER] \
  [list '5.0' INTEGER REAL    TEXT TEXT] \
  [list '-5.0' INTEGER REAL    TEXT TEXT] \
  [list '-5.0' INTEGER REAL    TEXT TEXT] \
  [list '5'   INTEGER INTEGER TEXT TEXT] \
  [list 'abc' TEXT    TEXT    TEXT TEXT] \
  [list NULL  NULL    NULL    NULL NULL] \
]


# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO <table> VALUE(<values>);
#







|









|
|
|
|
|
|
|
|
|
<
>







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

63
64
65
66
67
68
69
70
# types-3.*: Test that the '=' operator respects manifest types.
#


# Create a table with one column for each type of affinity
do_test types-1.1.0 {
  execsql {
    CREATE TABLE t1(i integer, n numeric, t text, o blob);
  }
} {}

# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    INTEGER REAL    TEXT REAL    }
  { 5      INTEGER INTEGER TEXT INTEGER }
  { '5.0'  INTEGER REAL    TEXT TEXT    }
  { '-5.0' INTEGER REAL    TEXT TEXT    }
  { '-5.0' INTEGER REAL    TEXT TEXT    }
  { '5'    INTEGER INTEGER TEXT TEXT    }
  { 'abc'  TEXT    TEXT    TEXT TEXT    }
  { NULL   NULL    NULL    NULL NULL    }

}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO <table> VALUE(<values>);
#
287
288
289
290
291
292
293
294
do_test types-2.5.3 {
  execsql {
    SELECT * FROM t1;
  }
} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]

finish_test








<
287
288
289
290
291
292
293

do_test types-2.5.3 {
  execsql {
    SELECT * FROM t1;
  }
} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000]

finish_test

Changes to test/types2.test.
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.2 2004/05/17 10:48:58 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.







|







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.3 2004/05/20 12:41:20 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.
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
  CREATE TABLE t1(
    i1 INTEGER,
    i2 INTEGER,
    n1 NUMERIC,
    n2 NUMERIC,
    t1 TEXT,
    t2 TEXT,
    o1,
    o2
  );
  INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
}

proc test_bool {testname vars expr res} {
  if { $vars != "" } {
    execsql "UPDATE t1 SET $vars"







|
|







36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
  CREATE TABLE t1(
    i1 INTEGER,
    i2 INTEGER,
    n1 NUMERIC,
    n2 NUMERIC,
    t1 TEXT,
    t2 TEXT,
    o1 BLOB,
    o2 BLOB
  );
  INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
}

proc test_bool {testname vars expr res} {
  if { $vars != "" } {
    execsql "UPDATE t1 SET $vars"
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
test_bool types2-1.27 {o1='500'} {500.0 = o1} 0
test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0

set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#              1  2    3    4      5  6    7    8      9  10   11   12

execsql {
  CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o);
  CREATE INDEX t2i1 ON t2(i);
  CREATE INDEX t2i2 ON t2(n);
  CREATE INDEX t2i3 ON t2(t);
  CREATE INDEX t2i4 ON t2(o);
}
foreach v $vals {
  execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"







|







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
test_bool types2-1.27 {o1='500'} {500.0 = o1} 0
test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0

set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#              1  2    3    4      5  6    7    8      9  10   11   12

execsql {
  CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY);
  CREATE INDEX t2i1 ON t2(i);
  CREATE INDEX t2i2 ON t2(n);
  CREATE INDEX t2i3 ON t2(t);
  CREATE INDEX t2i4 ON t2(o);
}
foreach v $vals {
  execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257

# Also test than IN(x, y, z) works on a rowid:
test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}

# Tests types2-7.* concentrate on expressions of the form 
# "x IN (SELECT...)" with no index.
execsql {
  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o);
  INSERT INTO t3 VALUES(1, 1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3, 3);
  INSERT INTO t3 VALUES('1', '1', '1', '1');
  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
}








|







243
244
245
246
247
248
249
250
251
252
253
254
255
256
257

# Also test than IN(x, y, z) works on a rowid:
test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}

# Tests types2-7.* concentrate on expressions of the form 
# "x IN (SELECT...)" with no index.
execsql {
  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
  INSERT INTO t3 VALUES(1, 1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3, 3);
  INSERT INTO t3 VALUES('1', '1', '1', '1');
  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
}

278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12
execsql {
  CREATE TABLE t4(i INTEGER, n NUMERIC, t TEXT, o);
  INSERT INTO t4 VALUES(10, 20, 20, 30);
}
test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}

finish_test














|
















<
<
<
<
<
<
<
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301







test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12
execsql {
  CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
  INSERT INTO t4 VALUES(10, 20, 20, 30);
}
test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}

finish_test