This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 04eececf8d [CALCITE-7087] SQLite does not support RIGHT/FULL JOIN
until version 3.39.0
04eececf8d is described below
commit 04eececf8d9547de355667899056c07f35e1205e
Author: Zhen Chen <[email protected]>
AuthorDate: Mon Dec 1 06:49:40 2025 +0800
[CALCITE-7087] SQLite does not support RIGHT/FULL JOIN until version 3.39.0
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 4 +
.../calcite/sql/dialect/SqliteSqlDialect.java | 19 +++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 85 +++++++++++++++++++++-
3 files changed, 107 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 8952736563..ece87d0b5c 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -194,6 +194,10 @@ public final Result visitRoot(RelNode r) {
rules.add(FullToLeftAndRightJoinRule.Config.DEFAULT.toRule());
}
+ if (!this.dialect.supportsJoinType(JoinRelType.RIGHT)) {
+ rules.add(CoreRules.JOIN_COMMUTE_RIGHT_TO_LEFT);
+ }
+
if (!this.dialect.supportsOrderByLiteral()) {
rules.add(CoreRules.SORT_REMOVE_CONSTANT_KEYS);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
index cd809efa8b..18e865f7e9 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
@@ -17,6 +17,7 @@
package org.apache.calcite.sql.dialect;
import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlNode;
@@ -40,9 +41,27 @@ public class SqliteSqlDialect extends SqlDialect {
public static final SqlDialect DEFAULT = new
SqliteSqlDialect(DEFAULT_CONTEXT);
+ private final int majorVersion;
+ private final int minorVersion;
+
/** Creates a SqliteSqlDialect. */
public SqliteSqlDialect(SqlDialect.Context context) {
super(context);
+ this.majorVersion = context.databaseMajorVersion();
+ this.minorVersion = context.databaseMinorVersion();
+ }
+
+ @Override public boolean supportsJoinType(JoinRelType joinType) {
+ // Unknown version means we conservatively assume support for no join types
+ if (majorVersion < 0) {
+ return false;
+ }
+
+ // For non-RIGHT/FULL join types, SQLite supports them in any version
+ // For RIGHT/FULL joins, SQLite added support in 3.39.0
+ // See: https://www.sqlite.org/releaselog/3_39_0.html
+ return (joinType != JoinRelType.RIGHT && joinType != JoinRelType.FULL)
+ || (majorVersion > 3 || (majorVersion == 3 && minorVersion >= 39));
}
@Override public boolean supportsAliasedValues() {
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 fd13824c87..b96530fc2a 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
@@ -3164,10 +3164,86 @@ private SqlDialect nonOrdinalDialect() {
.withHsqldb().ok(expectedHsqldb);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7087";>[CALCITE-7087]
+ * SQLite does not support RIGHT/FULL JOIN until version 3.39.0</a>. */
+ @Test void testSqliteRightJoinRewrittenToLeftOnOldVersion() {
+ final String query = "SELECT \"EMP\".\"ENAME\", \"DEPT\".\"DNAME\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "RIGHT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"";
+ final String expected = "SELECT \"EMP\".\"ENAME\", \"DEPT\".\"DNAME\"\n"
+ + "FROM \"scott\".\"DEPT\"\n"
+ + "LEFT JOIN \"scott\".\"EMP\" ON \"DEPT\".\"DEPTNO\" =
\"EMP\".\"DEPTNO\"";
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.SCOTT)
+ .withSQLite(3, 38)
+ .ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7087";>[CALCITE-7087]
+ * SQLite does not support RIGHT/FULL JOIN until version 3.39.0</a>. */
+ @Test void testSqliteRightJoinKeptOnNewerVersion() {
+ final String query = "SELECT \"EMP\".\"ENAME\", \"DEPT\".\"DNAME\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "RIGHT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"";
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.SCOTT)
+ .withSQLite(3, 39)
+ .ok(query);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7087";>[CALCITE-7087]
+ * SQLite does not support RIGHT/FULL JOIN until version 3.39.0</a>. */
+ @Test void testSqliteFullJoinThrowsOnOldVersion() {
+ final String query = "SELECT \"EMP\".\"ENAME\", \"DEPT\".\"DNAME\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "FULL JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"";
+ final String expected = "SELECT \"ENAME\", \"DNAME\"\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"\n"
+ + "UNION ALL\n"
+ + "SELECT *\n"
+ + "FROM (SELECT \"EMP0\".\"EMPNO\","
+ + " \"EMP0\".\"ENAME\","
+ + " \"EMP0\".\"JOB\","
+ + " \"EMP0\".\"MGR\","
+ + " \"EMP0\".\"HIREDATE\","
+ + " \"EMP0\".\"SAL\","
+ + " \"EMP0\".\"COMM\","
+ + " \"EMP0\".\"DEPTNO\","
+ + " \"DEPT0\".\"DEPTNO\" AS \"DEPTNO0\","
+ + " \"DEPT0\".\"DNAME\","
+ + " \"DEPT0\".\"LOC\"\n"
+ + "FROM \"scott\".\"DEPT\" AS \"DEPT0\"\n"
+ + "LEFT JOIN \"scott\".\"EMP\" AS \"EMP0\""
+ + " ON \"DEPT0\".\"DEPTNO\" = \"EMP0\".\"DEPTNO\") AS \"t\"\n"
+ + "WHERE \"t\".\"DEPTNO\" = \"t\".\"DEPTNO0\" IS NOT TRUE) AS \"t1\"";
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.SCOTT)
+ .withSQLite(3, 38)
+ .ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7087";>[CALCITE-7087]
+ * SQLite does not support RIGHT/FULL JOIN until version 3.39.0</a>. */
+ @Test void testSqliteFullJoinKeptOnNewerVersion() {
+ final String query = "SELECT *\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "FULL JOIN \"scott\".\"DEPT\""
+ + " ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"";
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.SCOTT)
+ .withSQLite(3, 39)
+ .ok(query);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3771";>[CALCITE-3771]
* Support of TRIM function for SPARK dialect and improvement in HIVE
Dialect</a>. */
-
@Test void testHiveAndSparkTrimWithLeadingChar() {
final String query = "SELECT TRIM(LEADING 'a' from 'abcd')\n"
+ "from \"foodmart\".\"reserve_employee\"";
@@ -11027,6 +11103,13 @@ Sql withSQLite() {
return dialect(DatabaseProduct.SQLITE.getDialect());
}
+ Sql withSQLite(int majorVersion, int minorVersion) {
+ return dialect(
+ new SqliteSqlDialect(SqliteSqlDialect.DEFAULT_CONTEXT
+ .withDatabaseMajorVersion(majorVersion)
+ .withDatabaseMinorVersion(minorVersion)));
+ }
+
Sql withSybase() {
return dialect(DatabaseProduct.SYBASE.getDialect());
}
