Materialize/test/sqllogictest/postgres/regex.slt

# Copyright 1994, Regents of the University of California.
# Copyright 1996-2019 PostgreSQL Global Development Group.
# 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 regression test suite in PostgreSQL.
# The original file was retrieved on October 23, 2020 from:
#
#     https://github.com/postgres/postgres/blob/783f0cc64dcc05e3d112a06b1cd181e5a1ca9099/src/test/regress/expected/regex.out
#
# The original source code is subject to the terms of the PostgreSQL
# license, a copy of which can be found in the LICENSE file at the
# root of this repository.

statement ok
CREATE TABLE strings (s text)

statement ok
INSERT INTO strings VALUES ('abc'), ('123')

query T
select regexp_match('abc', '')
----
{""}

query T
select regexp_match('abc', 'bc')
----
{bc}

query T
select regexp_match('abc', 'd') is null
----
true

query T
select regexp_match('abc', '(B)(c)', 'i')
----
{b,c}

query T
select regexp_match('abc', '(b)(c)(d)?')
----
{b,c,NULL}

query T rowsort
select regexp_match(s, '^.(.)')[1] FROM strings
----
2
b

# Test nullability

query B
SELECT regexp_match('a', 'a') IS NULL
----
false

query B
SELECT regexp_match(NULL, 'a') IS NULL
----
true

query B
SELECT regexp_match('a', NULL) IS NULL
----
true

query B
SELECT regexp_match(NULL, 'a', 'i') IS NULL
----
true

query B
SELECT regexp_match('a', NULL, 'i') IS NULL
----
true

query B
SELECT regexp_match('a', 'a', NULL) IS NULL
----
true