Bug #84308 MySQL JDBC driver do not report COLUMN_SIZE for TIME/TIMESTAMP related columns
Submitted: 22 Dec 2016 3:47 Modified: 18 Feb 2017 4:36
Reporter: Rahul Dhuvad Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.40 OS:Any
Assigned to: CPU Architecture:Any
Tags: COLUMN_SIZE, metadata, precision, ResultSet, scale, timestamp

[22 Dec 2016 3:47] Rahul Dhuvad
Description:
Consider below MySQL table:
create table SAMPLETIMESTAMP (c1_time time, c1_time_1 time(1), c1_time_2 time(2), c1_timestamp timestamp, c1_timestamp_1 timestamp(1), c1_timestamp_2 timestamp(2), c1_timestamp_3 timestamp(3));

If we try to extract metadata of the table by: conn.getMetaData().getColumns(null, null, "SAMPLETIMESTAMP", null); and check it's properties of the result-set, we get:

COLUMN_NAME   : c1_time     , DATA_TYPE   : 92, TYPE_NAME   : TIME  , COLUMN_SIZE   :   8, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_time_1    , DATA_TYPE   : 92, TYPE_NAME   : TIME  , COLUMN_SIZE   :   8, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_time_2    , DATA_TYPE   : 92, TYPE_NAME   : TIME  , COLUMN_SIZE   :   8, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_timestamp    , DATA_TYPE   : 93, TYPE_NAME   : TIMESTAMP , COLUMN_SIZE   :   19, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_timestamp_1   , DATA_TYPE   : 93, TYPE_NAME   : TIMESTAMP , COLUMN_SIZE   :   19, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_timestamp_2   , DATA_TYPE   : 93, TYPE_NAME   : TIMESTAMP , COLUMN_SIZE   :   19, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0
COLUMN_NAME   : c1_timestamp_3   , DATA_TYPE   : 93, TYPE_NAME   : TIMESTAMP , COLUMN_SIZE   :   19, OCTET_LENGTH  :   0, DECIMAL_DIGITS  : 0

As we can see, COLUMN_SIZE for all varying precision of TIME and TIMESTAMP is constant and i.e. 8 & 19 respectively. This disallows application to understand the actual precision with which a column has been created and it's capacity to hold the sub-second values etc.

When we check other database's (like say Oracle, DB2, SQL Server, etc.) similar output, it gives us proper values like 21,22,23 for TIMESTAMP(1), TIMESTAMP(2) and TIMESTAMP(3) respectively.

On the other hand, if we do ResultSetMetaData on "select * from SAMPLETIMESTAMP"; then we can get proper varying precision for above TIME & TIMESTAMP columns!

How to repeat:
Just do getColumns on the table mentioned in the Description above.

Suggested fix:
MariaDB latest JDBC driver (v1.5.5) has fixed this issue; and if we use this driver to connect to the same MySQL server, then we get proper COLUMN_SIZE values for TIME/TIMESTAMP columns.
[22 Dec 2016 7:48] Chiranjeevi Battula
Hello Rahul,

Thank you for the bug report.
Verified this behavior on MySQL Connector / J 5.1.40.

Thanks,
Chiranjeevi.
[22 Dec 2016 7:48] Chiranjeevi Battula
output:

Column name: c1_time DATA_TYPE : 92 TYPE_NAME : TIME COLUMN_SIZE : 8 DECIMAL_DIGITS : null
Column name: c1_time_1 DATA_TYPE : 92 TYPE_NAME : TIME COLUMN_SIZE : 8 DECIMAL_DIGITS : null
Column name: c1_time_2 DATA_TYPE : 92 TYPE_NAME : TIME COLUMN_SIZE : 8 DECIMAL_DIGITS : null
Column name: c1_timestamp DATA_TYPE : 93 TYPE_NAME : TIMESTAMP COLUMN_SIZE : 19 DECIMAL_DIGITS : null
Column name: c1_timestamp_1 DATA_TYPE : 93 TYPE_NAME : TIMESTAMP COLUMN_SIZE : 19 DECIMAL_DIGITS : null
Column name: c1_timestamp_2 DATA_TYPE : 93 TYPE_NAME : TIMESTAMP COLUMN_SIZE : 19 DECIMAL_DIGITS : null
Column name: c1_timestamp_3 DATA_TYPE : 93 TYPE_NAME : TIMESTAMP COLUMN_SIZE : 19 DECIMAL_DIGITS : null
BUILD SUCCESSFUL (total time: 0 seconds)
[6 Feb 2017 4:30] Rahul Dhuvad
Hi,

When can we expect the fix for this issue?

Thanks,
Rahul.
[18 Feb 2017 4:36] Rahul Dhuvad
Any updates on when this issue is expected to be fixed? We need to plan our application logic accordingly.

Thanks,
Rahul.