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 5b4f32aee5 [CALCITE-6641] Compiling programs with ASOF joins can
report obscure errors
5b4f32aee5 is described below
commit 5b4f32aee52c33d40669314b137ace36f13f9e85
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Oct 23 22:45:49 2024 -0700
[CALCITE-6641] Compiling programs with ASOF joins can report obscure errors
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/validate/SqlValidatorImpl.java | 24 ++++++++++++---
.../org/apache/calcite/test/SqlValidatorTest.java | 35 ++++++++++++++++++----
core/src/test/resources/sql/asof.iq | 16 ++++++++++
3 files changed, 65 insertions(+), 10 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 1a08b8cdb9..44c0dce6d9 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -3855,6 +3855,13 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
boolean rightFound = false;
// The two sides of the comparison must be from different tables
for (SqlNode operand : call.getOperandList()) {
+ if (operand instanceof SqlBasicCall) {
+ SqlBasicCall basicCall = (SqlBasicCall) operand;
+ if (basicCall.getKind() == SqlKind.CAST) {
+ // Allow casts applied to identifiers
+ operand = basicCall.operand(0);
+ }
+ }
if (!(operand instanceof SqlIdentifier)) {
throw newValidationError(call, this.exception);
}
@@ -3889,11 +3896,11 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
/**
* Shuttle which determines whether an expression is a simple conjunction
- * of equalities. */
+ * of equalities. Each equality may involve a cast */
private static class ConjunctionOfEqualities extends SqlShuttle {
boolean illegal = false;
- // Check an AND node. Children can be AND nodes or EQUAL nodes.
+ // Check an AND node. Children can be AND nodes or EQUAL nodes.
void checkAnd(SqlCall call) {
// This doesn't seem to use the visitor pattern,
// because we recurse explicitly on the tree structure.
@@ -3911,14 +3918,23 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
}
}
- @Override public @Nullable SqlNode visit(final
org.apache.calcite.sql.SqlCall call) {
+ @Override public @Nullable SqlNode visit(final SqlCall call) {
SqlKind kind = call.getKind();
- if (kind != SqlKind.AND && kind != SqlKind.EQUALS) {
+ if (kind != SqlKind.AND && kind != SqlKind.EQUALS && kind !=
SqlKind.CAST) {
illegal = true;
+ return null;
}
if (kind == SqlKind.AND) {
this.checkAnd(call);
}
+ if (kind == SqlKind.CAST) {
+ // Cast must be applied directly to a column
+ SqlNode operand = call.operand(0);
+ if (! (operand instanceof SqlIdentifier)) {
+ illegal = true;
+ return null;
+ }
+ }
return super.visit(call);
}
}
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 6c7c75e79b..8d2fd0f28f 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -3261,12 +3261,6 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
+ "on emp.ename = D.name";
sql(sql3).type(type2);
- // Longer sequence of comparisons
- final String sql6 = "select emp.empno, dept.deptno from emp asof join
dept\n"
- + "match_condition emp.deptno <= dept.deptno\n"
- + "on emp.ename = dept.name AND emp.deptno = dept.deptno AND emp.job =
dept.name";
- sql(sql6).type(type0);
-
// No table specified for on condition
final String sql4 = "select emp.empno, dept.deptno from emp asof join
dept\n"
+ "match_condition emp.deptno <= dept.deptno\n"
@@ -3280,6 +3274,29 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
+ "on ename = name";
sql(sql5).type(type0);
+ // Longer sequence of comparisons
+ final String sql6 = "select emp.empno, dept.deptno from emp asof join
dept\n"
+ + "match_condition emp.deptno <= dept.deptno\n"
+ + "on emp.ename = dept.name AND emp.deptno = dept.deptno AND emp.job =
dept.name";
+ sql(sql6).type(type0);
+
+ // 2 Test cases for https://issues.apache.org/jira/browse/CALCITE-6641
+ // Compiling programs with ASOF joins can report obscure errors
+ final String type7 = "RecordType(INTEGER NOT NULL EMPNO, BIGINT NOT NULL
DEPTNO) NOT NULL";
+ // ASOF involving casts
+ final String sql7 = "select emp.empno, dno as deptno from emp asof join "
+ + "(select CAST(deptno AS BIGINT) as dno, name from dept)\n"
+ + "match_condition deptno <= dno\n"
+ + "on ename = name";
+ sql(sql7).type(type7);
+
+ // ASOF involving casts
+ final String sql8 = "select emp.empno, dno as deptno from emp asof join "
+ + "(select CAST(deptno AS BIGINT) as dno, name from dept)\n"
+ + "match_condition ename <= name\n"
+ + "on deptno = dno";
+ sql(sql8).type(type7);
+
// Failure cases
// match condition is not an inequality test
sql("select emp.empno from emp asof join dept\n"
@@ -3314,6 +3331,12 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
+ "match_condition T0.b0 < T1.b0\n"
+ "on ^T0.b1 AND T1.b1^")
.fails("ASOF JOIN condition must be a conjunction of equality
comparisons");
+ // Condition contains a cast that is not applied to a column
+ sql("select * from (VALUES(true, false)) AS T0(b0, b1)\n"
+ + "asof join (VALUES(false, 1)) AS T1(b0, b1)\n"
+ + "match_condition T0.b0 < T1.b0\n"
+ + "on ^T0.b1 = CAST(T1.b1 + 1 AS BOOLEAN)^")
+ .fails("ASOF JOIN condition must be a conjunction of equality
comparisons");
}
@Test void testInvalidWindowFunctionWithGroupBy() {
diff --git a/core/src/test/resources/sql/asof.iq
b/core/src/test/resources/sql/asof.iq
index 287681ab20..c0484d94f4 100644
--- a/core/src/test/resources/sql/asof.iq
+++ b/core/src/test/resources/sql/asof.iq
@@ -27,6 +27,22 @@
# ASOF JOIN should always be a subset of the result produced by
# the corresponding normal JOIN.
+# Test case for https://issues.apache.org/jira/browse/CALCITE-6641
+# Compiling programs with ASOF joins can report obscure errors
+SELECT *
+FROM (VALUES (2, 3)) AS t1(k, t)
+ASOF JOIN (VALUES (2.0, 0.0)) AS t2(k, t)
+MATCH_CONDITION t2.t < t1.t
+ON t1.k = t2.k;
++---+---+-----+-----+
+| K | T | K0 | T0 |
++---+---+-----+-----+
+| 2 | 3 | 2.0 | 0.0 |
++---+---+-----+-----+
+(1 row)
+
+!ok
+
SELECT *
FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3), (1, 4), (2,
3), (3, 4)) AS t1(k, t)
ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS t2(k, t)
