This is an automated email from the ASF dual-hosted git repository.
rubenql pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 3bb7117446 [CALCITE-5989] Type inference for RPAD and LPAD functions
(BIGQUERY) is incorrect
3bb7117446 is described below
commit 3bb71174460d10542c3c9892c6a1a8effb573f9e
Author: Mihai Budiu <[email protected]>
AuthorDate: Fri Sep 29 12:02:01 2023 -0700
[CALCITE-5989] Type inference for RPAD and LPAD functions (BIGQUERY) is
incorrect
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/fun/SqlLibraryOperators.java | 10 ++++--
.../org/apache/calcite/test/RelOptRulesTest.java | 40 ++++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 34 ++++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 32 ++++++++---------
4 files changed, 98 insertions(+), 18 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 2835184788..d58ae302d2 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -275,12 +275,18 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction LENGTH =
SqlStdOperatorTable.CHAR_LENGTH.withName("LENGTH");
+ // Helper function for deriving types for the *PAD functions
+ private static RelDataType deriveTypePad(SqlOperatorBinding binding,
RelDataType type) {
+ SqlTypeName result = SqlTypeUtil.isBinary(type) ? SqlTypeName.VARBINARY :
SqlTypeName.VARCHAR;
+ return binding.getTypeFactory().createSqlType(result);
+ }
+
/** The "LPAD(original_value, return_length[, pattern])" function. */
@LibraryOperator(libraries = {BIG_QUERY, ORACLE})
public static final SqlFunction LPAD =
SqlBasicFunction.create(
"LPAD",
- ReturnTypes.ARG0_NULLABLE_VARYING,
+ ReturnTypes.ARG0.andThen(SqlLibraryOperators::deriveTypePad),
OperandTypes.STRING_NUMERIC_OPTIONAL_STRING,
SqlFunctionCategory.STRING);
@@ -289,7 +295,7 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction RPAD =
SqlBasicFunction.create(
"RPAD",
- ReturnTypes.ARG0_NULLABLE_VARYING,
+ ReturnTypes.ARG0.andThen(SqlLibraryOperators::deriveTypePad),
OperandTypes.STRING_NUMERIC_OPTIONAL_STRING,
SqlFunctionCategory.STRING);
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 71b80771ce..b16a480203 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -262,6 +262,46 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5989";>[CALCITE-5989]
+ * Type inference for RPAD and LPAD functions (BIGQUERY) is incorrect</a>. */
+ @Test void testRpad() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ReduceExpressionsRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
+
+ final String sql = "select RPAD('abc', 8, 'A')";
+ fixture()
+ .withFactory(
+ t -> t.withOperatorTable(opTab ->
+ SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ SqlLibrary.BIG_QUERY))) // needed for RPAD function
+ .sql(sql)
+ .withPlanner(hepPlanner)
+ .check();
+ }
+
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5989";>[CALCITE-5989]
+ * Type inference for RPAD and LPAD functions (BIGQUERY) is incorrect</a>. */
+ @Test void testLpad() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ReduceExpressionsRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
+
+ final String sql = "select LPAD('abc', 8, 'A')";
+ fixture()
+ .withFactory(
+ t -> t.withOperatorTable(opTab ->
+ SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ SqlLibrary.BIG_QUERY))) // needed for LPAD function
+ .sql(sql)
+ .withPlanner(hepPlanner)
+ .check();
+ }
+
/**
* Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5971";>[CALCITE-5971]
* Add the RelRule to rewrite the bernoulli sample as Filter</a>. */
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 65f8268c22..e529b3ae44 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6221,6 +6221,23 @@ EnumerableLimitSort(sort0=[$0], dir0=[ASC], offset=[5],
fetch=[10])
EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
EnumerableProject(MGR=[$3])
EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLpad">
+ <Resource name="sql">
+ <![CDATA[select LPAD('abc', 8, 'A')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[LPAD('abc', 8, 'A')])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=['AAAAAabc':VARCHAR])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
@@ -12876,6 +12893,23 @@ LogicalProject(EXPR$0=[1])
LogicalFilter(condition=[=($1, 'Charlie')])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRpad">
+ <Resource name="sql">
+ <![CDATA[select RPAD('abc', 8, 'A')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[RPAD('abc', 8, 'A')])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=['abcAAAAA':VARCHAR])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 0e23f66669..60bd95846d 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8225,20 +8225,20 @@ public class SqlOperatorTest {
@Test void testLpadFunction() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
f.setFor(SqlLibraryOperators.LPAD);
- f.check("select lpad('12345', 8, 'a')", "VARCHAR(5) NOT NULL", "aaa12345");
- f.checkString("lpad('12345', 8)", " 12345", "VARCHAR(5) NOT NULL");
- f.checkString("lpad('12345', 8, 'ab')", "aba12345", "VARCHAR(5) NOT NULL");
- f.checkString("lpad('12345', 3, 'a')", "123", "VARCHAR(5) NOT NULL");
+ f.check("select lpad('12345', 8, 'a')", "VARCHAR NOT NULL", "aaa12345");
+ f.checkString("lpad('12345', 8)", " 12345", "VARCHAR NOT NULL");
+ f.checkString("lpad('12345', 8, 'ab')", "aba12345", "VARCHAR NOT NULL");
+ f.checkString("lpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
f.checkFails("lpad('12345', -3, 'a')",
"Second argument for LPAD/RPAD must not be negative", true);
f.checkFails("lpad('12345', -3)",
"Second argument for LPAD/RPAD must not be negative", true);
f.checkFails("lpad('12345', 3, '')",
"Third argument (pad pattern) for LPAD/RPAD must not be empty", true);
- f.checkString("lpad(x'aa', 4, x'bb')", "bbbbbbaa", "VARBINARY(1) NOT
NULL");
- f.checkString("lpad(x'aa', 4)", "202020aa", "VARBINARY(1) NOT NULL");
- f.checkString("lpad(x'aaaaaa', 2)", "aaaa", "VARBINARY(3) NOT NULL");
- f.checkString("lpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY(3) NOT
NULL");
+ f.checkString("lpad(x'aa', 4, x'bb')", "bbbbbbaa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aa', 4)", "202020aa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
f.checkFails("lpad(x'aa', -3, x'bb')",
"Second argument for LPAD/RPAD must not be negative", true);
f.checkFails("lpad(x'aa', -3)",
@@ -8250,10 +8250,10 @@ public class SqlOperatorTest {
@Test void testRpadFunction() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
f.setFor(SqlLibraryOperators.RPAD);
- f.check("select rpad('12345', 8, 'a')", "VARCHAR(5) NOT NULL", "12345aaa");
- f.checkString("rpad('12345', 8)", "12345 ", "VARCHAR(5) NOT NULL");
- f.checkString("rpad('12345', 8, 'ab')", "12345aba", "VARCHAR(5) NOT NULL");
- f.checkString("rpad('12345', 3, 'a')", "123", "VARCHAR(5) NOT NULL");
+ f.check("select rpad('12345', 8, 'a')", "VARCHAR NOT NULL", "12345aaa");
+ f.checkString("rpad('12345', 8)", "12345 ", "VARCHAR NOT NULL");
+ f.checkString("rpad('12345', 8, 'ab')", "12345aba", "VARCHAR NOT NULL");
+ f.checkString("rpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
f.checkFails("rpad('12345', -3, 'a')",
"Second argument for LPAD/RPAD must not be negative", true);
f.checkFails("rpad('12345', -3)",
@@ -8261,10 +8261,10 @@ public class SqlOperatorTest {
f.checkFails("rpad('12345', 3, '')",
"Third argument (pad pattern) for LPAD/RPAD must not be empty", true);
- f.checkString("rpad(x'aa', 4, x'bb')", "aabbbbbb", "VARBINARY(1) NOT
NULL");
- f.checkString("rpad(x'aa', 4)", "aa202020", "VARBINARY(1) NOT NULL");
- f.checkString("rpad(x'aaaaaa', 2)", "aaaa", "VARBINARY(3) NOT NULL");
- f.checkString("rpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY(3) NOT
NULL");
+ f.checkString("rpad(x'aa', 4, x'bb')", "aabbbbbb", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aa', 4)", "aa202020", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
f.checkFails("rpad(x'aa', -3, x'bb')",
"Second argument for LPAD/RPAD must not be negative", true);
f.checkFails("rpad(x'aa', -3)",
