This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 2505f6fc57 [CALCITE-7132] Inconsistency with type coercion and
character types
2505f6fc57 is described below
commit 2505f6fc57b14dcdc5ae50dd5546d450e4a9dc36
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Aug 12 14:17:30 2025 -0700
[CALCITE-7132] Inconsistency with type coercion and character types
Signed-off-by: Mihai Budiu <[email protected]>
---
.../validate/implicit/AbstractTypeCoercion.java | 53 ++++++++++++++++++----
.../apache/calcite/test/SqlToRelConverterTest.java | 8 ++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 11 +++++
.../elasticsearch/ElasticSearchAdapterTest.java | 6 +--
.../calcite/adapter/innodb/InnodbAdapterTest.java | 4 +-
.../calcite/adapter/mongodb/MongoAdapterTest.java | 2 +-
7 files changed, 72 insertions(+), 16 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
index 1e1ac765db..32d39229b5 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
@@ -582,16 +582,53 @@ private RelDataType getTightestCommonTypeOrThrow(
}
if (SqlTypeUtil.isString(type1) && SqlTypeUtil.isString(type2)) {
- // Return the string with the larger precision
- if (type1.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED) {
- return factory.createTypeWithNullability(type1, anyNullable);
- } else if (type2.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED) {
- return factory.createTypeWithNullability(type2, anyNullable);
- } else if (type1.getPrecision() > type2.getPrecision()) {
- return factory.createTypeWithNullability(type1, anyNullable);
+ // Note that isString covers VAR/BINARY and VAR/CHAR
+ if (type1.getCharset() != null && type2.getCharset() != null
+ && type1.getCharset() != type2.getCharset()) {
+ // Cannot coerce; we count on the validator to throw later
+ return null;
+ }
+ if (type1.getCollation() != null && type2.getCollation() != null
+ && type1.getCollation() != type2.getCollation()) {
+ // Cannot coerce; we count on the validator to throw later
+ return null;
+ }
+
+ SqlTypeName type1Name = type1.getSqlTypeName();
+ SqlTypeName type2Name = type2.getSqlTypeName();
+ SqlTypeName resultName;
+
+ int precision;
+ if (type1.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED
+ || type2.getPrecision() == RelDataType.PRECISION_NOT_SPECIFIED) {
+ precision = RelDataType.PRECISION_NOT_SPECIFIED;
} else {
- return factory.createTypeWithNullability(type2, anyNullable);
+ precision = Math.max(type1.getPrecision(), type2.getPrecision());
+ }
+
+ if (type1Name == SqlTypeName.VARCHAR || type2Name ==
SqlTypeName.VARCHAR) {
+ resultName = SqlTypeName.VARCHAR;
+ } else if (type1Name == SqlTypeName.CHAR || type2Name ==
SqlTypeName.CHAR) {
+ resultName = SqlTypeName.CHAR;
+ // If any is BINARY, use VARCHAR
+ if (type1Name == SqlTypeName.BINARY || type2Name == SqlTypeName.BINARY
+ || type1Name == SqlTypeName.VARBINARY || type2Name ==
SqlTypeName.VARBINARY) {
+ resultName = SqlTypeName.VARCHAR;
+ // We use unlimited precision in this case
+ precision = RelDataType.PRECISION_NOT_SPECIFIED;
+ }
+ } else if (type1Name == SqlTypeName.VARBINARY || type2Name ==
SqlTypeName.VARBINARY) {
+ resultName = SqlTypeName.VARBINARY;
+ } else {
+ resultName = SqlTypeName.BINARY;
}
+
+ RelDataType resultType = factory.createSqlType(resultName, precision);
+ // Copy collation and charset
+ syncAttributes(type1, resultType);
+ // We copy twice, since type1 may be BINARY
+ syncAttributes(type2, resultType);
+ return factory.createTypeWithNullability(resultType, anyNullable);
}
// 1 > '1' will be coerced to 1 > 1.
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 990c9c876d..c53bf459fb 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3352,6 +3352,14 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).ok();
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7132";>[CALCITE-7132]
+ * Inconsistency with type coercion and character types</a>. */
+ @Test void testCoercion() {
+ sql("WITH c AS (SELECT CAST('x' as VARCHAR(2)) AS X, CAST('y' AS CHAR(2))
AS Y)"
+ + "SELECT X = Y AND Y = X FROM c")
+ .ok();
+ }
+
@Test void testDeleteWhere() {
final String sql = "delete from emp where deptno = 10";
sql(sql).ok();
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 8beb8062ad..2ae75251d2 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -15045,7 +15045,7 @@ where cast(e.job as varchar(1)) = 'Manager']]>
<Resource name="planBefore">
<![CDATA[
LogicalProject(EXPR$0=[CAST($1):VARCHAR(128) NOT NULL],
EXPR$1=[CAST($2):INTEGER NOT NULL])
- LogicalFilter(condition=[=(CAST(CAST($4):VARCHAR(1) NOT NULL):CHAR(7) NOT
NULL, 'Manager')])
+ LogicalFilter(condition=[=(CAST(CAST($4):VARCHAR(1) NOT NULL):VARCHAR(7) NOT
NULL, 'Manager')])
LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$3], ENAME=[$4], JOB=[$5],
MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO1=[$10], SLACKER=[$11])
LogicalJoin(condition=[=($2, $12)], joinType=[inner])
LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($0):INTEGER NOT
NULL])
@@ -15057,7 +15057,7 @@ LogicalProject(EXPR$0=[CAST($1):VARCHAR(128) NOT NULL],
EXPR$1=[CAST($2):INTEGER
<Resource name="planAfter">
<![CDATA[
LogicalProject(EXPR$0=[CAST($1):VARCHAR(128) NOT NULL], EXPR$1=[$2])
- LogicalFilter(condition=[=(CAST(CAST($4):VARCHAR(1) NOT NULL):CHAR(7) NOT
NULL, 'Manager')])
+ LogicalFilter(condition=[=(CAST(CAST($4):VARCHAR(1) NOT NULL):VARCHAR(7) NOT
NULL, 'Manager')])
LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$3], ENAME=[$4], JOB=[$5],
MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO1=[$10], SLACKER=[$11])
LogicalJoin(condition=[=($2, $12)], joinType=[inner])
LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$0])
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 8314beafdf..787bad3174 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -674,6 +674,17 @@ LogicalProject(DEPTNO=[$0], W=[CASE(IS NOT NULL($2),
CAST($3):INTEGER NOT NULL,
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[=($7, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testCoercion">
+ <Resource name="sql">
+ <![CDATA[WITH c AS (SELECT CAST('x' as VARCHAR(2)) AS X, CAST('y' AS
CHAR(2)) AS Y)SELECT X = Y AND Y = X FROM c]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[=($0, CAST($1):VARCHAR(2) NOT NULL)])
+ LogicalValues(tuples=[[{ 'x', 'y ' }]])
]]>
</Resource>
</TestCase>
diff --git
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
index eb241fe328..0f31965fe5 100644
---
a/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
+++
b/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java
@@ -594,7 +594,7 @@ private static Consumer<ResultSet>
sortedResultSetChecker(String column,
final String explain = "PLAN=ElasticsearchToEnumerableConverter\n"
+ " ElasticsearchSort(sort0=[$4], sort1=[$3], dir0=[ASC],
dir1=[ASC])\n"
+ " ElasticsearchProject(city=[CAST(ITEM($0, 'city')):VARCHAR(20)],
longitude=[CAST(ITEM(ITEM($0, 'loc'), 0)):FLOAT], latitude=[CAST(ITEM(ITEM($0,
'loc'), 1)):FLOAT], pop=[CAST(ITEM($0, 'pop')):INTEGER], state=[CAST(ITEM($0,
'state')):VARCHAR(2)], id=[CAST(ITEM($0, 'id')):VARCHAR(5)])\n"
- + " ElasticsearchFilter(condition=[AND(=(CAST(CAST(ITEM($0,
'state')):VARCHAR(2)):CHAR(2), 'CA'), >=(CAST(ITEM($0, 'pop')):INTEGER,
94000))])\n"
+ + " ElasticsearchFilter(condition=[AND(=(CAST(ITEM($0,
'state')):VARCHAR(2), 'CA'), >=(CAST(ITEM($0, 'pop')):INTEGER, 94000))])\n"
+ " ElasticsearchTableScan(table=[[elastic, zips]])\n\n";
calciteAssert()
.query(sql)
@@ -628,7 +628,7 @@ private static Consumer<ResultSet>
sortedResultSetChecker(String column,
final String explain = "PLAN=ElasticsearchToEnumerableConverter\n"
+ " ElasticsearchSort(sort0=[$4], sort1=[$3], dir0=[ASC],
dir1=[ASC])\n"
+ " ElasticsearchProject(city=[CAST(ITEM($0, 'city')):VARCHAR(20)],
longitude=[CAST(ITEM(ITEM($0, 'loc'), 0)):FLOAT], latitude=[CAST(ITEM(ITEM($0,
'loc'), 1)):FLOAT], pop=[CAST(ITEM($0, 'pop')):INTEGER], state=[CAST(ITEM($0,
'state')):VARCHAR(2)], id=[CAST(ITEM($0, 'id')):VARCHAR(5)])\n"
- + " ElasticsearchFilter(condition=[OR(=(CAST(CAST(ITEM($0,
'state')):VARCHAR(2)):CHAR(2), 'CA'), >=(CAST(ITEM($0, 'pop')):INTEGER,
94000))])\n"
+ + " ElasticsearchFilter(condition=[OR(=(CAST(ITEM($0,
'state')):VARCHAR(2), 'CA'), >=(CAST(ITEM($0, 'pop')):INTEGER, 94000))])\n"
+ " ElasticsearchTableScan(table=[[elastic, zips]])\n\n";
calciteAssert()
.query(sql)
@@ -713,7 +713,7 @@ private static Consumer<ResultSet>
sortedResultSetChecker(String column,
@Test void testFilter() {
final String explain = "PLAN=ElasticsearchToEnumerableConverter\n"
+ " ElasticsearchProject(state=[CAST(ITEM($0, 'state')):VARCHAR(2)],
city=[CAST(ITEM($0, 'city')):VARCHAR(20)])\n"
- + " ElasticsearchFilter(condition=[=(CAST(CAST(ITEM($0,
'state')):VARCHAR(2)):CHAR(2), 'CA')])\n"
+ + " ElasticsearchFilter(condition=[=(CAST(ITEM($0,
'state')):VARCHAR(2), 'CA')])\n"
+ " ElasticsearchTableScan(table=[[elastic, zips]])";
calciteAssert()
diff --git
a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
index 4b7724fda2..f7a1f484fd 100644
---
a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
+++
b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
@@ -896,8 +896,8 @@ public class InnodbAdapterTest {
@Test void testSelectByMultipleSkRangeQueryPushDownPartialCondition3() {
sql("SELECT EMPNO,DEPTNO,JOB FROM \"EMP\" WHERE JOB >= 'SALE' AND DEPTNO
>= 20")
.explainContains("PLAN=EnumerableCalc(expr#0..12=[{inputs}], "
- + "expr#13=[CAST($t2):CHAR(4) NOT NULL], "
- + "expr#14=['SALE'], expr#15=[>=($t13, $t14)], "
+ + "expr#13=[CAST($t2):VARCHAR(4) NOT NULL], "
+ + "expr#14=['SALE':VARCHAR(4)], expr#15=[>=($t13, $t14)], "
+ "EMPNO=[$t0], DEPTNO=[$t8], JOB=[$t2], $condition=[$t15])\n"
+ " InnodbToEnumerableConverter\n"
+ " InnodbFilter(condition=[(SK_RANGE_QUERY,
index=DEPTNO_JOB_KEY, "
diff --git
a/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
b/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
index 8d7dff5fd9..a7b483cc99 100644
---
a/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
+++
b/mongodb/src/test/java/org/apache/calcite/adapter/mongodb/MongoAdapterTest.java
@@ -659,7 +659,7 @@ private CalciteAssert.AssertThat assertModel(URL url) {
"STATE=CA; CITY=NORWALK")
.explainContains("PLAN=MongoToEnumerableConverter\n"
+ " MongoProject(STATE=[CAST(ITEM($0, 'state')):VARCHAR(2)],
CITY=[CAST(ITEM($0, 'city')):VARCHAR(20)])\n"
- + " MongoFilter(condition=[=(CAST(CAST(ITEM($0,
'state')):VARCHAR(2)):CHAR(2), 'CA')])\n"
+ + " MongoFilter(condition=[=(CAST(ITEM($0,
'state')):VARCHAR(2), 'CA')])\n"
+ " MongoTableScan(table=[[mongo_raw, zips]])");
}
