259
264
} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
266
# Ticket #2249. Make sure the OR optimization is not attempted if
267
# comparisons between columns of different affinities are needed.
271
CREATE TABLE t2249a(a TEXT UNIQUE);
272
CREATE TABLE t2249b(b INTEGER);
273
INSERT INTO t2249a VALUES('0123');
274
INSERT INTO t2249b VALUES(123);
277
-- Because a is type TEXT and b is type INTEGER, both a and b
278
-- will attempt to convert to NUMERIC before the comparison.
279
-- They will thus compare equal.
281
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
283
} {123 0123 nosort t2249b {} t2249a {}}
286
-- The + operator removes affinity from the rhs. No conversions
287
-- occur and the comparison is false. The result is an empty set.
289
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
291
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
292
do_test where2-6.9.2 {
293
# The same thing but with the expression flipped around.
295
SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
297
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
298
do_test where2-6.10 {
300
-- Use + on both sides of the comparison to disable indices
301
-- completely. Make sure we get the same result.
303
SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
305
} {nosort t2249b {} t2249a {}}
306
do_test where2-6.11 {
307
# This will not attempt the OR optimization because of the a=b
310
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
312
} {123 0123 nosort t2249b {} t2249a {}}
313
do_test where2-6.11.2 {
314
# Permutations of the expression terms.
316
SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
318
} {123 0123 nosort t2249b {} t2249a {}}
319
do_test where2-6.11.3 {
320
# Permutations of the expression terms.
322
SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
324
} {123 0123 nosort t2249b {} t2249a {}}
325
do_test where2-6.11.4 {
326
# Permutations of the expression terms.
328
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
330
} {123 0123 nosort t2249b {} t2249a {}}
331
ifcapable explain&&subquery {
332
# These tests are not run if subquery support is not included in the
333
# build. This is because these tests test the "a = 1 OR a = 2" to
334
# "a IN (1, 2)" optimisation transformation, which is not enabled if
335
# subqueries and the IN operator is not available.
337
do_test where2-6.12 {
338
# In this case, the +b disables the affinity conflict and allows
339
# the OR optimization to be used again. The result is now an empty
340
# set, the same as in where2-6.9.
342
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
344
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
345
do_test where2-6.12.2 {
346
# In this case, the +b disables the affinity conflict and allows
347
# the OR optimization to be used again. The result is now an empty
348
# set, the same as in where2-6.9.
350
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
352
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
353
do_test where2-6.12.3 {
354
# In this case, the +b disables the affinity conflict and allows
355
# the OR optimization to be used again. The result is now an empty
356
# set, the same as in where2-6.9.
358
SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
360
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
361
do_test where2-6.13 {
362
# The addition of +a on the second term disabled the OR optimization.
363
# But we should still get the same empty-set result as in where2-6.9.
365
SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
367
} {nosort t2249b {} t2249a {}}
370
# Variations on the order of terms in a WHERE clause in order
371
# to make sure the OR optimizer can recognize them all.
372
do_test where2-6.20 {
374
SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
376
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
377
ifcapable explain&&subquery {
378
# These tests are not run if subquery support is not included in the
379
# build. This is because these tests test the "a = 1 OR a = 2" to
380
# "a IN (1, 2)" optimisation transformation, which is not enabled if
381
# subqueries and the IN operator is not available.
383
do_test where2-6.21 {
385
SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
387
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
388
do_test where2-6.22 {
390
SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
392
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
393
do_test where2-6.23 {
395
SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
397
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
261
400
# Unique queries (queries that are guaranteed to return only a single
262
401
# row of result) do not call the sorter. But all tables must give
263
402
# a unique result. If any one table in the join does not give a unique
594
# Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
595
# when we have an index on A and B.
597
ifcapable or_opt&&tclvar {
601
CREATE TABLE t10(a,b,c);
602
INSERT INTO t10 VALUES(1,1,1);
603
INSERT INTO t10 VALUES(1,2,2);
604
INSERT INTO t10 VALUES(1,3,3);
606
for {set i 4} {$i<=1000} {incr i} {
607
execsql {INSERT INTO t10 VALUES(1,$i,$i)}
610
CREATE INDEX i10 ON t10(a,b);
612
SELECT count(*) FROM t10;
618
SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
added
removed
test/where2.test
