[DRILL-2420] [umbrella] Identify, fix DatabaseMetaData.getColumns() bugs

• Type: Task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.1.0
• Component/s: Client - JDBC, Metadata
• Assignee: Daniel Barclay
• Reporter: Daniel Barclay
• Created: 10/Mar/15 20:44
• Updated: 17/Jun/15 23:52
• Resolved: 17/Jun/15 23:52

Description

Drill's implementation of DatabaseMetaData.getColumns(...) (currently at org.apache.drill.jdbc.MetaImpl.getColumns()) doesn't match the JDBC specification (the Javadoc documentation for DatabaseMetaData.getColumns(...) (as of Java 7)). In the returned ResultSet:

1. Column DATA_TYPE is of type VARCHAR (containing the type name) rather than being of type INTEGER (containing values per java.sql.Types.*). [Fixed by DRILL-2128.]
2. Column TYPE_NAME is missing. [Fixed by DRILL-2128.]
3. Column COLUMN_SIZE is missing. [Fixed by DRILL-2465.]
4. (Columns after DATA_TYPE are at incorrect indexes.) [Fixed by DRILL-2465.]
5. Column DECIMAL_DIGITS is misnamed DECIMAL_PRECISION. [Fixed by DRILL-2465.]
6. Column REMARKS is an empty string, but probably should be NULL. [Fixed by DRILL-2465.]
7. Column COLUMN_DEF is an empty string, but probably should be NULL. [Fixed by DRILL-2465.]
8. Column CHAR_OCTET_LENGTH is always 4, but should be "the maximum number of bytes in the column" for character types. [Fixed by DRILL-2465.]
8.5 Column IS_NULLABLE seems to always return 'NO'.
9. Column ORDINAL_POSITION is always 1, but should be the index of the specific column. [Fixed by DRILL-2465.]
10. Column IS_NULLABLE is 'YES', which doesn't seem to correspond to the value for NULLABLE (DatabaseMetaData.columnNullableUnknown). [Fixed (unidentified change).\`]
11. Column SCOPE_CATALOG is an empty string, but should be NULL. [Fixed by DRILL-2465.]
12. Column SCOPE_SCHEMA is an empty string, but should be NULL. [Fixed by DRILL-2465.]
13. Column SCOPE_TABLE is an empty string, but should be NULL. [Fixed by DRILL-2465.]
14. Column SOURCE_DATA_TYPE is an empty string, but should be NULL. [Fixed by DRILL-2465.]
Additional bugs or suspect behavior:

• DECIMAL_DIGITS/DECIMAL_PRECISION is -1 when it should be NULL (when not applicable). [Fixed by DRILL-2465.]
• NUM_PREC_RADIX is -1 when it probably should be NULL (when not applicable). [Fixed by DRILL-2465.]

(Other columns to check:

Re BUFFER_LENGTH, SQL_DATA_TYPE, and SQL_DATETIME_SUB: When JDBC says a column is not used, are there any requirements on the values (e.g., being NULL)?

Re IS_AUTOINCREMENT: Do we know that a column is not auto-incremented? If so, the value could be 'NO' rather than an empty string.

Re IS_GENERATEDCOLUMN: Do we know that a column is not generated? If so, the value could be 'NO' rather than an empty string.

(Re NULLABLE : Do know whether a column is nullable or not? If so, we could return the specific answer rather that just saying that it's unknown.)

Issue Links

• contains: DRILL-2128 DatabaseMetaData.getColumns()'s DATA_TYPE has wrong type
• incorporates: DRILL-2450 JDBC : DatabaseMetaData.getColumns is missing the 'COLUMN_SIZE' in the result set
• incorporates: DRILL-2465 Fix multiple DatabaseMetaData.getColumns() bugs (some)
• incorporates: DRILL-2466 Fix NVARCHAR to VARCHAR in to-JDBC-types mapping
• is blocked by: DRILL-2503 Having many WHENs causes ClassReader.accept()/EXPAND_FRAMES error
• is blocked by: DRILL-2519 INFORMATION_SCHEMA.COLUMNS is missing <interval_qualifier> info
• Is contained by: DRILL-2436 [umbrella] Implement JDBC enough for relevant tools
• Is contained by: DRILL-2480 [umbrella] Identify, fix INFORMATION_SCHEMA and JDBC metadata bugs
• is related to: DRILL-2462 JDBC : ResultSetMetaData.isNullable returns true even when the column is a required one