modules/calcite/src/test/sql/function/string/test_like_escape.test

# name: test/sql/function/string/test_like_escape.test
# description: Test LIKE statement with custom ESCAPE
# group: [string]

# scalar like with escape
query T
SELECT '%++' LIKE '*%++' ESCAPE '*';
----
true

# Not Like
query T
SELECT '%++' NOT LIKE '*%++' ESCAPE '*';
----
false

# unterminated escapes
statement error
SELECT '%' LIKE '%' ESCAPE '%';

statement error
SELECT '%' LIKE '*' ESCAPE '*';

# Default tests
query T
SELECT '\' LIKE '\\' ESCAPE '\';
----
true

query T
SELECT '\\' LIKE '\\' ESCAPE '\';
----
false

query T
SELECT '%' LIKE '*%' ESCAPE '*';
----
true

query T
SELECT '_ ' LIKE '*_ ' ESCAPE '*';
----
true

query T
SELECT ' a ' LIKE '*_ ' ESCAPE '*';
----
false

query T
SELECT '*%' NOT LIKE '*%' ESCAPE '*';
----
true

# It should fail when more than one escape character is specified
statement error
SELECT '%_' LIKE '%_' ESCAPE '\\';

statement error
SELECT '%_' LIKE '%_' ESCAPE '**';

# Test LIKE statement with ESCAPE in the middle of the pattern
statement ok
CREATE TABLE strings(s VARCHAR, pat VARCHAR);

statement ok
INSERT INTO strings VALUES ('abab', 'ab%'), ('aaa', 'a*_a'), ('aaa', '*%b'), ('bbb', 'a%');

query T
SELECT s FROM strings ORDER BY s;
----
aaa
aaa
abab
bbb

query T
SELECT pat FROM strings ORDER BY pat;
----
*%b
a%
a*_a
ab%

query T
SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';
----
bbb

query T
SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*' ORDER BY s;
----
abab
bbb

query T
SELECT s FROM strings WHERE s LIKE pat ESCAPE '*';
----
abab