Materialize/test/sqllogictest/like.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.

mode cockroach

# We have some tests also in cockroach/like.slt, but those go through const folding. The below tests avoid const folding.

# Run all tests on a multi-process cluster, because there were some serialization issues in the past that happened
# only on multi-process clusters.
statement ok
CREATE CLUSTER multiprocess REPLICAS (a (SIZE '2-2'));

statement ok
SET cluster = 'multiprocess';

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 OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT s FROM t WHERE s LIKE 'a%';
----
Explained Query:
  Project (#0{s})
    Filter like["a%"](#0{s})
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=(like["a%"](#0{s}))

Target cluster: multiprocess

EOF

query T
SELECT s FROM t WHERE s LIKE 'a%';
----
abc

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

Source materialize.public.t
  filter=(ilike["a%"](#0{s}))

Target cluster: multiprocess

EOF

query T
SELECT s FROM t WHERE s ILIKE 'a%';
----
ABC
abc

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

Source materialize.public.t
  filter=(NOT(ilike["a%"](#0{s})))

Target cluster: multiprocess

EOF

query T
SELECT s FROM t WHERE s NOT ILIKE 'a%';
----
ccc


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

Source materialize.public.t
  filter=(NOT(ilike["a%"](#0{s})))

Target cluster: multiprocess

EOF

# Binary versions (MirScalarExpr::reduce changes them into unary when the pattern is a constant, which we prevent here.)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT s FROM t WHERE s LIKE like_pat;
----
Explained Query:
  Project (#0{s})
    Filter (#0{s} like #1{like_pat})
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=((#0{s} like #1{like_pat}))

Target cluster: multiprocess

EOF

query T
SELECT s FROM t WHERE s LIKE like_pat;
----
abc

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT s FROM t WHERE s ILIKE like_pat;
----
Explained Query:
  Project (#0{s})
    Filter (#0{s} ilike #1{like_pat})
      ReadStorage materialize.public.t

Source materialize.public.t
  filter=((#0{s} ilike #1{like_pat}))

Target cluster: multiprocess

EOF

query T
SELECT s FROM t WHERE s ILIKE like_pat;
----
ABC
abc

# Regression test for https://github.com/MaterializeInc/database-issues/issues/7790

statement ok
CREATE TABLE t2 (text_val TEXT);

statement ok
INSERT INTO t2 VALUES ('abc');

statement ok
CREATE CLUSTER test SIZE '2-1';

statement ok
SET cluster = test;

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

query B
SELECT text_val NOT ILIKE '%A%' FROM t2;
----
false

# Test that % matches \n
query B
SELECT E'test line 1\ntest line 2' LIKE '%1%2%';
----
true

statement ok
INSERT INTO t2 VALUES ('test line 1\ntest line 2');

query B
SELECT text_val LIKE '%1%2%' FROM t2;
----
false
true

statement ok
DROP CLUSTER multiprocess;