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