DRILL-3216: Part 2--Fix existing(+) INFORMATION_SCHEMA.COLUMNS columns. Added unit test. [TempInformationSchemaColumnsTest]
Fixed/added INFORMATION_SCHEMA.COLUMNS columns: - Changed -1 to NULL. - Moved column NUMERIC_PRECISION to correct position. - Fixed column ORDINAL_POSITION from 0-based to 1-based. - Moved CHAR length to column CHARACTER_MAXIMUM_LENGTH. - Moved BINARY and VARBINARY length to columnCHARACTER_MAXIMUM_LENGTH. - Added precision and scale values for integer types and floating-point types. - Added column COLUMN_DEFAULT. - Added column CHARACTER_OCTET_LENGTH. - Added column DATETIME_PRECISION. - Added column INTERVAL_TYPE. - Added column INTERVAL_PRECISION. - Fixed column DATA_TYPE to correct form of type names: - "INTERVAL_..." -> "INTERVAL" - short (e.g., "CHAR") to specified (e.g., "CHARACTER") Applied COLUMNS to JDBC DatabaseMetaData.getColumns() implementation: - Changed some getColumns() result values: - type names (DATA_TYPE changes) - precision radix for some cases - Adapted existing uses of COLUMNS data. - Applied new COLUMNS data (e.g., interval precision). - Updated getColumns() test (re changed result values). - Augmented getColumns() test (e.g., more intervals). Updated other tests using INFORMATION_SCHEMA.COLUMNS: - -1 -> NULL - integer types have precision and scale values - "CHAR" -> "CHARACTER", etc. [TestInfoSchema, TestInfoSchemaOnHiveStorage, TestInformationSchemaColumns, TestViewSupport] Project: http://git-wip-us.apache.org/repos/asf/drill/repo Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/c668a10d Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/c668a10d Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/c668a10d Branch: refs/heads/master Commit: c668a10dd4c1295f8ec944240974288ed5a24be5 Parents: b018234 Author: dbarclay <[email protected]> Authored: Thu Jun 4 14:31:04 2015 -0700 Committer: Parth Chandra <[email protected]> Committed: Wed Jun 17 11:46:05 2015 -0700 ---------------------------------------------------------------------- .../exec/hive/TestInfoSchemaOnHiveStorage.java | 21 +- .../exec/store/ischema/InfoSchemaConstants.java | 26 +- .../exec/store/ischema/InfoSchemaTable.java | 37 +- .../drill/exec/store/ischema/Records.java | 257 +- .../apache/drill/exec/sql/TestInfoSchema.java | 36 +- .../apache/drill/exec/sql/TestViewSupport.java | 6 +- .../org/apache/drill/jdbc/impl/MetaImpl.java | 336 +-- .../jdbc/DatabaseMetaDataGetColumnsTest.java | 665 +++-- .../jdbc/test/TestInformationSchemaColumns.java | 2626 ++++++++++++++++++ 9 files changed, 3502 insertions(+), 508 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/hive/TestInfoSchemaOnHiveStorage.java ---------------------------------------------------------------------- diff --git a/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/hive/TestInfoSchemaOnHiveStorage.java b/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/hive/TestInfoSchemaOnHiveStorage.java index a7bd8e2..0f578ad 100644 --- a/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/hive/TestInfoSchemaOnHiveStorage.java +++ b/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/hive/TestInfoSchemaOnHiveStorage.java @@ -24,7 +24,7 @@ import org.junit.Test; public class TestInfoSchemaOnHiveStorage extends HiveTestBase { private static final String[] baselineCols = new String[] {"COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE"}; private static final Object[] expVal1 = new Object[] {"key", "INTEGER", "YES"}; - private static final Object[] expVal2 = new Object[] {"value", "VARCHAR", "YES"}; + private static final Object[] expVal2 = new Object[] {"value", "CHARACTER VARYING", "YES"}; @Test public void showTablesFromDb() throws Exception{ @@ -142,7 +142,9 @@ public class TestInfoSchemaOnHiveStorage extends HiveTestBase { @Test public void varCharMaxLengthAndDecimalPrecisionInInfoSchema() throws Exception{ - final String query = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE " + + final String query = + "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " + + " NUMERIC_PRECISION_RADIX, NUMERIC_PRECISION, NUMERIC_SCALE " + "FROM INFORMATION_SCHEMA.`COLUMNS` " + "WHERE TABLE_SCHEMA = 'hive.default' AND TABLE_NAME = 'infoschematest' AND " + "(COLUMN_NAME = 'stringtype' OR COLUMN_NAME = 'varchartype' OR " + @@ -152,11 +154,16 @@ public class TestInfoSchemaOnHiveStorage extends HiveTestBase { .sqlQuery(query) .unOrdered() .optionSettingQueriesForTestQuery("USE hive") - .baselineColumns("COLUMN_NAME", "DATA_TYPE", "CHARACTER_MAXIMUM_LENGTH", "NUMERIC_PRECISION", "NUMERIC_SCALE") - .baselineValues("inttype", "INTEGER", -1, -1, -1) - .baselineValues("decimaltype", "DECIMAL", -1, 38, 2) - .baselineValues("stringtype", "VARCHAR", 65535, -1, -1) - .baselineValues("varchartype", "VARCHAR", 20, -1, -1) + .baselineColumns("COLUMN_NAME", + "DATA_TYPE", + "CHARACTER_MAXIMUM_LENGTH", + "NUMERIC_PRECISION_RADIX", + "NUMERIC_PRECISION", + "NUMERIC_SCALE") + .baselineValues("inttype", "INTEGER", null, 2, 32, 0) + .baselineValues("decimaltype", "DECIMAL", null, 10, 38, 2) + .baselineValues("stringtype", "CHARACTER VARYING", 65535, null, null, null) + .baselineValues("varchartype", "CHARACTER VARYING", 20, null, null, null) .go(); } http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaConstants.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaConstants.java b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaConstants.java index 1c29235..78c19c1 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaConstants.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaConstants.java @@ -61,14 +61,38 @@ public final class InfoSchemaConstants { // Remaining VIEWS column names: public static final String VIEWS_COL_VIEW_DEFINITION = "VIEW_DEFINITION"; + // COLUMNS columns, from SQL standard: + // 1. TABLE_CATALOG + // 2. TABLE_SCHEMA + // 3. TABLE_NAME + // 4. COLUMN_NAME + // 5. ORDINAL_POSITION + // 6. COLUMN_DEFAULT + // 7. IS_NULLABLE + // 8. DATA_TYPE + // 9. CHARACTER_MAXIMUM_LENGTH + // 10. CHARACTER_OCTET_LENGTH + // 11. NUMERIC_PRECISION + // 12. NUMERIC_PRECISION_RADIX + // 13. NUMERIC_SCALE + // 14. DATETIME_PRECISION + // 15. INTERVAL_TYPE + // 16. INTERVAL_PRECISION + // 17. CHARACTER_SET_CATALOG ... + // Remaining COLUMNS column names: public static final String COLS_COL_COLUMN_NAME = "COLUMN_NAME"; public static final String COLS_COL_ORDINAL_POSITION = "ORDINAL_POSITION"; + public static final String COLS_COL_COLUMN_DEFAULT = "COLUMN_DEFAULT"; public static final String COLS_COL_IS_NULLABLE = "IS_NULLABLE"; public static final String COLS_COL_DATA_TYPE = "DATA_TYPE"; public static final String COLS_COL_CHARACTER_MAXIMUM_LENGTH = "CHARACTER_MAXIMUM_LENGTH"; + public static final String COLS_COL_CHARACTER_OCTET_LENGTH = "CHARACTER_OCTET_LENGTH"; + public static final String COLS_COL_NUMERIC_PRECISION = "NUMERIC_PRECISION"; public static final String COLS_COL_NUMERIC_PRECISION_RADIX = "NUMERIC_PRECISION_RADIX"; public static final String COLS_COL_NUMERIC_SCALE = "NUMERIC_SCALE"; - public static final String COLS_COL_NUMERIC_PRECISION = "NUMERIC_PRECISION"; + public static final String COLS_COL_DATETIME_PRECISION = "DATETIME_PRECISION"; + public static final String COLS_COL_INTERVAL_TYPE = "INTERVAL_TYPE"; + public static final String COLS_COL_INTERVAL_PRECISION = "INTERVAL_PRECISION"; } http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaTable.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaTable.java b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaTable.java index 0f8b8a0..3f8d35f 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaTable.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/InfoSchemaTable.java @@ -89,6 +89,8 @@ public abstract class InfoSchemaTable { /** Layout for the CATALOGS table. */ static public class Catalogs extends InfoSchemaTable { + // NOTE: Nothing seems to verify that the types here (apparently used + // by SQL validation) match the types of the fields in Records.Catalogs). private static final List<Field> fields = ImmutableList.of( Field.create(CATS_COL_CATALOG_NAME, VARCHAR), Field.create(CATS_COL_CATALOG_DESCRIPTION, VARCHAR), @@ -106,6 +108,8 @@ public abstract class InfoSchemaTable { /** Layout for the SCHEMATA table. */ public static class Schemata extends InfoSchemaTable { + // NOTE: Nothing seems to verify that the types here (apparently used + // by SQL validation) match the types of the fields in Records.Schemata). private static final List<Field> fields = ImmutableList.of( Field.create(SCHS_COL_CATALOG_NAME, VARCHAR), Field.create(SCHS_COL_SCHEMA_NAME, VARCHAR), @@ -125,6 +129,8 @@ public abstract class InfoSchemaTable { /** Layout for the TABLES table. */ public static class Tables extends InfoSchemaTable { + // NOTE: Nothing seems to verify that the types here (apparently used + // by SQL validation) match the types of the fields in Records.Tables). private static final List<Field> fields = ImmutableList.of( Field.create(SHRD_COL_TABLE_CATALOG, VARCHAR), Field.create(SHRD_COL_TABLE_SCHEMA, VARCHAR), @@ -143,6 +149,8 @@ public abstract class InfoSchemaTable { /** Layout for the VIEWS table. */ static public class Views extends InfoSchemaTable { + // NOTE: Nothing seems to verify that the types here (apparently used + // by SQL validation) match the types of the fields in Records.Views). private static final List<Field> fields = ImmutableList.of( Field.create(SHRD_COL_TABLE_CATALOG, VARCHAR), Field.create(SHRD_COL_TABLE_SCHEMA, VARCHAR), @@ -161,18 +169,45 @@ public abstract class InfoSchemaTable { /** Layout for the COLUMNS table. */ public static class Columns extends InfoSchemaTable { + // COLUMNS columns, from SQL standard: + // 1. TABLE_CATALOG + // 2. TABLE_SCHEMA + // 3. TABLE_NAME + // 4. COLUMN_NAME + // 5. ORDINAL_POSITION + // 6. COLUMN_DEFAULT + // 7. IS_NULLABLE + // 8. DATA_TYPE + // 9. CHARACTER_MAXIMUM_LENGTH + // 10. CHARACTER_OCTET_LENGTH + // 11. NUMERIC_PRECISION + // 12. NUMERIC_PRECISION_RADIX + // 13. NUMERIC_SCALE + // 14. DATETIME_PRECISION + // 15. INTERVAL_TYPE + // 16. INTERVAL_PRECISION + // 17. CHARACTER_SET_CATALOG ... + + // NOTE: Nothing seems to verify that the types here (apparently used + // by SQL validation) match the types of the fields in Records.Columns). private static final List<Field> fields = ImmutableList.of( Field.create(SHRD_COL_TABLE_CATALOG, VARCHAR), Field.create(SHRD_COL_TABLE_SCHEMA, VARCHAR), Field.create(SHRD_COL_TABLE_NAME, VARCHAR), Field.create(COLS_COL_COLUMN_NAME, VARCHAR), Field.create(COLS_COL_ORDINAL_POSITION, INT), + Field.create(COLS_COL_COLUMN_DEFAULT, VARCHAR), Field.create(COLS_COL_IS_NULLABLE, VARCHAR), Field.create(COLS_COL_DATA_TYPE, VARCHAR), Field.create(COLS_COL_CHARACTER_MAXIMUM_LENGTH, INT), + Field.create(COLS_COL_CHARACTER_OCTET_LENGTH, INT), + Field.create(COLS_COL_NUMERIC_PRECISION, INT), Field.create(COLS_COL_NUMERIC_PRECISION_RADIX, INT), Field.create(COLS_COL_NUMERIC_SCALE, INT), - Field.create(COLS_COL_NUMERIC_PRECISION, INT)); + Field.create(COLS_COL_DATETIME_PRECISION, INT), + Field.create(COLS_COL_INTERVAL_TYPE, VARCHAR), + Field.create(COLS_COL_INTERVAL_PRECISION, INT) + ); public Columns() { super(TAB_COLUMNS, fields); http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/Records.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/Records.java b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/Records.java index 5a9387f..64b9907 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/Records.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ischema/Records.java @@ -18,9 +18,13 @@ package org.apache.drill.exec.store.ischema; +import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.drill.exec.planner.types.DrillRelDataTypeSystem; +import org.slf4j.Logger; +import static org.slf4j.LoggerFactory.getLogger; public class Records { @@ -41,18 +45,32 @@ public class Records { /** Pojo object for a record in INFORMATION_SCHEMA.COLUMNS */ public static class Column { + private static final Logger logger = getLogger( Column.class ); + + // TODO: Resolve: Do we have such a constant elsewhere? If so, use it. + // If not, where should this really live?: + private static final int MAX_UTF8_BYTES_PER_CHARACTER = 4; + public final String TABLE_CATALOG; public final String TABLE_SCHEMA; public final String TABLE_NAME; public final String COLUMN_NAME; public final int ORDINAL_POSITION; + public final String COLUMN_DEFAULT; public final String IS_NULLABLE; public final String DATA_TYPE; - public final int CHARACTER_MAXIMUM_LENGTH; - public final int NUMERIC_PRECISION_RADIX; - public final int NUMERIC_SCALE; - public final int NUMERIC_PRECISION; + public final Integer CHARACTER_MAXIMUM_LENGTH; + public final Integer CHARACTER_OCTET_LENGTH; + public final Integer NUMERIC_PRECISION; + public final Integer NUMERIC_PRECISION_RADIX; + public final Integer NUMERIC_SCALE; + public final Integer DATETIME_PRECISION; + public final String INTERVAL_TYPE; + public final Integer INTERVAL_PRECISION; + // See: + // - ISO/IEC 9075-11:2011(E) 5.21 COLUMNS view + // - ISO/IEC 9075-11:2011(E) 6.22 DATA_TYPE_DESCRIPTOR base table public Column(String catalog, String schemaName, String tableName, RelDataTypeField field) { this.TABLE_CATALOG = catalog; this.TABLE_SCHEMA = schemaName; @@ -60,34 +78,227 @@ public class Records { this.COLUMN_NAME = field.getName(); final RelDataType relDataType = field.getType(); + + // (Like SQL data type names, but not standard ones.) final SqlTypeName sqlTypeName = relDataType.getSqlTypeName(); - this.ORDINAL_POSITION = field.getIndex(); - this.IS_NULLABLE = relDataType.isNullable() ? "YES" : "NO"; + // Get 1-based column ordinal position from 0-based field/column index: + this.ORDINAL_POSITION = 1 + field.getIndex(); - if (sqlTypeName == SqlTypeName.ARRAY || sqlTypeName == SqlTypeName.MAP || sqlTypeName == SqlTypeName.ROW) { - // For complex types use SqlTypeName's toString method to display the - // inside elements. - String typeString = relDataType.toString(); + this.COLUMN_DEFAULT = null; + this.IS_NULLABLE = relDataType.isNullable() ? "YES" : "NO"; - // RelDataType.toString prints "RecordType" for "STRUCT". - this.DATA_TYPE = relDataType.toString().replace("RecordType", "STRUCT"); - } else { - this.DATA_TYPE = sqlTypeName.toString(); + switch ( sqlTypeName ) { + // 1. SqlTypeName enumerators whose names (currently) match SQL's values + // for DATA_TYPE (those which have been seen in tests and verified): + case BOOLEAN: + case TINYINT: + case SMALLINT: + case INTEGER: + case BIGINT: + case DECIMAL: + case FLOAT: + case REAL: + case DOUBLE: + case DATE: + case TIME: + case TIMESTAMP: + // INTERVAL_YEAR_MONTH - Not identical; see below. + // INTERVAL_DAY_TIME - Not identical; see below. + // CHAR - Not identical; see below. + // VARCHAR - Not identical; see below. + case BINARY: + // VARBINARY - Not identical; see below. + // TODO(DRILL-3253): Update these once we have test plugin supporting + // all needed types: + // NULL - Not seen/explicitly addressed. + // ANY - " " + // SYMBOL - " " + // MULTISET - " " + case ARRAY: + case MAP: + // DISTINCT - Not seen/explicitly addressed. + // STRUCTURED - " " + // ROW - " " + // OTHER - " " + // CURSOR - " " + // COLUMN_LIST - " " + this.DATA_TYPE = sqlTypeName.name(); + break; + // 2. SqlTypeName enumerators whose names (currently) do not match SQL's + // values for DATA_TYPE: + case CHAR: this.DATA_TYPE = "CHARACTER"; break; + case VARCHAR: this.DATA_TYPE = "CHARACTER VARYING"; break; + case VARBINARY: this.DATA_TYPE = "BINARY VARYING"; break; + case INTERVAL_YEAR_MONTH: this.DATA_TYPE = "INTERVAL"; break; + case INTERVAL_DAY_TIME: this.DATA_TYPE = "INTERVAL"; break; + // 3: SqlTypeName enumerators not yet seen and confirmed or handled. + default: + logger.warn( "Type not handled explicitly (code needs review): " + + sqlTypeName ); + this.DATA_TYPE = sqlTypeName.toString(); + break; } - this.NUMERIC_PRECISION_RADIX = (sqlTypeName == SqlTypeName.DECIMAL) ? 10 : -1; // TODO: where do we get radix? + // Note: The branches are in the same order as SQL constraint + // DATA_TYPE_DESCRIPTOR_DATA_TYPE_CHECK_COMBINATIONS. + switch ( sqlTypeName ) { + case CHAR: + case VARCHAR: + this.CHARACTER_MAXIMUM_LENGTH = relDataType.getPrecision(); + if ( this.CHARACTER_MAXIMUM_LENGTH + < Integer.MAX_VALUE / MAX_UTF8_BYTES_PER_CHARACTER ) { + this.CHARACTER_OCTET_LENGTH = + this.CHARACTER_MAXIMUM_LENGTH * MAX_UTF8_BYTES_PER_CHARACTER; + } + else { + this.CHARACTER_OCTET_LENGTH = Integer.MAX_VALUE; + } + this.NUMERIC_PRECISION = null; + this.NUMERIC_PRECISION_RADIX = null; + this.NUMERIC_SCALE = null; + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case BINARY: + case VARBINARY: + this.CHARACTER_MAXIMUM_LENGTH = relDataType.getPrecision(); + this.CHARACTER_OCTET_LENGTH = this.CHARACTER_MAXIMUM_LENGTH; + this.NUMERIC_PRECISION = null; + this.NUMERIC_PRECISION_RADIX = null; + this.NUMERIC_SCALE = null; + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case TINYINT: + case SMALLINT: + case INTEGER: + case BIGINT: + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + // This NUMERIC_PRECISION is in bits since NUMERIC_PRECISION_RADIX is 2. + switch ( sqlTypeName ) { + case TINYINT: NUMERIC_PRECISION = 8; break; + case SMALLINT: NUMERIC_PRECISION = 16; break; + case INTEGER: NUMERIC_PRECISION = 32; break; + case BIGINT: NUMERIC_PRECISION = 64; break; + default: + throw new AssertionError( + "Unexpected " + sqlTypeName.getClass().getName() + " value " + + sqlTypeName ); + //break; + } + this.NUMERIC_PRECISION_RADIX = 2; + this.NUMERIC_SCALE = 0; + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case DECIMAL: + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + // This NUMERIC_PRECISION is in decimal digits since + // NUMERIC_PRECISION_RADIX is 10. + this.NUMERIC_PRECISION = relDataType.getPrecision(); + this.NUMERIC_PRECISION_RADIX = 10; + this.NUMERIC_SCALE = relDataType.getScale(); + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case REAL: + case FLOAT: + case DOUBLE: + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + // This NUMERIC_PRECISION is in bits since NUMERIC_PRECISION_RADIX is 2. + switch ( sqlTypeName ) { + case REAL: NUMERIC_PRECISION = 24; break; + case FLOAT: NUMERIC_PRECISION = 24; break; + case DOUBLE: NUMERIC_PRECISION = 53; break; + default: + throw new AssertionError( + "Unexpected type " + sqlTypeName + " in approximate-types branch" ); + //break; + } + this.NUMERIC_PRECISION_RADIX = 2; + this.NUMERIC_SCALE = null; + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case DATE: + case TIME: + case TIMESTAMP: + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + this.NUMERIC_PRECISION = null; + this.NUMERIC_PRECISION_RADIX = null; + this.NUMERIC_SCALE = null; + // TODO: Resolve whether this gets _SQL_-defined precision. + // (RelDataType.getPrecision()'s doc. says "JDBC-defined + // precision.") + this.DATETIME_PRECISION = relDataType.getPrecision(); + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; + case INTERVAL_YEAR_MONTH: + case INTERVAL_DAY_TIME: + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + this.NUMERIC_PRECISION = null; + this.NUMERIC_PRECISION_RADIX = null; + this.NUMERIC_SCALE = null; + switch ( sqlTypeName ) { + case INTERVAL_YEAR_MONTH: + // NOTE: Apparently can't get use RelDataType, etc.; it seems to + // apply a default fractional seconds precision of 6 for SECOND, + // even though SECOND does not exist for this case. + this.DATETIME_PRECISION = 0; + break; + case INTERVAL_DAY_TIME: + this.DATETIME_PRECISION = + relDataType + .getIntervalQualifier() + .getFractionalSecondPrecision( + DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM ); + break; + default: + throw new AssertionError( + "Unexpected type " + sqlTypeName + " in interval-types branch" ); + //break; + } + { + final TimeUnit start = relDataType.getIntervalQualifier().getStartUnit(); + final TimeUnit end = relDataType.getIntervalQualifier().getEndUnit(); + // NOTE: getEndUnit() returns null instead of YEAR for "INTERVAL YEAR". + if ( start == end || null == end ) { + this.INTERVAL_TYPE = start.name(); + } + else { + this.INTERVAL_TYPE = start + " TO " + end; + } + } + this.INTERVAL_PRECISION = + relDataType + .getIntervalQualifier() + .getStartPrecision(DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM); + break; - if (sqlTypeName == SqlTypeName.VARCHAR) { - // Max length is stored as precision in Optiq. - this.CHARACTER_MAXIMUM_LENGTH = (sqlTypeName.allowsPrec()) ? relDataType.getPrecision() : -1; - this.NUMERIC_PRECISION = -1; - } else { - this.CHARACTER_MAXIMUM_LENGTH = -1; - this.NUMERIC_PRECISION = (sqlTypeName.allowsPrec()) ? relDataType.getPrecision() : -1; + default: + this.NUMERIC_PRECISION_RADIX = null; + this.CHARACTER_MAXIMUM_LENGTH = null; + this.CHARACTER_OCTET_LENGTH = null; + this.NUMERIC_PRECISION = null; + this.NUMERIC_SCALE = null; + this.DATETIME_PRECISION = null; + this.INTERVAL_TYPE = null; + this.INTERVAL_PRECISION = null; + break; } - this.NUMERIC_SCALE = (sqlTypeName.allowsScale())?relDataType.getScale(): -1; } } http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestInfoSchema.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestInfoSchema.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestInfoSchema.java index 9a35be4..8e7498f 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestInfoSchema.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestInfoSchema.java @@ -153,9 +153,9 @@ public class TestInfoSchema extends BaseTestQuery { .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("CATALOG_NAME", "VARCHAR", "NO") - .baselineValues("CATALOG_DESCRIPTION", "VARCHAR", "NO") - .baselineValues("CATALOG_CONNECT", "VARCHAR", "NO") + .baselineValues("CATALOG_NAME", "CHARACTER VARYING", "NO") + .baselineValues("CATALOG_DESCRIPTION", "CHARACTER VARYING", "NO") + .baselineValues("CATALOG_CONNECT", "CHARACTER VARYING", "NO") .go(); } @@ -165,10 +165,10 @@ public class TestInfoSchema extends BaseTestQuery { .sqlQuery("DESCRIBE INFORMATION_SCHEMA.`TABLES`") .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("TABLE_CATALOG", "VARCHAR", "NO") - .baselineValues("TABLE_SCHEMA", "VARCHAR", "NO") - .baselineValues("TABLE_NAME", "VARCHAR", "NO") - .baselineValues("TABLE_TYPE", "VARCHAR", "NO") + .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_SCHEMA", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_NAME", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_TYPE", "CHARACTER VARYING", "NO") .go(); } @@ -190,10 +190,10 @@ public class TestInfoSchema extends BaseTestQuery { .sqlQuery("DESCRIBE INFORMATION_SCHEMA.`TABLES`") .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("TABLE_CATALOG", "VARCHAR", "NO") - .baselineValues("TABLE_SCHEMA", "VARCHAR", "NO") - .baselineValues("TABLE_NAME", "VARCHAR", "NO") - .baselineValues("TABLE_TYPE", "VARCHAR", "NO") + .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_SCHEMA", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_NAME", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_TYPE", "CHARACTER VARYING", "NO") .go(); } finally { test("DROP VIEW dfs_test.tmp.`TABLES`"); @@ -207,7 +207,7 @@ public class TestInfoSchema extends BaseTestQuery { .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("TABLE_CATALOG", "VARCHAR", "NO") + .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "NO") .go(); } @@ -217,7 +217,7 @@ public class TestInfoSchema extends BaseTestQuery { .sqlQuery("DESCRIBE INFORMATION_SCHEMA.`TABLES` TABLE_CATALOG") .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("TABLE_CATALOG", "VARCHAR", "NO") + .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "NO") .go(); } @@ -228,9 +228,9 @@ public class TestInfoSchema extends BaseTestQuery { .unOrdered() .optionSettingQueriesForTestQuery("USE INFORMATION_SCHEMA") .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("TABLE_CATALOG", "VARCHAR", "NO") - .baselineValues("TABLE_SCHEMA", "VARCHAR", "NO") - .baselineValues("TABLE_NAME", "VARCHAR", "NO") + .baselineValues("TABLE_CATALOG", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_SCHEMA", "CHARACTER VARYING", "NO") + .baselineValues("TABLE_NAME", "CHARACTER VARYING", "NO") .go(); } @@ -240,8 +240,8 @@ public class TestInfoSchema extends BaseTestQuery { .sqlQuery("DESCRIBE INFORMATION_SCHEMA.SCHEMATA 'SCHEMA%'") .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") - .baselineValues("SCHEMA_NAME", "VARCHAR", "NO") - .baselineValues("SCHEMA_OWNER", "VARCHAR", "NO") + .baselineValues("SCHEMA_NAME", "CHARACTER VARYING", "NO") + .baselineValues("SCHEMA_OWNER", "CHARACTER VARYING", "NO") .go(); } http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java index e3156d0..955da48 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/sql/TestViewSupport.java @@ -66,8 +66,8 @@ public class TestViewSupport extends TestBaseViewSupport { .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("cust_id", "BIGINT", "YES") - .baselineValues("fname", "VARCHAR", "YES") - .baselineValues("country", "VARCHAR", "YES") + .baselineValues("fname", "CHARACTER VARYING", "YES") + .baselineValues("country", "CHARACTER VARYING", "YES") .go(); testBuilder() @@ -454,7 +454,7 @@ public class TestViewSupport extends TestBaseViewSupport { .unOrdered() .baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE") .baselineValues("id", "INTEGER", "YES") - .baselineValues("name", "VARCHAR", "YES") + .baselineValues("name", "CHARACTER VARYING", "YES") .baselineValues("bday", "DATE", "YES") .go(); } finally { http://git-wip-us.apache.org/repos/asf/drill/blob/c668a10d/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/MetaImpl.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/MetaImpl.java b/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/MetaImpl.java index f93f5e3..bb8be97 100644 --- a/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/MetaImpl.java +++ b/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/MetaImpl.java @@ -35,51 +35,20 @@ import org.apache.drill.common.util.DrillStringUtils; class MetaImpl implements Meta { private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(MetaImpl.class); - // TODO: Use more central version of these constants if availabe. - - /** Radix used to report precision and scale of integral exact numeric types. */ - private static final int RADIX_INTEGRAL = 10; - /** Radix used to report precision and scale of non-integral exact numeric - types (DECIMAL). */ - private static final int RADIX_DECIMAL = 10; - /** Radix used to report precision and scale of approximate numeric types - (FLOAT, etc.). */ - private static final int RADIX_APPROXIMATE = 10; + // TODO: Use more central version of these constants if available. + + /** JDBC conventional(?) number of fractional decimal digits for REAL. */ + private static final int DECIMAL_DIGITS_REAL = 7; + /** JDBC conventional(?) number of fractional decimal digits for FLOAT. */ + private static final int DECIMAL_DIGITS_FLOAT = DECIMAL_DIGITS_REAL; + /** JDBC conventional(?) number of fractional decimal digits for DOUBLE. */ + private static final int DECIMAL_DIGITS_DOUBLE = 15; + + /** Radix used to report precisions of "datetime" types. */ + private static final int RADIX_DATETIME = 10; /** Radix used to report precisions of interval types. */ private static final int RADIX_INTERVAL = 10; - /** (Maximum) precision of TINYINT. */ - private static final int PREC_TINYINT = 3; - /** (Maximum) precision of SMALLINT. */ - private static final int PREC_SMALLINT = 5; - /** (Maximum) precision of INTEGER. */ - private static final int PREC_INTEGER = 10; - /** (Maximum) precision of BIGINT. */ - private static final int PREC_BIGINT = 19; - - /** Precision of REAL. */ - // TEMPORARY partial change (corrected to 7 in Part 2): - private static final int PREC_REAL = 15; - /** Precision of FLOAT. */ - private static final int PREC_FLOAT = 7; - /** Precision of DOUBLE. */ - private static final int PREC_DOUBLE = 15; - - /** Scale of INTEGER types. */ - private static final int SCALE_INTEGRAL = 0; - // TEMPORARY partial change (corrected to 7 in Part 2): - /** JDBC conventional(?) scale value for REAL. */ - private static final int SCALE_REAL = 15; - /** JDBC conventional(?) scale value for FLOAT. */ - private static final int SCALE_FLOAT = 7; - /** JDBC conventional(?) scale value for DOUBLE. */ - private static final int SCALE_DOUBLE = 15; - - /** (Apparent) maximum precision for starting unit of INTERVAL type. */ - private static final int PREC_INTERVAL_LEAD_MAX = 10; - /** (Apparent) maximum fractional seconds precision for INTERVAL type. */ - private static final int PREC_INTERVAL_TRAIL_MAX = 9; - final DrillConnectionImpl connection; @@ -199,19 +168,21 @@ class MetaImpl implements Meta { + "\n CASE DATA_TYPE " // (All values in JDBC 4.0/Java 7 java.sql.Types except for types.NULL:) - // Exact-match cases: + + "\n WHEN 'ARRAY' THEN " + Types.ARRAY + + "\n WHEN 'BIGINT' THEN " + Types.BIGINT + "\n WHEN 'BINARY' THEN " + Types.BINARY // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'BIT' THEN " + Types.BIT + + "\n WHEN 'BINARY LARGE OBJECT' THEN " + Types.BLOB + + "\n WHEN 'BINARY VARYING' THEN " + Types.VARBINARY // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'BLOB', 'BINARY LARGE OBJECT' THEN " + Types.BLOB + + "\n WHEN 'BIT' THEN " + Types.BIT + "\n WHEN 'BOOLEAN' THEN " + Types.BOOLEAN - + "\n WHEN 'CHAR', 'CHARACTER' THEN " + Types.CHAR + + "\n WHEN 'CHARACTER' THEN " + Types.CHAR // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'CLOB', 'CHARACTER LARGE OBJECT' " - + "\n THEN " + Types.CLOB + + "\n WHEN 'CHARACTER LARGE OBJECT' THEN " + Types.CLOB + + "\n WHEN 'CHARACTER VARYING' THEN " + Types.VARCHAR // Resolve: Not seen in Drill yet. Can it appear?: + "\n WHEN 'DATALINK' THEN " + Types.DATALINK @@ -224,13 +195,7 @@ class MetaImpl implements Meta { + "\n WHEN 'FLOAT' THEN " + Types.FLOAT + "\n WHEN 'INTEGER' THEN " + Types.INTEGER - - // Drill's INFORMATION_SCHEMA's COLUMNS currently has - // "INTERVAL_YEAR_MONTH" and "INTERVAL_DAY_TIME" instead of SQL standard - // 'INTERVAL'. - + "\n WHEN 'INTERVAL', " - + "\n 'INTERVAL_YEAR_MONTH', " - + "\n 'INTERVAL_DAY_TIME' THEN " + Types.OTHER + + "\n WHEN 'INTERVAL' THEN " + Types.OTHER // Resolve: Not seen in Drill yet. Can it ever appear?: + "\n WHEN 'JAVA_OBJECT' THEN " + Types.JAVA_OBJECT @@ -242,10 +207,12 @@ class MetaImpl implements Meta { // Resolve: Not seen in Drill yet. Can it appear?: + "\n WHEN 'LONGVARCHAR' THEN " + Types.LONGVARCHAR + + "\n WHEN 'MAP' THEN " + Types.OTHER + // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'NCHAR', 'NATIONAL CHARACTER' THEN " + Types.NCHAR + + "\n WHEN 'NATIONAL CHARACTER' THEN " + Types.NCHAR // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'NCLOB', 'NATIONAL CHARACTER LARGE OBJECT' " + + "\n WHEN 'NATIONAL CHARACTER LARGE OBJECT' " + "\n THEN " + Types.NCLOB // TODO: Resolve following about NULL (and then update comment and code): // It is not clear whether Types.NULL can represent a type (perhaps the @@ -256,16 +223,14 @@ class MetaImpl implements Meta { // (No NUMERIC--Drill seems to map any to DECIMAL currently.) + "\n WHEN 'NUMERIC' THEN " + Types.NUMERIC // Resolve: Not seen in Drill yet. Can it appear?: - + "\n WHEN 'NVARCHAR', 'NATIONAL CHARACTER VARYING' " - + "\n THEN " + Types.NVARCHAR + + "\n WHEN 'NATIONAL CHARACTER' THEN " + Types.NCHAR + // Resolve: Not seen in Drill yet. Can it appear?: + + "\n WHEN 'NATIONAL CHARACTER VARYING' THEN " + Types.NVARCHAR // Resolve: Unexpectedly, has appeared in Drill. Should it? + "\n WHEN 'OTHER' THEN " + Types.OTHER + "\n WHEN 'REAL' THEN " + Types.REAL - // SQL source syntax: - // <reference type> ::= - // REF <left paren> <referenced type> <right paren> [ <scope clause> ] // Resolve: Not seen in Drill yet. Can it appear?: + "\n WHEN 'REF' THEN " + Types.REF // Resolve: Not seen in Drill yet. Can it appear?: @@ -274,60 +239,16 @@ class MetaImpl implements Meta { + "\n WHEN 'SMALLINT' THEN " + Types.SMALLINT // Resolve: Not seen in Drill yet. Can it appear?: + "\n WHEN 'SQLXML' THEN " + Types.SQLXML + + "\n WHEN 'STRUCT' THEN " + Types.STRUCT + "\n WHEN 'TIME' THEN " + Types.TIME + "\n WHEN 'TIMESTAMP' THEN " + Types.TIMESTAMP + "\n WHEN 'TINYINT' THEN " + Types.TINYINT - + "\n WHEN 'VARBINARY', 'BINARY VARYING' THEN " + Types.VARBINARY - + "\n WHEN 'VARCHAR', 'CHARACTER VARYING' " - + "\n THEN " + Types.VARCHAR - - + "\n ELSE" - // Pattern-match cases: - + "\n CASE " - - // TODO: RESOLVE: How does ARRAY appear in COLUMNS.DATA_TYPE? - // - Only at end (with no maximum size, as "VARCHAR(65535) ARRAY")? - // - Possibly with maximum size (as "... ARRAY[10]")? - // - Then, how should it appear in JDBC ("ARRAY"? "... ARRAY"?) - // (SQL source syntax: - // <array type> ::= - // <data type> ARRAY - // [ <left bracket or trigraph> <maximum cardinality> - // <right bracket or trigraph> ] - + "\n WHEN DATA_TYPE LIKE '% ARRAY' THEN " + Types.ARRAY - - // TODO: RESOLVE: How does MAP appear in COLUMNS.DATA_TYPE? - // - Only at end? - // - Otherwise? - // TODO: RESOLVE: Should it map to Types.OTHER or something else? - // Has appeared in Drill. Should it? - + "\n WHEN DATA_TYPE LIKE '% MAP' THEN " + Types.OTHER - - // TODO: RESOLVE: How does "STRUCT" appear? - // - Only at beginning (as "STRUCT(INTEGER sint, BOOLEAN sboolean")? - // - Otherwise too? - // - Then, how should it appear in JDBC ("STRUCT"? "STRUCT(...)"?) - + "\n WHEN DATA_TYPE LIKE 'STRUCT(%' THEN " + Types.STRUCT - - + "\n ELSE " + Types.OTHER - + "\n END " + + "\n ELSE " + Types.OTHER + "\n END as DATA_TYPE, " - /* 6 TYPE_NAME */ - // Map Drill's current info. schema values to what SQL standard - // specifies (for DATA_TYPE)--and assume that that's what JDBC wants. - + "\n CASE DATA_TYPE " - + "\n WHEN 'INTERVAL_YEAR_MONTH', " - + "\n 'INTERVAL_DAY_TIME' THEN 'INTERVAL'" - // TODO: Resolve how non-scalar types should appear in - // INFORMATION_SCHEMA.COLUMNS and here in JDBC: - // - "ARRAY" or "... ARRAY"? - // - "MAP" or "... MAP"? - // - "STRUCT" or "STRUCT(...)"? - + "\n ELSE DATA_TYPE " - + "\n END as TYPE_NAME, " + + /* 6 */ "\n DATA_TYPE as TYPE_NAME, " /* 7 COLUMN_SIZE */ /* "... COLUMN_SIZE .... @@ -341,119 +262,108 @@ class MetaImpl implements Meta { * Null is returned for data types where the column size is not applicable." * * Note: "Maximum precision" seems to mean the maximum number of - * significant decimal digits that can appear (not the number of digits - * that can be counted on, and not the maximum number of characters - * needed to display a value). + * significant digits that can appear (not the number of decimal digits + * that can be counted on, and not the maximum number of (decimal) + * characters needed to display a value). */ + "\n CASE DATA_TYPE " - // "For numeric data, ... the maximum precision": - // TODO: Change literals to references to declared constant fields: - // - exact numeric types: - // (in decimal digits, coordinated with NUM_PREC_RADIX = 10) - + "\n WHEN 'TINYINT' THEN " + PREC_TINYINT - + "\n WHEN 'SMALLINT' THEN " + PREC_SMALLINT - + "\n WHEN 'INTEGER' THEN " + PREC_INTEGER - + "\n WHEN 'BIGINT' THEN " + PREC_BIGINT - + "\n WHEN 'DECIMAL', 'NUMERIC' THEN NUMERIC_PRECISION " - // - approximate numeric types: - // (in decimal digits, coordinated with NUM_PREC_RADIX = 10) - // TODO: REVISIT: Should these be in bits or decimal digits (with - // NUM_PREC_RADIX coordinated)? INFORMATION_SCHEMA.COLUMNS's value - // are supposed to be in bits (per the SQL spec.). What does JDBC - // require and allow? - + "\n WHEN 'REAL' THEN " + PREC_REAL - + "\n WHEN 'FLOAT' THEN " + PREC_FLOAT - + "\n WHEN 'DOUBLE' THEN " + PREC_DOUBLE - - // "For character data, ... the length in characters": - // TODO: BUG: DRILL-2459: For CHARACTER / CHAR, length is not in - // CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION. - // Workaround: - + "\n WHEN 'VARCHAR', 'CHARACTER VARYING' " - + "\n THEN CHARACTER_MAXIMUM_LENGTH " - + "\n WHEN 'CHAR', 'CHARACTER', " - + "\n 'NCHAR', 'NATIONAL CHAR', 'NATIONAL CHARACTER' " - + "\n THEN NUMERIC_PRECISION " - - // "For datetime datatypes ... length ... String representation - // (assuming the maximum ... precision of ... fractional seconds ...)": - + "\n WHEN 'DATE' THEN 10 " // YYYY-MM-DD - + "\n WHEN 'TIME' THEN " + // 1. "For numeric data, ... the maximum precision": + + "\n WHEN 'TINYINT', 'SMALLINT', 'INTEGER', 'BIGINT', " + + "\n 'DECIMAL', 'NUMERIC', " + + "\n 'REAL', 'FLOAT', 'DOUBLE' " + + "\n THEN NUMERIC_PRECISION " + + // 2. "For character data, ... the length in characters": + + "\n WHEN 'CHARACTER', 'CHARACTER VARYING' " + + "\n THEN CHARACTER_MAXIMUM_LENGTH " + + // 3. "For datetime datatypes ... length ... String representation + // (assuming the maximum ... precision of ... fractional seconds ...)": + // SQL datetime types: + + "\n WHEN 'DATE' THEN 10 " // YYYY-MM-DD + + "\n WHEN 'TIME' THEN " + "\n CASE " - + "\n WHEN NUMERIC_PRECISION > 0 " // HH:MM:SS.sss - + "\n THEN 8 + 1 + NUMERIC_PRECISION" - + "\n ELSE 8" // HH:MM:SS + + "\n WHEN DATETIME_PRECISION > 0 " // HH:MM:SS.sss + + "\n THEN 8 + 1 + DATETIME_PRECISION" + + "\n ELSE 8" // HH:MM:SS + + "\n END " + + "\n WHEN 'TIMESTAMP' THEN " + + "\n CASE " // date + "T" + time + + "\n WHEN DATETIME_PRECISION > 0 " + + " THEN 10 + 1 + 8 + 1 + DATETIME_PRECISION" + + "\n ELSE 10 + 1 + 8" + "\n END " - + "\n WHEN 'TIMESTAMP' THEN " - + "\n CASE " // date + "T" + time (above) - + "\n WHEN NUMERIC_PRECISION > 0 " - + " THEN 10 + 1 + 8 + 1 + NUMERIC_PRECISION" - + "\n ELSE 10 + 1 + 8" + // SQL interval types: + // Note: Not addressed by JDBC 4.1; providing length of current string + // representation (not length of, say, interval literal). + + "\n WHEN 'INTERVAL' THEN " + + "\n INTERVAL_PRECISION " + + "\n + " + + "\n CASE INTERVAL_TYPE " + // a. Single field, not SECOND: + + "\n WHEN 'YEAR', 'MONTH', 'DAY' THEN 2 " // like P...Y + + "\n WHEN 'HOUR', 'MINUTE' THEN 3 " // like PT...M + // b. Two adjacent fields, no SECOND: + + "\n WHEN 'YEAR TO MONTH' THEN 5 " // P...Y12M + + "\n WHEN 'DAY TO HOUR' THEN 6 " // P...DT12H + + "\n WHEN 'HOUR TO MINUTE' THEN 6 " // PT...H12M + // c. Three contiguous fields, no SECOND: + + "\n WHEN 'DAY TO MINUTE' THEN 9 " // P...DT12H12M + // d. With SECOND field: + + "\n ELSE " + + "\n CASE INTERVAL_TYPE " + + "\n WHEN 'DAY TO SECOND' THEN 12 " // P...DT12H12M12...S + + "\n WHEN 'HOUR TO SECOND' THEN 9 " // PT...H12M12...S + + "\n WHEN 'MINUTE TO SECOND' THEN 6 " // PT...M12...S + + "\n WHEN 'SECOND' THEN 3 " // PT......S + + "\n ELSE " // Make net result be -1: + // WORKAROUND: This "0" is to work around Drill's failure to support + // unary minus syntax (negation): + + "\n 0-INTERVAL_PRECISION - 1 " + + "\n END " + + "\n + " + + "\n DATETIME_PRECISION" + + "\n + " + + "\n CASE " // If frac. digits, also add 1 for decimal point. + + "\n WHEN DATETIME_PRECISION > 0 THEN 1" + + "\n ELSE 0 " + + "\n END" + // - For INTERVAL ... TO SECOND(0): "P...DT12H12M12S" + "\n END " - // TODO: DRILL-2531: When DRILL-2519 is fixed, use start and end unit - // and start-unit precision to implement maximum width more precisely - // (narrowly) than this workaround: - // For INTERVAL_YEAR_MONTH, maximum width is from "P1234567890Y12M" - // (5 + apparent maximum start unit precision of 10) - // unit precision): - + "\n WHEN 'INTERVAL_YEAR_MONTH' " - + "\n THEN 5 + " - + PREC_INTERVAL_LEAD_MAX - // For INTERVAL_DAY_TIME, maximum width is from - // "P1234567890D12H12M12.123456789S" (12 + apparent maximum start unit - // precision of 10 + apparent maximum seconds fractional precision of 9): - + "\n WHEN 'INTERVAL_DAY_TIME' " - + "\n THEN 12 + " - + ( PREC_INTERVAL_LEAD_MAX - + PREC_INTERVAL_TRAIL_MAX ) - - // "For binary data, ... the length in bytes": - // BUG: DRILL-2459: BINARY and BINARY VARYING / VARBINARY length is - // not in CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION. - // Workaround: - + "\n WHEN 'VARBINARY', 'BINARY VARYING', " - + "\n 'BINARY' THEN NUMERIC_PRECISION " - - // "For ... ROWID datatype...": Not in Drill? - - // "Null ... for data types [for which] ... not applicable.": - + "\n ELSE NULL " + // 4. "For binary data, ... the length in bytes": + + "\n WHEN 'BINARY', 'BINARY VARYING' " + + "\n THEN CHARACTER_MAXIMUM_LENGTH " + + // 5. "For ... ROWID datatype...": Not in Drill? + + // 6. "Null ... for data types [for which] ... not applicable.": + + "\n ELSE NULL " + "\n END as COLUMN_SIZE, " + /* 8 */ "\n CHARACTER_MAXIMUM_LENGTH as BUFFER_LENGTH, " /* 9 DECIMAL_DIGITS */ + "\n CASE DATA_TYPE" - + "\n WHEN 'TINYINT', " - + "\n 'SMALLINT', " - + "\n 'INTEGER', " - + "\n 'BIGINT' THEN " + SCALE_INTEGRAL - + "\n WHEN 'DECIMAL', " - + "\n 'NUMERIC' THEN NUMERIC_SCALE " - + "\n WHEN 'REAL' THEN " + SCALE_REAL - + "\n WHEN 'FLOAT' THEN " + SCALE_FLOAT - + "\n WHEN 'DOUBLE' THEN " + SCALE_DOUBLE - + "\n WHEN 'INTERVAL' THEN NUMERIC_SCALE " - + "\n WHEN 'INTERVAL_YEAR_MONTH' THEN 0 " - + "\n WHEN 'INTERVAL_DAY_TIME' THEN NUMERIC_SCALE " + + "\n WHEN 'TINYINT', 'SMALLINT', 'INTEGER', 'BIGINT', " + + "\n 'DECIMAL', 'NUMERIC' THEN NUMERIC_SCALE " + + "\n WHEN 'REAL' THEN " + DECIMAL_DIGITS_REAL + + "\n WHEN 'FLOAT' THEN " + DECIMAL_DIGITS_FLOAT + + "\n WHEN 'DOUBLE' THEN " + DECIMAL_DIGITS_DOUBLE + + "\n WHEN 'DATE', 'TIME', 'TIMESTAMP' THEN DATETIME_PRECISION " + + "\n WHEN 'INTERVAL' THEN DATETIME_PRECISION " + "\n END as DECIMAL_DIGITS, " /* 10 NUM_PREC_RADIX */ + "\n CASE DATA_TYPE " - + "\n WHEN 'TINYINT', " - + "\n 'SMALLINT', " - + "\n 'INTEGER', " - + "\n 'BIGINT' THEN " + RADIX_INTEGRAL - + "\n WHEN 'DECIMAL', " - + "\n 'NUMERIC' THEN " + RADIX_DECIMAL - + "\n WHEN 'REAL', " - + "\n 'FLOAT', " - + "\n 'DOUBLE' THEN " + RADIX_APPROXIMATE - + "\n WHEN 'INTERVAL_YEAR_MONTH', " - + "\n 'INTERVAL_DAY_TIME' THEN " + RADIX_INTERVAL - + "\n ELSE NULL" + + "\n WHEN 'TINYINT', 'SMALLINT', 'INTEGER', 'BIGINT', " + + "\n 'DECIMAL', 'NUMERIC', " + + "\n 'REAL', 'FLOAT', 'DOUBLE' THEN NUMERIC_PRECISION_RADIX " + // (NUMERIC_PRECISION_RADIX is NULL for these:) + + "\n WHEN 'INTERVAL' THEN " + RADIX_INTERVAL + + "\n WHEN 'DATE', 'TIME', 'TIMESTAMP' THEN " + RADIX_DATETIME + + "\n ELSE NULL" + "\n END as NUM_PREC_RADIX, " /* 11 NULLABLE */ @@ -465,23 +375,21 @@ class MetaImpl implements Meta { + "\n END as NULLABLE, " + /* 12 */ "\n CAST( NULL as VARCHAR ) as REMARKS, " - + /* 13 */ "\n CAST( NULL as VARCHAR ) as COLUMN_DEF, " + + /* 13 */ "\n COLUMN_DEFAULT as COLUMN_DEF, " + /* 14 */ "\n 0 as SQL_DATA_TYPE, " + /* 15 */ "\n 0 as SQL_DATETIME_SUB, " /* 16 CHAR_OCTET_LENGTH */ + "\n CASE DATA_TYPE" - + "\n WHEN 'VARCHAR', 'CHARACTER VARYING' " - + "\n THEN 4 * CHARACTER_MAXIMUM_LENGTH " - + "\n WHEN 'CHAR', 'CHARACTER', " - + "\n 'NCHAR', 'NATIONAL CHAR', 'NATIONAL CHARACTER' " - // TODO: BUG: DRILL-2459: For CHARACTER / CHAR, length is not in - // CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION. Workaround: - + "\n THEN 4 * NUMERIC_PRECISION " + + "\n WHEN 'CHARACTER', " + + "\n 'CHARACTER VARYING', " + + "\n 'NATIONAL CHARACTER', " + + "\n 'NATIONAL CHARACTER VARYING' " + + "\n THEN CHARACTER_OCTET_LENGTH " + "\n ELSE NULL " + "\n END as CHAR_OCTET_LENGTH, " - + /* 17 */ "\n 1 + ORDINAL_POSITION as ORDINAL_POSITION, " + + /* 17 */ "\n ORDINAL_POSITION as ORDINAL_POSITION, " + /* 18 */ "\n IS_NULLABLE as IS_NULLABLE, " + /* 19 */ "\n CAST( NULL as VARCHAR ) as SCOPE_CATALOG, " + /* 20 */ "\n CAST( NULL as VARCHAR ) as SCOPE_SCHEMA, "
