Strange behavior when passing SelectArg for orderByRaw(). #169
Comments
|
Sorry for the delay @gogos-venge . I've grabbed your unit test code but it doesn't fail for me. Should it? |
|
It's impossible not to get this assert to fail: If this: Does not produce something like this: ... ORDER BY `hello` IS NULL ASCThen this documentation is, in my opinion, misleading: public QueryBuilder<T, ID> orderByRaw(String rawSql, com.j256.ormlite.stmt.ArgumentHolder... args)
Add raw SQL "ORDER BY" clause to the SQL query statement.
Params:
rawSql – The raw SQL order by clause. This should not include the "ORDER BY".
args – Optional arguments that correspond to any ? specified in the rawSql. Each of the arguments must have the sql-type set.Or, there's something really wrong with my perception |
|
It's not supposed to generate I tried to run your test but the assert does not fail. I see the same order in the list whether with Does your test code still fail for you? If so, what version of ORMLite are you running and on what OS? |
j256
changed the title
|
This is, really strange. It fails all the times for me, I'm 100% positive. Here let me append the output: I'm using version 5.1 (please check it here in the build.gradle) |
|
Huh. Thanks for looking into it. This seems to be an issue with H2. Looks like they changed their behavior between version 1.2.128 that ORMLite is using and 1.4.200 that you are using. If I upgrade ORMLite to 1.4.200 my test fails as well. In any case, this doesn't seem to be an ORMLite issue. It does mean that I should support the NULLS FIRST and LAST constructs however. |
|
Sorry I have to insist here. I didn't notice this problem because of the failing h2 driver, but because it actually happened in a production build which used sqlite-jdbc. I've updated the tests which now include one for h2, and one for sqlite. They both fail. I still use ORMlite 5.1 and now also use sqlite-jdbc driver '3.32.3.2' . |
|
You are going to insist what? That ORMLite should modify the order of the results returned by these databases? That would be a serious bug . This is not an ORMLite issue. ORMLite is translating the query into SQL appropriate for H2 and Sqlite. Your problem is with the underlying database not returning the results in the order you expect. If you did not use ORMLite but you did the same queries to H2 and Sqlite directly, you should see the same order. That make sense? That said, I've added support for NULLS FIRST and LAST in order by. See: 4174d74 I can push a new release now if that would be helpful. |
|
Thank you for looking into this |
I am re-posting this issue here on github, as there is already an open question in SO. I stumbled upon some strange behavior regarding
orderByRaw. I was trying to model this query, which resembles adistinct:In a populated database, this query should group by the occupation value, and order by the
IS NULLvalue.IS NULLis either 1 or 0, which depends on whether the value is null or not correspondingly. Now if you order byIS NULLvalue ascending, then you ask SQL to return all the zeroes first, and all the ones in the end, literally giving a result ofNULLSin the end.In order to model the above query using ORMlite, I did this:
Apparently one should expect the null values to be returned after all the non-null ones, but instead the query seems to be run as is:
The above query is a perfectly valid one. I've never seen a logically valid usage of
?in this context but still it's syntactically correct, and therefore the driver just accepts it without errors, but the ordering is unaffected.I created a JUnit test with an assert test in my github. You can check it here.
The text was updated successfully, but these errors were encountered: