MySQL Bugs: #11718: ResultSet.getObject() returns incorrect object class with ORDER BY clause

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

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..

Updated synopsis.

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.

Updated submission

Bug not present (can't repeat) in 5.0.12. tested on linux x86_64

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.

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

Backport from 5.0
Fixed in 4.1.15, cset 1.2377.3.1

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>