Bug #11718 ResultSet.getObject() returns incorrect object class with ORDER BY clause
Submitted: 4 Jul 2005 8:31 Modified: 29 Aug 2005 21:48
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.1.10, 4.1.12, 5.0.7 OS:
Assigned to: Evgeny Potemkin CPU Architecture:Any

[4 Jul 2005 8:31] Shane Bester
Description:
An object of incorrect class is returned by a call to rs.getObject(1) when an ORDER BY clause is added to an existing working query.   

See private OrderByTest.java file for test case program.  The incorrect type seems to occur itself when joining the two tables.

Two identical queries - one with ORDER BY and the end and the other without ORDER BY are used.

System.out.println(rs.getObject(1).getClass());

Without ORDER BY : class java.sql.Date
With ORDER BY : class java.lang.String

How to repeat:
DROP TABLE IF EXISTS TBL_PJ_PHASE;

CREATE TABLE TBL_PJ_PHASE (
  PJ_PHASE_ID        BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  PHASE_NAME         VARCHAR(100) NOT NULL,
  PHASE_DESCRIPTION  TEXT,
  SEQUENCE_ID        NUMERIC(9),
  UPDATE_DATETIME    DATETIME  NOT NULL,
  UPDATE_USER        NUMERIC(10)  NOT NULL
);

DROP TABLE IF EXISTS TBL_PJ_PHASE_UPDATE;

CREATE TABLE TBL_PJ_PHASE_UPDATE (
  PROJECT_ID      NUMERIC(9)   NOT NULL,
  PJ_PHASE_ID     NUMERIC(9)   NOT NULL,
  COMPLETE_DATE   NUMERIC(8)   NOT NULL,
  UPDATE_DATETIME DATETIME     NOT NULL,
  UPDATE_USER     NUMERIC(10)  NOT NULL
);

INSERT INTO TBL_PJ_PHASE (PHASE_NAME, PHASE_DESCRIPTION, SEQUENCE_ID, UPDATE_USER)
  VALUES ('1', 'aaa', 0, 0);
INSERT INTO TBL_PJ_PHASE (PHASE_NAME, PHASE_DESCRIPTION, SEQUENCE_ID, UPDATE_USER)
  VALUES ('2', 'bbb', 1, 0);
INSERT INTO TBL_PJ_PHASE (PHASE_NAME, PHASE_DESCRIPTION, SEQUENCE_ID, UPDATE_USER)
  VALUES ('3', 'ccc', 2, 0);

INSERT INTO TBL_PJ_PHASE_UPDATE VALUES (7, 1, 20030301, 20030301, 1);
INSERT INTO TBL_PJ_PHASE_UPDATE VALUES (8, 2, 20030302, 20030302, 1);
INSERT INTO TBL_PJ_PHASE_UPDATE VALUES (9, 3, 20030302, 20030302, 1);

Now compile and run the OrderByTest.java program:
$ javac OrderByTest.java
$ java -classpath .:mysql-connector-java-3.1.10-bin.jar OrderByTest
=== Run test : Without ORDER BY
QUERY                 : SELECT STR_TO_DATE(CONCAT(PHU.COMPLETE_DATE), '%Y%m%d') AS COMPLETE_DATE FROM TBL_PJ_PHASE PH,TBL_PJ_PHASE_UPDATE PHU WHERE PH.PJ_PHASE_ID=PHU.PJ_PHASE_ID and PHU.PROJECT_ID='7'
Type of COMPLETE_DATE : class java.sql.Date

=== Run test : With ORDER BY
QUERY                 : SELECT STR_TO_DATE(CONCAT(PHU.COMPLETE_DATE), '%Y%m%d') AS COMPLETE_DATE FROM TBL_PJ_PHASE PH,TBL_PJ_PHASE_UPDATE PHU WHERE PH.PJ_PHASE_ID=PHU.PJ_PHASE_ID and PHU.PROJECT_ID='7'  ORDER BY PH.SEQUENCE_ID
Type of COMPLETE_DATE : class java.lang.String

Suggested fix:
not sure..
[4 Jul 2005 10:49] MySQL Verification Team
Updated synopsis.
[4 Jul 2005 16:17] Mark Matthews
Not a Connector/J bug. The JDBC driver uses whatever type the server tells it to use. This is yet another case of query resolution through temporary tables (and other side effects of ORDER BY) that cause the server to return non-correct types to clients.
[5 Jul 2005 6:06] MySQL Verification Team
Updated submission
[17 Aug 2005 10:18] Evgeny Potemkin
Bug not present (can't repeat) in 5.0.12. tested on linux x86_64
[17 Aug 2005 19:44] Evgeny Potemkin
In v4.1 create_tmp_field_from_item() was creating tmp field without regard to original field type of Item. this results in wrong type being reported to client.
[17 Aug 2005 19:52] 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/internals/28414
[22 Aug 2005 13:05] Evgeny Potemkin
Backport from 5.0
Fixed in 4.1.15, cset 1.2377.3.1
[29 Aug 2005 21:48] Mike Hillyer
Documented in 4.1.15 changelog:

<listitem><para>Queries that created implicit temporary tables could return incorrect column types for some columns. (Bug #11718)</para></listitem>