uuid problems

[pgulutzan]

uuid problems

I have Ubuntu 20.04. I have Tarantool 2.9, pulled from source today.
I intend to document the new UUID feature.
But I encounter problems, and some of them make me wonder what to say.

tarantool> box.execute([[SELECT QUOTE(UUID());]])
tarantool: /home/pgulutzan/tarantool-master/src/box/sql/func.c:1184: quoteFunc: Assertion `mem_is_null(argv[0])' failed.
Aborted (core dumped)

SELECT CAST(1 AS UUID); causes error = Type mismatch: can not convert 1 to uuid
SELECT CAST(X'01' AS UUID); causes error = Type mismatch: can not convert varbinary to uuid
In the first case, the error is that I cannot cast a value i.e. 1.
In the second case, the error is that I cannot cast a type i.e. varbinary.
The second error message is misleading, it is legal to cast varbinary to uuid.
Messages should consistently say the problem is with the value, not with the type.

These functions, which are designed for strings, work with UUID()) arguments:

• CHAR()
• COALESCE()
• HEX()
• LENGTH()
• LOWER()
• REPLACE()
• SOUNDEX()
• SUBSTR()
• TRIM()
• UNICODE()
• UPPER()

But these functions / operators do not work with UUID() arguments:

• POSITION()
• LIKE

So there is an inconsistency -- sometimes it is okay to treat UUID()
as if it is a string, sometimes it is not okay.
By the way, HEX(UUID()) is not the same as HEX(CAST(UUID()) AS STRING).

box.execute([[DROP TABLE t;]])
box.execute([[CREATE TABLE t (s1 SCALAR PRIMARY KEY);]])
box.execute([[INSERT INTO t VALUES ('000024ac-7ca6-4ab2-bd75-34742ac91213');]])
box.execute([[INSERT INTO t VALUES (CAST('00000000-0000-0000-0000-000000000000' AS UUID));]])
box.execute([[SELECT * FROM t WHERE s1 < (SELECT MAX(s1) FROM t);]])
box.execute([[SELECT * FROM t WHERE s1 > (SELECT MAX(s1) FROM t);]])
box.execute([[SELECT * FROM t WHERE s1 = (SELECT MAX(s1) FROM t);]])
box.execute([[SELECT * FROM t WHERE s1 <> (SELECT MAX(s1) FROM t);]])

... The INSERT statements succeed so there are two rows, a string and a uuid.
... The SELECT statements with < and > and = do not cause error messages
(the result for < is wrong but I will address that separately).
The SELECT statement with <> causes an error message:
'Type mismatch: can not convert text to uuid'
To me this looks odd that < and > and = are legal but <> is not.

box.execute([[SELECT GREATEST(1,X'44',UUID(),1e555);]])
box.execute([[DROP TABLE t3;]])
box.execute([[CREATE TABLE t3 (s1 SCALAR PRIMARY KEY);]])
box.execute([[INSERT INTO t3 VALUES (false),(1.0),(X'00'),(CAST('00000000-0000-0000-0000-000000000000' AS UUID));]])
box.execute([[SELECT MIN(s1),MAX(s1) FROM t3;]])

I read in tarantool/doc#2151 (comment)
that for scalar comparisons "The order is as follows: boolean < number < string < varbinary < uuid."
But the GREATEST() function returns the varbinary value not the uuid value.
And the MAX(s1) function returns the varbinary value not the uuid value.
(SELECT MAX(s1) FROM t3; works correctly but SELECT MIN(S1),MAX(s1) FROM t3; does not.)
And as noted for point 4 the result from s1 < SELECT MAX(s1) was wrong.

I see that uuid(4) is supposed to return a type-4 uuid, okay.
But I'm worried that maybe nobody has thought about syntax when there are other types.
(I assume issue #5444 Support all RFC 4122 UUID versions will happen someday.)
For example, CAST('...' AS UUID) works perectly now because assumed format of '...'
is 4. But how will I be able to specify the required type when it is not 4?
It seems to me that CAST('...' AS UUID(n)) might work even though that's the
function's syntax as well as the type's syntax. But is that the plan?

box.execute([[SELECT ?;]], {require("uuid").new()})

fails. It's possible to pass values of other types, so perhaps I am merely failing
to think of the appropriate way to pass values of this type.

tarantool> box.execute([[SELECT CAST('468aed62-6e44-4989-a48e-f85cf26413dc' AS VARBINARY);]])
result looks like ['468aed62-6e44-4989-a48e-f85cf26413dc']
...
tarantool> box.execute([[SELECT CAST(CAST('468aed62-6e44-4989-a48e-f85cf26413dc' AS UUID) AS VARBINARY);]])
result looks like [!!binary Yu2KRkRuiUmkjvhc8mQT3A==]

The result of the first SELECT looks good, the result of the second SELECT does not look good.

For the documentation:

• Should I document that some string functions work with uuid, or ignore that?
• Should I document that uuid() can be expressed as uuid(4), or ignore that?

[ImeevMA]

Hi! Thank you for the issue. I have some answers.

1. True, I missed this function somehow. I will fix this.
2. All VARBINARY values printed as just 'varbinary'. We should think about how to print these values, for example we can print hex of these values. Not exactly UUID problem.
3. This should be solved as part of sql: length takes not-string values #4159. Not exactly UUID problem.
4. According to RFC we cannot use implicit cast in comparison. Still, you can compare them in index. Operation <> does not use index. Not exactly UUID problem.
5. This is also my error, I will fix this.
6. Is there a need to keep the uuid version? I thought the UUID version is only needed for generation.
7. True, my error, I will fix this.
8. This was suggested by @tsafin

About documentation - according to our implicit cast rules all functions that accepts STRING/VARBINARY should accept UUID instead of STRING/VARBINARY. Right now this is not exactly so, but we plan to fix this in current release.

About uuid(4) - I believe this is worth to be documented.

[pgulutzan]

Thanks for your quick attention.
Re the items that are "not exactly UUID problem" or "suggested by tsafin": I think these could be regarded as bugs, but I will not file separate issues about them.
Re 4: the idea that you can compare if they're indexed, but not compare if they're unindexed: I didn't expect that answer. If it's intentional behaviour, then why do I not get an error when I say
box.execute([[SELECT * FROM t NOT INDEXED WHERE s1 = (SELECT MAX(s1) FROM t);]])
?
Re 6: This is my error. There is no need for uuid version in a CAST.
About documentation: okay.

[tsafin]

tarantool> box.execute([[SELECT CAST('468aed62-6e44-4989-a48e-f85cf26413dc' AS VARBINARY);]])
result looks like ['468aed62-6e44-4989-a48e-f85cf26413dc']
...
tarantool> box.execute([[SELECT CAST(CAST('468aed62-6e44-4989-a48e-f85cf26413dc' AS UUID) AS VARBINARY);]])
result looks like [!!binary Yu2KRkRuiUmkjvhc8mQT3A==]

The result of the first SELECT looks good, the result of the second SELECT does not look good.

Quite the contrary - showing textual presentation for varbinary is not ok for 1st case, but 2nd result is ok. Let me clarify actions we proceed here:

1. In the 1st case we reinterpret 36 bytes of asciiz string as is, but attribute it blob type;
2. in the 2nd case though we proceed a little bit more: we parse string as uuid of type 4, form 128-bit binary data, and outer cast makes us reinterpret this 16 bytes as pure varbinary.

[pgulutzan]

Thanks, but now I am worried about what is "pure varbinary".
I apologize if I seem to take a long time to get to the point.

box.execute([[CREATE TABLE t (s1 UUID PRIMARY KEY, s2 UUID);]])
box.execute([[INSERT INTO t VALUES
              (CAST(X'000102030405060708090a0b0c0d0e0f' AS UUID),
               CAST('00010203-0405-0607-0809-0a0b0c0d0e0f' AS UUID));]])

The digits are the same but there will be no "duplicate key" error.
So with hexdump I look at the .xlog and see the encodings are:
d8 02 03 02 01 00 05 04 07 06 08 09 0a 0b 0c 0d 0e 0f
d8 02 00 01 02 03 04 05 06 07 08 09 0a 0b 0c 0d 0e 0f
Not the same, because in the first value there has been byte
swapping to little-endian (for example 06 07 has become
07 06), in the second case there has been no byte swapping.

Now, I suppose, you might reply "the RFC says byte swapping is okay".
But in that case:
(a) Why no byte swapping when input is STRING rather than VARBINARY?
(b) The top bits in all the bytes are 0s, and the algorithm didn't
change them, so I guess it trusted me to get them right.
So why not trust me to get the byte order right?
(c) @ImeevMA persuaded me that my point 6 regarding CAST was invalid.
Now I am not so sure, maybe version and variant should affect
how the casting is done.

By the way, I notice in https://docs.python.org/3/library/uuid.html
"For example, these expressions all yield the same UUID: ..."
UUID('{12345678-1234-5678-1234-567812345678}')
UUID('12345678123456781234567812345678')
..." which perhaps is a similar case. I don't know what the result is.

[tsafin]

Why no byte swapping when input is STRING rather than VARBINARY?

Good point! We may need some way to control endianness of a target data, and in this case it's essential for varbinary to uuid cast. How it might be implemented in the explicit cast expression? I see in MySQL there would be optional swap_flag in BIN_TO_UUID(binary_uuid, swap_flag) helper function https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_bin-to-uuid

(Unfortunately,) In our design we have decided not to use builtins for conversion here, but rather to use standard CAST(..) expression, and I don't know how to extend CAST( AS UUID) with swap order option, and still be SQL-ish?
@pgulutzan I do recollect you've suggested some way for extra CAST() argument, but I do not recollect exact form...

[pgulutzan]

Well, it seems then that this is another uuid problem:

Inconsistent switching to/from little-endian, as discussed above.
I do recall writing that I believed CAST should handle all
type conversions, but do not recall making an intelligent
suggestion about the format. I suppose that the version and
the variant might be different, and I suppose that usually
users will not care. Generally speaking the possibilities are:
() determine from whether source is string or varbinary
() add something in parentheses e.g. (4) or maybe even (4,1)
() UUID WITH BYTE SWAPPING
() UUID LITTLE ENDIAN
() UUID VERSION 4, VARIANT 1 WITH NO BYTE SWAPPING
I suppose it will need to be discussed as a separate issue later.

Some additional matters, with additional numbers:

ImeevMA wrote in tarantool/doc#2189
"A STRING or VARBINARY value can be explicitly converted to a UUID if it
conforms to the UUID standard."
I think that this only means that the format must be conformant,
if I have misunderstood then correct me please.

box.execute([[CREATE TABLE tsc (s1 SCALAR PRIMARY KEY);]])
box.execute([[INSERT INTO tsc VALUES (uuid());]])
box.execute([[SELECT * FROM tsc WHERE s1 > false;]])
box.execute([[SELECT * FROM tsc WHERE s1 > 1;]])
"> false" works okay, "> 1" causes an error.

box.execute([[create table nana (s1 int primary key);]])
box.execute([[insert into nana values (4);]])
box.execute([[SELECT uuid(s1) from nana;]])
This works okay.
Initially I had guessed that, for UUID(n),
n is supposed to be an integer literal.
But it seems to be an integer expression whose value
can be determined at runtime.
I'm not complaining, I'm just asking: is it deliberate?

For the documentation I have made a pull request
tarantool/doc#2201

[ImeevMA]

Hi! Thank you for your questions. I have some answers:
9) According to documentation https://www.tarantool.io/en/doc/latest/dev_guide/internals/msgpack_extensions/#the-uuid-type we have defined way to keep UUIDs. As for the CAST() - according to this https://en.wikipedia.org/wiki/Universally_unique_identifier version and variant of UUID can be determined from the UUID value. Also, I think this is not problem of SQL and should be solved for Tarantool as a whole. I mean this:

tarantool> a = box.execute([[SELECT CAST(CAST('9f9de041-89b6-40a8-a2e5-331217a5d4aa' AS UUID) AS VARBINARY);]]).rows[1][1]
---
...

tarantool> b = require('uuid').fromstr('9f9de041-89b6-40a8-a2e5-331217a5d4aa'):bin()
---
...

tarantool> a == b
---
- true
...

10. In general it should mean that UUID should follow rules set for version and variant, which could be defined from UUID value. However, in our case check is a lot simpler:

struct tt_uuid {
	uint32_t time_low;
	uint16_t time_mid;
	uint16_t time_hi_and_version;
	uint8_t clock_seq_hi_and_reserved;
	uint8_t clock_seq_low;
	uint8_t node[6];
};

inline int
tt_uuid_validate(struct tt_uuid *uu)
{
	/* Check variant (NCS, RFC4122, MSFT) */
	uint8_t n = uu->clock_seq_hi_and_reserved;
	if ((n & 0x80) != 0x00 && (n & 0xc0) != 0x80 &&	(n & 0xe0) != 0xc0)
		return 1;
	return 0;
}

So, if tt_uuid_validate() returns 0 than there is no problem with given value.

11. This problem valid not only for UUID:

tarantool> box.execute([[CREATE TABLE tsc (s1 SCALAR PRIMARY KEY);]])
---
- row_count: 1
...

tarantool> box.execute([[INSERT INTO tsc VALUES ('asd');]])
---
- row_count: 1
...

tarantool> box.execute([[SELECT * FROM tsc WHERE s1 > false;]])
---
- metadata:
  - name: S1
    type: scalar
  rows:
  - ['asd']
...

tarantool> box.execute([[SELECT * FROM tsc WHERE s1 > 1;]])
---
- null
- 'Type mismatch: can not convert asd to numeric'
...

Here is again problem with using index. I do not know when it will be fixed. I think this has something to do with planner.
12. Function UUID() accepts integer (in future it will be anything that could be implicitly cast to integer). Value of integer is checked during run-time. I never thought that argument should be limited to literal. Why?

[pgulutzan]

Re 10: So version can be 5 and variant can be 0, so I haven't misunderstood, good.
Re 12: (Initially I had a silly answer here, I edited this comment and erased it, I am not objecting.)

[pgulutzan]

Here is another case which I think is odd.
I tried with the latest branch and the imeevma/gh-6164-uuid-follow-ups branch.
13.
box.execute([[CREATE TABLE "tester" ("a" number PRIMARY KEY, "b" scalar) WITH ENGINE='vinyl';]])
box.execute([[INSERT INTO "tester" VALUES (1, true), (2,X'00'), (3, null), (4, 'x'), (5,-1);]])
box.execute([[INSERT INTO "tester" VALUES (6, false), (7,X'FF'), (8, null), (9, ''), (0, 1e44);]])
box.execute([[SELECT "a","b",typeof("b") FROM "tester" ORDER BY "b";]])
box.execute([[INSERT INTO "tester" VALUES (-1,uuid()), (10, uuid());]])
box.execute([[SELECT "a","b",typeof("b") FROM "tester" ORDER BY "b";]])
The first SELECT returns everything in the correct order.
The second SELECT -- after I added two UUID values -- does not.
I can fix it by creating an index on "b".
So maybe this is another example of "if and only if there is an index, such queries work".
But the oddness is: I do not see an ORDER BY problem unless there is a UUID.

[ImeevMA]

@pgulutzan thanks for your discoveries! Issue 13 has the same origin as issue 5. I tried to fix issue 5 locally, but it looks like it needs to be fixed more globally. Patches are already in work.