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 f2ec11fe7e [CALCITE-6678] Calcite should support dual table query when
db provides this feature
f2ec11fe7e is described below
commit f2ec11fe7e23ecf2db903bc02c40609242993aad
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Fri Nov 8 20:40:16 2024 +0800
[CALCITE-6678] Calcite should support dual table query when db provides
this feature
---
.../apache/calcite/jdbc/CalciteConnectionImpl.java | 30 ++++++++++++
.../sql/validate/SqlAbstractConformance.java | 4 ++
.../calcite/sql/validate/SqlConformance.java | 15 ++++++
.../calcite/sql/validate/SqlConformanceEnum.java | 10 ++++
.../sql/validate/SqlDelegatingConformance.java | 4 ++
.../org/apache/calcite/test/CoreQuidemTest.java | 9 ++++
core/src/test/resources/sql/dummy.iq | 54 ++++++++++++++++++++++
7 files changed, 126 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
index 7b69e2c3ed..b01b6ad951 100644
--- a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
+++ b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
@@ -18,6 +18,7 @@ package org.apache.calcite.jdbc;
import org.apache.calcite.DataContext;
import org.apache.calcite.DataContexts;
+import org.apache.calcite.adapter.java.AbstractQueryableTable;
import org.apache.calcite.adapter.java.JavaTypeFactory;
import org.apache.calcite.avatica.AvaticaConnection;
import org.apache.calcite.avatica.AvaticaFactory;
@@ -36,6 +37,7 @@ import org.apache.calcite.jdbc.CalcitePrepare.Context;
import org.apache.calcite.linq4j.BaseQueryable;
import org.apache.calcite.linq4j.Enumerable;
import org.apache.calcite.linq4j.Enumerator;
+import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.linq4j.QueryProvider;
import org.apache.calcite.linq4j.Queryable;
@@ -46,6 +48,8 @@ import org.apache.calcite.materialize.MaterializationService;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.prepare.CalciteCatalogReader;
import org.apache.calcite.rel.type.DelegatingTypeSystem;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.TimeFrameSet;
import org.apache.calcite.rel.type.TimeFrames;
@@ -76,6 +80,7 @@ import org.checkerframework.checker.nullness.qual.Nullable;
import java.lang.reflect.Type;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
@@ -149,6 +154,10 @@ abstract class CalciteConnectionImpl
requireNonNull(rootSchema != null
? rootSchema
: CalciteSchema.createRootSchema(true));
+ // Add dual table metadata when isSupportedDualTable return true
+ if (cfg.conformance().isSupportedDualTable()) {
+ this.rootSchema.add("DUAL", new DualTable(String.class));
+ }
checkArgument(this.rootSchema.isRoot(), "must be root schema");
this.properties.put(InternalProperty.CASE_SENSITIVE, cfg.caseSensitive());
this.properties.put(InternalProperty.UNQUOTED_CASING,
cfg.unquotedCasing());
@@ -621,4 +630,25 @@ abstract class CalciteConnectionImpl
}
}
+ /** Implementation of {@link AbstractQueryableTable} for dual table. */
+ private static class DualTable extends AbstractQueryableTable {
+
+ DualTable(Class<String> clazz) {
+ super(clazz);
+ }
+
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return typeFactory.createStructType(
+ // Dual table has one column DUMMY, and defined to be VARCHAR2(1)
+ Collections.singletonList(typeFactory.createJavaType(String.class)),
+ Collections.singletonList("DUMMY"));
+ }
+
+ @SuppressWarnings("unchecked")
+ @Override public Queryable<String> asQueryable(QueryProvider queryProvider,
+ SchemaPlus schema, String tableName) {
+ // Dual table contains one row with a value X
+ return Linq4j.asEnumerable(Collections.singletonList("X")).asQueryable();
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 3205a48b40..ce750242fc 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -33,6 +33,10 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
return SqlConformanceEnum.DEFAULT.allowCharLiteralAlias();
}
+ @Override public boolean isSupportedDualTable() {
+ return SqlConformanceEnum.DEFAULT.isSupportedDualTable();
+ }
+
@Override public boolean isGroupByAlias() {
return SqlConformanceEnum.DEFAULT.isGroupByAlias();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index b8501f02f1..2517e4577b 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -177,6 +177,21 @@ public interface SqlConformance {
*/
boolean isSortByAliasObscures();
+ /**
+ * Whether this dialect supports dual table.
+ *
+ * <p>For example,
+ *
+ * <blockquote><pre>SELECT 1 + 1 FROM DUAL</pre></blockquote>
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#MYSQL_5},
+ * {@link SqlConformanceEnum#ORACLE_10},
+ * {@link SqlConformanceEnum#ORACLE_12},
+ * false otherwise.
+ */
+ boolean isSupportedDualTable();
+
/**
* Whether {@code FROM} clause is required in a {@code SELECT} statement.
*
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index a610f19f16..66358f9685 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -103,6 +103,16 @@ public enum SqlConformanceEnum implements SqlConformance {
}
}
+ @Override public boolean isSupportedDualTable() {
+ switch (this) {
+ case MYSQL_5:
+ case ORACLE_10:
+ case ORACLE_12:
+ return true;
+ default:
+ return false;
+ }
+ }
@Override public boolean isGroupByAlias() {
switch (this) {
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index c8e2f7cdd2..5cac7afca7 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -39,6 +39,10 @@ public class SqlDelegatingConformance implements
SqlConformance {
return delegate.allowCharLiteralAlias();
}
+ @Override public boolean isSupportedDualTable() {
+ return delegate.isSupportedDualTable();
+ }
+
@Override public boolean isGroupByAlias() {
return delegate.isGroupByAlias();
}
diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
index e7a3c636ab..be327c89a8 100644
--- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
@@ -121,6 +121,15 @@ class CoreQuidemTest extends QuidemTest {
SqlConformanceEnum.MYSQL_5)
.with(CalciteAssert.Config.SCOTT)
.connect();
+ case "scott-oracle":
+ // Same as "scott", but uses Oracle conformance.
+ return CalciteAssert.that()
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.ORACLE_10)
+ .with(CalciteAssert.Config.SCOTT)
+ .connect();
case "steelwheels":
return CalciteAssert.that()
.with(CalciteConnectionProperty.PARSER_FACTORY,
diff --git a/core/src/test/resources/sql/dummy.iq
b/core/src/test/resources/sql/dummy.iq
index 166e1b06d8..7647c72dd6 100644
--- a/core/src/test/resources/sql/dummy.iq
+++ b/core/src/test/resources/sql/dummy.iq
@@ -20,4 +20,58 @@ values 1;
EXPR$0
1
!ok
+
+# [CALCITE-6678] Support dual table query (enabled in MySQL, Oracle libraries)
+!set outputformat mysql
+!use scott-mysql
+
+# MySQL supports users to specify the dual table, and also supports users not
to specify the dual table.
+SELECT 1 + 1 FROM DUAL;
++--------+
+| EXPR$0 |
++--------+
+| 2 |
++--------+
+(1 row)
+
+!ok
+
+SELECT 1 + 1;
++--------+
+| EXPR$0 |
++--------+
+| 2 |
++--------+
+(1 row)
+
+!ok
+
+# Oracle supports users to specify the dual table, but not supports users not
to specify the dual table.
+!use scott-oracle
+
+SELECT 1 + 1 FROM DUAL;
++--------+
+| EXPR$0 |
++--------+
+| 2 |
++--------+
+(1 row)
+
+!ok
+
+SELECT 1 + 1;
+java.sql.SQLException: Error while executing SQL "SELECT 1 + 1": From line 1,
column 1 to line 1, column 12: SELECT must have a FROM clause
+
+!error
+
+SELECT * FROM DUAL;
++-------+
+| DUMMY |
++-------+
+| X |
++-------+
+(1 row)
+
+!ok
+
# End dummy.iq
