This is an automated email from the ASF dual-hosted git repository. tanner pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 1b11d99e65d03a15ae4b25c47250b6918ce9aa10 Author: zstan <[email protected]> AuthorDate: Wed Jul 5 08:09:18 2023 +0300 [CALCITE-5708] SUBSTRING validation error if any parameter is a NULL literal Close apache/calcite#3211 --- .../org/apache/calcite/sql/fun/SqlSubstringFunction.java | 9 +++++---- .../java/org/apache/calcite/test/SqlValidatorTest.java | 6 ++++++ core/src/test/resources/sql/functions.iq | 15 +++++++++++++++ 3 files changed, 26 insertions(+), 4 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java index ddc6c9796b..53cc96fa83 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSubstringFunction.java @@ -44,10 +44,10 @@ import java.util.Objects; * Definition of the "SUBSTRING" builtin SQL function. */ public class SqlSubstringFunction extends SqlFunction { - /** Type checker for 3 argument calls. Put the STRING_NUMERIC_NUMERIC checker + /** Type checker for 3 argument calls. Put the STRING_INTEGER_INTEGER checker * first because almost every other type can be coerced to STRING. */ private static final SqlSingleOperandTypeChecker CHECKER3 = - OperandTypes.STRING_NUMERIC_NUMERIC + OperandTypes.STRING_INTEGER_INTEGER .or(OperandTypes.STRING_STRING_STRING); //~ Constructors ----------------------------------------------------------- @@ -74,7 +74,8 @@ public class SqlSubstringFunction extends SqlFunction { case 3: return "{0}({1} FROM {2} FOR {3})"; default: - throw new AssertionError(); + throw new AssertionError("Incorrect " + getName() + " signature, operands " + + "count = " + operandsCount); } } @@ -121,7 +122,7 @@ public class SqlSubstringFunction extends SqlFunction { return false; } } - if (!SqlTypeUtil.inSameFamily(t1, t2)) { + if (!SqlTypeUtil.inSameFamilyOrNull(t1, t2)) { if (throwOnFailure) { throw callBinding.newValidationSignatureError(); } diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 16c267dd72..6d5823e22d 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -1040,6 +1040,12 @@ public class SqlValidatorTest extends SqlValidatorTestCase { .columnType("VARCHAR(1) NOT NULL"); expr("substring('a', 1, '3')") .columnType("VARCHAR(1) NOT NULL"); + + // Correctly processed null string and params. + expr("SUBSTRING(NULL FROM 1 FOR 2)").ok(); + expr("SUBSTRING('text' FROM 1 FOR NULL)").ok(); + expr("SUBSTRING('text' FROM NULL FOR 2)").ok(); + expr("SUBSTRING('text' FROM NULL)").ok(); } @Test void testSubstringFails() { diff --git a/core/src/test/resources/sql/functions.iq b/core/src/test/resources/sql/functions.iq index 92dc4f15b9..1a7157dc3e 100644 --- a/core/src/test/resources/sql/functions.iq +++ b/core/src/test/resources/sql/functions.iq @@ -938,5 +938,20 @@ table(AUX.TBLFUN_IDENTITY(3)) as t3(v); !ok +# SUBSTRING +-- returns 'null' +select SUBSTRING(NULL FROM 1 FOR 2); +select SUBSTRING('text' FROM 1 FOR NULL); +select SUBSTRING('text' FROM NULL FOR 2); +select SUBSTRING(s FROM i FOR l) FROM (VALUES ('abc', NULL, 2)) AS t (s, i, l); +select SUBSTRING('text' FROM NULL); ++--------+ +| EXPR$0 | ++--------+ +| | ++--------+ +(1 row) + +!ok # End functions.iq
