[DRILL-3216] Fix existing(+) INFORMATION_SCHEMA.COLUMNS columns

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.1.0
• Assignee: Daniel Barclay
• Reporter: Daniel Barclay
• Created: 29/May/15 16:58
• Updated: 17/Jun/15 22:35
• Resolved: 17/Jun/15 22:35

Description

Change logical null from -1 to actual NULL:

• Change column CHARACTER_MAXIMUM_LENGTH.
• Change column NUMERIC_PRECISION.
• Change column NUMERIC_PRECISION_RADIX.
• Change column NUMERIC_SCALE.

Change column ORDINAL_POSITION from zero-based to one-based.

Change column DATA_TYPE from short names (e.g., "CHAR") to specified names (e.g., "CHARACTER").

• "CHAR" -> "CHARACTER"
• "VARCHAR" -> "CHARACTER VARYING"
• "VARBINARY" -> "BINARY VARYING"
• "... ARRAY" -> "ARRAY"
• "STRUCT (...)" -> "STRUCT"
• ("(...) MAP" -> "MAP" (extension to standard, but follows standard's pattern))

Fix data type names "INTERVAL_DAY_TIME" and "INTERVAL_YEAR_MONTH" to "INTERVAL":

• Change column DATA_TYPE to list "INTERVAL" for interval types.
• Add column INTERVAL_TYPE.

Move CHAR length from NUMERIC_PRECISION to CHARACTER_MAXIMUM_LENGTH (same as VARCHAR length):

• Change column NUMERIC_PRECISION from length to logical null for CHAR.
• Change column CHARACTER_MAXIMUM_LENGTH from logical null to length for CHAR.

Move BINARY and VARBINARY length from NUMERIC_PRECISION to CHARACTER_MAXIMUM_LENGTH (same as CHAR and VARCHAR length):

• Change column NUMERIC_PRECISION from length to logical null for BINARY and VARBINARY.
• Change column CHARACTER_MAXIMUM_LENGTH from logical null to length for BINARY and VARBINARY.

To correct ordinal position of some existing columns:

• Add column COLUMN_DEFAULT.
• Add column CHARACTER_OCTET_LENGTH.
• Reorder column NUMERIC_PRECISION.

Move date/time and interval precisions from NUMERIC_PRECISION to DATETIME_PRECISION and INTERVAL_PRECISION:

• Change column NUMERIC_PRECISION to logically null for date/time and interval types.
• Add column DATETIME_PRECISION.
• Add column INTERVAL_PRECISION.

Implement NUMERIC_PRECISION_RADIX:

• Change column NUMERIC_PRECISION_RADIX from always logically null to appropriate values (2, 10, NULL).

Add missing numeric precision and scale values (for non-DECIMAL types):

• Change NUMERIC_SCALE from logical null to zero for integer types.
• Change NUMERIC_PRECISION from logical null to precision for non-DECIMAL numeric types.

Update implementation of JDBC's DatabaseMeta.getColumns():

• first, enough to not break
• second, to use newly available data to fix some partial implementations.

Issue Links

• depends upon: DRILL-2089 Split JDBC implementation out of org.apache.drill.jdbc, so that pkg. is place for doc.
• is depended upon by: DRILL-2763 [umbrella] Implement INFORMATION_SCHEMA.COLUMNS enough for relevant tools
• is related to: DRILL-3225 SQL parser seems to ignore specified <fractional seconds precision>
• is related to: DRILL-3244 Start unit precision getting into fractional seconds precision in View.FieldType serialization
• is related to: DRILL-2465 Fix multiple DatabaseMetaData.getColumns() bugs (some)
• is related to: DRILL-2459 INFO._SCHEMA's CHARACTER_MAXIMUM_LENGTH is -1 for type CHAR
• supercedes: DRILL-2519 INFORMATION_SCHEMA.COLUMNS is missing <interval_qualifier> info
• supercedes: DRILL-2530 getColumns() doesn't return right COLUMN_SIZE for INTERVAL types
• supercedes: DRILL-2531 getColumns() not right/implemented for INTERVAL types
• supercedes: DRILL-2459 INFO._SCHEMA's CHARACTER_MAXIMUM_LENGTH is -1 for type CHAR
• supercedes: DRILL-2721 Identify, fix _existing_ INFO._SCHEMA columns in conflict with SQL spec. by 1.0