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

# name: test/sql/function/string/test_substring.test
# description: Substring test
# group: [string]

statement ok
CREATE TABLE strings(id INTEGER, s VARCHAR, off INTEGER, length INTEGER);

statement ok
INSERT INTO strings VALUES (0, 'hello', 1, 2), (1, 'world', 2, 3), (2, 'b', 1, 1), (3, NULL, 2, 2)

# test zero length
query TT
SELECT SUBSTRING('🦆ab', 1, 0), SUBSTRING('abc', 1, 0)
----
(empty)	(empty)

# constant offset/length
# normal substring
query T
SELECT substring(s from 1 for 2) FROM strings ORDER BY id
----
he
wo
b
NULL

# substring out of range
query T
SELECT substring(s from 2 for 2) FROM strings ORDER BY id
----
el
or
(empty)
NULL

# variable length offset/length
query T
SELECT substring(s from off for length) FROM strings ORDER BY id
----
he
orl
b
NULL

query T
SELECT substring(s from off for 2) FROM strings ORDER BY id
----
he
or
b
NULL

query T
SELECT substring(s from 1 for length) FROM strings ORDER BY id
----
he
wor
b
NULL

query T
SELECT substring('hello' from off for length) FROM strings ORDER BY id
----
he
ell
h
el

# test substrings with constant nulls in different places
query T
SELECT substring(NULL from off for length) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring('hello' from NULL::int for length) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring('hello' from off for NULL::int) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring(NULL from NULL::int for length) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring('hello' from NULL::int for NULL::int) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring(NULL from off for NULL::int) FROM strings
----
NULL
NULL
NULL
NULL

query T
SELECT substring(NULL from NULL::int for NULL::int) FROM strings
----
NULL
NULL
NULL
NULL

# Negative from
query T
SELECT substring(s from -2 for 2) FROM strings ORDER BY id
----
(empty)
(empty)
(empty)
NULL

# zero offset (this is accepted by SQLite)
query T
SELECT substring(s from 0 for length) FROM strings ORDER BY id
----
h
wo
(empty)
NULL

# Negative length
statement error
SELECT substring(s, 2, -2) FROM strings ORDER BY id

# negative offset and negative length
statement error
SELECT substring(s, -2, -2) FROM strings ORDER BY id

# length 0
query T
SELECT substring(s, 2, 0) FROM strings ORDER BY id
----
(empty)
(empty)
(empty)
NULL

# no length
query T
SELECT substring(s, 2) FROM strings ORDER BY id
----
ello
orld
(empty)
NULL

# very large offset and length
query T
SELECT substring(s, 2147483647, 2147483647) FROM strings ORDER BY id
----
(empty)
(empty)
(empty)
NULL

statement error
SELECT substring(s, 2147483647, -2147483647) FROM strings ORDER BY id

query T
SELECT substring(s, -2147483647, 2147483647) FROM strings ORDER BY id
----
(empty)
(empty)
(empty)
NULL

statement error
SELECT substring(s, -2147483647, -2147483647) FROM strings ORDER BY id