Bug #69786 Java throws exception fetching TIME(6) column
Submitted: 18 Jul 2013 23:24 Modified: 29 Jul 2013 17:33
Reporter: Bill Karwin (Candidate Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any

[18 Jul 2013 23:24] Bill Karwin
Description:
I'm testing Java object type mapping for MySQL 5.6 data types.  I found that the new TIME data type with optional precision throws a Java exception when I try to fetch it as an object.

How to repeat:
Environment:
MySQL Connector/J 5.1.22
JDK 1.6.0_51
Client: OS X 10.6.8 Snow Leopard
Server: CentOS 6.4 64-bit
MySQL 5.6.12

Steps to repeat:

1.  Define a table with different data types, including a TIME column and a TIME(6) column.

CREATE TABLE test.test_data_types ( bi BIGINT, biu BIGINT UNSIGNED, i INT, iu INT UNSIGNED, mi MEDIUMINT, miu MEDIUMINT UNSIGNED, si SMALLINT, siu SMALLINT UNSIGNED, ti TINYINT, tiu TINYINT UNSIGNED, f FLOAT, ff DOUBLE, n NUMERIC(9,2), d DATE, dt DATETIME, dtp DATETIME(6), t TIME, tp TIME(6), ts TIMESTAMP, tsp TIMESTAMP(6), y YEAR, c CHAR(255), bc BINARY(255), v VARCHAR(255), lv VARCHAR(32767), vb VARBINARY(255), b1 BIT(1), b2 BIT(64), lb LONGBLOB, mb MEDIUMBLOB, b BLOB, tb TINYBLOB, lt LONGTEXT, mt MEDIUMTEXT, tx TEXT, tt TINYTEXT, e ENUM('A', 'B', 'C'), s SET('A', 'B', 'C'), PRIMARY KEY (bi) )

2. Insert some dummy data:

INSERT INTO test.test_data_types SET bi = 123, biu = 123, i  = 123, iu = 123, mi = 123, miu = 123, si = 123, siu = 123, ti = 123, tiu = 123, f  = 3.14, ff = 3.14, n  = 3.14, d  = CURRENT_DATE(), dt = CURRENT_TIMESTAMP(), dtp = CURRENT_TIMESTAMP(6), t  = CURRENT_TIME(), tp = CURRENT_TIME(6), ts = CURRENT_TIMESTAMP(), tsp = CURRENT_TIMESTAMP(6), y  = 2013, c  = 'char', v  = 'varchar', lv = 'longvarchar', b1 = 1, b2 = 1, lb = 'longblob', mb = 'mediumblob', b  = 'blob', tb = 'tinyblob', lt = 'longtext', mt = 'mediumtext', tx = 'text', tt = 'tinytext', e  = 'A', s  = 'A,B'

3. Query the table.

SELECT * FROM test.test_data_types

4. Fetch results with the JDBC ResultSet method rs.next(), and then read columns with getObject():

This works for most data types (but not those that return byte[]).  It works for the column defined as TIME data type.  But when I fetch the column defined as TIME(6), it throws the following exception:

SQLException: Bad format for Time '1231231231231231231231231231233.143.143.14
2013-07-182013-07-18 14:59:452013-07-18 14:59:45.1011414:59:4514:59:45.1011402013-07-18 14:59:452013-07-18 14:59:45.1011402013char?varchar
                  longvarchalongblob
mediumblobblotinyblolongtext
mediumtexttextinytextAA,B' in column 18
SQLState: S1009
VendorError: 0

It does work correctly for DATETIME(6) and TIMESTAMP(6).  It also fails if I used TIME(1) through TIME(5).

I will attach my Java class that reproduces the behavior. 

Suggested fix:
Debug why TIME(6) does not behaving the same way that DATETIME(6) and TIMESTAMP(6) do, and make it format TIME with precision correctly.
[18 Jul 2013 23:39] Bill Karwin
test JDBC type mapping

Attachment: TestTypeMap.java (application/octet-stream, text), 3.81 KiB.

[19 Jul 2013 6:03] MySQL Verification Team
Hello Bill,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh
[19 Jul 2013 6:05] MySQL Verification Team
//

java -cp ".:mysql-connector-java-commercial-5.1.22-bin.jar" TestTypeMap
Column=1 ColumnName=bi SQLType=-5 SQLTypeName=BIGINT Precision=20 Scale=0 MetadataClassName=java.lang.Long

Column=2 ColumnName=biu SQLType=-5 SQLTypeName=BIGINT UNSIGNED Precision=20 Scale=0 MetadataClassName=java.math.BigInteger

Column=3 ColumnName=i SQLType=4 SQLTypeName=INT Precision=11 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=4 ColumnName=iu SQLType=4 SQLTypeName=INT UNSIGNED Precision=10 Scale=0 MetadataClassName=java.lang.Long
 ObjectClassName=java.lang.Long Value="123"

Column=5 ColumnName=mi SQLType=4 SQLTypeName=MEDIUMINT Precision=9 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=6 ColumnName=miu SQLType=4 SQLTypeName=MEDIUMINT UNSIGNED Precision=8 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=7 ColumnName=si SQLType=5 SQLTypeName=SMALLINT Precision=6 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=8 ColumnName=siu SQLType=5 SQLTypeName=SMALLINT UNSIGNED Precision=5 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=9 ColumnName=ti SQLType=-6 SQLTypeName=TINYINT Precision=4 Scale=0 MetadataClassName=java.lang.Integer

Column=10 ColumnName=tiu SQLType=-6 SQLTypeName=TINYINT UNSIGNED Precision=3 Scale=0 MetadataClassName=java.lang.Integer

Column=11 ColumnName=f SQLType=7 SQLTypeName=FLOAT Precision=12 Scale=31 MetadataClassName=java.lang.Float
 ObjectClassName=java.lang.Float Value="3.14"

Column=12 ColumnName=ff SQLType=8 SQLTypeName=DOUBLE Precision=22 Scale=31 MetadataClassName=java.lang.Double
 ObjectClassName=java.lang.Double Value="3.14"

Column=13 ColumnName=n SQLType=3 SQLTypeName=DECIMAL Precision=9 Scale=2 MetadataClassName=java.math.BigDecimal
 ObjectClassName=java.math.BigDecimal Value="3.14"

Column=14 ColumnName=d SQLType=91 SQLTypeName=DATE Precision=10 Scale=0 MetadataClassName=java.sql.Date
 ObjectClassName=java.sql.Date Value="2013-07-19"

Column=15 ColumnName=dt SQLType=93 SQLTypeName=DATETIME Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.0"

Column=16 ColumnName=dtp SQLType=93 SQLTypeName=DATETIME Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.216083"

Column=17 ColumnName=t SQLType=92 SQLTypeName=TIME Precision=10 Scale=0 MetadataClassName=java.sql.Time
 ObjectClassName=java.sql.Time Value="16:45:49"

Column=18 ColumnName=tp SQLType=92 SQLTypeName=TIME Precision=17 Scale=0 MetadataClassName=java.sql.Time
SQLException: Bad format for Time '1231231231231231231231231231233.143.143.14
2013-07-192013-07-19 16:45:492013-07-19 16:45:49.2160816:45:4916:45:49.2160832013-07-19 16:45:492013-07-19 16:45:49.2160832013char�varchar
                                                                                                                                            longvarcharïlongblob
mediumblobblotinyblolongtext
mediumtexttextinytextAA,B' in column 18
SQLState: S1009
VendorError: 0
Column=19 ColumnName=ts SQLType=93 SQLTypeName=TIMESTAMP Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.0"

Column=20 ColumnName=tsp SQLType=93 SQLTypeName=TIMESTAMP Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.216083"

Column=21 ColumnName=y SQLType=91 SQLTypeName=YEAR Precision=4 Scale=0 MetadataClassName=java.sql.Date
 ObjectClassName=java.sql.Date Value="2013-01-01"

Column=22 ColumnName=c SQLType=1 SQLTypeName=CHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="char"

Column=23 ColumnName=bc SQLType=-2 SQLTypeName=BINARY Precision=255 Scale=0 MetadataClassName=[B

Column=24 ColumnName=v SQLType=12 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="varchar"

Column=25 ColumnName=lv SQLType=12 SQLTypeName=VARCHAR Precision=32767 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="longvarchar"

Column=26 ColumnName=vb SQLType=-3 SQLTypeName=VARBINARY Precision=255 Scale=0 MetadataClassName=[B

Column=27 ColumnName=b1 SQLType=-7 SQLTypeName=BIT Precision=1 Scale=0 MetadataClassName=java.lang.Boolean

Column=28 ColumnName=b2 SQLType=-3 SQLTypeName=BIT Precision=64 Scale=0 MetadataClassName=[B

Column=29 ColumnName=lb SQLType=-4 SQLTypeName=LONGBLOB Precision=2147483647 Scale=0 MetadataClassName=[B

Column=30 ColumnName=mb SQLType=-4 SQLTypeName=MEDIUMBLOB Precision=16777215 Scale=0 MetadataClassName=[B

Column=31 ColumnName=b SQLType=-4 SQLTypeName=BLOB Precision=65535 Scale=0 MetadataClassName=[B

Column=32 ColumnName=tb SQLType=-3 SQLTypeName=TINYBLOB Precision=255 Scale=0 MetadataClassName=[B

Column=33 ColumnName=lt SQLType=-1 SQLTypeName=VARCHAR Precision=2147483647 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="longtext"

Column=34 ColumnName=mt SQLType=-1 SQLTypeName=VARCHAR Precision=16777215 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="mediumtext"

Column=35 ColumnName=tx SQLType=-1 SQLTypeName=VARCHAR Precision=65535 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="text"

Column=36 ColumnName=tt SQLType=-1 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="tinytext"

Column=37 ColumnName=e SQLType=1 SQLTypeName=CHAR Precision=1 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="A"

Column=38 ColumnName=s SQLType=1 SQLTypeName=CHAR Precision=5 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="A,B"
[29 Jul 2013 9:14] Alexander Soklakov
Hi Bill,

Thanks for your report.
You need to upgrade c/J to at least 5.1.23 version where we fixed several problems with milliseconds and precision support.

Here is the output of your class with 5.1.25 driver and 5.6.11 server:

===
Column=1 ColumnName=bi SQLType=-5 SQLTypeName=BIGINT Precision=20 Scale=0 MetadataClassName=java.lang.Long

Column=2 ColumnName=biu SQLType=-5 SQLTypeName=BIGINT UNSIGNED Precision=20 Scale=0 MetadataClassName=java.math.BigInteger

Column=3 ColumnName=i SQLType=4 SQLTypeName=INT Precision=11 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=4 ColumnName=iu SQLType=4 SQLTypeName=INT UNSIGNED Precision=10 Scale=0 MetadataClassName=java.lang.Long
 ObjectClassName=java.lang.Long Value="123"

Column=5 ColumnName=mi SQLType=4 SQLTypeName=MEDIUMINT Precision=9 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=6 ColumnName=miu SQLType=4 SQLTypeName=MEDIUMINT UNSIGNED Precision=8 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=7 ColumnName=si SQLType=5 SQLTypeName=SMALLINT Precision=6 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=8 ColumnName=siu SQLType=5 SQLTypeName=SMALLINT UNSIGNED Precision=5 Scale=0 MetadataClassName=java.lang.Integer
 ObjectClassName=java.lang.Integer Value="123"

Column=9 ColumnName=ti SQLType=-6 SQLTypeName=TINYINT Precision=4 Scale=0 MetadataClassName=java.lang.Integer

Column=10 ColumnName=tiu SQLType=-6 SQLTypeName=TINYINT UNSIGNED Precision=3 Scale=0 MetadataClassName=java.lang.Integer

Column=11 ColumnName=f SQLType=7 SQLTypeName=FLOAT Precision=12 Scale=31 MetadataClassName=java.lang.Float
 ObjectClassName=java.lang.Float Value="3.14"

Column=12 ColumnName=ff SQLType=8 SQLTypeName=DOUBLE Precision=22 Scale=31 MetadataClassName=java.lang.Double
 ObjectClassName=java.lang.Double Value="3.14"

Column=13 ColumnName=n SQLType=3 SQLTypeName=DECIMAL Precision=9 Scale=2 MetadataClassName=java.math.BigDecimal
 ObjectClassName=java.math.BigDecimal Value="3.14"

Column=14 ColumnName=d SQLType=91 SQLTypeName=DATE Precision=10 Scale=0 MetadataClassName=java.sql.Date
 ObjectClassName=java.sql.Date Value="2013-07-29"

Column=15 ColumnName=dt SQLType=93 SQLTypeName=DATETIME Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.0"

Column=16 ColumnName=dtp SQLType=93 SQLTypeName=DATETIME Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.538874"

Column=17 ColumnName=t SQLType=92 SQLTypeName=TIME Precision=10 Scale=0 MetadataClassName=java.sql.Time
 ObjectClassName=java.sql.Time Value="13:02:31"

Column=18 ColumnName=tp SQLType=92 SQLTypeName=TIME Precision=17 Scale=0 MetadataClassName=java.sql.Time
 ObjectClassName=java.sql.Time Value="13:02:31"

Column=19 ColumnName=ts SQLType=93 SQLTypeName=TIMESTAMP Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.0"

Column=20 ColumnName=tsp SQLType=93 SQLTypeName=TIMESTAMP Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
 ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.538874"

Column=21 ColumnName=y SQLType=91 SQLTypeName=YEAR Precision=4 Scale=0 MetadataClassName=java.sql.Date
 ObjectClassName=java.sql.Date Value="2013-01-01"

Column=22 ColumnName=c SQLType=1 SQLTypeName=CHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="char"

Column=23 ColumnName=bc SQLType=-2 SQLTypeName=BINARY Precision=255 Scale=0 MetadataClassName=[B

Column=24 ColumnName=v SQLType=12 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="varchar"

Column=25 ColumnName=lv SQLType=12 SQLTypeName=VARCHAR Precision=32767 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="longvarchar"

Column=26 ColumnName=vb SQLType=-3 SQLTypeName=VARBINARY Precision=255 Scale=0 MetadataClassName=[B

Column=27 ColumnName=b1 SQLType=-7 SQLTypeName=BIT Precision=1 Scale=0 MetadataClassName=java.lang.Boolean

Column=28 ColumnName=b2 SQLType=-3 SQLTypeName=BIT Precision=64 Scale=0 MetadataClassName=[B

Column=29 ColumnName=lb SQLType=-4 SQLTypeName=LONGBLOB Precision=2147483647 Scale=0 MetadataClassName=[B

Column=30 ColumnName=mb SQLType=-4 SQLTypeName=MEDIUMBLOB Precision=16777215 Scale=0 MetadataClassName=[B

Column=31 ColumnName=b SQLType=-4 SQLTypeName=BLOB Precision=65535 Scale=0 MetadataClassName=[B

Column=32 ColumnName=tb SQLType=-3 SQLTypeName=TINYBLOB Precision=255 Scale=0 MetadataClassName=[B

Column=33 ColumnName=lt SQLType=-1 SQLTypeName=VARCHAR Precision=2147483647 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="longtext"

Column=34 ColumnName=mt SQLType=-1 SQLTypeName=VARCHAR Precision=16777215 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="mediumtext"

Column=35 ColumnName=tx SQLType=-1 SQLTypeName=VARCHAR Precision=65535 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="text"

Column=36 ColumnName=tt SQLType=-1 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="tinytext"

Column=37 ColumnName=e SQLType=1 SQLTypeName=CHAR Precision=1 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="A"

Column=38 ColumnName=s SQLType=1 SQLTypeName=CHAR Precision=5 Scale=0 MetadataClassName=java.lang.String
 ObjectClassName=java.lang.String Value="A,B"
===

I close this report as "Not a Bug", but feel free to reopen it if you find any other problem here.
[29 Jul 2013 17:33] Bill Karwin
Confirmed; I downloaded Connector/J 5.1.25 and my test runs without throwing an exception.  Thanks for the update.