# 2017 April 11 # # 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. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix indexexpr2 do_execsql_test 1 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); CREATE INDEX i1 ON t1(b || 'x'); } do_execsql_test 1.1 { SELECT 'TWOX' == (b || 'x') FROM t1 WHERE (b || 'x')>'onex' } {0 0} do_execsql_test 1.2 { SELECT 'TWOX' == (b || 'x') COLLATE nocase FROM t1 WHERE (b || 'x')>'onex' } {0 1} do_execsql_test 2.0 { CREATE INDEX i2 ON t1(a+1); } do_execsql_test 2.1 { SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; } {2 2 3 3 4 4} #------------------------------------------------------------------------- # At one point SQLite was incorrectly using indexes on expressions to # optimize ORDER BY and GROUP BY clauses even when the collation # sequences of the query and index did not match (ticket [e20dd54ab0e4]). # The following tests - 3.* - attempt to verify that this has been fixed. # reset_db do_execsql_test 3.1.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); } {} do_eqp_test 3.1.1 { SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL GROUP BY b COLLATE nocase ORDER BY b COLLATE nocase; } {/USE TEMP B-TREE FOR GROUP BY/} do_execsql_test 3.2.0 { CREATE TABLE t2(x); INSERT INTO t2 VALUES('.ABC'); INSERT INTO t2 VALUES('.abcd'); INSERT INTO t2 VALUES('.defg'); INSERT INTO t2 VALUES('.DEF'); } {} do_execsql_test 3.2.1 { SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.2.2 { CREATE INDEX i2 ON t2( substr(x, 2) ); SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.3.0 { CREATE TABLE t3(x); } ifcapable json1 { do_eqp_test 3.3.1 { SELECT json_extract(x, '$.b') FROM t2 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; } [string map {"\n " \n} { QUERY PLAN |--SCAN TABLE t2 `--USE TEMP B-TREE FOR GROUP BY }] do_execsql_test 3.3.2 { CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); } {} do_eqp_test 3.3.3 { SELECT json_extract(x, '$.b') FROM t3 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; } [string map {"\n " \n} { QUERY PLAN |--SEARCH TABLE t3 USING INDEX i3 (=?) `--USE TEMP B-TREE FOR GROUP BY }] } do_execsql_test 3.4.0 { CREATE TABLE t4(a, b); INSERT INTO t4 VALUES('.ABC', 1); INSERT INTO t4 VALUES('.abc', 2); INSERT INTO t4 VALUES('.ABC', 3); INSERT INTO t4 VALUES('.abc', 4); } do_execsql_test 3.4.1 { SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } do_execsql_test 3.4.2 { CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } do_execsql_test 3.4.3 { DROP INDEX i4; UPDATE t4 SET a = printf('%s%d',a,b); SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} do_execsql_test 3.4.4 { SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} do_execsql_test 3.4.5 { CREATE INDEX i4 ON t4( Substr(a,-2) COLLATE nocase ); SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {.ABC1 1 .abc2 2 .ABC3 3 .abc4 4} do_execsql_test 3.4.5eqp { EXPLAIN QUERY PLAN SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase; } {/SCAN TABLE t4 USING INDEX i4/} do_execsql_test 3.4.6 { SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary; } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4} # 2014-09-15: Verify that UPDATEs of columns not referenced by a # index on expression do not modify the index. # unset -nocomplain cnt set cnt 0 proc refcnt {x} { global cnt incr cnt return $x } db close sqlite3 db :memory: db function refcnt -deterministic refcnt do_test 4.100 { db eval { CREATE TABLE t1(a,b,c,d,e,f); CREATE INDEX t1abc ON t1(refcnt(a+b+c)); } set ::cnt } {0} do_test 4.110 { db eval {INSERT INTO t1 VALUES(1,2,3,4,5,6);} set ::cnt # The refcnt() function is invoked once to compute the index value } {1} do_test 4.120 { set ::cnt 0 db eval {UPDATE t1 SET b=b+1;} set ::cnt # The refcnt() function is invoked twice, once to remove the old index # entry and a second time to insert the new one. } {2} do_test 4.130 { set ::cnt 0 db eval {UPDATE t1 SET d=d+1;} set ::cnt # Refcnt() should not be invoked because that index does not change. } {0} # Additional test cases to show that UPDATE does not modify indexes that # do not involve unchanged columns. # ifcapable vtab { load_static_extension db explain do_execsql_test 4.200 { CREATE TABLE t2(a,b,c,d,e,f); INSERT INTO t2 VALUES(2,3,4,5,6,7); CREATE INDEX t2abc ON t2(a+b+c); CREATE INDEX t2cd ON t2(c*d); CREATE INDEX t2def ON t2(d,e+25*f); SELECT sqlite_master.name FROM sqlite_master, explain('UPDATE t2 SET b=b+1') WHERE explain.opcode LIKE 'Open%' AND sqlite_master.rootpage=explain.p2 ORDER BY 1; } {t2 t2abc} do_execsql_test 4.210 { SELECT sqlite_master.name FROM sqlite_master, explain('UPDATE t2 SET c=c+1') WHERE explain.opcode LIKE 'Open%' AND sqlite_master.rootpage=explain.p2 ORDER BY 1; } {t2 t2abc t2cd} do_execsql_test 4.220 { SELECT sqlite_master.name FROM sqlite_master, explain('UPDATE t2 SET c=c+1, f=NULL') WHERE explain.opcode LIKE 'Open%' AND sqlite_master.rootpage=explain.p2 ORDER BY 1; } {t2 t2abc t2cd t2def} } #------------------------------------------------------------------------- # Test that ticket [d96eba87] has been fixed. # do_execsql_test 5.0 { CREATE TABLE t5(a INTEGER, b INTEGER); INSERT INTO t5 VALUES(2, 4), (3, 9); } do_execsql_test 5.1 { SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; } {2 4 3 9} do_execsql_test 5.2 { CREATE INDEX t5a ON t5( abs(a) ); CREATE INDEX t5b ON t5( abs(b) ); } do_execsql_test 5.4 { SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9; } {2 4 3 9} #------------------------------------------------------------------------- do_execsql_test 6.0 { CREATE TABLE x1(a INTEGER PRIMARY KEY, b); INSERT INTO x1 VALUES (1, 123), (2, '123'), (3, '123abc'), (4, 123.0), (5, 1234); } do_execsql_test 6.1.1 { SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; } {1 123 2 123 3 123abc 4 123.0} do_execsql_test 6.1.2 { CREATE INDEX x1i ON x1( CAST(b AS INTEGER) ); SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; } {1 123 2 123 3 123abc 4 123.0} do_eqp_test 6.1.3 { SELECT a, b FROM x1 WHERE CAST(b AS INTEGER) = 123; } {SEARCH TABLE x1 USING INDEX x1i (=?)} do_execsql_test 6.2.1 { SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; } {1 123 2 123} do_execsql_test 6.2.2 { CREATE INDEX x1i2 ON x1( CAST(b AS TEXT) ); SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; } {1 123 2 123} do_eqp_test 6.2.3 { SELECT a, b FROM x1 WHERE CAST(b AS TEXT) = 123; } {SEARCH TABLE x1 USING INDEX x1i2 (=?)} do_execsql_test 7.0 { CREATE TABLE IF NOT EXISTS t0(c0); INSERT INTO t0(c0) VALUES (-9223372036854775808); BEGIN; } do_catchsql_test 7.1 { CREATE INDEX i0 ON t0(ABS(c0)); } {1 {integer overflow}} do_execsql_test 7.2 { COMMIT; SELECT sql FROM sqlite_master WHERE tbl_name = 't0'; CREATE INDEX i0 ON t0(c0); } {{CREATE TABLE t0(c0)}} do_execsql_test 7.3 { REINDEX; } {} finish_test