MIN() and MAX() give incorrect value

Help
2013-05-16
2014-01-19
  • Thomas Suckow
    Thomas Suckow
    2013-05-16

    HSQLDB: 2.2.9

    MIN() and MAX() give value one greater than expected when used on a column generated by ROWNUM()

    Example:

    Create Table Test ( a INTEGER );
    Insert Into Test Values( 1 );
    Insert Into Test Values( 2 );
    Insert Into Test Values( 3 );
    Insert Into Test Values( 3 );
    Insert Into Test Values( 2 );
    
    Select t.*, ROWNUM() as r From Test t;
    
    1   1
    2   2
    3   3
    3   4
    2   5
    

    Table and rows are as expected

    But

    Select sub.a, min(sub.r) as r From (Select t.*, ROWNUM() as r From Test t) sub GROUP BY sub.a;
    

    Gives:

    1   2
    2   3
    3   4
    

    Wat.

     
  • Thomas Suckow
    Thomas Suckow
    2013-05-17

    It does appear to be fixed in the latest 2.3 SNAPSHOT.

    I did notice one change, not sure if it is intentional or not. It doesn't affect me as I don't check the return value of sqltool but shutdown now results in an error. Thought I would mention it.

    java -jar sqltool.jar --inlineRc=url=jdbc:hsqldb:hsql://localhost,user=SA,password= --sql="shutdown;"
    [Server@257db177]: Initiating shutdown sequence...
    SEVERE  SQL Error at '--sql' line 1:
    "shutdown"
    connection exception: connection failure: java.io.EOFException
    org.hsqldb.cmdline.SqlTool$SqlToolException