order by causes problems in subquery (1.3.176)

[Dave Anonymous]

i have a table like that:

CREATE TABLE ALARM_HISTORY( 

    ID INT AUTO_INCREMENT NOT NULL, 

    ACCOUNT INTEGER NOT NULL,

    ALARMDATE TIMESTAMP

);
ALTER TABLE PUBLIC.ALARM_HISTORY ADD CONSTRAINT PUBLIC.PK_ALARM_HISTORY PRIMARY KEY(ID);
CREATE INDEX PUBLIC.IDX_ALARM_HISTORY_ALARMDATE ON PUBLIC.ALARM_HISTORY(ALARMDATE);
CREATE INDEX PUBLIC.IDX_ALARM_HISTORY_ACCOUNTALARMDATE ON PUBLIC.ALARM_HISTORY(ACCOUNT, ALARMDATE);

Account is a foreign key.

Problem:

This is working (result is 500.000):

SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT 500000;
SELECT * FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ID ASC LIMIT 500000);


This is not working (result is 0)

SELECT * FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT 500000);


or

DELETE FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID IN (SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE ASC LIMIT 500000);


Background:

I want to delete all rows user specific except the last n entries. So i get the currentcount per user first and delete currentCount-maxcount = limit.

other possibility would be this (but slow) but it seems to work

DELETE FROM ALARM_HISTORY WHERE ACCOUNT=1 AND ID NOT IN (SELECT ID FROM ALARM_HISTORY WHERE ACCOUNT=1 ORDER BY ALARMDATE DESC LIMIT maxCount);


[Thomas Mueller]

Hi,

I think this is fixed in the trunk. Could you check the latest nightly build? See http://h2database.com/html/build.html#automated

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Dave Anonymous]

Thanks,

i will check it. But will it be fixed in stable (1.3.x) too?

Regards

Dave

[Thomas Mueller]

Hi,

1.4 is still beta because of the new MVStore storage engine. If you disable that, then I consider it stable. Just append ";mv_store=false" to the database URL.

Regards,

Thomas

--

[Dave Anonymous]

I tried to use the 1.4 but i get this problem (rechecked with 1.3 -> no problem):

General error: "java.lang.IllegalArgumentException: timeNanos out of range 86400998000000"; SQL statement:

SELECT * FROM ALARM_HISTORY [50000-190] HY000/50000 (Help)

org.h2.jdbc.JdbcSQLException: General error: "java.lang.IllegalArgumentException: timeNanos out of range 86400998000000"; SQL statement:

SELECT * FROM ALARM_HISTORY [50000-190] 

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) 

    at org.h2.message.DbException.get(DbException.java:168) 

    at org.h2.message.DbException.convert(DbException.java:295) 

    at org.h2.command.Command.executeQuery(Command.java:213) 

    at org.h2.server.TcpServerThread.process(TcpServerThread.java:320) 

    at org.h2.server.TcpServerThread.run(TcpServerThread.java:159) 

    at java.lang.Thread.run(Unknown Source) 

Caused by: java.lang.IllegalArgumentException: timeNanos out of range 86400998000000 

    at org.h2.value.ValueTimestamp.<init>(ValueTimestamp.java:54) 

    at org.h2.value.ValueTimestamp.fromDateValueAndNanos(ValueTimestamp.java:68) 

    at org.h2.value.ValueTimestamp.fromMillisNanos(ValueTimestamp.java:95) 

    at org.h2.store.Data.readValue(Data.java:771) 

    at org.h2.index.PageDataLeaf.readRow(PageDataLeaf.java:624) 

    at org.h2.index.PageDataLeaf.getRowAt(PageDataLeaf.java:334) 

    at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:101) 

    at org.h2.index.PageDataCursor.next(PageDataCursor.java:67) 

    at org.h2.index.IndexCursor.next(IndexCursor.java:305) 

    at org.h2.table.TableFilter.next(TableFilter.java:465) 

    at org.h2.command.dml.Select.queryFlat(Select.java:541) 

    at org.h2.command.dml.Select.queryWithoutCache(Select.java:654) 

    at org.h2.command.dml.Query.query(Query.java:341) 

    at org.h2.command.dml.Query.query(Query.java:309) 

    at org.h2.command.dml.Query.query(Query.java:36) 

    at org.h2.command.CommandContainer.query(CommandContainer.java:110) 

    at org.h2.command.Command.executeQuery(Command.java:201) 

    ... 3 more 

    at org.h2.engine.SessionRemote.done(SessionRemote.java:624) 

    at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:158) 

    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:179) 

    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) 

    at org.h2.server.web.WebApp.getResult(WebApp.java:1388) 

    at org.h2.server.web.WebApp.query(WebApp.java:1061) 

    at org.h2.server.web.WebApp$1.next(WebApp.java:1023) 

    at org.h2.server.web.WebApp$1.next(WebApp.java:1010) 

    at org.h2.server.web.WebThread.process(WebThread.java:164) 

    at org.h2.server.web.WebThread.run(WebThread.java:89) 

    at java.lang.Thread.run(Unknown Source)