UUID 'null' becomes '00000000-0000-0000-0000-000000000000'

29,976 views
Skip to first unread message

Frederik T.

unread,
Jan 26, 2012, 8:15:01 AM1/26/12
to H2 Database
Hello everybody,

I found something that seems to be illogically to me.

My database uses UUIDs as primary and foreign keys. The software
stores the ids as strings for historical reasons. An error set the id
to null what leaded to a query like

INSERT INTO table (id) VALUES ('null');

This query did not raise an error and the resulting UUID is
'00000000-0000-0000-0000-000000000000'!!

For comparision:

If I insert an empty UUID like

INSERT INTO table (id) VALUES ('');

the values are zeropadded which is okay in my opinion.
The resulting UUID is '00000000-0000-0000-0000-000000000000'.

If I insert a short UUID like

INSERT INTO table (id) VALUES ('1');

the values are zeropadded which is okay, too.
The resulting UUID is '00000000-0000-0000-0000-000000000001'.

But 'null' (not null) is neither a hexadecimal representation of a
number and so must not be zero padded. But it's not empty, too. So I
think an error should be raised.

The table definition is

CREATE TABLE table (
id UUID not null
);

So

INSERT INTO table (id) VALUES (null);

can't be inserted.

I would exspect H2 not to insert this incorrect dataset and not to
zeropad any string that can't be converted to an number.


Kind Regards

Frederik

Thomas Mueller

unread,
Jan 31, 2012, 3:39:53 PM1/31/12
to h2-da...@googlegroups.com
Hi,

Currently, converting text to a UUID is very lenient: unknown characters are simply ignored. That means this will work:

call cast('014-xyz' as uuid);
call cast('null' as uuid);

I will change the parsing algorithm to reject any character that is not hex, '-', and the ASCII code is larger than 32 (space).

Regards,
Thomas

Frederik T.

unread,
Feb 1, 2012, 3:16:58 AM2/1/12
to h2-da...@googlegroups.com
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> 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.
Hi Thomas,

that's a good idea. Thank's a lot.


Regards,

Frederik

Reply all
Reply to author
Forward
0 new messages