Materialize/test/sqllogictest/transform/fold_vs_dataflow/4_text_folding.slt

# 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.

# use mode standard due to spaces (and to allow easier file comparisons with the other computation mode)
mode standard

# -------------------------------
# ILIKE operator
# -------------------------------

statement ok
CREATE TABLE t_data (f1 TEXT);

statement ok
INSERT INTO t_data VALUES ('ABCDEF'), ('abcdef'), ('aBcDeF'), ('AbCdEf');

query T
SELECT * FROM t_data
WHERE f1 ILIKE '%bCd%'
ORDER BY f1;
----
ABCDEF
AbCdEf
aBcDeF
abcdef

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t_data
WHERE f1 ILIKE '%bCd%';
----
Explained Query:
  Filter ilike["%bCd%"](#0{f1})
    ReadStorage materialize.public.t_data

Source materialize.public.t_data
  filter=(ilike["%bCd%"](#0{f1}))

Target cluster: quickstart

EOF

# -------------------------------
# Further text operators
# -------------------------------

statement ok
CREATE VIEW v_using_constant_folding (f1, f2, f3, f4, f1ls, f1rs) AS
SELECT
 'ABCDef', 'CD', 'cd', 'XX', ' ABCDef', 'ABCDef ';

query TTTTTTTTTTTTTTTTT
SELECT
 f1 || f4,
 UPPER(f1), LOWER(f1),
 SUBSTRING(f1, 1),
 REPLACE(f1, f2, f3),
 POSITION(f2 IN f1),
 SPLIT_PART(f1, f2, 1),
 TRANSLATE(f1, 'C', 'Z'),
 BTRIM(f1ls), LTRIM(f1ls),
 BTRIM(f1rs), RTRIM(f1rs),
 LPAD(f1, 1),
 LPAD(f1, 10),
 LPAD(f1, 10, f2),
 REGEXP_MATCH(f1, f2),
 REGEXP_MATCH(f1, f3, 'i') AS case_insensitive
FROM v_using_constant_folding;
----
ABCDefXX
ABCDEF
abcdef
ABCDef
ABcdef
3
AB
ABZDef
ABCDef
ABCDef
ABCDef
ABCDef
A
    ABCDef
CDCDABCDef
{CD}
{CD}

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT
 f1 || f4,
 UPPER(f1), LOWER(f1),
 SUBSTRING(f1, 1),
 REPLACE(f1, f2, f3),
 POSITION(f2 IN f1),
 SPLIT_PART(f1, f2, 1),
 TRANSLATE(f1, 'C', 'Z'),
 BTRIM(f1ls), LTRIM(f1ls),
 BTRIM(f1rs), RTRIM(f1rs),
 LPAD(f1, 1),
 LPAD(f1, 10),
 LPAD(f1, 10, f2),
  REGEXP_MATCH(f1, f2),
 REGEXP_MATCH(f1, f3, 'i') AS case_insensitive
FROM v_using_constant_folding;
----
Explained Query (fast path):
  Constant
    - ("ABCDefXX", "ABCDEF", "abcdef", "ABCDef", "ABcdef", 3, "AB", "ABZDef", "ABCDef", "ABCDef", "ABCDef", "ABCDef", "A", "    ABCDef", "CDCDABCDef", {"CD"}, {"CD"})

Target cluster: quickstart

EOF