Bug #11032 getObject() returns a String for a sub-query of type datetime
Submitted: 2 Jun 2005 12:57 Modified: 27 Nov 2006 19:22
Reporter: Mick Francis (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.13 OS:NT4
Assigned to: Georgi Kodinov CPU Architecture:Any

[2 Jun 2005 12:57] Mick Francis
Description:
If the following query is run, the ResultSet.getObject(3) method returns an Object of class String rather than java.sql.Timestamp:

SELECT dUpdateStart,
       dUpdateEnd,
       (SELECT dQueryCacheCleared
        FROM SfnQueryCacheDates
        WHERE cDataTable       = 'T'
          AND cHostNameAndPath = 'X'
       ) AS dQueryCacheCleared 
FROM SfnDataTables
WHERE cDataTable = 'T';

SfnQueryCacheDates.dQueryCacheCleared is of type datetime.

The sub-query is not truely correlated, as it has no free variables - it is merely used to save a client-server round-trip. Workaround is simple - use 2 queries.

If a PreparedStatement is used, getObject() returns a Timestamp as expected:

SELECT dUpdateStart,
       dUpdateEnd,
       (SELECT dQueryCacheCleared
        FROM SfnQueryCacheDates
        WHERE cDataTable       = ?
          AND cHostNameAndPath = ?
       ) AS dQueryCacheCleared 
FROM SfnDataTables
WHERE cDataTable = ?

How to repeat:
Run the above query (or similar) over JDBC. Table creation:

create table SfnQueryCacheDates (
    cHostNameAndPath   varchar(255) not null,
    cDataTable         varchar(128) not null,
    dAutoCached        datetime     null,
    dQueryCacheCleared datetime     null) ENGINE=INNODB;

ALTER TABLE SfnQueryCacheDates ADD CONSTRAINT PK_SfnQueryCacheDates PRIMARY KEY 
    (cHostNameAndPath, cDataTable);

create table SfnDataTables ( 
    cDataTable          varchar(128)  not null,
    cCacheTable         varchar(255) null,
    cPkColumns          varchar(255) null,
    dUpdateStart        datetime     null,
    dUpdateEnd          datetime     null,
    bEnableQueryCaching tinyint      not null,
    bEnableAutoCaching  tinyint      not null) ENGINE=INNODB;

alter table SfnDataTables
    add constraint SfnDataTables_PK primary key (cDataTable);
[2 Jun 2005 13:57] Mark Matthews
This is actually a server issue, there were some bugs with it changing the datatype when resolving queries using temp tables.

Please let us know what version of the server you are using. These should be fixed in the latest version(s) of 4.1 and 5.0.
[2 Jun 2005 13:58] Mick Francis
Using server 4.1.9 (essentials)
[3 Jun 2005 9:24] Vasily Kishkin
Tested on Win 2000 Sp4 , JDK 1.5, MyODBC 3.51.11, MySQL 4.1.13. Test case is attached.
Test case returns  java.lang.String
[3 Jun 2005 9:25] Vasily Kishkin
Test case

Attachment: test.java (text/java), 1.06 KiB.

[7 Nov 2006 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14931

ChangeSet@1.2539, 2006-11-07 10:47:13+02:00, gkodinov@macbook.gmz +4 -0
  Bug #11032: getObject() returns a String for a sub-query of type datetime
   - When returning metadata for scalar subqueries the actual type of the
     column was calculated based on the value type, which limits the actual
     type of a scalar subselect to the set of (currently) 3 basic types : 
     integer, double precision or string. This columns of types other then
     the basic ones (e.g. date/time) to be reported having the corresponding
     basic type.
     Fixed by storing/returning information for the column type in addition
     to the result type.
[7 Nov 2006 16:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14979

ChangeSet@1.2539, 2006-11-07 18:16:17+02:00, gkodinov@macbook.gmz +4 -0
  Bug #11032: getObject() returns a String for a sub-query of type datetime
   - When returning metadata for scalar subqueries the actual type of the
     column was calculated based on the value type, which limits the actual
     type of a scalar subselect to the set of (currently) 3 basic types : 
     integer, double precision or string. This is the reason that columns
     of types other then the basic ones (e.g. date/time) are reported as
     being of the corresponding basic type.
     Fixed by storing/returning information for the column type in addition
     to the result type.
[27 Nov 2006 17:10] Georgi Kodinov
Pushed in 4.1.23/5.0.32/5.1.14-beta
[27 Nov 2006 19:22] Paul Dubois
Noted in 4.1.23, 5.0.32, 5.1.14 changelogs.

Metadata for columns calculated from scalar subqueries was limited to
integer, double, or string, even if the actual type of the column was
different.