Materialize/test/sqllogictest/transform/fold_vs_dataflow/4_text_dataflow.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');

statement ok
CREATE TABLE t_operator (op_val TEXT);

statement ok
INSERT INTO t_operator VALUES ('%bCd%');

# DIFF TO CONSTANT FOLDING (ILIKE on TEXT)!
# to be addressed with https://github.com/MaterializeInc/database-issues/issues/5462
query T
SELECT * FROM t_data
WHERE f1 ILIKE (SELECT op_val FROM t_operator)
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 (SELECT op_val FROM t_operator);
----
Explained Query:
  Project (#0{f1})
    Filter (#0{f1} ilike #1{op_val})
      CrossJoin type=differential
        ArrangeBy keys=[[]]
          ReadStorage materialize.public.t_data
        ArrangeBy keys=[[]]
          Union
            ReadStorage materialize.public.t_operator
            Project (#1)
              FlatMap guard_subquery_size(#0{count})
                Reduce aggregates=[count(*)]
                  Project ()
                    ReadStorage materialize.public.t_operator

Source materialize.public.t_data
Source materialize.public.t_operator

Target cluster: quickstart

EOF

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

statement ok
CREATE TABLE t_using_dataflow_rendering (f1 TEXT, f2 TEXT, f3 TEXT, f4 TEXT, f1ls TEXT, f1rs TEXT);

statement ok
INSERT INTO t_using_dataflow_rendering VALUES ('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 t_using_dataflow_rendering;
----
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 t_using_dataflow_rendering;
----
Explained Query:
  Project (#6..=#22)
    Map ((#0{f1} || #3{f4}), upper(#0{f1}), lower(#0{f1}), substr(#0{f1}, 1), replace(#0{f1}, #1{f2}, #2{f3}), position(#1{f2}, #0{f1}), split_string(#0{f1}, #1{f2}, 1), translate(#0{f1}, "C", "Z"), btrim(#4{f1ls}), ltrim(#4{f1ls}), btrim(#5{f1rs}), rtrim(#5{f1rs}), lpad(#0{f1}, 1), lpad(#0{f1}, 10), lpad(#0{f1}, 10, #1{f2}), regexp_match(#0{f1}, #1{f2}), regexp_match(#0{f1}, #2{f3}, "i"))
      ReadStorage materialize.public.t_using_dataflow_rendering

Source materialize.public.t_using_dataflow_rendering

Target cluster: quickstart

EOF