This is an automated email from the ASF dual-hosted git repository.
zstan pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 8d247de7a9 IGNITE-22323: Remove duplicate tests from ItFunctionsTest
(#4187)
8d247de7a9 is described below
commit 8d247de7a9d1ab51665eddb800106b2973fe3520
Author: Max Zhuravkov <[email protected]>
AuthorDate: Thu Aug 8 12:29:18 2024 +0300
IGNITE-22323: Remove duplicate tests from ItFunctionsTest (#4187)
---
.../internal/sql/engine/ItFunctionsTest.java | 325 ---------------------
.../sql/function/numeric/test_mod.test | 9 +
.../function/operator/test_percent_reminder.test | 28 ++
.../sql/function/other/test_system_range.test | 107 +++++++
.../sql/function/other/test_typeof.txt | 80 +++++
.../sql/function/string/regex_replace.test_ignore | 28 +-
.../sql/function/string/regex_search.test | 142 ++++++++-
.../sql/function/string/regex_search.test_ignore | 143 ---------
.../sql/function/string/test_length.test | 5 +
.../sql/function/string/test_replace.test | 25 ++
.../sql/function/string/test_substr.test | 250 ++++++++++++++++
.../sql/function/string/test_substring.test | 67 ++++-
.../sql/function/timestamp/test_timestampdiff.test | 10 +
.../sql/types/blob/test_blob_function.test | 20 ++
14 files changed, 762 insertions(+), 477 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
index da5b5b0d76..649f31067f 100644
---
a/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
+++
b/modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItFunctionsTest.java
@@ -17,14 +17,10 @@
package org.apache.ignite.internal.sql.engine;
-import static org.apache.calcite.util.Static.RESOURCE;
import static org.apache.ignite.internal.lang.IgniteStringFormatter.format;
-import static
org.apache.ignite.internal.sql.engine.util.SqlTestUtils.assertThrowsSqlException;
-import static
org.apache.ignite.internal.testframework.IgniteTestUtils.assertThrowsWithCause;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.instanceOf;
import static org.junit.jupiter.api.Assertions.assertEquals;
-import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.math.BigDecimal;
@@ -39,14 +35,10 @@ import java.time.temporal.Temporal;
import java.util.List;
import java.util.function.Function;
import java.util.stream.Stream;
-import org.apache.calcite.sql.validate.SqlValidatorException;
import org.apache.ignite.internal.sql.BaseSqlIntegrationTest;
import org.apache.ignite.internal.sql.engine.util.MetadataMatcher;
import org.apache.ignite.internal.util.ArrayUtils;
-import org.apache.ignite.lang.ErrorGroups.Sql;
-import org.apache.ignite.lang.IgniteException;
import org.apache.ignite.sql.ColumnType;
-import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.MethodSource;
@@ -55,32 +47,7 @@ import org.junit.jupiter.params.provider.ValueSource;
/**
* Test Ignite SQL functions.
*/
-@SuppressWarnings("ThrowableNotThrown")
public class ItFunctionsTest extends BaseSqlIntegrationTest {
- private static final Object[] NULL_RESULT = { null };
-
- @Test
- public void testTimestampDiffWithFractionsOfSecond() {
- assertQuery("SELECT TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '2022-02-01
10:30:28.000', "
- + "TIMESTAMP '2022-02-01
10:30:28.128')").returns(128000).check();
-
- assertQuery("SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-02-01
10:30:28.000', "
- + "TIMESTAMP '2022-02-01
10:30:28.128')").returns(128000000L).check();
- }
-
- @Test
- public void testLength() {
- assertQuery("SELECT LENGTH('TEST')").returns(4).check();
- assertQuery("SELECT LENGTH(NULL)").returns(NULL_RESULT).check();
- }
-
- @Test
- public void testOctetLength() {
- assertQuery("SELECT OCTET_LENGTH('TEST')").returns(4).check();
- assertQuery("SELECT OCTET_LENGTH('我愛Java')").returns(10).check();
- assertQuery("SELECT OCTET_LENGTH(x'012F')").returns(2).check();
- assertQuery("SELECT OCTET_LENGTH(NULL)").returns(NULL_RESULT).check();
- }
/**
* SQL F051-06 feature. Basic date and time. CURRENT_DATE.
@@ -144,298 +111,6 @@ public class ItFunctionsTest extends
BaseSqlIntegrationTest {
}
}
- @Test
- public void testRange() {
- assertQuery("SELECT * FROM table(system_range(1, 4))")
- .returns(1L)
- .returns(2L)
- .returns(3L)
- .returns(4L)
- .check();
-
- assertQuery("SELECT * FROM table(system_range(1, 4, 2))")
- .returns(1L)
- .returns(3L)
- .check();
-
- assertQuery("SELECT * FROM table(system_range(4, 1, -1))")
- .returns(4L)
- .returns(3L)
- .returns(2L)
- .returns(1L)
- .check();
-
- assertQuery("SELECT * FROM table(system_range(4, 1, -2))")
- .returns(4L)
- .returns(2L)
- .check();
-
- assertEquals(0, sql("SELECT * FROM table(system_range(4, 1))").size());
-
- assertEquals(0, sql("SELECT * FROM table(system_range(null,
1))").size());
-
- assertEquals(0, sql("SELECT * FROM table(system_range(1,
null))").size());
-
- assertThrowsSqlException(
- Sql.RUNTIME_ERR,
- "Increment can't be 0",
- () -> sql("SELECT * FROM table(system_range(1, 1, 0))"));
-
- assertQuery("SELECT (SELECT * FROM table(system_range(4, 1)))")
- .returns(null)
- .check();
-
- assertQuery("SELECT (SELECT * FROM table(system_range(1, 1)))")
- .returns(1L)
- .check();
-
- assertThrowsSqlException(
- Sql.RUNTIME_ERR,
- "Subquery returned more than 1 value",
- () -> sql("SELECT (SELECT * FROM table(system_range(1,
10)))"));
- }
-
- @Test
- public void testRangeWithCache() {
- sql("CREATE TABLE test(id INT PRIMARY KEY, val INT)");
-
- try {
- for (int i = 0; i < 100; i++) {
- sql("INSERT INTO test VALUES (?, ?)", i, i);
- }
-
- // Correlated INNER join.
- assertQuery("SELECT t.val FROM test t WHERE t.val < 5 AND "
- + "t.id in (SELECT x FROM table(system_range(t.val,
t.val))) ")
- .returns(0)
- .returns(1)
- .returns(2)
- .returns(3)
- .returns(4)
- .check();
-
- // Correlated LEFT joins.
- assertQuery("SELECT t.val FROM test t WHERE t.val < 5 AND "
- + "EXISTS (SELECT x FROM table(system_range(t.val, t.val))
WHERE mod(x, 2) = 0) ")
- .returns(0)
- .returns(2)
- .returns(4)
- .check();
-
- assertQuery("SELECT t.val FROM test t WHERE t.val < 5 AND "
- + "NOT EXISTS (SELECT x FROM table(system_range(t.val,
t.val)) WHERE mod(x, 2) = 0) ")
- .returns(1)
- .returns(3)
- .check();
-
- assertQuery("SELECT t.val FROM test t WHERE "
- + "EXISTS (SELECT x FROM table(system_range(t.val, null)))
")
- .check();
-
- // Non-correlated join.
- assertQuery("SELECT t.val FROM test t JOIN table(system_range(1,
50)) as r ON t.id = r.x "
- + "WHERE mod(r.x, 10) = 0")
- .returns(10)
- .returns(20)
- .returns(30)
- .returns(40)
- .returns(50)
- .check();
- } finally {
- sql("DROP TABLE IF EXISTS test");
- }
- }
-
- @Test
- public void testPercentRemainder() {
- assertQuery("SELECT 3 % 2").returns(1).check();
- assertQuery("SELECT 4 % 2").returns(0).check();
- assertQuery("SELECT NULL % 2").returns(NULL_RESULT).check();
- assertQuery("SELECT 3 % NULL::int").returns(NULL_RESULT).check();
- assertQuery("SELECT 3 % NULL").returns(NULL_RESULT).check();
- }
-
- @Test
- public void testNullFunctionArguments() {
- // Don't infer result data type from arguments (result is always
INTEGER_NULLABLE).
- assertQuery("SELECT ASCII(NULL)").returns(NULL_RESULT).check();
- // Inferring result data type from first STRING argument.
- assertQuery("SELECT REPLACE(NULL, '1',
'2')").returns(NULL_RESULT).check();
- // Inferring result data type from both arguments.
- assertQuery("SELECT MOD(1, null)").returns(NULL_RESULT).check();
- // Inferring result data type from first NUMERIC argument.
- assertQuery("SELECT TRUNCATE(NULL, 0)").returns(NULL_RESULT).check();
- // Inferring arguments data types and then inferring result data type
from all arguments.
- assertQuery("SELECT FALSE AND NULL").returns(false).check();
- }
-
- @Test
- public void testReplace() {
- assertQuery("SELECT REPLACE('12341234', '1',
'55')").returns("5523455234").check();
- assertQuery("SELECT REPLACE(NULL, '1',
'5')").returns(NULL_RESULT).check();
- assertQuery("SELECT REPLACE('1', NULL,
'5')").returns(NULL_RESULT).check();
- assertQuery("SELECT REPLACE('11', '1',
NULL)").returns(NULL_RESULT).check();
- assertQuery("SELECT REPLACE('11', '1', '')").returns("").check();
- }
-
- @Test
- public void testMonthnameDayname() {
- assertQuery("SELECT MONTHNAME(DATE
'2021-01-01')").returns("January").check();
- assertQuery("SELECT DAYNAME(DATE
'2021-01-01')").returns("Friday").check();
- }
-
- @Test
- public void testRegex() {
- assertQuery("SELECT 'abcd' ~ 'ab[cd]'").returns(true).check();
- assertQuery("SELECT 'abcd' ~ 'ab[cd]$'").returns(false).check();
- assertQuery("SELECT 'abcd' ~ 'ab[CD]'").returns(false).check();
- assertQuery("SELECT 'abcd' ~* 'ab[cd]'").returns(true).check();
- assertQuery("SELECT 'abcd' ~* 'ab[cd]$'").returns(false).check();
- assertQuery("SELECT 'abcd' ~* 'ab[CD]'").returns(true).check();
- assertQuery("SELECT 'abcd' !~ 'ab[cd]'").returns(false).check();
- assertQuery("SELECT 'abcd' !~ 'ab[cd]$'").returns(true).check();
- assertQuery("SELECT 'abcd' !~ 'ab[CD]'").returns(true).check();
- assertQuery("SELECT 'abcd' !~* 'ab[cd]'").returns(false).check();
- assertQuery("SELECT 'abcd' !~* 'ab[cd]$'").returns(true).check();
- assertQuery("SELECT 'abcd' !~* 'ab[CD]'").returns(false).check();
- assertQuery("SELECT null ~ 'ab[cd]'").returns(NULL_RESULT).check();
- assertQuery("SELECT 'abcd' ~ null").returns(NULL_RESULT).check();
- assertQuery("SELECT null ~ null").returns(NULL_RESULT).check();
- assertQuery("SELECT null ~* 'ab[cd]'").returns(NULL_RESULT).check();
- assertQuery("SELECT 'abcd' ~* null").returns(NULL_RESULT).check();
- assertQuery("SELECT null ~* null").returns(NULL_RESULT).check();
- assertQuery("SELECT null !~ 'ab[cd]'").returns(NULL_RESULT).check();
- assertQuery("SELECT 'abcd' !~ null").returns(NULL_RESULT).check();
- assertQuery("SELECT null !~ null").returns(NULL_RESULT).check();
- assertQuery("SELECT null !~* 'ab[cd]'").returns(NULL_RESULT).check();
- assertQuery("SELECT 'abcd' !~* null").returns(NULL_RESULT).check();
- assertQuery("SELECT null !~* null").returns(NULL_RESULT).check();
- assertThrows(IgniteException.class, () -> sql("SELECT 'abcd' ~
'[a-z'"));
- }
-
- @Test
- public void testCastToBoolean() {
- assertQuery("SELECT 'true'::BOOLEAN").returns(true).check();
- assertQuery("SELECT 'TruE'::BOOLEAN").returns(true).check();
- assertQuery("SELECT 'false'::BOOLEAN").returns(false).check();
- assertQuery("SELECT 'FalsE'::BOOLEAN").returns(false).check();
- assertQuery("SELECT NULL::CHAR::BOOLEAN").returns(NULL_RESULT).check();
- assertQuery("SELECT
?::CHAR::BOOLEAN").withParams(NULL_RESULT).returns(NULL_RESULT).check();
-
- assertThrowsSqlException(Sql.STMT_VALIDATION_ERR,
deriveCannotCastMessage("INTEGER", "BOOLEAN"), () -> sql("SELECT 1::BOOLEAN"));
- assertThrowsSqlException(
- Sql.STMT_VALIDATION_ERR,
- deriveCannotCastMessage("INTEGER", "BOOLEAN"),
- () -> sql("SELECT ?::BOOLEAN", 1));
- assertThrowsSqlException(
- Sql.STMT_VALIDATION_ERR,
- deriveCannotCastMessage("DECIMAL(2, 1)", "BOOLEAN"),
- () -> sql("SELECT 1.0::BOOLEAN"));
- assertThrowsSqlException(
- Sql.STMT_VALIDATION_ERR,
- deriveCannotCastMessage("DOUBLE", "BOOLEAN"),
- () -> sql("SELECT ?::BOOLEAN", 1.0));
- assertThrowsSqlException(Sql.RUNTIME_ERR, "Invalid character for
cast", () -> sql("SELECT '1'::BOOLEAN"));
- assertThrowsSqlException(Sql.RUNTIME_ERR, "Invalid character for
cast", () -> sql("SELECT ?::BOOLEAN", "1"));
- }
-
- private String deriveCannotCastMessage(String fromType, String toType) {
- return RESOURCE.cannotCastValue(fromType, toType).ex().getMessage();
- }
-
- @Test
- public void testTypeOf() {
- assertQuery("SELECT TYPEOF(1)").returns("INTEGER").check();
- assertQuery("SELECT TYPEOF(1.1::DOUBLE)").returns("DOUBLE").check();
- assertQuery("SELECT TYPEOF(1.1::DECIMAL(3, 2))").returns("DECIMAL(3,
2)").check();
- assertQuery("SELECT TYPEOF('a')").returns("CHAR(1)").check();
- assertQuery("SELECT
TYPEOF('a'::varchar(1))").returns("VARCHAR(1)").check();
- assertQuery("SELECT TYPEOF(NULL)").returns("NULL").check();
- assertQuery("SELECT
TYPEOF(NULL::VARCHAR(100))").returns("VARCHAR(100)").check();
- // A compound expression
- assertQuery("SELECT TYPEOF('abcd' || COALESCE('efg',
?))").withParams("2").returns("VARCHAR").check();
-
- // An expression that produces an error
- assertThrowsSqlException(Sql.RUNTIME_ERR, "Invalid input string for
type INTEGER: \"NONE\"",
- () -> sql("SELECT typeof(CAST('NONE' as INTEGER))"));
-
- assertThrowsWithCause(() -> sql("SELECT TYPEOF()"),
SqlValidatorException.class, "Invalid number of arguments");
-
- assertThrowsWithCause(() -> sql("SELECT TYPEOF(1, 2)"),
SqlValidatorException.class, "Invalid number of arguments");
-
- assertThrowsWithCause(() -> sql("SELECT TYPEOF(SELECT 1, 2)"),
IgniteException.class);
- }
-
- /**
- * Tests for {@code SUBSTRING(str, start[, length])} function.
- */
- @Test
- public void testSubstring() {
- assertQuery("SELECT SUBSTRING('1234567', 1,
3)").returns("123").check();
- assertQuery("SELECT SUBSTRING('1234567',
2)").returns("234567").check();
- assertQuery("SELECT SUBSTRING('1234567',
-1)").returns("1234567").check();
- assertQuery("SELECT SUBSTRING(1000, 1, 3)").returns("100").check();
-
- assertQuery("SELECT SUBSTRING(NULL FROM 1 FOR
2)").returns(null).check();
- assertQuery("SELECT SUBSTRING('text' FROM 1 FOR
null)").returns(null).check();
- assertQuery("SELECT SUBSTRING('test' FROM null FOR
2)").returns(null).check();
-
- assertQuery("SELECT SUBSTRING(s from i for l) from (values ('abc',
null, 2)) as t (s, i, l);").returns(null).check();
-
- assertThrowsSqlException(Sql.STMT_VALIDATION_ERR,
- "Cannot apply 'SUBSTRING' to arguments of type
'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)> FOR <DECIMAL(2, 1)>)'",
- () -> sql("SELECT SUBSTRING('1234567', 2.1, 3.1)"));
- assertThrowsSqlException(Sql.STMT_VALIDATION_ERR,
- "Cannot apply 'SUBSTRING' to arguments of type
'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)> FOR <INTEGER>)'",
- () -> sql("SELECT SUBSTRING('1234567', 2.1, 3)"));
- assertThrowsSqlException(Sql.STMT_VALIDATION_ERR,
- "Cannot apply 'SUBSTRING' to arguments of type
'SUBSTRING(<CHAR(7)> FROM <INTEGER> FOR <DECIMAL(2, 1)>)'",
- () -> sql("SELECT SUBSTRING('1234567', 2, 3.1)"));
- // Uncomment after IGNITE-22417
- // assertThrowsSqlException(Sql.STMT_VALIDATION_ERR,
- // "Cannot apply 'SUBSTRING' to arguments of type
'SUBSTRING(<CHAR(7)> FROM <DECIMAL(2, 1)>)'",
- // () -> sql("SELECT SUBSTRING('1234567', 2.1)"));
-
- // type coercion
- assertQuery("SELECT SUBSTRING('1234567', 2,
'1');").returns("2").check();
- assertQuery("SELECT SUBSTRING('1234567', '2',
1);").returns("2").check();
-
- assertQuery(String.format("SELECT SUBSTRING('1234567', 1, %d)",
Long.MAX_VALUE)).returns("1234567").check();
- assertQuery(String.format("SELECT SUBSTRING('1234567', %d)",
Long.MAX_VALUE)).returns("").check();
- assertQuery(String.format("SELECT SUBSTRING('1234567', %d::BIGINT)",
1)).returns("1234567").check();
- assertQuery(String.format("SELECT SUBSTRING('1234567', %d)",
Long.MIN_VALUE)).returns("1234567").check();
- assertQuery(String.format("SELECT SUBSTRING('1234567', %d)",
Integer.MIN_VALUE)).returns("1234567").check();
- assertQuery(String.format("SELECT SUBSTRING('1234567', %d, %d)", -1,
5)).returns("123").check();
-
- assertThrowsSqlException(Sql.RUNTIME_ERR, "negative substring length",
() -> sql("SELECT SUBSTRING('1234567', 1, -1)"));
- assertThrowsSqlException(Sql.RUNTIME_ERR, "negative substring length",
() ->
- sql(String.format("SELECT SUBSTRING('1234567', %d, %d)",
Long.MIN_VALUE, Long.MIN_VALUE)));
- assertThrowsSqlException(Sql.RUNTIME_ERR, "negative substring length",
() -> sql("SELECT SUBSTRING('abcdefg', 1, -3)"));
- assertThrowsSqlException(Sql.RUNTIME_ERR, "negative substring length",
() -> sql("SELECT SUBSTRING('abcdefg' FROM 1 FOR -1)"));
- }
-
- /** Tests LOWER, UPPER functions. */
- @Test
- public void testLowerUpper() {
- assertQuery("SELECT LOWER(NULL)").returns(null).check();
- assertQuery("SELECT LOWER('NULL')").returns("null").check();
- assertQuery("SELECT UPPER(NULL)").returns(null).check();
- assertQuery("SELECT UPPER('NULL')").returns("NULL").check();
- }
-
- /**
- * Tests for {@code SUBSTR(str, start[, length])} function.
- */
- @Test
- public void testSubstr() {
- assertQuery("SELECT SUBSTR('1234567', 1, 3)").returns("123").check();
- assertQuery("SELECT SUBSTR('1234567', 2)").returns("234567").check();
- assertQuery("SELECT SUBSTR('1234567', -1)").returns("1234567").check();
- assertQuery("SELECT SUBSTR(1000, 1, 3)").returns("100").check();
-
- assertThrowsWithCause(() -> sql("SELECT SUBSTR('1234567', 1, -3)"),
IgniteException.class, "negative substring length");
- }
-
@ParameterizedTest(name = "{0}")
@MethodSource("integralTypes")
public void testRoundIntTypes(ParseNum parse, MetadataMatcher matcher) {
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/numeric/test_mod.test
b/modules/sql-engine/src/integrationTest/sql/function/numeric/test_mod.test
index 3ba98dc12a..fe49f7debb 100644
--- a/modules/sql-engine/src/integrationTest/sql/function/numeric/test_mod.test
+++ b/modules/sql-engine/src/integrationTest/sql/function/numeric/test_mod.test
@@ -23,3 +23,12 @@ select CAST(mod(b, 2.1) AS FLOAT) from modme
----
0.900000
+query T
+select MOD(1, null)
+----
+NULL
+
+query T
+select MOD(null, 1)
+----
+NULL
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/operator/test_percent_reminder.test
b/modules/sql-engine/src/integrationTest/sql/function/operator/test_percent_reminder.test
new file mode 100644
index 0000000000..ee4c6f61d2
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/function/operator/test_percent_reminder.test
@@ -0,0 +1,28 @@
+# name: test/sql/function/operator/test_percent_reminder.test
+# description: percent reminder
+# group: [operator]
+
+query I
+SELECT 3 % 2
+----
+1
+
+query I
+SELECT 4 % 2
+----
+0
+
+query T
+SELECT NULL % 2
+----
+null
+
+query T
+SELECT 3 % NULL::int
+----
+null
+
+query T
+SELECT 3 % NULL
+----
+null
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/other/test_system_range.test
b/modules/sql-engine/src/integrationTest/sql/function/other/test_system_range.test
new file mode 100644
index 0000000000..ecc84bf70a
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/function/other/test_system_range.test
@@ -0,0 +1,107 @@
+# name: test/sql/function/other/typeof.test
+# description: SYSTEM_RANGE function
+# group: [other]
+
+query I
+SELECT * FROM system_range(1, 4)
+----
+1
+2
+3
+4
+
+query I
+SELECT * FROM system_range(1, 4)
+----
+1
+2
+3
+4
+
+query I
+SELECT * FROM system_range(1, 4, 2)
+----
+1
+3
+
+query I
+SELECT * FROM system_range(4, 1, -1)
+----
+4
+3
+2
+1
+
+query I
+SELECT * FROM system_range(4, 1, -2)
+----
+4
+2
+
+query T
+SELECT (SELECT * FROM system_range(4, 1))
+----
+null
+
+query I
+SELECT (SELECT * FROM system_range(1, 1))
+----
+1
+
+statement error: Increment can't be 0
+SELECT * FROM table(system_range(1, 1, 0))
+
+statement error: Subquery returned more than 1 value
+SELECT (SELECT * FROM table(system_range(1, 10)))
+
+# With TABLE clause
+
+query I
+SELECT * FROM TABLE(system_range(1, 3))
+----
+1
+2
+3
+
+# other
+
+statement ok
+CREATE TABLE test(id INT PRIMARY KEY, val INT)
+
+statement ok
+INSERT INTO test (id, val) SELECT x, x FROM (SELECT * FROM system_range(0,
99)) AS t(x)
+
+query I rowsort
+SELECT t.val FROM test t WHERE t.val < 5 AND t.id in (SELECT x FROM
table(system_range(t.val, t.val)))
+----
+0
+1
+2
+3
+4
+
+query I rowsort
+SELECT t.val FROM test t WHERE t.val < 5 AND EXISTS (SELECT x FROM
table(system_range(t.val, t.val)) WHERE mod(x, 2) = 0)
+----
+0
+2
+4
+
+query I rowsort
+SELECT t.val FROM test t WHERE t.val < 5 AND NOT EXISTS (SELECT x FROM
table(system_range(t.val, t.val)) WHERE mod(x, 2) = 0)
+----
+1
+3
+
+query T rowsort
+SELECT t.val FROM test t WHERE EXISTS (SELECT x FROM table(system_range(t.val,
null)))
+----
+
+query I rowsort
+SELECT t.val FROM test t JOIN table(system_range(1, 50)) as r ON t.id = r.x
WHERE mod(r.x, 10) = 0
+----
+10
+20
+30
+40
+50
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/other/test_typeof.txt
b/modules/sql-engine/src/integrationTest/sql/function/other/test_typeof.txt
new file mode 100644
index 0000000000..12a35e00a8
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/function/other/test_typeof.txt
@@ -0,0 +1,80 @@
+# name: test/sql/function/other/typeof.test
+# description: TYPEOF function
+# group: [other]
+
+query T
+SELECT typeof(1)
+----
+INTEGER
+
+query T
+SELECT typeof(100000000000)
+----
+BIGINT
+
+query T
+SELECT typeof(1.1::DOUBLE)
+----
+DOUBLE
+
+query T
+SELECT typeof(1.1::DECIMAL(3, 2))
+----
+DECIMAL(3, 2)
+
+query T
+SELECT typeof('a')
+----
+CHAR(1)
+
+query T
+SELECT typeof('00001111-0001-0002-0003-00002222333'::UUID)
+----
+UUID
+
+query T
+SELECT typeof(SELECT 1)
+----
+INTEGER
+
+query T
+SELECT '<' || typeof(NULL) || '>'
+----
+<NULL>
+
+query T
+SELECT typeof(NULL::VARCHAR(100))
+----
+VARCHAR(100)
+
+query T
+SELECT typeof('abcd' || COALESCE('efg', 'a'))
+----
+CHAR(7)
+
+statement error: Invalid input string for type INTEGER
+SELECT typeof(CAST('NONE' as INTEGER))
+
+statement error: Invalid number of arguments
+SELECT typeof()
+
+statement error: Invalid number of arguments
+SELECT typeof(1, 2)
+
+statement error: Failed to validate query. From line 1, column 15 to line 1,
column 25: Cannot apply
+SELECT typeof(SELECT 1, 2)
+
+query T
+SELECT typeof(SELECT 1)
+----
+INTEGER
+
+query T
+SELECT typeof(INTERVAL '90' MONTH)
+----
+P7Y6M
+
+query T
+SELECT typeof(INTERVAL '12' HOURS)
+----
+P7Y6M
\ No newline at end of file
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_replace.test_ignore
b/modules/sql-engine/src/integrationTest/sql/function/string/regex_replace.test_ignore
index befd414722..8ccbd32834 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_replace.test_ignore
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/regex_replace.test_ignore
@@ -1,37 +1,48 @@
# name: test/sql/function/string/regex_replace.test
# description: regex replace test
# group: [string]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-15001
# standard replace
+# https://issues.apache.org/jira/browse/IGNITE-15001
+skipif ignite3
query T
SELECT regexp_replace('foobarbaz', 'b..', 'X')
----
fooXbaz
# global replace
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
query T
SELECT regexp_replace('ana ana', 'ana', 'banana', 'g')
----
banana banana
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
query T
SELECT regexp_replace('ANA ana', 'ana', 'banana', 'gi')
----
banana banana
# case sensitivity
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT regexp_replace('ana', 'ana', 'banana', 'c')
----
banana
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT regexp_replace('ANA', 'ana', 'banana', 'i')
----
banana
# dot matches newline
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT regexp_replace('hello
world', '.*', 'x', 'sg')
@@ -41,6 +52,8 @@ x
# the result here is a single row with a newline ('x\nx')
# this is a bit complicated to check in sqllogictest, so we use a JOIN with a
count
# to verify the correct result
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT COUNT(*) FROM (SELECT 'x
x') t1(a) JOIN (SELECT regexp_replace('hello
@@ -55,12 +68,16 @@ CREATE TABLE test(v VARCHAR);
statement ok
INSERT INTO test VALUES ('hello'), ('HELLO');
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT regexp_replace(v, 'h.*', 'world', 'i') FROM test ORDER BY v
----
world
world
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
query T
SELECT regexp_replace(v, 'h.*', 'world', 'c') FROM test ORDER BY v
----
@@ -68,10 +85,13 @@ HELLO
world
# we cannot use non-constant options (currently)
-statement error
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
+statement error: Fix the error message
SELECT regexp_replace(v, 'h.*', 'world', v) FROM test ORDER BY v
# throw on invalid options
-statement error
+# https://issues.apache.org/jira/browse/IGNITE-22929 REGEX_REPLACE. Unable to
compile expression
+skipif ignite3
+statement error: Fix the error message
SELECT regexp_replace('asdf', '.*SD.*', 'a', 'q')
-
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test
b/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test
index f949b548c3..d5ffcee425 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test
@@ -39,6 +39,81 @@ 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.*'
@@ -66,15 +141,72 @@ 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
+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)
@@ -127,3 +259,11 @@ 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
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test_ignore
b/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test_ignore
deleted file mode 100644
index 2ba8a85781..0000000000
---
a/modules/sql-engine/src/integrationTest/sql/function/string/regex_search.test_ignore
+++ /dev/null
@@ -1,143 +0,0 @@
-# name: test/sql/function/string/regex_search.test
-# description: regex search test
-# group: [string]
-# Ignored https://issues.apache.org/jira/browse/IGNITE-15562
-
-# 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
-
-# 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 'foobarbequebaz' ~ '(bar)(beque)'
-----
-true
-
-# postgres says throw error on invalid regex
-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
-----
-NULL
-true
-true
-true
-
-query T
-SELECT s ~ p FROM regex order by s, p
-----
-NULL
-NULL
-false
-true
-
-# test regex with case sensitivity
-query T
-SELECT 'asdf' ~* '.*SD.*'
-----
-true
-
-query T
-SELECT 'asdf' ~ '.*SD.*'
-----
-false
-
-# newlines
-query T
-SELECT 'hello
-world' ~ '.*'
-----
-true
-
-query T
-SELECT 'hello
-world' ~ '^.*$'
-----
-true
-
-# 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
-
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/test_length.test
b/modules/sql-engine/src/integrationTest/sql/function/string/test_length.test
index d52501b9d5..109e8d4fc3 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/string/test_length.test
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/test_length.test
@@ -24,3 +24,8 @@ SELECT length(s || ' ' || 'a') FROM strings ORDER BY s NULLS
LAST
7
NULL
+query T
+SELECT length(null)
+----
+NULL
+
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/test_replace.test
b/modules/sql-engine/src/integrationTest/sql/function/string/test_replace.test
index f03cc743fb..309a7b8c4f 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/string/test_replace.test
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/test_replace.test
@@ -63,6 +63,31 @@ select REPLACE(a, 'H', '') FROM strings WHERE b IS NOT NULL
ORDER BY a
ello
Motöread
+query T
+SELECT REPLACE('12341234', '1', '55')
+----
+5523455234
+
+query T
+SELECT REPLACE(NULL, '1', '5')
+----
+null
+
+query T
+SELECT REPLACE('1', NULL, '5')
+----
+null
+
+query T
+SELECT REPLACE('11', '1', NULL)
+----
+null
+
+query T
+SELECT REPLACE('11', '1', '')
+----
+(empty)
+
# test incorrect usage of replace
statement error
select REPLACE(1)
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/test_substr.test
b/modules/sql-engine/src/integrationTest/sql/function/string/test_substr.test
new file mode 100644
index 0000000000..310126afbf
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/test_substr.test
@@ -0,0 +1,250 @@
+# name: test/sql/function/string/test_substring.test
+# description: substr 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 substr('🦆ab', 1, 0), substr('abc', 1, 0)
+----
+(empty) (empty)
+
+# constant offset/length
+# normal substr
+query T
+SELECT substr(s, 1, 2) FROM strings ORDER BY id
+----
+he
+wo
+b
+NULL
+
+# substr out of range
+query T
+SELECT substr(s, 2, 2) FROM strings ORDER BY id
+----
+el
+or
+(empty)
+NULL
+
+# variable length offset/length
+query T
+SELECT substr(s, off, length) FROM strings ORDER BY id
+----
+he
+orl
+b
+NULL
+
+query T
+SELECT substr(s, off, 2) FROM strings ORDER BY id
+----
+he
+or
+b
+NULL
+
+query T
+SELECT substr(s, 1, length) FROM strings ORDER BY id
+----
+he
+wor
+b
+NULL
+
+query T
+SELECT substr('hello', off, length) FROM strings ORDER BY id
+----
+he
+ell
+h
+el
+
+# test substrings with constant nulls in different places
+query T
+SELECT substr(NULL, off, length) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr('hello', NULL::int, length) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr('hello', off, NULL::int) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr(NULL, NULL::int, length) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr('hello', NULL::int, NULL::int) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr(NULL, off, NULL::int) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+query T
+SELECT substr(NULL, NULL::int, NULL::int) FROM strings
+----
+NULL
+NULL
+NULL
+NULL
+
+# Negative from
+query T
+SELECT substr(s, -2, 2) FROM strings ORDER BY id
+----
+(empty)
+(empty)
+(empty)
+NULL
+
+# zero offset (this is accepted by SQLite)
+query T
+SELECT substr(s, 0, length) FROM strings ORDER BY id
+----
+h
+wo
+(empty)
+NULL
+
+# Negative length
+statement error: negative substring length
+SELECT substr(s, 2, -2) FROM strings ORDER BY id
+
+# negative offset and negative length
+statement error: negative substring length
+SELECT substr(s, -2, -2) FROM strings ORDER BY id
+
+# length 0
+query T
+SELECT substr(s, 2, 0) FROM strings ORDER BY id
+----
+(empty)
+(empty)
+(empty)
+NULL
+
+# no length
+query T
+SELECT substr(s, 2) FROM strings ORDER BY id
+----
+ello
+orld
+(empty)
+NULL
+
+# very large offset and length
+query T
+SELECT substr(s, 2147483647, 2147483647) FROM strings ORDER BY id
+----
+(empty)
+(empty)
+(empty)
+NULL
+
+# BIGINT
+query T
+SELECT substr('1234567', 1, 9223372036854775807)
+----
+1234567
+
+# BIGINT
+query T
+SELECT substr('1234567', 9223372036854775807)
+----
+(empty)
+
+# BIGINT
+query T
+SELECT substr('1234567', 1::BIGINT)
+----
+1234567
+
+# The second argument is a BIGINT
+query T
+SELECT substr('1234567', -9223372036854775808)
+----
+1234567
+
+statement error: negative substring length
+SELECT substr('1234567', -9223372036854775808, -9223372036854775808)
+
+statement error: negative substring length
+SELECT substr(s, 2147483647, -2147483647) FROM strings ORDER BY id
+
+query T
+SELECT substr(s, -2147483647, 2147483647) FROM strings ORDER BY id
+----
+(empty)
+(empty)
+(empty)
+NULL
+
+statement error: negative substring length
+SELECT substr(s, -2147483647, -2147483647) FROM strings ORDER BY id
+
+statement error: Cannot apply 'SUBSTR' to arguments of type 'SUBSTR(<CHAR(7)>,
<INTEGER>, <DECIMAL(2, 1)>)'
+SELECT substr('1234567', 2, 3.1)
+
+statement error: Cannot apply 'SUBSTR' to arguments of type 'SUBSTR(<CHAR(7)>,
<DECIMAL(2, 1)>, <INTEGER>)'
+SELECT substr('1234567', 2.1, 3)
+
+statement error: Cannot apply 'SUBSTR' to arguments of type 'SUBSTR(<CHAR(7)>,
<DECIMAL(2, 1)>, <DECIMAL(2, 1)>)'
+SELECT substr('1234567', 2.1, 3.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 'SUBSTR' to arguments of type 'SUBSTR(<CHAR(7)>,
<DECIMAL(2, 1)>)'
+SELECT substr('1234567', 2.1)
+
+# Coercion
+
+query T
+SELECT substr('1234567', 2, '1')
+----
+2
+
+query T
+SELECT substr('1234567', '2', 1)
+----
+2
+
+query T
+SELECT substr('1234567', '2', '1')
+----
+2
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/string/test_substring.test
b/modules/sql-engine/src/integrationTest/sql/function/string/test_substring.test
index ebb7c1b055..cdcc3c7589 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/string/test_substring.test
+++
b/modules/sql-engine/src/integrationTest/sql/function/string/test_substring.test
@@ -142,11 +142,11 @@ wo
NULL
# Negative length
-statement error
+statement error: negative substring length
SELECT substring(s, 2, -2) FROM strings ORDER BY id
# negative offset and negative length
-statement error
+statement error: negative substring length
SELECT substring(s, -2, -2) FROM strings ORDER BY id
# length 0
@@ -176,7 +176,34 @@ SELECT substring(s, 2147483647, 2147483647) FROM strings
ORDER BY id
(empty)
NULL
-statement error
+# 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
@@ -187,5 +214,37 @@ SELECT substring(s, -2147483647, 2147483647) FROM strings
ORDER BY id
(empty)
NULL
-statement error
+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)
+
+# 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)
+
+# Coercion
+
+query T
+SELECT SUBSTRING('1234567', 2, '1')
+----
+2
+
+query T
+SELECT SUBSTRING('1234567', '2', 1)
+----
+2
+
+query T
+SELECT SUBSTRING('1234567', '2', '1')
+----
+2
diff --git
a/modules/sql-engine/src/integrationTest/sql/function/timestamp/test_timestampdiff.test
b/modules/sql-engine/src/integrationTest/sql/function/timestamp/test_timestampdiff.test
index 6d3ef3453b..26ff47b1ff 100644
---
a/modules/sql-engine/src/integrationTest/sql/function/timestamp/test_timestampdiff.test
+++
b/modules/sql-engine/src/integrationTest/sql/function/timestamp/test_timestampdiff.test
@@ -136,3 +136,13 @@ query T
SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-05-01 10:30:28.122',
TIMESTAMP '2022-02-01 10:30:26.398')
----
-7689601724000000
+
+query T
+SELECT TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '2022-02-01 10:30:28.000',
TIMESTAMP '2022-02-01 10:30:28.128')
+----
+128000
+
+query T
+SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-02-01 10:30:28.000',
TIMESTAMP '2022-02-01 10:30:28.128')
+----
+128000000
\ No newline at end of file
diff --git
a/modules/sql-engine/src/integrationTest/sql/types/blob/test_blob_function.test
b/modules/sql-engine/src/integrationTest/sql/types/blob/test_blob_function.test
index 7649e9e11d..6a3f0f6838 100644
---
a/modules/sql-engine/src/integrationTest/sql/types/blob/test_blob_function.test
+++
b/modules/sql-engine/src/integrationTest/sql/types/blob/test_blob_function.test
@@ -68,3 +68,23 @@ SELECT OCTET_LENGTH(b) FROM blobs ORDER BY 1
----
1
10
+
+query I
+SELECT OCTET_LENGTH('TEST')
+----
+4
+
+query I
+SELECT OCTET_LENGTH('我愛Java')
+----
+10
+
+query I
+SELECT OCTET_LENGTH(x'012F')
+----
+2
+
+query T
+SELECT OCTET_LENGTH(NULL)
+----
+null
