I have come across this issue in mysql Ver 8.40 Distrib 4.1.3-beta as
well as mysql Ver 8.40 Distrib 4.0.16. I am using
mysql-connector-java-3.0.14-production-bin.jar on Mac OS X 10.3.5
I have a table that i created like this:
stmt.executeUpdate( "CREATE TABLE OFFERING ("
+ "ID INTEGER NOT NULL AUTO_INCREMENT, "
+ "PREFIX VARCHAR (20) NOT NULL, "
+ "COURSEID INTEGER NOT NULL, "
+ "MAXCAPACITY INTEGER, "
+ "REG_ST_DATE DATETIME NOT NULL DEFAULT \"1970-01-01 00:00:00\", "
+ "DESCRIPTION TEXT, "
+ "OWNERID INTEGER NOT NULL, "
+ "INDEX (COURSEID), "
+ "UNIQUE (COURSEID, PREFIX), "
+ "PRIMARY KEY ( ID )"
+ ") TYPE = innoDB");
I then execute a query like this:
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM OFFERING
WHERE COURSEID = ?");
pstmt.setInt(1, courseId);
ResultSet rs = pstmt.executeQuery();
I get data like this:
+----+--------+----------+-------------+---------------------+---------
+-----------------------------------------------------------------------
--------------+
| ID | PREFIX | COURSEID | MAXCAPACITY | REG_ST_DATE | OWNERID
| DESCRIPTION
|
+----+--------+----------+-------------+---------------------+---------
+-----------------------------------------------------------------------
--------------+
| 70 | A 0001 | 25 | -1 | 2004-10-02 00:00:00 | 1
|
|
| 6 | O 0001 | 25 | 30 | 2004-08-01 00:00:00 | 2
| This offering will help you to effectively communicate your ideas to
your audience. |
| 7 | O 0002 | 25 | 45 | 2004-12-15 00:00:00 | 2
| This offering will help you to effectively communicate your ideas to
your audience. |
+----+--------+----------+-------------+---------------------+---------
+-----------------------------------------------------------------------
--------------+
The resultset returned is correct, the only problem is that the
resultset is sorted on PREFIX for some reason I do not understand!!!.
Does specifying UNIQUE(CourseID, PREFIX) tell mysql to sort the
resultset as well? (When I remove the "UNIQUE(CourseID, PREFIX)"
everything behaves as expected i.e the resultset is not sorted on the
PREFIX column).
Can someone shed light on this please?
Regards,
Mufaddal Khumri.
