Repository: calcite Updated Branches: refs/heads/master be5404713 -> 6c7a7edd1
[CALCITE-2713] JDBC adapter may generate casts on PostgreSQL for VARCHAR type exceeding max length Close apache/calcite#944 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/6c7a7edd Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/6c7a7edd Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/6c7a7edd Branch: refs/heads/master Commit: 6c7a7edd1b9b11f2c0826db04d60cc2b62bea636 Parents: be54047 Author: Jesus Camacho Rodriguez <[email protected]> Authored: Mon Dec 3 14:15:52 2018 -0800 Committer: Jesus Camacho Rodriguez <[email protected]> Committed: Mon Dec 3 16:19:00 2018 -0800 ---------------------------------------------------------------------- .../java/org/apache/calcite/sql/SqlDialect.java | 52 ++++++++++++++++---- .../sql/dialect/PostgresqlSqlDialect.java | 26 +++++++++- .../rel/rel2sql/RelToSqlConverterTest.java | 47 ++++++++++++++++++ 3 files changed, 114 insertions(+), 11 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/6c7a7edd/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- 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 c70aeeb..819c9a9 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -23,6 +23,7 @@ import org.apache.calcite.linq4j.function.Experimental; import org.apache.calcite.rel.RelFieldCollation; 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.dialect.AnsiSqlDialect; import org.apache.calcite.sql.dialect.CalciteSqlDialect; import org.apache.calcite.sql.dialect.JethroDataSqlDialect; @@ -133,6 +134,7 @@ public class SqlDialect { private final String identifierEscapedQuote; private final DatabaseProduct databaseProduct; protected final NullCollation nullCollation; + private final RelDataTypeSystem dataTypeSystem; //~ Constructors ----------------------------------------------------------- @@ -190,6 +192,7 @@ public class SqlDialect { */ public SqlDialect(Context context) { this.nullCollation = Objects.requireNonNull(context.nullCollation()); + this.dataTypeSystem = Objects.requireNonNull(context.dataTypeSystem()); this.databaseProduct = Objects.requireNonNull(context.databaseProduct()); String identifierQuoteString = context.identifierQuoteString(); @@ -214,7 +217,7 @@ public class SqlDialect { /** Creates an empty context. Use {@link #EMPTY_CONTEXT} if possible. */ protected static Context emptyContext() { return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1, null, - NullCollation.HIGH, JethroDataSqlDialect.JethroInfo.EMPTY); + NullCollation.HIGH, RelDataTypeSystemImpl.DEFAULT, JethroDataSqlDialect.JethroInfo.EMPTY); } /** @@ -291,6 +294,11 @@ public class SqlDialect { } } + /** Returns the type system implementation for this dialect. */ + public RelDataTypeSystem getTypeSystem() { + return dataTypeSystem; + } + /** * Encloses an identifier in quotation marks appropriate for the current SQL * dialect. @@ -684,9 +692,18 @@ public class SqlDialect { public SqlNode getCastSpec(RelDataType type) { if (type instanceof BasicSqlType) { + int precision = type.getPrecision(); + switch (type.getSqlTypeName()) { + case VARCHAR: + // if needed, adjust varchar length to max length supported by the system + int maxPrecision = getTypeSystem().getMaxPrecision(type.getSqlTypeName()); + if (type.getPrecision() > maxPrecision) { + precision = maxPrecision; + } + } return new SqlDataTypeSpec( new SqlIdentifier(type.getSqlTypeName().name(), SqlParserPos.ZERO), - type.getPrecision(), + precision, type.getScale(), type.getCharset() != null && supportsCharSet() @@ -1037,6 +1054,8 @@ public class SqlDialect { Context withIdentifierQuoteString(String identifierQuoteString); @Nonnull NullCollation nullCollation(); Context withNullCollation(@Nonnull NullCollation nullCollation); + @Nonnull RelDataTypeSystem dataTypeSystem(); + Context withDataTypeSystem(@Nonnull RelDataTypeSystem dataTypeSystem); JethroDataSqlDialect.JethroInfo jethroInfo(); Context withJethroInfo(JethroDataSqlDialect.JethroInfo jethroInfo); } @@ -1050,12 +1069,14 @@ public class SqlDialect { private final int databaseMinorVersion; private final String identifierQuoteString; private final NullCollation nullCollation; + private final RelDataTypeSystem dataTypeSystem; private final JethroDataSqlDialect.JethroInfo jethroInfo; private ContextImpl(DatabaseProduct databaseProduct, String databaseProductName, String databaseVersion, int databaseMajorVersion, int databaseMinorVersion, String identifierQuoteString, NullCollation nullCollation, + RelDataTypeSystem dataTypeSystem, JethroDataSqlDialect.JethroInfo jethroInfo) { this.databaseProduct = Objects.requireNonNull(databaseProduct); this.databaseProductName = databaseProductName; @@ -1064,6 +1085,7 @@ public class SqlDialect { this.databaseMinorVersion = databaseMinorVersion; this.identifierQuoteString = identifierQuoteString; this.nullCollation = Objects.requireNonNull(nullCollation); + this.dataTypeSystem = Objects.requireNonNull(dataTypeSystem); this.jethroInfo = Objects.requireNonNull(jethroInfo); } @@ -1075,7 +1097,7 @@ public class SqlDialect { @Nonnull DatabaseProduct databaseProduct) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } public String databaseProductName() { @@ -1085,7 +1107,7 @@ public class SqlDialect { public Context withDatabaseProductName(String databaseProductName) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } public String databaseVersion() { @@ -1095,7 +1117,7 @@ public class SqlDialect { public Context withDatabaseVersion(String databaseVersion) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } public int databaseMajorVersion() { @@ -1105,7 +1127,7 @@ public class SqlDialect { public Context withDatabaseMajorVersion(int databaseMajorVersion) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } public int databaseMinorVersion() { @@ -1115,7 +1137,7 @@ public class SqlDialect { public Context withDatabaseMinorVersion(int databaseMinorVersion) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } public String identifierQuoteString() { @@ -1125,7 +1147,7 @@ public class SqlDialect { public Context withIdentifierQuoteString(String identifierQuoteString) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } @Nonnull public NullCollation nullCollation() { @@ -1135,7 +1157,17 @@ public class SqlDialect { public Context withNullCollation(@Nonnull NullCollation nullCollation) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); + } + + @Nonnull public RelDataTypeSystem dataTypeSystem() { + return dataTypeSystem; + } + + public Context withDataTypeSystem(@Nonnull RelDataTypeSystem dataTypeSystem) { + return new ContextImpl(databaseProduct, databaseProductName, + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } @Nonnull public JethroDataSqlDialect.JethroInfo jethroInfo() { @@ -1145,7 +1177,7 @@ public class SqlDialect { public Context withJethroInfo(JethroDataSqlDialect.JethroInfo jethroInfo) { return new ContextImpl(databaseProduct, databaseProductName, databaseVersion, databaseMajorVersion, databaseMinorVersion, - identifierQuoteString, nullCollation, jethroInfo); + identifierQuoteString, nullCollation, dataTypeSystem, jethroInfo); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/6c7a7edd/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java index 61028a9..5f1b8d0 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java @@ -18,6 +18,8 @@ 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.SqlCall; import org.apache.calcite.sql.SqlDataTypeSpec; import org.apache.calcite.sql.SqlDialect; @@ -27,15 +29,37 @@ import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlWriter; import org.apache.calcite.sql.fun.SqlFloorFunction; import org.apache.calcite.sql.parser.SqlParserPos; +import org.apache.calcite.sql.type.SqlTypeName; /** * A <code>SqlDialect</code> implementation for the PostgreSQL database. */ public class PostgresqlSqlDialect extends SqlDialect { + + /** PostgreSQL type system. */ + private static final RelDataTypeSystem POSTGRESQL_TYPE_SYSTEM = + new RelDataTypeSystemImpl() { + @Override public int getMaxPrecision(SqlTypeName typeName) { + switch (typeName) { + case VARCHAR: + // From htup_details.h in postgresql: + // MaxAttrSize is a somewhat arbitrary upper limit on the declared size of + // data fields of char(n) and similar types. It need not have anything + // directly to do with the *actual* upper limit of varlena values, which + // is currently 1Gb (see TOAST structures in postgres.h). I've set it + // at 10Mb which seems like a reasonable number --- tgl 8/6/00. */ + return 10 * 1024 * 1024; + default: + return super.getMaxPrecision(typeName); + } + } + }; + public static final SqlDialect DEFAULT = new PostgresqlSqlDialect(EMPTY_CONTEXT .withDatabaseProduct(DatabaseProduct.POSTGRESQL) - .withIdentifierQuoteString("\"")); + .withIdentifierQuoteString("\"") + .withDataTypeSystem(POSTGRESQL_TYPE_SYSTEM)); /** Creates a PostgresqlSqlDialect. */ public PostgresqlSqlDialect(Context context) { http://git-wip-us.apache.org/repos/asf/calcite/blob/6c7a7edd/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- 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 93676c1..ca27782 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,12 +24,14 @@ 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.RelDataTypeSystemImpl; import org.apache.calcite.rex.RexNode; import org.apache.calcite.runtime.FlatLists; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlDialect.Context; +import org.apache.calcite.sql.SqlDialect.DatabaseProduct; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlSelect; import org.apache.calcite.sql.SqlWriter; @@ -37,8 +39,10 @@ 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.PostgresqlSqlDialect; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParser; +import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql2rel.SqlToRelConverter; import org.apache.calcite.test.CalciteAssert; import org.apache.calcite.test.RelBuilderTest; @@ -268,6 +272,30 @@ public class RelToSqlConverterTest { } /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2713";>[CALCITE-2713] + * JDBC adapter may generate casts on PostgreSQL for VARCHAR type exceeding + * max length</a>. */ + @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" + + "FROM \"foodmart\".\"expense_fact\""; + sql(query).withPostgresqlModifiedTypeSystem().ok(expected); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2713";>[CALCITE-2713] + * JDBC adapter may generate casts on PostgreSQL for VARCHAR type exceeding + * max length</a>. */ + @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" + + "FROM \"foodmart\".\"expense_fact\""; + sql(query).withPostgresqlModifiedTypeSystem().ok(expected); + } + + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1174";>[CALCITE-1174] * When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)</a>. */ @Test public void testSum0BecomesCoalesce() { @@ -2980,6 +3008,25 @@ public class RelToSqlConverterTest { return dialect(SqlDialect.DatabaseProduct.VERTICA.getDialect()); } + Sql withPostgresqlModifiedTypeSystem() { + // Postgresql dialect with max length for varchar set to 256 + final PostgresqlSqlDialect postgresqlSqlDialect = + new PostgresqlSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseProduct(DatabaseProduct.POSTGRESQL) + .withIdentifierQuoteString("\"") + .withDataTypeSystem(new RelDataTypeSystemImpl() { + @Override public int getMaxPrecision(SqlTypeName typeName) { + switch (typeName) { + case VARCHAR: + return 256; + default: + return super.getMaxPrecision(typeName); + } + } + })); + return dialect(postgresqlSqlDialect); + } + Sql config(SqlToRelConverter.Config config) { return new Sql(schema, sql, dialect, config, transforms); }
