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);
}
On Tuesday, December 11, 2012 7:10:22 AM UTC, Noel Grandin wrote:
>
> 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
> <http://localhost:8082/query.do?jsessionid=6f1c7d9767ef118f4a6200a29c62d7ab#>
>
> 99 aa 09 0a (4 rows, 0 ms)
>
> The sorting should of course be:
>
> X
> <http://localhost:8082/query.do?jsessionid=6f1c7d9767ef118f4a6200a29c62d7ab#>
>
> 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 [email protected]<javascript:>
> .
> To unsubscribe from this group, send email to
> [email protected] <javascript:>.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
>
--
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/-/wu5GVgozSagJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
