@@ -187,3 +187,135 @@ sensor_B Utf8
187187
188188statement ok
189189DROP TABLE ree_readings;
190+
191+ # LIKE keeps an outer REE layer over Utf8 or dictionary-encoded Utf8.
192+ # A dictionary around REE or another dictionary still requires coercion.
193+ statement ok
194+ CREATE TABLE ree_strings AS
195+ SELECT id,
196+ arrow_cast(value, 'RunEndEncoded("run_ends": non-null Int32, "values": Dictionary(UInt32, Utf8))') AS value,
197+ arrow_cast(value, 'RunEndEncoded("run_ends": non-null Int32, "values": Utf8)') AS plain_value,
198+ arrow_cast(arrow_cast(value, 'Dictionary(UInt32, Utf8)'),
199+ 'Dictionary(UInt32, RunEndEncoded("run_ends": non-null Int32, "values": Utf8))') AS dict_run,
200+ arrow_cast(arrow_cast(value, 'Dictionary(UInt32, Utf8)'),
201+ 'Dictionary(UInt32, Dictionary(UInt32, Utf8))') AS dict_dict,
202+ pattern
203+ FROM (VALUES
204+ (1, 'Ab_c', '%b\_%'),
205+ (2, 'Ab_c', 'a%'),
206+ (3, 'Äpfel', 'ä%'),
207+ (4, NULL, '%'),
208+ (5, 'Other', NULL)
209+ ) AS t(id, value, pattern);
210+
211+ query IBBBBBBBB
212+ SELECT id, value LIKE '%b\_%', value NOT LIKE '%b\_%',
213+ value ILIKE 'ä%', value NOT ILIKE 'ä%',
214+ plain_value LIKE '%b\_%', plain_value NOT LIKE '%b\_%',
215+ plain_value ILIKE 'ä%', plain_value NOT ILIKE 'ä%'
216+ FROM ree_strings ORDER BY id;
217+ ----
218+ 1 true false false true true false false true
219+ 2 true false false true true false false true
220+ 3 false true true false false true true false
221+ 4 NULL NULL NULL NULL NULL NULL NULL NULL
222+ 5 false true false true false true false true
223+
224+ query TT
225+ EXPLAIN SELECT id FROM ree_strings WHERE value ILIKE '%b\_%';
226+ ----
227+ logical_plan
228+ 01)Projection: ree_strings.id
229+ 02)--Filter: ree_strings.value ILIKE Utf8("%b\_%")
230+ 03)----TableScan: ree_strings projection=[id, value]
231+ physical_plan
232+ 01)FilterExec: value@1 ILIKE %b\_%, projection=[id@0]
233+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
234+
235+ query I
236+ SELECT id FROM ree_strings WHERE value ILIKE '%b\_%' ORDER BY id;
237+ ----
238+ 1
239+ 2
240+
241+ query IBBBB
242+ SELECT id, value ILIKE pattern, plain_value ILIKE pattern,
243+ dict_run ILIKE pattern, dict_dict ILIKE pattern
244+ FROM ree_strings ORDER BY id;
245+ ----
246+ 1 true true true true
247+ 2 true true true true
248+ 3 true true true true
249+ 4 NULL NULL NULL NULL
250+ 5 NULL NULL NULL NULL
251+
252+ query TT
253+ EXPLAIN SELECT value ILIKE pattern, plain_value ILIKE pattern,
254+ dict_run ILIKE pattern, dict_dict ILIKE pattern
255+ FROM ree_strings;
256+ ----
257+ logical_plan
258+ 01)Projection: ree_strings.value ILIKE ree_strings.pattern, ree_strings.plain_value ILIKE ree_strings.pattern, CAST(ree_strings.dict_run AS Utf8) ILIKE ree_strings.pattern, CAST(ree_strings.dict_dict AS Utf8) ILIKE ree_strings.pattern
259+ 02)--TableScan: ree_strings projection=[value, plain_value, dict_run, dict_dict, pattern]
260+ physical_plan
261+ 01)ProjectionExec: expr=[value@0 ILIKE pattern@4 as ree_strings.value ILIKE ree_strings.pattern, plain_value@1 ILIKE pattern@4 as ree_strings.plain_value ILIKE ree_strings.pattern, CAST(dict_run@2 AS Utf8) ILIKE pattern@4 as ree_strings.dict_run ILIKE ree_strings.pattern, CAST(dict_dict@3 AS Utf8) ILIKE pattern@4 as ree_strings.dict_dict ILIKE ree_strings.pattern]
262+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
263+
264+ # Nested dictionaries keep their existing coercion for all four operators.
265+ query IBBBBBBBB
266+ SELECT id, dict_run LIKE '%b\_%', dict_run NOT LIKE '%b\_%',
267+ dict_run ILIKE 'ä%', dict_run NOT ILIKE 'ä%',
268+ dict_dict LIKE '%b\_%', dict_dict NOT LIKE '%b\_%',
269+ dict_dict ILIKE 'ä%', dict_dict NOT ILIKE 'ä%'
270+ FROM ree_strings ORDER BY id;
271+ ----
272+ 1 true false false true true false false true
273+ 2 true false false true true false false true
274+ 3 false true true false false true true false
275+ 4 NULL NULL NULL NULL NULL NULL NULL NULL
276+ 5 false true false true false true false true
277+
278+ # A different pattern layout must still coerce the REE operand's values.
279+ query IBB
280+ SELECT id, value ILIKE arrow_cast(pattern, 'LargeUtf8'),
281+ value ILIKE arrow_cast(pattern, 'Utf8View')
282+ FROM ree_strings ORDER BY id;
283+ ----
284+ 1 true true
285+ 2 true true
286+ 3 true true
287+ 4 NULL NULL
288+ 5 NULL NULL
289+
290+ query TT
291+ EXPLAIN SELECT value ILIKE arrow_cast('a%', 'LargeUtf8') FROM ree_strings;
292+ ----
293+ logical_plan
294+ 01)Projection: CAST(ree_strings.value AS LargeUtf8) ILIKE LargeUtf8("a%") AS ree_strings.value ILIKE arrow_cast(Utf8("a%"),Utf8("LargeUtf8"))
295+ 02)--TableScan: ree_strings projection=[value]
296+ physical_plan
297+ 01)ProjectionExec: expr=[CAST(value@0 AS LargeUtf8) ILIKE a% as ree_strings.value ILIKE arrow_cast(Utf8("a%"),Utf8("LargeUtf8"))]
298+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
299+
300+ # SIMILAR TO uses the regex kernel, which still needs decoded strings.
301+ query IB
302+ SELECT id, value SIMILAR TO 'Ab%' FROM ree_strings ORDER BY id;
303+ ----
304+ 1 true
305+ 2 true
306+ 3 false
307+ 4 NULL
308+ 5 false
309+
310+ query TT
311+ EXPLAIN SELECT value SIMILAR TO 'Ab%' FROM ree_strings;
312+ ----
313+ logical_plan
314+ 01)Projection: CAST(ree_strings.value AS Utf8) SIMILAR TO Utf8("Ab%")
315+ 02)--TableScan: ree_strings projection=[value]
316+ physical_plan
317+ 01)ProjectionExec: expr=[CAST(value@0 AS Utf8) ~ ^(?:Ab(?s:.*))$ as ree_strings.value SIMILAR TO Utf8("Ab%")]
318+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
319+
320+ statement ok
321+ DROP TABLE ree_strings;
0 commit comments