> I assume the problem whether nulls are sorted first or last, right? I mean
> the system property "h2.sortNullsHigh".
>
Yes, for operator `>` query using my index can return empty result because
of that, while `<` works fine.
I think if I'll switch h2.sortNullsHigh property this will go opposite.
> > for data row it always must be ValueNull.INSTANCE
>
> Yes, I think so. It's true, ValueNull.INSTANCE can occur here, and
> NULLS_FIRST and NULLS_LAST are ignored in that case. I think H2 indexes
> don't currently support explicit NULLS_FIRST / NULLS_LAST (see below).
>
Looks like a bug to me since SQL index declaration syntax supports this
feature. Though I don't think it is widely used or critical.
> > then result of this comparison depends on null sorting settings of the
> database.
>
> Currently, for H2, Value.compareTypeSave (I will rename it to
> "compareTypeSafe" by the way) uses an hardcoded order:
>
> if (this == v) {
> return 0;
> } else if (this == ValueNull.INSTANCE) {
> return -1;
> } else if (v == ValueNull.INSTANCE) {
> return 1;
> }
> return compareSecure(v, mode);
>
>
This could be changed, but would require some work. Either the system
> property "h2.sortNullsHigh" would need to be ignored for that, or the
> "nulls first / last" setting would need to be explicitly stored in each
> index definition in a database, because the system property could be
> changed between two runs, and data in the index itself can not.
>
Isn't this information already stored for index columns in the database? As
I see persistent database restores db objects from SQL command in metadata,
so since IndexColumn.getSQL and Parser.parseIndexColumnList do support this
syntax, it must be persisted and restored correctly.
I think indexes do not actually use this property anyways, because in
BaseIndex.comapareRows method value from `compare` row is correctly checked
for null and in `rowData` it must be ValueNull.INSTANCE. So this code
should never be executed normally and can be ignored safely.
I've sent a pull request with a simple fix for this issue, could you please
review?
https://github.com/h2database/h2database/pull/164
By the way I looked at value comparison logic and it seems that is coded in
few places which is misleading and error prone.
I think it can work inconsistently in some scenarios already.
Probably it must be refactored to be in a single place first and then add
NULLS_FIRST / NULLS_LAST support to that place?
Sergi
>
> Regards,
> Thomas
>
> On Thursday, August 20, 2015, Sergi Vladykin <[email protected]>
> wrote:
>
>> Thomas,
>>
>> I have a question about BaseIndex.compareValues method.
>> It contains the following lines:
>>
>> boolean aNull = a == null, bNull = b == null;
>> if (aNull || bNull) {
>> return SortOrder.compareNull(aNull, sortType);
>> }
>>
>> As far as I understand, value can be null only in a SearchRow when it has
>> an open bound, for data row it always must be ValueNull.INSTANCE, no?
>> Is this comparison correct? May be it must be something like aNull = a ==
>> ValueNull.INSTANCE, bNull = b == ValueNull.INSTANCE?
>>
>> I was not able to exploit this thing in H2 but I have a problem with my
>> Index implementation because of this.
>> At some point I need to check if the lower bound search row is not
>> greater than upper bound search row (which means we need to return empty
>> result),
>> and if query condition contains an open bound (null in a search row) then
>> result of this comparison depends on null sorting settings of the database.
>>
>> Could you please comment on this?
>>
>> Sergi
>>
>> --
>> 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 [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
