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