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 bfbe8930f4 [CALCITE-6910] RelToSql does not handle ASOF joins
bfbe8930f4 is described below
commit bfbe8930f4ed7ba8da530e862e212a057191cfa3
Author: Mihai Budiu <[email protected]>
AuthorDate: Mon Mar 24 17:57:40 2025 -0700
[CALCITE-6910] RelToSql does not handle ASOF joins
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/rel/rel2sql/RelToSqlConverter.java | 17 +++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 17 +++++++++++++++++
2 files changed, 34 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index e2cbb582a9..807073618a 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -27,6 +27,7 @@
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.AsofJoin;
import org.apache.calcite.rel.core.Calc;
import org.apache.calcite.rel.core.Correlate;
import org.apache.calcite.rel.core.CorrelationId;
@@ -60,6 +61,7 @@
import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.sql.JoinConditionType;
import org.apache.calcite.sql.JoinType;
+import org.apache.calcite.sql.SqlAsofJoin;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDelete;
@@ -255,6 +257,21 @@ public Result visit(Join e) {
rightContext);
condType = JoinConditionType.ON;
}
+ if (joinType == JoinType.ASOF || joinType == JoinType.LEFT_ASOF) {
+ final RexNode match = ((AsofJoin) e).getMatchCondition();
+ final SqlNode matchCondition = convertConditionToSqlNode(match,
leftContext, rightContext);
+ SqlNode join =
+ new SqlAsofJoin(POS,
+ leftResult.asFrom(),
+ SqlLiteral.createBoolean(false, POS),
+ joinType.symbol(POS),
+ rightResult.asFrom(),
+ condType.symbol(POS),
+ sqlCondition,
+ matchCondition);
+ return result(join, leftResult, rightResult);
+ }
+
SqlNode join =
new SqlJoin(POS,
leftResult.asFrom(),
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index bd61e213e4..59e1e34114 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -266,6 +266,23 @@ private static String toSql(RelNode root, SqlDialect
dialect,
.withInformix().ok(expectedInformix);
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6910";>[CALCITE-6910]
+ * RelToSql does not handle ASOF joins</a>. */
+ @Test void testAsofJoin() {
+ final String sql = "select \"employee\".\"full_name\" from \"employee\"
asof join \"product\"\n"
+ + "match_condition \"employee\".\"department_id\" <=
\"product\".\"product_id\"\n"
+ + "on \"employee\".\"salary\" = \"product\".\"gross_weight\"";
+ final String expected = "SELECT \"t\".\"full_name\"\nFROM "
+ + "((SELECT \"employee_id\", \"full_name\", \"first_name\",
\"last_name\", \"position_id\", "
+ + "\"position_title\", \"store_id\", \"department_id\",
\"birth_date\", \"hire_date\", "
+ + "\"end_date\", \"salary\", \"supervisor_id\", \"education_level\",
\"marital_status\", "
+ + "\"gender\", \"management_role\", CAST(\"salary\" AS DOUBLE) AS
\"salary0\"\n"
+ + "FROM \"foodmart\".\"employee\") AS \"t\" ASOF JOIN
\"foodmart\".\"product\" "
+ + "MATCH_CONDITION \"t\".\"department_id\" <=
\"product\".\"product_id\" ON "
+ + "\"t\".\"salary0\" = \"product\".\"gross_weight\")";
+ sql(sql).ok(expected);
+ }
+
@Test void testGroupByDateLiteral() {
String query = "select avg(\"salary\") from \"employee\" group by DATE
'2022-01-01'";
String expectedRedshift = "SELECT AVG(\"employee\".\"salary\")\n"
