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
|
# name: test/optimizer/like_optimizer.test
# description: Test Like Optimization Rules
# group: [optimizer]
statement ok
CREATE TABLE test(S VARCHAR);
statement ok
INSERT INTO test VALUES ('aaa');
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY;
# no special symbols optimization: aaa -> S=a
query I nosort nosymbols
EXPLAIN SELECT S LIKE 'aaa' FROM test
----
query I nosort nosymbols
EXPLAIN SELECT S='aaa' FROM test
----
# prefix optimization: aaa% -> prefix(aaa)
query I nosort prefix
EXPLAIN SELECT S LIKE 'aaa%' FROM test
----
query I nosort prefix
EXPLAIN SELECT prefix(S, 'aaa') FROM test
----
query I nosort prefix
EXPLAIN SELECT S LIKE 'aaa%%%%' FROM test
----
# suffix optimization: %aaa -> suffix(aaa)
query I nosort suffix
EXPLAIN SELECT S LIKE '%aaa' FROM test
----
query I nosort suffix
EXPLAIN SELECT suffix(S, 'aaa') FROM test
----
query I nosort suffix
EXPLAIN SELECT S LIKE '%%%%aaa' FROM test
----
# contains optimization: %aaa% -> contains(aaa)
query I nosort contains
EXPLAIN SELECT S LIKE '%aaa%' FROM test
----
query I nosort contains
EXPLAIN SELECT contains(S, 'aaa') FROM test
----
query I nosort contains
EXPLAIN SELECT S LIKE '%%%%aaa%%' FROM test
----
# NOT LIKE
# no special symbols optimization: aaa -> S<>a
query I nosort notlikenosymbols
EXPLAIN SELECT S NOT LIKE 'aaa' FROM test
----
query I nosort notlikenosymbols
EXPLAIN SELECT S<>'aaa' FROM test
----
# prefix optimization: aaa% -> prefix(aaa)
query I nosort notlikeprefix
EXPLAIN SELECT S NOT LIKE 'aaa%' FROM test
----
query I nosort notlikeprefix
EXPLAIN SELECT NOT(prefix(S, 'aaa')) FROM test
----
query I nosort notlikeprefix
EXPLAIN SELECT S NOT LIKE 'aaa%%%%' FROM test
----
# suffix optimization: %aaa -> suffix(aaa)
query I nosort notlikesuffix
EXPLAIN SELECT S NOT LIKE '%aaa' FROM test
----
query I nosort notlikesuffix
EXPLAIN SELECT NOT(suffix(S, 'aaa')) FROM test
----
query I nosort notlikesuffix
EXPLAIN SELECT S NOT LIKE '%%%%aaa' FROM test
----
# contains optimization: %aaa% -> contains(aaa)
query I nosort notlikecontains
EXPLAIN SELECT S NOT LIKE '%aaa%' FROM test
----
query I nosort notlikecontains
EXPLAIN SELECT NOT(contains(S, 'aaa')) FROM test
----
query I nosort notlikecontains
EXPLAIN SELECT S NOT LIKE '%%%%aaa%%' FROM test
----
|
