MySQL Bugs: #84308: MySQL JDBC driver do not report COLUMN_SIZE for TIME/TIMESTAMP related columns

Bug #84308	MySQL JDBC driver do not report COLUMN_SIZE for TIME/TIMESTAMP related columns
Submitted:	22 Dec 2016 3:47	Modified:	5 Nov 2022 2:04
Reporter:	Rahul Dhuvad	Email Updates:
Status:	Closed	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

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.

Hello Rahul,

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

Thanks,
Chiranjeevi.

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)

Hi,

When can we expect the fix for this issue?

Thanks,
Rahul.

Any updates on when this issue is expected to be fixed? We need to plan our application logic accordingly.

Thanks,
Rahul.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/