forked from MaterializeInc/materialize
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlike.slt
132 lines (103 loc) · 2.43 KB
/
like.slt
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
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
mode cockroach
# We have some tests also in cockroach/like.slt, but those go through const folding. The below tests avoid const folding.
statement ok
CREATE TABLE t(s string, like_pat string, regex_pat string);
statement ok
INSERT INTO t VALUES ('abc', 'a%', 'a.*'), ('ABC', 'a%', 'a.*'), ('ccc', 'a%', 'a.*');
query T multiline
EXPLAIN
SELECT s FROM t WHERE s LIKE 'a%';
----
Explained Query:
Project (#0)
Filter like["a%"](#0)
ReadStorage materialize.public.t
Source materialize.public.t
filter=(like["a%"](#0))
EOF
query T
SELECT s FROM t WHERE s LIKE 'a%';
----
abc
query T multiline
EXPLAIN
SELECT s FROM t WHERE s ILIKE 'a%';
----
Explained Query:
Project (#0)
Filter ilike["a%"](#0)
ReadStorage materialize.public.t
Source materialize.public.t
filter=(ilike["a%"](#0))
EOF
query T
SELECT s FROM t WHERE s ILIKE 'a%';
----
ABC
abc
query T multiline
EXPLAIN
SELECT s FROM t WHERE s NOT ILIKE 'a%';
----
Explained Query:
Project (#0)
Filter NOT(ilike["a%"](#0))
ReadStorage materialize.public.t
Source materialize.public.t
filter=(NOT(ilike["a%"](#0)))
EOF
query T
SELECT s FROM t WHERE s NOT ILIKE 'a%';
----
ccc
query T multiline
EXPLAIN
SELECT s FROM t WHERE NOT (s ILIKE 'a%');
----
Explained Query:
Project (#0)
Filter NOT(ilike["a%"](#0))
ReadStorage materialize.public.t
Source materialize.public.t
filter=(NOT(ilike["a%"](#0)))
EOF
# Binary versions (MirScalarExpr::reduce changes them into unary when the pattern is a constant, which we prevent here.)
query T multiline
EXPLAIN
SELECT s FROM t WHERE s LIKE like_pat;
----
Explained Query:
Project (#0)
Filter (#0 like #1)
ReadStorage materialize.public.t
Source materialize.public.t
filter=((#0 like #1))
EOF
query T
SELECT s FROM t WHERE s LIKE like_pat;
----
abc
query T multiline
EXPLAIN
SELECT s FROM t WHERE s ILIKE like_pat;
----
Explained Query:
Project (#0)
Filter (#0 ilike #1)
ReadStorage materialize.public.t
Source materialize.public.t
filter=((#0 ilike #1))
EOF
query T
SELECT s FROM t WHERE s ILIKE like_pat;
----
ABC
abc