MySQL Bugs: #34029: Time data types are not reported as correct type when used in a sub query

Bug #34029	Time data types are not reported as correct type when used in a sub query
Submitted:	24 Jan 2008 5:39	Modified:	24 Jan 2008 7:15
Reporter:	Malcolm Sharman	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:	Linux (Fedora Core 5 + 6)
Assigned to:		CPU Architecture:	Any
Tags:	datatype, subquery, time

Description:
When using a subquery to retrieve a time value, the data type is incorrectly returned as a string and not as time. This causes the Connector/J JDBC driver (version > 5 ) to be unable to tell what java.sql.xxx type that it is supposed to  construct for the resultset.

I discovered a workaround in that if i rewrite the query to use an inner join instead of a subquery the results are returned correctly.

I ran mysql -T to get the all the info about the data type returned and sure enough the types are different with the 2 queries

Malcolm Sharman 

 

How to repeat:

CREATE TABLE  `test`.`time` (
  `timeid` int(10) unsigned NOT NULL auto_increment,
  `starttime` time NOT NULL,
  `info` varchar(45) NOT NULL,
  `endtime` time NOT NULL,
  `locationCode` int(10) unsigned NOT NULL,
  `timecode` char(1) NOT NULL,
  PRIMARY KEY  USING BTREE (`timeid`)
  );
  

  CREATE TABLE `test`.`location` (
  `locationid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `location` VARCHAR(45) NOT NULL,
  `displayname` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`locationid`)
  );

  INSERT INTO location (location, displayname)
  VALUES ('testlocation1', 'Test Location');
  
  INSERT INTO location (location, displayname)
  VALUES ('testlocation2', 'Test Location 2');

  INSERT INTO location (location, displayname)
  VALUES ('testlocation3', 'Test Location 3');

  INSERT INTO location (location, displayname)
  VALUES ('testlocation4', 'Test Location 4');

  INSERT INTO time (startTime, endTime, info, locationcode, timecode)
  VALUES ('05:00:00','15:30:00', 'morning', '1', 'M');
  
  INSERT INTO time (startTime, endTime, info, locationcode, timecode)
  VALUES ('12:00:00','18:30:00', 'afternoon', '1', 'A');

-----------------------
 
        select l.locationid, l.location, l.displayname,
          (select StartTime from time where locationCode = 1 and timecode = 'A') as StartTime,
          (select EndTime from time  WHERE locationcode = 1 and timeCode = 'A') as EndTime
          from location l
          where locationid = 1;

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

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'locationid'
Type:       3
Length:     10
Max length: 1
Is_null:    0
Flags:      49699
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'location'
Type:       253
Length:     45
Max length: 13
Is_null:    0
Flags:      4097
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'displayname'
Type:       253
Length:     45
Max length: 13
Is_null:    0
Flags:      4097
Decimals:   0

Catalog:    'def'
Database:   ''
Table:      ''
Name:       'StartTime'
Type:       253
Length:     8
Max length: 8
Is_null:    1
Flags:      128
Decimals:   31

Catalog:    'def'
Database:   ''
Table:      ''
Name:       'EndTime'
Type:       253
Length:     8
Max length: 8
Is_null:    1
Flags:      128
Decimals:   31

+------------+---------------+---------------+-----------+----------+
| locationid | location      | displayname   | StartTime | EndTime  |
+------------+---------------+---------------+-----------+----------+
|          1 | testlocation1 | Test Location | 05:00:00  | 15:30:00 |
+------------+---------------+---------------+-----------+----------+
1 row in set (0.01 sec)

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

    select l.locationid, l.location, l.displayname, t.starttime,t.endtime
    from location as l
    inner join time as t on l.locationid = t.locationCode
    where t.timecode = 'A'
    and t.locationcode = 1

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

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'locationid'
Type:       3
Length:     10
Max length: 1
Is_null:    0
Flags:      49699
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'location'
Type:       253
Length:     45
Max length: 13
Is_null:    0
Flags:      4097
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      'l'
Name:       'displayname'
Type:       253
Length:     45
Max length: 13
Is_null:    0
Flags:      4097
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      't'
Name:       'starttime'
Type:       11
Length:     8
Max length: 8
Is_null:    0
Flags:      4225
Decimals:   0

Catalog:    'def'
Database:   'test'
Table:      't'
Name:       'endtime'
Type:       11
Length:     8
Max length: 8
Is_null:    0
Flags:      4225
Decimals:   0

+------------+---------------+---------------+-----------+----------+
| locationid | location      | displayname   | starttime | endtime  |
+------------+---------------+---------------+-----------+----------+
|          1 | testlocation1 | Test Location | 05:00:00  | 15:30:00 |
+------------+---------------+---------------+-----------+----------+
1 row in set (0.00 sec)

minor update to adjust the tags

I've now verified the bug still exists in the latest release (5.0.45)

Sorry, but I was not able to repeat the behaviour described on newer 5.0.x version:

C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -T -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE  `test`.`time` (
    ->   `timeid` int(10) unsigned NOT NULL auto_increment,
    ->   `starttime` time NOT NULL,
    ->   `info` varchar(45) NOT NULL,
    ->   `endtime` time NOT NULL,
    ->   `locationCode` int(10) unsigned NOT NULL,
    ->   `timecode` char(1) NOT NULL,
    ->   PRIMARY KEY  USING BTREE (`timeid`)
    ->   );
Query OK, 0 rows affected (0.14 sec)

mysql>   CREATE TABLE `test`.`location` (
    ->   `locationid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `location` VARCHAR(45) NOT NULL,
    ->   `displayname` VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (`locationid`)
    ->   );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO location (location, displayname)
    ->   VALUES ('testlocation1', 'Test Location');
