modules/sql-engine/src/integrationTest/sql/group1/function/string/regex_search.test

# name: test/sql/function/string/regex_search.test
# description: regex search test
# group: [string]

# constant strings
query T
SELECT 'asdf' ~ '.*sd.*'
----
true

query T
SELECT 'asdf' ~ '.*yu.*'
----
false

query T
SELECT 'asdf' ~ ''
----
true

# partial matches okay
query T
SELECT 'asdf' ~ 'sd'
----
true

query T
SELECT 'asdf' ~ '^sd$'
----
false

query T
SELECT 'asdf' ~ '^.sd.$'
----
true

query T
SELECT 'asdf' ~ '^sdf$'
----
false

query T
SELECT 'abcd' ~ 'ab[cd]'
----
true

query T
SELECT 'abcd' ~ 'ab[cd]$'
----
false

query T
SELECT 'abcd' ~ 'ab[CD]'
----
false

query T
SELECT 'abcd' ~* 'ab[cd]'
----
true

query T
SELECT 'abcd' ~ 'ab[cd]$'
----
false

query T
SELECT 'abcd' ~ 'ab[CD]'
----
false

query T
SELECT 'abcd' ~* 'ab[cd]'
----
true

query T
SELECT 'abcd' ~* 'ab[cd]$'
----
false

query T
SELECT 'abcd' ~* 'ab[CD]'
----
true

query T
SELECT 'abcd' !~ 'ab[cd]'
----
false

query T
SELECT 'abcd' !~ 'ab[cd]$'
----
true

query T
SELECT 'abcd' !~ 'ab[CD]'
----
true

query T
SELECT 'abcd' !~* 'ab[cd]'
----
false

query T
SELECT 'abcd' !~* 'ab[cd]$'
----
true

query T
SELECT 'abcd' !~* 'ab[CD]'
----
false

# empty strings
query T
SELECT '' ~ '.*yu.*'
----
false

query T
SELECT '' ~ '.*'
----
true

# NULLs
query T
SELECT 'asdf' ~ CAST(NULL AS VARCHAR)
----
NULL

query T
SELECT CAST(NULL AS VARCHAR) ~ '.*sd.*'
----
NULL

query T
SELECT CAST(NULL AS VARCHAR) ~ CAST(NULL AS VARCHAR)
----
NULL

query T
SELECT null ~ 'ab[cd]'
----
null

query T
SELECT 'abcd' ~ null
----
null

query T
SELECT null ~ null
----
null

query T
SELECT null ~* 'ab[cd]'
----
null

query T
SELECT null ~* 'ab[cd]'
----
null

query T
SELECT null ~* null
----
null

query T
SELECT null !~ 'ab[cd]'
----
null

query T
SELECT null !~ null
----
null

query T
SELECT null !~* 'ab[cd]'
----
null

query T
SELECT null !~* null
----
null

query T
SELECT 'foobarbequebaz' ~ '(bar)(beque)'
----
true

# postgres says throw error on invalid regex
statement error: java.util.regex.PatternSyntaxException: Unclosed character class near index 3
SELECT '' ~ '[a-z'

skipif ignite3
# postgres says throw error on invalid regex
# Query Error: error: invalid regular expression: invalid escape \ sequence
# https://issues.apache.org/jira/browse/IGNITE-22930
statement error
SELECT '' ~ '\X'

statement ok
CREATE TABLE regex(s VARCHAR, p VARCHAR)

statement ok
INSERT INTO regex VALUES ('asdf', 'sd'), ('asdf', '^sd'), (NULL, '^sd'), ('asdf', NULL)

query T
SELECT s ~ '.*' FROM regex ORDER BY s
----
true
true
true
NULL

query T
SELECT s ~ p FROM regex order by s, p
----
false
true
NULL
NULL

# test regex with case sensitivity
query T
SELECT 'asdf' ~* '.*SD.*'
----
true

query T
SELECT 'asdf' ~ '.*SD.*'
----
false

# this also works with tables
statement ok
CREATE TABLE test(v VARCHAR);

statement ok
INSERT INTO test VALUES ('hello'), ('HELLO');

query T
SELECT v ~* 'h.*' FROM test ORDER BY v
----
true
true

query T
SELECT v ~ 'h.*' FROM test ORDER BY v
----
false
true

# https://issues.apache.org/jira/browse/IGNITE-15562
# multiline regex string
skipif ignite3
query T
SELECT 'hello
 world' ~ '^.*$'
----
true