1# 2015-03-12 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# Test that deterministic scalar functions passed constant arguments 12# are used with stat4 data. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set ::testprefix analyzeF 18 19ifcapable {!stat4} { 20 finish_test 21 return 22} 23 24proc isqrt {i} { expr { int(sqrt($i)) } } 25db func isqrt isqrt 26 27do_execsql_test 1.0 { 28 CREATE TABLE t1(x INTEGER, y INTEGER); 29 WITH data(i) AS ( 30 SELECT 1 UNION ALL SELECT i+1 FROM data 31 ) 32 INSERT INTO t1 SELECT isqrt(i), isqrt(i) FROM data LIMIT 400; 33 CREATE INDEX t1x ON t1(x); 34 CREATE INDEX t1y ON t1(y); 35 ANALYZE; 36} 37 38proc str {a} { return $a } 39db func str str 40 41# Note: tests 7 to 12 might be unstable - as they assume SQLite will 42# prefer the expression to the right of the AND clause. Which of 43# course could change. 44# 45# Note 2: tests 9 and 10 depend on the tcl interface creating functions 46# without the SQLITE_DETERMINISTIC flag set. 47# 48foreach {tn where idx} { 49 1 "x = 4 AND y = 19" {t1x (x=?)} 50 2 "x = 19 AND y = 4" {t1y (y=?)} 51 3 "x = '4' AND y = '19'" {t1x (x=?)} 52 4 "x = '19' AND y = '4'" {t1y (y=?)} 53 5 "x = substr('5195', 2, 2) AND y = substr('145', 2, 1)" {t1y (y=?)} 54 6 "x = substr('145', 2, 1) AND y = substr('5195', 2, 2)" {t1x (x=?)} 55 56 7 "x = substr('5195', 2, 2+0) AND y = substr('145', 2, 1+0)" {t1y (y=?)} 57 8 "x = substr('145', 2, 1+0) AND y = substr('5195', 2, 2+0)" {t1y (y=?)} 58 59 9 "x = str('19') AND y = str('4')" {t1y (y=?)} 60 10 "x = str('4') AND y = str('19')" {t1y (y=?)} 61 62 11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)} 63 12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)} 64} { 65 set res "SEARCH t1 USING INDEX $idx" 66 do_eqp_test 1.$tn "SELECT * FROM t1 WHERE $where" $res 67} 68 69# Test that functions that do not exist - "func()" - do not cause an error. 70# 71do_catchsql_test 2.1 { 72 SELECT * FROM t1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3) 73} {1 {no such function: func}} 74do_catchsql_test 2.2 { 75 UPDATE t1 SET y=y+1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3) 76} {1 {no such function: func}} 77 78 79# Check that functions that accept zero arguments do not cause problems. 80# 81proc ret {x} { return $x } 82 83db func det4 -deterministic [list ret 4] 84db func nondet4 [list ret 4] 85db func det19 -deterministic [list ret 19] 86db func nondet19 [list ret 19] 87 88foreach {tn where idx} { 89 1 "x = det4() AND y = det19()" {t1x (x=?)} 90 2 "x = det19() AND y = det4()" {t1y (y=?)} 91 92 3 "x = nondet4() AND y = nondet19()" {t1y (y=?)} 93 4 "x = nondet19() AND y = nondet4()" {t1y (y=?)} 94} { 95 set res "SEARCH t1 USING INDEX $idx" 96 do_eqp_test 3.$tn "SELECT * FROM t1 WHERE $where" $res 97} 98 99 100execsql { DELETE FROM t1 } 101 102proc throw_error {err} { error $err } 103db func error -deterministic throw_error 104do_catchsql_test 4.1 { 105 SELECT * FROM t1 WHERE x = error('error one') AND y = 4; 106} {1 {error one}} 107 108do_catchsql_test 4.2 { 109 SELECT * FROM t1 WHERE x = zeroblob(2200000000) AND y = 4; 110} {1 {string or blob too big}} 111 112sqlite3_limit db SQLITE_LIMIT_LENGTH 1000000 113proc dstr {} { return [string repeat x 1100000] } 114db func dstr -deterministic dstr 115do_catchsql_test 4.3 { 116 SELECT * FROM t1 WHERE x = dstr() AND y = 11; 117} {1 {string or blob too big}} 118 119do_catchsql_test 4.4 { 120 SELECT * FROM t1 WHERE x = test_zeroblob(1100000) AND y = 4; 121} {1 {string or blob too big}} 122 123# 2016-12-08: Constraints of the form "x=? AND x IS NOT NULL" were being 124# mishandled. The sqlite3Stat4ProbeSetValue() routine was assuming that 125# valueNew() was returning a Mem object that was preset to NULL, which is 126# not the case. The consequence was the the "x IS NOT NULL" constraint 127# was used to drive the index (via the "x>NULL" pseudo-constraint) rather 128# than the "x=?" constraint. 129# 130do_execsql_test 5.1 { 131 DROP TABLE IF EXISTS t1; 132 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT); 133 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10000) 134 INSERT INTO t1(a, c) SELECT x, x FROM c; 135 UPDATE t1 SET b=printf('x%02x',a/500) WHERE a>4000; 136 UPDATE t1 SET b='xyz' where a>=9998; 137 CREATE INDEX t1b ON t1(b); 138 ANALYZE; 139 SELECT count(*), b FROM t1 GROUP BY 2 ORDER BY 2; 140} {4000 {} 499 x08 500 x09 500 x0a 500 x0b 500 x0c 500 x0d 500 x0e 500 x0f 500 x10 500 x11 500 x12 498 x13 3 xyz} 141do_execsql_test 5.2 { 142 explain query plan 143 SELECT * FROM t1 WHERE b='xyz' AND b IS NOT NULL ORDER BY +a; 144 /* v---- Should be "=", not ">" */ 145} {/USING INDEX t1b .b=/} 146do_execsql_test 5.3 { 147 SELECT * FROM t1 WHERE b='xyz' AND b IS NOT NULL ORDER BY +a; 148} {9998 xyz 9998 9999 xyz 9999 10000 xyz 10000} 149 150finish_test 151