This is an automated email from the ASF dual-hosted git repository.
jiajunxie 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 ed42c35bd1 [CALCITE-5813] Type inference for sql functions REPEAT,
SPACE, XML_TRANSFORM, and XML_EXTRACT is incorrect
ed42c35bd1 is described below
commit ed42c35bd1a357dd0b5d5dc268e93573b724a800
Author: Mihai Budiu <[email protected]>
AuthorDate: Fri Aug 11 22:11:33 2023 -0700
[CALCITE-5813] Type inference for sql functions REPEAT, SPACE,
XML_TRANSFORM, and XML_EXTRACT is incorrect
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/fun/SqlLibraryOperators.java | 8 +-
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 59 ++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 51 ++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 103 ++++++++++++++++-----
5 files changed, 197 insertions(+), 26 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 6b234c2165..c3bcb53607 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
@@ -493,13 +493,13 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {ORACLE})
public static final SqlFunction XML_TRANSFORM =
SqlBasicFunction.create("XMLTRANSFORM",
- ReturnTypes.VARCHAR_2000.andThen(SqlTypeTransforms.FORCE_NULLABLE),
+ ReturnTypes.VARCHAR.andThen(SqlTypeTransforms.FORCE_NULLABLE),
OperandTypes.STRING_STRING);
@LibraryOperator(libraries = {ORACLE})
public static final SqlFunction EXTRACT_XML =
SqlBasicFunction.create("EXTRACT",
- ReturnTypes.VARCHAR_2000.andThen(SqlTypeTransforms.FORCE_NULLABLE),
+ ReturnTypes.VARCHAR.andThen(SqlTypeTransforms.FORCE_NULLABLE),
OperandTypes.STRING_STRING_OPTIONAL_STRING);
@LibraryOperator(libraries = {ORACLE})
@@ -802,7 +802,7 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
public static final SqlFunction REPEAT =
SqlBasicFunction.create("REPEAT",
- ReturnTypes.ARG0_NULLABLE_VARYING,
+ ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING_INTEGER,
SqlFunctionCategory.STRING);
@@ -814,7 +814,7 @@ public abstract class SqlLibraryOperators {
@LibraryOperator(libraries = {MYSQL})
public static final SqlFunction SPACE =
SqlBasicFunction.create("SPACE",
- ReturnTypes.VARCHAR_2000_NULLABLE,
+ ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.INTEGER,
SqlFunctionCategory.STRING);
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 65bb44e9e7..530368d278 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1574,7 +1574,7 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
/** The {@code REPLACE(string, search, replace)} function. Not standard SQL,
* but in Oracle and Postgres. */
public static final SqlFunction REPLACE =
- SqlBasicFunction.create("REPLACE", ReturnTypes.ARG0_NULLABLE_VARYING,
+ SqlBasicFunction.create("REPLACE", ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
/** The {@code CONVERT(charValue, srcCharsetName, destCharsetName)}
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 a9b66f1076..c296ab7ca2 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -224,6 +224,65 @@ class RelOptRulesTest extends RelOptTestBase {
&& "item".equalsIgnoreCase(((RexCall) expr).getOperator().getName());
}
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5813";>[CALCITE-5813]
+ * Type inference for sql functions REPEAT, SPACE, XML_TRANSFORM,
+ * and XML_EXTRACT is incorrect</a>. */
+ @Test void testRepeat() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ReduceExpressionsRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
+
+ final String sql = "select REPEAT('abc', 2)";
+ fixture()
+ .withFactory(
+ t -> t.withOperatorTable(opTab ->
+ SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ SqlLibrary.BIG_QUERY))) // needed for REPEAT function
+ .sql(sql)
+ .withPlanner(hepPlanner)
+ .check();
+ }
+
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5813";>[CALCITE-5813]
+ * Type inference for sql functions REPEAT, SPACE, XML_TRANSFORM,
+ * and XML_EXTRACT is incorrect</a>. */
+ @Test void testReplace() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ReduceExpressionsRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
+
+ final String sql = "select REPLACE('abc', 'c', 'cd')";
+ fixture()
+ .sql(sql)
+ .withPlanner(hepPlanner)
+ .check();
+ }
+
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5813";>[CALCITE-5813]
+ * Type inference for sql functions REPEAT, SPACE, XML_TRANSFORM,
+ * and XML_EXTRACT is incorrect</a>. */
+ @Test void testSpace() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ReduceExpressionsRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS);
+
+ final String sql = "select SPACE(2001)";
+ fixture()
+ .withFactory(
+ t -> t.withOperatorTable(opTab ->
+ SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ SqlLibrary.MYSQL))) // needed for SPACE function
+ .sql(sql)
+ .withPlanner(hepPlanner)
+ .check();
+ }
+
@Test void testGroupByDateLiteralSimple() {
final String query = "select avg(sal)\n"
+ "from emp\n"
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 aa13309171..bdef8ae751 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -12256,6 +12256,40 @@ LogicalProject(ENAME=[$1])
LogicalFilter(condition=[=($1, 'foo')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRepeat">
+ <Resource name="sql">
+ <![CDATA[select REPEAT('abc', 2)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[REPEAT('abc', 2)])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=['abcabc':VARCHAR])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testReplace">
+ <Resource name="sql">
+ <![CDATA[select REPLACE('abc', 'c', 'cd')]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[REPLACE('abc', 'c', 'cd')])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=['abcd':VARCHAR])
+ LogicalValues(tuples=[[{ 0 }]])
]]>
</Resource>
</TestCase>
@@ -13747,6 +13781,23 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
LogicalProject(NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSpace">
+ <Resource name="sql">
+ <![CDATA[select SPACE(2001)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[SPACE(2001)])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EXPR$0=['
[...]
+ 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 4ae29895df..35f23a8d3f 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -1678,21 +1678,21 @@ public class SqlOperatorTest {
f.checkScalar("{fn LTRIM(' xxx ')}", "xxx ", "VARCHAR(6) NOT NULL");
- f.checkScalar("{fn REPEAT('a', -100)}", "", "VARCHAR(1) NOT NULL");
+ f.checkScalar("{fn REPEAT('a', -100)}", "", "VARCHAR NOT NULL");
f.checkNull("{fn REPEAT('abc', cast(null as integer))}");
f.checkNull("{fn REPEAT(cast(null as varchar(1)), cast(null as
integer))}");
f.checkString("{fn REPLACE('JACK and JUE','J','BL')}",
- "BLACK and BLUE", "VARCHAR(12) NOT NULL");
+ "BLACK and BLUE", "VARCHAR NOT NULL");
// REPLACE returns NULL in Oracle but not in Postgres or in Calcite.
// When [CALCITE-815] is implemented and SqlConformance#emptyStringIsNull
is
// enabled, it will return empty string as NULL.
f.checkString("{fn REPLACE('ciao', 'ciao', '')}", "",
- "VARCHAR(4) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("{fn REPLACE('hello world', 'o', '')}", "hell wrld",
- "VARCHAR(11) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkNull("{fn REPLACE(cast(null as varchar(5)), 'ciao', '')}");
f.checkNull("{fn REPLACE('ciao', cast(null as varchar(3)), 'zz')}");
@@ -1707,7 +1707,7 @@ public class SqlOperatorTest {
f.checkScalar("{fn SOUNDEX('Miller')}", "M460", "VARCHAR(4) NOT NULL");
f.checkNull("{fn SOUNDEX(cast(null as varchar(1)))}");
- f.checkScalar("{fn SPACE(-100)}", "", "VARCHAR(2000) NOT NULL");
+ f.checkScalar("{fn SPACE(-100)}", "", "VARCHAR NOT NULL");
f.checkNull("{fn SPACE(cast(null as integer))}");
f.checkScalar(
@@ -3897,9 +3897,9 @@ public class SqlOperatorTest {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.REPLACE, VmName.EXPAND);
f.checkString("REPLACE('ciao', 'ciao', '')", "",
- "VARCHAR(4) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkString("REPLACE('hello world', 'o', '')", "hell wrld",
- "VARCHAR(11) NOT NULL");
+ "VARCHAR NOT NULL");
f.checkNull("REPLACE(cast(null as varchar(5)), 'ciao', '')");
f.checkNull("REPLACE('ciao', cast(null as varchar(3)), 'zz')");
f.checkNull("REPLACE('ciao', 'bella', cast(null as varchar(3)))");
@@ -4328,11 +4328,11 @@ public class SqlOperatorTest {
"No match found for function signature REPEAT\\(<CHARACTER>,
<NUMERIC>\\)",
false);
final Consumer<SqlOperatorFixture> consumer = f -> {
- f.checkString("REPEAT('a', -100)", "", "VARCHAR(1) NOT NULL");
- f.checkString("REPEAT('a', -1)", "", "VARCHAR(1) NOT NULL");
- f.checkString("REPEAT('a', 0)", "", "VARCHAR(1) NOT NULL");
- f.checkString("REPEAT('a', 2)", "aa", "VARCHAR(1) NOT NULL");
- f.checkString("REPEAT('abc', 3)", "abcabcabc", "VARCHAR(3) NOT NULL");
+ f.checkString("REPEAT('a', -100)", "", "VARCHAR NOT NULL");
+ f.checkString("REPEAT('a', -1)", "", "VARCHAR NOT NULL");
+ f.checkString("REPEAT('a', 0)", "", "VARCHAR NOT NULL");
+ f.checkString("REPEAT('a', 2)", "aa", "VARCHAR NOT NULL");
+ f.checkString("REPEAT('abc', 3)", "abcabcabc", "VARCHAR NOT NULL");
f.checkNull("REPEAT(cast(null as varchar(1)), -1)");
f.checkNull("REPEAT(cast(null as varchar(1)), 2)");
f.checkNull("REPEAT('abc', cast(null as integer))");
@@ -4345,11 +4345,11 @@ public class SqlOperatorTest {
final SqlOperatorFixture f = fixture()
.setFor(SqlLibraryOperators.SPACE)
.withLibrary(SqlLibrary.MYSQL);
- f.checkString("SPACE(-100)", "", "VARCHAR(2000) NOT NULL");
- f.checkString("SPACE(-1)", "", "VARCHAR(2000) NOT NULL");
- f.checkString("SPACE(0)", "", "VARCHAR(2000) NOT NULL");
- f.checkString("SPACE(2)", " ", "VARCHAR(2000) NOT NULL");
- f.checkString("SPACE(5)", " ", "VARCHAR(2000) NOT NULL");
+ f.checkString("SPACE(-100)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(-1)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(0)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(2)", " ", "VARCHAR NOT NULL");
+ f.checkString("SPACE(5)", " ", "VARCHAR NOT NULL");
f.checkNull("SPACE(cast(null as integer))");
}
@@ -5316,7 +5316,45 @@ public class SqlOperatorTest {
+ "</xsl:stylesheet>')";
f.checkString(sql2,
" Article - My Article Authors: - Mr. Foo - Mr. Bar",
- "VARCHAR(2000)");
+ "VARCHAR");
+
+ // Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5813";>[CALCITE-5813]
+ // Type inference for REPEAT sql function is incorrect</a>. This test
shows that the
+ // output of the XML_TRANSFORM function can exceed 2000 characters. */
+ StringBuilder sql3 = new StringBuilder();
+ StringBuilder expected = new StringBuilder();
+ sql3.append("XMLTRANSFORM("
+ + "'<?xml version=\"1.0\"?>\n"
+ + "<Article>\n"
+ + " <Title>My Article</Title>\n"
+ + " <Authors>\n"
+ + " <Author>Mr. Foo</Author>\n"
+ + " <Author>Mr. Bar</Author>\n");
+ expected.append(" Article - My Article Authors: - Mr. Foo -
Mr. Bar");
+ for (int i = 0; i < 40; i++) {
+ final String row = "Mr. Bar
" + i;
+ sql3.append(" <Author>")
+ .append(row)
+ .append("</Author>\n");
+ expected.append(" - ").append(row);
+ }
+ sql3.append(" </Authors>\n"
+ + " <Body>This is my article text.</Body>\n"
+ + "</Article>'"
+ + ","
+ + "'<?xml version=\"1.0\"?>\n"
+ + "<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3";
+ + ".org/1999/XSL/Transform\">"
+ + " <xsl:output method=\"text\"/>"
+ + " <xsl:template match=\"/\">"
+ + " Article - <xsl:value-of select=\"/Article/Title\"/>"
+ + " Authors: <xsl:apply-templates
select=\"/Article/Authors/Author\"/>"
+ + " </xsl:template>"
+ + " <xsl:template match=\"Author\">"
+ + " - <xsl:value-of select=\".\" />"
+ + " </xsl:template>"
+ + "</xsl:stylesheet>')");
+ f.checkString(sql3.toString(), expected.toString(), "VARCHAR");
}
@Test void testExtractXml() {
@@ -5339,7 +5377,7 @@ public class SqlOperatorTest {
+ "<Body>article text.</Body>"
+ "</Article>', '/Article/Title')",
"<Title>Article1</Title>",
- "VARCHAR(2000)");
+ "VARCHAR");
f.checkString("\"EXTRACT\"('"
+ "<Article>"
@@ -5349,7 +5387,30 @@ public class SqlOperatorTest {
+ "<Body>article text.</Body>"
+ "</Article>', '/Article/Title')",
"<Title>Article1</Title><Title>Article2</Title>",
- "VARCHAR(2000)");
+ "VARCHAR");
+
+ // Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5813";>[CALCITE-5813]
+ // Type inference for REPEAT sql function is incorrect</a>. This test
shows that
+ // the output of the 'XML_EXTRACT' function can exceed 2000 characters. */
+ StringBuilder sql = new StringBuilder();
+ StringBuilder expected = new StringBuilder();
+ sql.append("\"EXTRACT\"('"
+ + "<Article>"
+ + "<Title>Article1</Title>"
+ + "<Title>Article2");
+ expected.append("<Title>Article1</Title><Title>Article2");
+ final String spaces =
+ "
";
+ for (int i = 0; i < 40; i++) {
+ sql.append(spaces);
+ expected.append(spaces);
+ }
+ sql.append("Long</Title>"
+ + "<Authors><Author>Foo</Author><Author>Bar</Author></Authors>"
+ + "<Body>article text.</Body>"
+ + "</Article>', '/Article/Title')");
+ expected.append("Long</Title>");
+ f.checkString(sql.toString(), expected.toString(), "VARCHAR");
f.checkString("\"EXTRACT\"(\n"
+ "'<books xmlns=\"http://www.contoso.com/books\";>"
@@ -5362,7 +5423,7 @@ public class SqlOperatorTest {
+ "'books=\"http://www.contoso.com/books\";')",
"<book
xmlns=\"http://www.contoso.com/books\";><title>Title</title><author>Author "
+ "Name</author><price>5.50</price></book>",
- "VARCHAR(2000)");
+ "VARCHAR");
}
@Test void testExistsNode() {
