Asc order problem in a Date Data type Column using Pluggable / User-Defined Table

[Pedro Almeida]

Hello,

I am using H2 Version 1.4.193 and I am creating a Pluggable / User-Defined Table with ENGINE "..."

Just to sum up it is a Table that gathers data from a REST Endpoint.

I have implemented the Table, Index and Cursor everything is working in order 

and simple queries like SELECT * FROM TableName work as expected.

The problem is when I send a SQL query to sort by a column of data type DATE

in ascending order the data returned is not correctly sorted 

In descending order works fine!

SELECT * FROM TableName ORDER BY DateColumn ASC

Sorting asc and desc in Numeric Columns works fine too.

Is this a possible bug on the H2 code? or maybe I forgot to implement something?

Any ideas will be appreciated. Thanks.

Pedro

[Noel Grandin]

I updated our unit tests to test this case, and it seems to be working fine.

If you are convinced the problem lies in H2, try creating a small standalone test case, and then we could help you debug it.

[Pedro Almeida]

Hi Noel,

thank you very much for the quick response I am working in isolating the code and the problem.

From what I gather so far I strongly believe the problem lies in H2 when working with the TableFilter

The Cursor I implemented (that is returned by public Cursor find(Session session, SearchRow first, SearchRow last) ) correctly sets the dates as ValueDate. It seems the problem is when H2 applies the transformations on the Cursor afterwards.

In the meantime I can't seem to find the test you added on https://github.com/h2database/h2database

Is it pushed yet?

Pedro

[Pedro Almeida]

Here it is a very minimal project where the issue can be verified.

https://github.com/UlTriX/h2problem

I appreciate any help. Thank you

Pedro 

On Wednesday, 21 December 2016 14:49:02 UTC, Noel Grandin wrote:

[Noel Grandin]

I just pushed an updated unit test to GitHub.

Sorry, I can see anything obviously wrong with your code.

[Pedro Almeida]

Thank you for your time.

Could you replicate the issue with my code though ?

[Pedro Almeida]

The unit test you added I can see that the sort test is being applied to a INT column.

The problem I detect is only happening with columns of data type DATE 

and only when sorting with asc

On Thursday, 22 December 2016 07:37:30 UTC, Noel Grandin wrote:

[Noel Grandin]

Hmmm, I notice in your code that you are declaring the column as type date, but then filling it with strings?

--
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Pedro Almeida]

Hi Noel,

They are strings at first but then converted to the correct data type (it is this way since I am gathering the data from JSON)

After some debugging of my code and H2 code I have understood and fixed the problem.

I had to implement the getCreateSQL() on my extending index returning null because if not the index in some situations can be used

to sort and then not triggering the Collections.sort() in the SortOrder class

@Override
public String getCreateSQL() {
    return null;
}

Thank you very much for your time.

Happy Holidays.

Pedro