Re: BINARY sorting is incorrect

[Noel Grandin]

Tricky.

In this case
   org.h2.value.ValueBytes#compareSecure
is calling
   org.h2.util.Utils#compareNotNull(byte[], byte[])
to do the comparison, and it treats the data as a sequence of signed bytes.

Since, for example, 0x99 == -103, it naturally sorts below 0x09.

You should really not be relying on any kind of sort order when it comes to BINARY columns, any sort order we could define over it would always be unsuitable to someone.

On 2012-12-10 15:14, Matthew Painter wrote:

Hi all,

Just wanted to check that this is a bug:

create table bin( x binary(1) );

insert into bin(x) values (x'09'),(x'0a'),(x'99'),(x'aa');

select * from bin order by x;

X
99
aa
09
0a

(4 rows, 0 ms)

The sorting should of course be:

X
09
0a
99
aa

(4 rows, 0 ms)

Am I right in thinking this is a bug? Or is there an obscure setting to correct his?


Thanks :)


Matt --
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/Kib7PA3uF4wJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Matthew Painter]

This is contrary to the behaviour in all databases/column stores I've ever come across. As such I'd view it as a bug.

The logic to change is pretty small (see below). HBase, HSQLDB, SQL Server, and MySQL all implement actual binary sorting. 

With this feature, we can't use H2 with MySQL compatibility for testing, and we'll have to stick to HSQLDB.

I guess I could always write a patch :)

M

public static int unsignedByteToInt(byte b) {

return (int) b & FF;

}

public static int compare(final byte[] o1, int offset1, int length1, final byte[] o2, int offset2, int length2) {

final int len = Math.min(length1, length2);

for (int i = 0; i < len; i++) {

byte byte1 = o1[offset1];

byte byte2 = o2[offset2];

if (byte1 != byte2) {

return unsignedByteToInt(byte1) > unsignedByteToInt(byte2) ? 1 : -1;

}

offset1++;

offset2++;

}

final int d = length1 - length2;

return d == 0 ? 0 : (d < 0 ? -1 : 1);

[Thomas Mueller]

Hi,

I understand the current behavior is unexpected (it's also unexpected to me). Maybe we should change it, but then such a change wouldn't be backward compatible, so we would have to wait for the next major release (1.4 I guess).

Regards,

Thomas

[Sergi Vladykin]

Hi,

I guess we can create respective system property which will default to current behavior and remove it in 1.4.

Sergi

[Matthew Painter]

When's 1.4 due? Working out whether or not to fork for now...?

[Matthew Painter]

Hi all,

Did anything ever come of this? I would be super keen to use H2 if it only did correct binary sorting!

Thanks

Matt

[Thomas Mueller]

Hi,

I'm sorry, it will take a while until version 1.4 is out. If you want, you could add a system property to the code that allows you to switch the behavior for version 1.3 - patches are welcome!

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?hl=en.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Matthew Painter]

Thanks for the update. It is on my list of things to do - just very low down right now!

Thanks again

matt

[Noel Grandin]

I have created a new command

SET BINARY_COLLATION UNSIGNED

which should do what you want.

[Matthew Painter]

Awesome! I will give it a go :)

Thanks so much!

Matt

--
Sent from Gmail Mobile