modules/sql-engine/src/integrationTest/sql/group1/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: negative substring length
SELECT substring(s, 2, -2) FROM strings ORDER BY id

# negative offset and negative length
statement error: negative substring length
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

# BIGINT
query T
SELECT SUBSTRING('1234567', 1, 9223372036854775807)
----
1234567

# BIGINT
query T
SELECT SUBSTRING('1234567', 9223372036854775807)
----
(empty)

# BIGINT
query T
SELECT SUBSTRING('1234567', 1::BIGINT)
----
1234567

# The second argument is a BIGINT
query T
SELECT SUBSTRING('1234567', -9223372036854775808)
----
1234567

statement error: negative substring length
SELECT SUBSTRING('1234567', -9223372036854775808, -9223372036854775808)

statement error: negative substring length
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: negative substring length
SELECT substring(s, -2147483647, -2147483647) FROM strings ORDER BY id

statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <INTEGER> FOR <DECIMAL(2, 1)>)
SELECT SUBSTRING('1234567', 2, 3.1)

statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)> FOR <INTEGER>)
SELECT SUBSTRING('1234567', 2.1, 3)

statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)> FOR <DECIMAL(2, 1)>)
SELECT SUBSTRING('1234567', 2.1, 3.1)

statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <INTEGER> FOR <CHAR(1)>)'
SELECT SUBSTRING('1234567', 2, '1')

statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <CHAR(1)> FOR <INTEGER>)'
SELECT SUBSTRING('1234567', '2', 1)

# https://issues.apache.org/jira/browse/IGNITE-22417
# Actual error while resolving method 'substring[class java.lang.String, class java.math.BigDecimal]' in class class org.apache.calcite.runtime.SqlFunctions
skipif ignite3
statement error: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)>
SELECT SUBSTRING('1234567', 2.1)