You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jc...@apache.org on 2019/07/12 22:16:09 UTC
[calcite] branch master updated: [CALCITE-3189] Multiple fixes for
Oracle SQL dialect
This is an automated email from the ASF dual-hosted git repository.
jcamacho pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 0732283 [CALCITE-3189] Multiple fixes for Oracle SQL dialect
0732283 is described below
commit 0732283cab7894ffdce6a22ebe5d31b28d389a4d
Author: Jesus Camacho Rodriguez <jc...@apache.org>
AuthorDate: Tue Jul 9 17:22:35 2019 -0700
[CALCITE-3189] Multiple fixes for Oracle SQL dialect
Close apache/calcite#1312
---
.../java/org/apache/calcite/sql/SqlDialect.java | 5 +
.../calcite/sql/dialect/OracleSqlDialect.java | 78 +++++++++++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 117 ++++++++++++++++++++-
3 files changed, 195 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 723eb36..3eab428 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -696,6 +696,11 @@ public class SqlDialect {
return CalendarPolicy.NULL;
}
+ /** Returns whether this dialect supports a given type. */
+ public boolean supportsDataType(RelDataType type) {
+ return true;
+ }
+
public SqlNode getCastSpec(RelDataType type) {
if (type instanceof BasicSqlType) {
int precision = type.getPrecision();
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
index ad58319..14c72da 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
@@ -17,23 +17,51 @@
package org.apache.calcite.sql.dialect;
import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeSystem;
+import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
+import org.apache.calcite.sql.SqlAbstractDateTimeLiteral;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDataTypeSpec;
+import org.apache.calcite.sql.SqlDateLiteral;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlTimeLiteral;
+import org.apache.calcite.sql.SqlTimestampLiteral;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlFloorFunction;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeName;
/**
* A <code>SqlDialect</code> implementation for the Oracle database.
*/
public class OracleSqlDialect extends SqlDialect {
+
+ /** OracleDB type system. */
+ private static final RelDataTypeSystem ORACLE_TYPE_SYSTEM =
+ new RelDataTypeSystemImpl() {
+ @Override public int getMaxPrecision(SqlTypeName typeName) {
+ switch (typeName) {
+ case VARCHAR:
+ // Maximum size of 4000 bytes for varchar2.
+ return 4000;
+ default:
+ return super.getMaxPrecision(typeName);
+ }
+ }
+ };
+
public static final SqlDialect DEFAULT =
new OracleSqlDialect(EMPTY_CONTEXT
.withDatabaseProduct(DatabaseProduct.ORACLE)
- .withIdentifierQuoteString("\""));
+ .withIdentifierQuoteString("\"")
+ .withDataTypeSystem(ORACLE_TYPE_SYSTEM));
/** Creates an OracleSqlDialect. */
public OracleSqlDialect(Context context) {
@@ -44,6 +72,38 @@ public class OracleSqlDialect extends SqlDialect {
return false;
}
+ @Override public boolean supportsDataType(RelDataType type) {
+ switch (type.getSqlTypeName()) {
+ case BOOLEAN:
+ return false;
+ default:
+ return super.supportsDataType(type);
+ }
+ }
+
+ @Override public SqlNode getCastSpec(RelDataType type) {
+ String castSpec;
+ switch (type.getSqlTypeName()) {
+ case SMALLINT:
+ castSpec = "_NUMBER(5)";
+ break;
+ case INTEGER:
+ castSpec = "_NUMBER(10)";
+ break;
+ case BIGINT:
+ castSpec = "_NUMBER(19)";
+ break;
+ case DOUBLE:
+ castSpec = "_DOUBLE PRECISION";
+ break;
+ default:
+ return super.getCastSpec(type);
+ }
+
+ return new SqlDataTypeSpec(new SqlIdentifier(castSpec, SqlParserPos.ZERO),
+ -1, -1, null, null, SqlParserPos.ZERO);
+ }
+
@Override protected boolean allowsAs() {
return false;
}
@@ -52,6 +112,22 @@ public class OracleSqlDialect extends SqlDialect {
return false;
}
+ @Override public void unparseDateTimeLiteral(SqlWriter writer,
+ SqlAbstractDateTimeLiteral literal, int leftPrec, int rightPrec) {
+ if (literal instanceof SqlTimestampLiteral) {
+ writer.literal("TO_TIMESTAMP ('"
+ + literal.toFormattedString() + "', 'YYYY-MM-DD HH24:MI:SS.FF')");
+ } else if (literal instanceof SqlDateLiteral) {
+ writer.literal("TO_DATE ('"
+ + literal.toFormattedString() + "', 'YYYY-MM-DD')");
+ } else if (literal instanceof SqlTimeLiteral) {
+ writer.literal("TO_TIME ('"
+ + literal.toFormattedString() + "', 'HH24:MI:SS.FF')");
+ } else {
+ super.unparseDateTimeLiteral(writer, literal, leftPrec, rightPrec);
+ }
+ }
+
@Override public void unparseCall(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
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 95b2b05..d249137 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
@@ -24,6 +24,9 @@ import org.apache.calcite.plan.hep.HepProgram;
import org.apache.calcite.plan.hep.HepProgramBuilder;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.rules.UnionMergeRule;
+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.RelDataTypeSystemImpl;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.runtime.FlatLists;
@@ -39,9 +42,11 @@ import org.apache.calcite.sql.dialect.CalciteSqlDialect;
import org.apache.calcite.sql.dialect.HiveSqlDialect;
import org.apache.calcite.sql.dialect.JethroDataSqlDialect;
import org.apache.calcite.sql.dialect.MysqlSqlDialect;
+import org.apache.calcite.sql.dialect.OracleSqlDialect;
import org.apache.calcite.sql.dialect.PostgresqlSqlDialect;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
+import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.test.CalciteAssert;
@@ -69,7 +74,9 @@ import static org.apache.calcite.test.Matchers.isLinux;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.notNullValue;
+import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertThat;
+import static org.junit.Assert.assertTrue;
/**
* Tests for {@link RelToSqlConverter}.
@@ -443,9 +450,17 @@ public class RelToSqlConverterTest {
@Test public void testCastLongVarchar1() {
final String query = "select cast(\"store_id\" as VARCHAR(10485761))\n"
+ " from \"expense_fact\"";
- final String expected = "SELECT CAST(\"store_id\" AS VARCHAR(256))\n"
+ final String expectedPostgreSQL = "SELECT CAST(\"store_id\" AS VARCHAR(256))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
- sql(query).withPostgresqlModifiedTypeSystem().ok(expected);
+ sql(query)
+ .withPostgresqlModifiedTypeSystem()
+ .ok(expectedPostgreSQL);
+
+ final String expectedOracle = "SELECT CAST(\"store_id\" AS VARCHAR(512))\n"
+ + "FROM \"foodmart\".\"expense_fact\"";
+ sql(query)
+ .withOracleModifiedTypeSystem()
+ .ok(expectedOracle);
}
/** Test case for
@@ -455,9 +470,17 @@ public class RelToSqlConverterTest {
@Test public void testCastLongVarchar2() {
final String query = "select cast(\"store_id\" as VARCHAR(175))\n"
+ " from \"expense_fact\"";
- final String expected = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ final String expectedPostgreSQL = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ + "FROM \"foodmart\".\"expense_fact\"";
+ sql(query)
+ .withPostgresqlModifiedTypeSystem()
+ .ok(expectedPostgreSQL);
+
+ final String expectedOracle = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
- sql(query).withPostgresqlModifiedTypeSystem().ok(expected);
+ sql(query)
+ .withOracleModifiedTypeSystem()
+ .ok(expectedOracle);
}
/** Test case for
@@ -3443,6 +3466,73 @@ public class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ @Test public void testSmallintOracle() {
+ String query = "SELECT CAST(\"department_id\" AS SMALLINT) FROM \"employee\"";
+ String expected = "SELECT CAST(\"department_id\" AS NUMBER(5))\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testBigintOracle() {
+ String query = "SELECT CAST(\"department_id\" AS BIGINT) FROM \"employee\"";
+ String expected = "SELECT CAST(\"department_id\" AS NUMBER(19))\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testDoubleOracle() {
+ String query = "SELECT CAST(\"department_id\" AS DOUBLE) FROM \"employee\"";
+ String expected = "SELECT CAST(\"department_id\" AS DOUBLE PRECISION)\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testDateLiteralOracle() {
+ String query = "SELECT DATE '1978-05-02' FROM \"employee\"";
+ String expected = "SELECT TO_DATE ('1978-05-02', 'YYYY-MM-DD')\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testTimestampLiteralOracle() {
+ String query = "SELECT TIMESTAMP '1978-05-02 12:34:56.78' FROM \"employee\"";
+ String expected = "SELECT TO_TIMESTAMP ('1978-05-02 12:34:56.78', 'YYYY-MM-DD HH24:MI:SS.FF')\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testTimeLiteralOracle() {
+ String query = "SELECT TIME '12:34:56.78' FROM \"employee\"";
+ String expected = "SELECT TO_TIME ('12:34:56.78', 'HH24:MI:SS.FF')\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withOracle()
+ .ok(expected);
+ }
+
+ @Test public void testSupportsDataType() {
+ final RelDataTypeFactory typeFactory =
+ new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
+ final RelDataType booleanDataType = typeFactory.createSqlType(SqlTypeName.BOOLEAN);
+ final RelDataType integerDataType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final SqlDialect oracleDialect = SqlDialect.DatabaseProduct.ORACLE.getDialect();
+ assertFalse(oracleDialect.supportsDataType(booleanDataType));
+ assertTrue(oracleDialect.supportsDataType(integerDataType));
+ final SqlDialect postgresqlDialect = SqlDialect.DatabaseProduct.POSTGRESQL.getDialect();
+ assertTrue(postgresqlDialect.supportsDataType(booleanDataType));
+ assertTrue(postgresqlDialect.supportsDataType(integerDataType));
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final SchemaPlus schema;
@@ -3554,6 +3644,25 @@ public class RelToSqlConverterTest {
return dialect(postgresqlSqlDialect);
}
+ Sql withOracleModifiedTypeSystem() {
+ // Oracle dialect with max length for varchar set to 512
+ final OracleSqlDialect oracleSqlDialect =
+ new OracleSqlDialect(SqlDialect.EMPTY_CONTEXT
+ .withDatabaseProduct(DatabaseProduct.ORACLE)
+ .withIdentifierQuoteString("\"")
+ .withDataTypeSystem(new RelDataTypeSystemImpl() {
+ @Override public int getMaxPrecision(SqlTypeName typeName) {
+ switch (typeName) {
+ case VARCHAR:
+ return 512;
+ default:
+ return super.getMaxPrecision(typeName);
+ }
+ }
+ }));
+ return dialect(oracleSqlDialect);
+ }
+
Sql config(SqlToRelConverter.Config config) {
return new Sql(schema, sql, dialect, config, transforms);
}