Materialize/test/sqllogictest/cockroach/like.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/sem/tree/testdata/eval/like
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

query B
SELECT '' LIKE 'TEST'
----
false

query B
SELECT 'T' LIKE 'TEST'
----
false

query B
SELECT 'TEST' LIKE 'TEST'
----
true

query B
SELECT 'TESTS' LIKE 'TEST'
----
false

query B
SELECT 'TESTESTEST' LIKE 'TEST'
----
false

query B
SELECT 'TEST' LIKE 'test'
----
false

query B
SELECT 'TEST' ILIKE 'test'
----
true

query B
SELECT 'TEST' LIKE 'TESTER'
----
false

query B
SELECT 'TEST' LIKE ''
----
false

query B
SELECT '' LIKE ''
----
true

query B
SELECT '' LIKE '%'
----
true

query B
SELECT '' LIKE '_'
----
false

query B
SELECT 'a
b' LIKE '%'
----
true

query B
SELECT 'a
b' ILIKE '%'
----
true

query B
SELECT 'a'::char(10) LIKE 'a'
----
false

query B
SELECT 'a'::char(10) NOT LIKE 'a'
----
true

query B
SELECT 'a'::char(10) ILIKE 'a'
----
false

query B
SELECT 'a'::char(10) NOT ILIKE 'a'
----
true

query B
SELECT '' LIKE 'foo'
----
false

query B
SELECT 'foo' LIKE 'foo'
----
true

query B
SELECT 'bar' LIKE 'foo'
----
false

query B
SELECT 'foo' LIKE 'food'
----
false

# UNICODE and case folding

query B
SELECT 'TEST' LIKE 'ΜΆΪΟΣ'
----
false

query B
SELECT 'ΜΆΪΟΣ' LIKE 'ΜΆΪΟΣ'
----
true

query B
SELECT 'Μάϊος' LIKE 'ΜΆΪΟΣ'
----
false

query B
SELECT 'Μάϊος' ILIKE 'ΜΆΪΟΣ'
----
true

# Single-character wildcard in middle of pattern

query B
SELECT 'foo' LIKE 'b_t'
----
false

query B
SELECT 'b' LIKE 'b_t'
----
false

query B
SELECT 'bt' LIKE 'b_t'
----
false

query B
SELECT 'but' LIKE 'b_t'
----
true

query B
SELECT 'butt' LIKE 'b_t'
----
false

query B
SELECT 'boot' LIKE 'b__t'
----
true

query B
SELECT 'ii' LIKE '_i_i'
----
false

query B
SELECT 'iii' LIKE '_i_i'
----
false

query B
SELECT 'wifi' LIKE '_i_i'
----
true

query B
SELECT 'wifi!' LIKE '_i_i'
----
false

# Single-character wildcard at beginning of pattern

query B
SELECT 'a' LIKE '_'
----
true

query B
SELECT 'aa' LIKE '_'
----
false

query B
SELECT 't' LIKE '_o'
----
false

query B
SELECT 'to' LIKE '_o'
----
true

query B
SELECT 'too' LIKE '_o'
----
false

query B
SELECT 'to' LIKE '__o'
----
false

query B
SELECT 'too' LIKE '__o'
----
true

query B
SELECT 'tool' LIKE '__o'
----
false

query B
SELECT '漢漢' LIKE '_漢'
----
true

# Single-character wildcard at the end of a pattern

query B
SELECT 'a' LIKE 'a_'
----
false

query B
SELECT 'aa' LIKE 'a_'
----
true

query B
SELECT 'f' LIKE 'f___'
----
false

query B
SELECT 'foo' LIKE 'f___'
----
false

query B
SELECT 'food' LIKE 'f___'
----
true

query B
SELECT 'foods' LIKE 'f___'
----
false

query B
SELECT '漢漢' LIKE '漢_'
----
true

# Multiple-character wildcard at beginning of pattern

query B
SELECT '' LIKE '%a'
----
false

query B
SELECT 'a' LIKE '%a'
----
true

query B
SELECT 'b' LIKE '%a'
----
false

query B
SELECT 'aaa' LIKE '%a'
----
true

query B
SELECT 'baaa' LIKE '%a'
----
true

query B
SELECT 'bad' LIKE '%a'
----
false

query B
SELECT 'a' LIKE '%%%%a'
----
true

query B
SELECT 'baaa' LIKE '%%%%a'
----
true

query B
SELECT 'AAA' LIKE '%AA%A'
----
true

# Multiple-character wildcard in middle of pattern

query B
SELECT '' LIKE 'f%d'
----
false

query B
SELECT 'fd' LIKE 'f%d'
----
true

query B
SELECT 'fa' LIKE 'f%d'
----
false

query B
SELECT 'fad' LIKE 'f%d'
----
true

query B
SELECT 'food' LIKE 'f%d'
----
true

