[DRILL-2459] INFO._SCHEMA's CHARACTER_MAXIMUM_LENGTH is -1 for type CHAR

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.2.0
• Component/s: Metadata
• Assignee: Daniel Barclay
• Reporter: Daniel Barclay
• Created: 14/Mar/15 03:09
• Updated: 04/Jun/15 22:54
• Resolved: 04/Jun/15 22:54

Description

INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH does not report the length for type CHAR. For example, for type descriptor "CHAR(4)", it doesn't return 4. Instead, it returns -1:

0: jdbc:drill:zk=local> USE dfs.tmp;
----------------------+

ok

summary

----------------------+

true	Default schema changed to 'dfs.tmp'

----------------------+
1 row selected (0.05 seconds)
0: jdbc:drill:zk=local> CREATE OR REPLACE VIEW TempView AS SELECT CAST( NULL AS VARCHAR(3) ), CAST( NULL AS CHAR(4) ) FROM INFORMATION_SCHEMA.CATALOGS LIMIT 1 ;
----------------------+

ok

summary

----------------------+

true	View 'TempView' replaced successfully in 'dfs.tmp' schema

----------------------+
1 row selected (0.05 seconds)
0: jdbc:drill:zk=local> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TempView';
------------------------------------------------------------------------------------------------------------------------------------------------------------------

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

IS_NULLABLE

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

NUMERIC_PRECISION_RADIX

NUMERIC_SCALE

NUMERIC_PRECISION

------------------------------------------------------------------------------------------------------------------------------------------------------------------

DRILL	dfs.tmp	TempView	EXPR$0	0	NO	VARCHAR	3	-1	-1	-1
DRILL	dfs.tmp	TempView	EXPR$1	1	NO	CHAR	-1	-1	-1	4

------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows selected (0.072 seconds)
0: jdbc:drill:zk=local>

Hmm. Note the 4 in the NUMERIC_PRECISION column:

0: jdbc:drill:zk=local> SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TempView';
-----------------------------------------------------

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

NUMERIC_PRECISION

-----------------------------------------------------

VARCHAR	3	-1
CHAR	-1	4

-----------------------------------------------------
2 rows selected (0.065 seconds)
0: jdbc:drill:zk=local>

Issue Links

• Is contained by: DRILL-2480 [umbrella] Identify, fix INFORMATION_SCHEMA and JDBC metadata bugs
• is part of: DRILL-2480 [umbrella] Identify, fix INFORMATION_SCHEMA and JDBC metadata bugs
• is superceded by: DRILL-3216 Fix existing(+) INFORMATION_SCHEMA.COLUMNS columns
• relates to: DRILL-2763 [umbrella] Implement INFORMATION_SCHEMA.COLUMNS enough for relevant tools
• relates to: DRILL-3216 Fix existing(+) INFORMATION_SCHEMA.COLUMNS columns