Bug #1035 order by desc flips entries for datetime field within a second of eachother
Submitted: 12 Aug 2003 11:27 Modified: 13 Aug 2003 12:08
Reporter: Kerry Ward Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.14b OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[12 Aug 2003 11:27] Kerry Ward
Description:
I have a table that contains entries with a DATETIME field that reflects when the entry was added to the table.  Under some conditions the table gets updated very quickly and two entries are made where the second field is the same.  When I select the table and order by the DATETIME field, the returned entries match the order that they were added to the table.  However, if I specify to order by the DATETIME field in descending order, those entries that have the same date and time to the second value are sorted in the reverse order than expected.

How to repeat:
1. Create a INNODB table that includes a DATETIME field
2. Add entries into the table so that the DATETIME field reflects entries less than a second apart.
3. Execute a select statement with order by the DATETIME field.
4. Execute a select statement with order by the DATETIME field desc.
5. With desc, the entries with the same second value will be reversed.

Here is my table.

CREATE TABLE SIGNED_DOC_HISTORY (
       HISTORY_ID           BIGINT UNSIGNED	NOT NULL,
       ACTION_DATE      	DATETIME		NOT NULL,
       SIGNED_DOCUMENT_ID   BIGINT UNSIGNED	NOT NULL,
       STATUS_CHANGE		SMALLINT UNSIGNED	NOT NULL,
       ACTION				VARCHAR(1) NOT NULL,
       MESSAGE              VARCHAR(255) NULL
) TYPE=INNODB;

Here is my java code snipit for setting the entries in the table

  public void processStatusChange(long id, long sdId, long status, String message, String action)
  {
	PreparedStatement statement = null;
	Connection connection = null;

    try
    {
        connection = getConnection();

        String sql = null;

        sql = "insert into SIGNED_DOC_HISTORY " +
                "(HISTORY_ID, ACTION_DATE, SIGNED_DOCUMENT_ID, STATUS_CHANGE, ACTION, MESSAGE) " +
                " values (?, ?, ?, ?, ?, ?)";

        statement = connection.prepareStatement(sql);

        statement.clearParameters();
		statement.setLong(1, id);
        statement.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
        statement.setLong(3, sdId);
        statement.setLong(4, status);
        statement.setString(5, action);
		statement.setString(6, message);
        
        int i = statement.executeUpdate();
        if(i != 1)
        {
            System.out.println("error returned after insert into SIGNED_DOC_HISTORY");
        }

    }
    catch (Exception exc)
    {
	exc.printStackTrace();
    }
    finally
    {
	    try
	    {
		    if (statement != null) statement.close();
		    if (connection != null) connection.close();
	    }
	    catch (SQLException sqle2)
	    {
	    }
	}
  }

Here is the sql select statements.

select STATUS_CHANGE, ACTION_DATE, MESSAGE from SIGNED_DOC_HISTORY 
where SIGNED_DOCUMENT_ID=1 order by ACTION_DATE;

select STATUS_CHANGE, ACTION_DATE, MESSAGE from SIGNED_DOC_HISTORY 
where SIGNED_DOCUMENT_ID=1 order by ACTION_DATE desc;
[13 Aug 2003 12:08] Indrek Siitan
MySQL doesn't store a time more detailed than on a second level, so
the entries that have the same second value, are sorted with an
indefinite result - most probably they're displayed just in the order
they come in the datafile, which, if you haven't done much deletes
on the table, is the order the records are inserted (which is probably
what looks "flipped" to you).

If you need a reverse chronological order relative to the record insert
time, I would suggest adding an AUTO_INCREMENT field to the table, 
which always increases with new records added, and then 
DESC-sorting on that.