query B
SELECT 'fooooooooooooood' LIKE 'f%d'
----
true

query B
SELECT 'foods' LIKE 'f%d'
----
false

query B
SELECT 'bar' LIKE 'f%d'
----
false

query B
SELECT 'fad' LIKE 'f%%d'
----
true

query B
SELECT 'food' LIKE 'f%%d'
----
true

query B
SELECT 'foods' LIKE 'f%%d'
----
false

query B
SELECT 'food' LIKE 'f%d%e'
----
false

query B
SELECT 'foodie' LIKE 'f%d%e'
----
true

# Multiple-character wildcard at end of pattern

query B
SELECT '' LIKE 'f%'
----
false

query B
SELECT 'f' LIKE 'f%'
----
true

query B
SELECT 'fi' LIKE 'f%'
----
true

query B
SELECT 'foo' LIKE 'f%'
----
true

query B
SELECT 'bar' LIKE 'f%'
----
false

query B
SELECT 'f' LIKE 'f%%%'
----
true

query B
SELECT 'foo' LIKE 'f%%%'
----
true

# Mixed patterns

query B
SELECT 'aaa' LIKE '%aa_'
----
true

query B
SELECT 'fd' LIKE 'f%_d'
----
false

query B
SELECT 'fed' LIKE 'f%_d'
----
true

query B
SELECT 'food' LIKE 'f%_d'
----
true

query B
SELECT 'fd' LIKE 'f_%d'
----
false

query B
SELECT 'fed' LIKE 'f_%d'
----
true

query B
SELECT 'food' LIKE 'f_%d'
----
true

# Regex special characters.

query B
SELECT '[' LIKE '['
----
true

query B
SELECT '.' LIKE '.'
----
true

query B
SELECT '.A' LIKE '._'
----
true

query B
SELECT 'AB' LIKE '._'
----
false

query B
SELECT '.*B' LIKE '.*B'
----
true

query B
SELECT 'AB' LIKE '.*B'
----
false

# Escaped character cases.

query B
SELECT '[' LIKE '\['
----
true

query B
SELECT '.' LIKE '\.'
----
true

query B
SELECT '\' LIKE '\\%'
----
true

query B
SELECT '\' LIKE '%\\'
----
true

query B
SELECT '\' LIKE '%\\%'
----
true

query B
SELECT '\%' LIKE '\\\%'
----
true

query B
SELECT '\.*' LIKE '\\.*'
----
true

query B
SELECT '\.*' LIKE '\\.\*'
----
true

query B
SELECT '\.*' LIKE '\\\.\*'
----
true

query B
SELECT '\\.' LIKE '\\.'
----
false

query B
SELECT '\\.' LIKE '\\\\.'
----
true

query B
SELECT '\\.' LIKE '\\\\\.'
----
true

query B
SELECT '\A' LIKE '\\A'
----
true

query B
SELECT 'A' LIKE '\\A'
----
false

query B
SELECT '_' LIKE '\_'
----
true

query B
SELECT '\' LIKE '\\'
----
true

query B
SELECT 'A\A' LIKE '_\\_'
----
true

query B
SELECT '__' LIKE '_\\_'
----
false

query B
SELECT '\_' LIKE '\\\_'
----
true

query B
SELECT '\\' LIKE '\\'
----
false

query B
SELECT '\\' LIKE '\\_'
----
true

query B
SELECT '\\' LIKE '_\\'
----
true

query B
SELECT 'A\' LIKE '_\\'
----
true

query B
SELECT '%' LIKE '\%'
----
true

query B
SELECT 'ABC' LIKE '\AB%'
----
true

query B
SELECT 'ABC' LIKE '\AB_'
----
true

query B
SELECT 'ABC' LIKE '%B\C'
----
true

query B
SELECT 'ABC' LIKE '_B\C'
----
true

query B
SELECT 'TEST' LIKE 'TE\ST'
----
true

query B
SELECT '_漢' LIKE '\__'
----
true

query B
SELECT '漢漢' LIKE '漢\漢'
----
true

query B
SELECT '_漢' LIKE '\_\漢'
----
true

query B
SELECT 'TEST' LIKE 'TE%'
----
true

query B
SELECT 'TEST' LIKE '%E%'
----
true

query B
SELECT 'TEST' LIKE '%e%'
----
false

query B
SELECT 'TEST' LIKE 'TES_'
----
true

query B
SELECT 'TEST' LIKE 'TE_%'
----
true

query B
SELECT 'TEST' LIKE 'TE_'
----
false

query B
SELECT 'TEST' LIKE '%'
----
true

query B
SELECT 'TEST' LIKE '%R'
----
false

query B
SELECT 'T' LIKE '\_'
----
false

query B
SELECT 'T' LIKE '\%'
----
false

query B
SELECT 'TE_T' LIKE 'TE\_T'
----
true

