xref: /sqlite-3.40.0/test/types2.test (revision 4dcbdbff) 
1# 2001 September 15
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. The focus
12# of this file is testing the interaction of manifest types, type affinity
13# and comparison expressions.
14#
15# $Id: types2.test,v 1.5 2005/01/21 03:12:16 danielk1977 Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Tests in this file are organized roughly as follows:
21#
22# types2-1.*: The '=' operator in the absence of an index.
23# types2-2.*: The '=' operator implemented using an index.
24# types2-3.*: The '<' operator implemented using an index.
25# types2-4.*: The '>' operator in the absence of an index.
26# types2-5.*: The 'IN(x, y...)' operator in the absence of an index.
27# types2-6.*: The 'IN(x, y...)' operator with an index.
28# types2-7.*: The 'IN(SELECT...)' operator in the absence of an index.
29# types2-8.*: The 'IN(SELECT...)' operator with an index.
30#
31# All tests test the operators using literals and columns, but no
32# other types of expressions. All expressions except columns are
33# handled similarly in the implementation.
34
35execsql {
36  CREATE TABLE t1(
37    i1 INTEGER,
38    i2 INTEGER,
39    n1 NUMERIC,
40    n2 NUMERIC,
41    t1 TEXT,
42    t2 TEXT,
43    o1 BLOB,
44    o2 BLOB
45  );
46  INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
47}
48
49proc test_bool {testname vars expr res} {
50  if { $vars != "" } {
51    execsql "UPDATE t1 SET $vars"
52  }
53
54  foreach {t e r} [list $testname $expr $res] {}
55
56  do_test $t.1 "execsql {SELECT $e FROM t1}" $r
57  do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
58  do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
59}
60
61# Compare literals against literals. This should always use a numeric
62# comparison.
63#
64# Changed by ticket #805:  Use no affinity for literal comparisons.
65#
66test_bool types2-1.1 "" {500 = 500.0} 1
67test_bool types2-1.2 "" {'500' = 500.0} 0
68test_bool types2-1.3 "" {500 = '500.0'} 0
69test_bool types2-1.4 "" {'500' = '500.0'} 0
70
71# Compare literals against a column with TEXT affinity
72test_bool types2-1.5 {t1=500} {500 = t1} 1
73test_bool types2-1.6 {t1=500} {'500' = t1} 1
74test_bool types2-1.7 {t1=500} {500.0 = t1} 0
75test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
76test_bool types2-1.9 {t1='500'} {500 = t1} 1
77test_bool types2-1.10 {t1='500'} {'500' = t1} 1
78test_bool types2-1.11 {t1='500'} {500.0 = t1} 0
79test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0
80
81# Compare literals against a column with NUMERIC affinity
82test_bool types2-1.13 {n1=500} {500 = n1} 1
83test_bool types2-1.14 {n1=500} {'500' = n1} 1
84test_bool types2-1.15 {n1=500} {500.0 = n1} 1
85test_bool types2-1.16 {n1=500} {'500.0' = n1} 1
86test_bool types2-1.17 {n1='500'} {500 = n1} 1
87test_bool types2-1.18 {n1='500'} {'500' = n1} 1
88test_bool types2-1.19 {n1='500'} {500.0 = n1} 1
89test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1
90
91# Compare literals against a column with affinity NONE
92test_bool types2-1.21 {o1=500} {500 = o1} 1
93test_bool types2-1.22 {o1=500} {'500' = o1} 0
94test_bool types2-1.23 {o1=500} {500.0 = o1} 1
95test_bool types2-1.24 {o1=500} {'500.0' = o1} 0
96test_bool types2-1.25 {o1='500'} {500 = o1} 0
97test_bool types2-1.26 {o1='500'} {'500' = o1} 1
98test_bool types2-1.27 {o1='500'} {500.0 = o1} 0
99test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0
100
101set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
102#              1  2    3    4      5  6    7    8      9  10   11   12
103
104execsql {
105  CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY);
106  CREATE INDEX t2i1 ON t2(i);
107  CREATE INDEX t2i2 ON t2(n);
108  CREATE INDEX t2i3 ON t2(t);
109  CREATE INDEX t2i4 ON t2(o);
110}
111foreach v $vals {
112  execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"
113}
114
115proc test_boolset {testname where set} {
116  set ::tb_sql "SELECT rowid FROM t2 WHERE $where"
117  do_test $testname {
118    lsort -integer [execsql $::tb_sql]
119  } $set
120}
121
122test_boolset types2-2.1 {i = 10} {1 2 3 4}
123test_boolset types2-2.2 {i = 10.0} {1 2 3 4}
124test_boolset types2-2.3 {i = '10'} {1 2 3 4}
125test_boolset types2-2.4 {i = '10.0'} {1 2 3 4}
126
127test_boolset types2-2.5 {n = 20} {5 6 7 8}
128test_boolset types2-2.6 {n = 20.0} {5 6 7 8}
129test_boolset types2-2.7 {n = '20'} {5 6 7 8}
130test_boolset types2-2.8 {n = '20.0'} {5 6 7 8}
131
132test_boolset types2-2.9 {t = 20} {5 7}
133test_boolset types2-2.10 {t = 20.0} {6 8}
134test_boolset types2-2.11 {t = '20'} {5 7}
135test_boolset types2-2.12 {t = '20.0'} {6 8}
136
137test_boolset types2-2.10 {o = 30} {9 10}
138test_boolset types2-2.11 {o = 30.0} {9 10}
139test_boolset types2-2.12 {o = '30'} 11
140test_boolset types2-2.13 {o = '30.0'} 12
141
142test_boolset types2-3.1 {i < 20} {1 2 3 4}
143test_boolset types2-3.2 {i < 20.0} {1 2 3 4}
144test_boolset types2-3.3 {i < '20'} {1 2 3 4}
145test_boolset types2-3.4 {i < '20.0'} {1 2 3 4}
146
147test_boolset types2-3.1 {n < 20} {1 2 3 4}
148test_boolset types2-3.2 {n < 20.0} {1 2 3 4}
149test_boolset types2-3.3 {n < '20'} {1 2 3 4}
150test_boolset types2-3.4 {n < '20.0'} {1 2 3 4}
151
152test_boolset types2-3.1 {t < 20} {1 2 3 4}
153test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7}
154test_boolset types2-3.3 {t < '20'} {1 2 3 4}
155test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}
156
157test_boolset types2-3.1 {o < 20} {1 2}
158test_boolset types2-3.2 {o < 20.0} {1 2}
159test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
160test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}
161
162# Compare literals against literals (always a numeric comparison).
163# Change (by ticket #805):  No affinity in comparisons
164test_bool types2-4.1 "" {500 > 60.0} 1
165test_bool types2-4.2 "" {'500' > 60.0} 1
166test_bool types2-4.3 "" {500 > '60.0'} 0
167test_bool types2-4.4 "" {'500' > '60.0'} 0
168
169# Compare literals against a column with TEXT affinity
170test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
171test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
172test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
173test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
174test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1
175test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1
176test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0
177test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0
178
179# Compare literals against a column with NUMERIC affinity
180test_bool types2-4.13 {n1=400} {500 > n1} 1
181test_bool types2-4.14 {n1=400} {'500' > n1} 1
182test_bool types2-4.15 {n1=400} {500.0 > n1} 1
183test_bool types2-4.16 {n1=400} {'500.0' > n1} 1
184test_bool types2-4.17 {n1='400'} {500 > n1} 1
185test_bool types2-4.18 {n1='400'} {'500' > n1} 1
186test_bool types2-4.19 {n1='400'} {500.0 > n1} 1
187test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1
188
189# Compare literals against a column with affinity NONE
190test_bool types2-4.21 {o1=500} {500 > o1} 0
191test_bool types2-4.22 {o1=500} {'500' > o1} 1
192test_bool types2-4.23 {o1=500} {500.0 > o1} 0
193test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
194test_bool types2-4.25 {o1='500'} {500 > o1} 0
195test_bool types2-4.26 {o1='500'} {'500' > o1} 0
196test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
197test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1
198
199ifcapable subquery {
200  # types2-5.* - The 'IN (x, y....)' operator with no index.
201  #
202  # Compare literals against literals (always a numeric comparison).
203  test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
204  test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1
205  test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1
206  test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
207  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1
208
209  # Compare literals against a column with TEXT affinity
210  test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
211  test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
212  test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
213  test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
214  test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1
215
216  # Compare literals against a column with NUMERIC affinity
217  test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
218  test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
219  test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
220  test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
221  test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1
222
223  # Compare literals against a column with affinity NONE
224  test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
225  test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
226  test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
227  test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
228  test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
229  test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
230  test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
231  test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1
232}
233
234# Tests named types2-6.* use the same infrastructure as the types2-2.*
235# tests. The contents of the vals array is repeated here for easy
236# reference.
237#
238# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
239#                1  2    3    4      5  6    7    8      9  10   11   12
240
241ifcapable subquery {
242  test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
243  test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
244  test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
245  test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
246  test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
247  test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
248  test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
249  test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
250
251  # Also test than IN(x, y, z) works on a rowid:
252  test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}
253}
254
255# Tests types2-7.* concentrate on expressions of the form
256# "x IN (SELECT...)" with no index.
257execsql {
258  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
259  INSERT INTO t3 VALUES(1, 1, 1, 1);
260  INSERT INTO t3 VALUES(2, 2, 2, 2);
261  INSERT INTO t3 VALUES(3, 3, 3, 3);
262  INSERT INTO t3 VALUES('1', '1', '1', '1');
263  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
264}
265
266ifcapable subquery {
267  test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
268  test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
269  test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
270  test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
271  test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1
272
273  test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
274  test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
275  test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
276  test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
277  test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1
278
279  test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
280  test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
281  test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
282  test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
283  test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
284  test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
285  test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1
286
287  test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
288  test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
289  test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1
290}
291
292# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
293#                1  2    3    4      5  6    7    8      9  10   11   12
294execsql {
295  CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
296  INSERT INTO t4 VALUES(10, 20, 20, 30);
297}
298ifcapable subquery {
299  test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
300  test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
301  test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
302  test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
303  test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
304  test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
305  test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
306  test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
307  test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
308  test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
309  test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
310  test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
311}
312
313finish_test
314