Query OK, 1 row affected (0.09 sec)

mysql>   INSERT INTO location (location, displayname)
    ->   VALUES ('testlocation2', 'Test Location 2');
Query OK, 1 row affected (0.02 sec)

mysql>   INSERT INTO location (location, displayname)
    ->   VALUES ('testlocation3', 'Test Location 3');
Query OK, 1 row affected (0.03 sec)

mysql>   INSERT INTO location (location, displayname)
    ->   VALUES ('testlocation4', 'Test Location 4');
Query OK, 1 row affected (0.05 sec)

mysql>   INSERT INTO time (startTime, endTime, info, locationcode, timecode)
    ->   VALUES ('05:00:00','15:30:00', 'morning', '1', 'M');
Query OK, 1 row affected (0.03 sec)

mysql>   INSERT INTO time (startTime, endTime, info, locationcode, timecode)
    ->   VALUES ('12:00:00','18:30:00', 'afternoon', '1', 'A');
Query OK, 1 row affected (0.05 sec)

mysql> select l.locationid, l.location, l.displayname,
    ->           (select StartTime from time where locationCode = 1 and timecode
 = 'A') as
    -> StartTime,
    ->           (select EndTime from time  WHERE locationcode = 1 and timeCode
= 'A') as
    -> EndTime
    ->           from location l
    ->           where locationid = 1;
Field   1:  `locationid`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `location`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     135
Max_length: 13
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   3:  `displayname`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     135
Max_length: 13
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   4:  `StartTime`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     8
Max_length: 8
Decimals:   31
Flags:      BINARY

Field   5:  `EndTime`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     8
Max_length: 8
Decimals:   31
Flags:      BINARY

+------------+---------------+---------------+-----------+----------+
| locationid | location      | displayname   | StartTime | EndTime  |
+------------+---------------+---------------+-----------+----------+
|          1 | testlocation1 | Test Location | 12:00:00  | 18:30:00 |
+------------+---------------+---------------+-----------+----------+
1 row in set (0.06 sec)

mysql> select l.locationid, l.location, l.displayname, t.starttime,t.endtime
    ->     from location as l
    ->     inner join time as t on l.locationid = t.locationCode
    ->     where t.timecode = 'A'
    ->     and t.locationcode = 1
    -> ;
Field   1:  `locationid`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `location`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     135
Max_length: 13
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   3:  `displayname`
Catalog:    `def`
Database:   `test`
Table:      `l`
Org_table:  `location`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     135
Max_length: 13
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   4:  `starttime`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `time`
Type:       TIME
Collation:  binary (63)
Length:     8
Max_length: 8
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE

Field   5:  `endtime`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  `time`
Type:       TIME
Collation:  binary (63)
Length:     8
Max_length: 8
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE

+------------+---------------+---------------+-----------+----------+
| locationid | location      | displayname   | starttime | endtime  |
+------------+---------------+---------------+-----------+----------+
|          1 | testlocation1 | Test Location | 12:00:00  | 18:30:00 |
+------------+---------------+---------------+-----------+----------+
1 row in set (0.00 sec)

So, please, check with a newer version, 5.0.51 at least.