query B
SELECT 'TE\AT' LIKE 'TE\_T'
----
false

query B
SELECT 'TES%T' LIKE 'TES\%T'
----
true

query B
SELECT 'TES\AT' LIKE 'TES\%T'
----
false

query B
SELECT 'T' LIKE '_'
----
true

query B
SELECT 'TE' LIKE '_'
----
false

query B
SELECT 'TE' LIKE '_%'
----
true

query B
SELECT 'T' LIKE '_%'
----
true

query B
SELECT '' LIKE '_%'
----
false

query B
SELECT 'TE' LIKE '%_'
----
true

query B
SELECT '' LIKE '%_'
----
false

query B
SELECT 'T' LIKE '%_'
----
true

query B
SELECT 'TEST' LIKE '_ES_'
----
true

query B
SELECT '' LIKE '__'
----
false

query B
SELECT 'A' LIKE 'T_'
----
false

query B
SELECT 'A' LIKE '_T'
----
false

query B
SELECT 'TEST' LIKE '_E%'
----
true

query B
SELECT 'TEST' LIKE '_E\%'
----
false

query B
SELECT 'TES_' LIKE '%S\_'
----
true

query B
SELECT 'TES%' LIKE '%S\%'
----
true

query B
SELECT 'TES_' LIKE '_ES\_'
----
true

query B
SELECT 'TES%' LIKE '_ES\%'
----
true

query B
SELECT 'TEST' LIKE '%S_'
----
true

query B
SELECT 'TEST' LIKE '%S\_'
----
false

query B
SELECT 'TEST' NOT LIKE '%E%'
----
false

query B
SELECT 'TEST' NOT LIKE 'TES_'
----
false

query B
SELECT 'TEST' NOT LIKE 'TeS_'
----
true

query B
SELECT 'TEST' NOT LIKE 'TE_'
----
true

# Test custom escape characters

query B
SELECT 'banana!' LIKE 'ban%na!' ESCAPE 'n'
----
false

query B
SELECT 'ba%a!' LIKE 'ban%na!' ESCAPE 'n'
----
true

query B
SELECT 'banana!' LIKE 'ban\ana!' ESCAPE ''
----
false

query B
SELECT 'ban\ana!' LIKE 'ban\ana!' ESCAPE ''
----
true

query B
SELECT 'banana!' LIKE 'ban%%%na!' ESCAPE '%'
----
false

query B
SELECT 'ban%na!' LIKE 'ban%%%na!' ESCAPE '%'
----
true

query B
SELECT 'banana!' LIKE 'ba\n%na!' ESCAPE '\'
----
true

# Test bad escaping.

query error unterminated escape sequence in LIKE
SELECT 'a' LIKE '\'

query error invalid escape string
SELECT 'a' LIKE 'a' ESCAPE 'foo'

# Test massive LIKE patterns:

query error LIKE pattern exceeds maximum length
SELECT 'x' LIKE repeat('x', 367416)

# Test nullability

query B
SELECT ('foo' LIKE 'f%') IS NULL;
----
false

query B
SELECT ('foo' LIKE 'f%' ESCAPE '~') IS NULL;
----
false

query B
SELECT (NULL LIKE 'f%') IS NULL;
----
true

query B
SELECT (NULL LIKE 'f%' ESCAPE '~') IS NULL;
----
true

query B
SELECT ('foo' LIKE NULL) IS NULL;
----
true

query B
SELECT ('foo' LIKE NULL ESCAPE '~') IS NULL;
----
true

query B
SELECT ('foo' LIKE 'f%' ESCAPE NULL) IS NULL;
----
true

# Verify that the PostgreSQL operators work

query B
SELECT 'foo' ~~ 'f%';
----
true

query B
SELECT 'foo' !~~ 'f%';
----
false

query B
SELECT 'foo' ~~ 'F%';
----
false

query B
SELECT 'foo' !~~ 'f%';
----
false

query B
SELECT 'foo' !~~ 'g%';
----
true

query B
SELECT 'foo' ~~* 'F%';
----
true

query B
SELECT 'foo' ~~* 'G%';
----
false

query B
SELECT 'foo' !~~* 'G%';
----
true

query B
SELECT 'foo' !~~* 'f%';
----
false

query B
SELECT (NULL ~~ 'f%') IS NULL;
----
true

query B
SELECT ('foo' ~~ NULL) IS NULL;
----
true

query B
SELECT (NULL ~~* 'f%') IS NULL;
----
true

query B
SELECT ('foo' ~~* NULL) IS NULL;
----
true

query B
SELECT (NULL !~~ 'f%') IS NULL;
----
true

query B
SELECT ('foo' !~~ NULL) IS NULL;
----
true

query B
SELECT (NULL !~~* 'f%') IS NULL;
----
true

query B
SELECT ('foo' !~~* NULL) IS NULL;
----
true