Date: 2020-07-22 16:25:08 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.37.7 (Jun2020)

Last updated: 2020-07-27 09:30:11 +0200

Comment 27931

Date: 2020-07-22 16:25:08 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:78.0) Gecko/20100101 Firefox/78.0
Build Identifier:

sys.isauuid('e31960fb-dc8b-452d-ab30-b342723e7565XYZ') returns true while it should return false.
The argument string contains invalid end characters XYZ.
Also the provided string is more than 36 characters long and uuid strings are 36 characters.
Apparently sys.isauuid() only looks at the first 36 (or 32 when no hyphens are used) characters and ignores any successive characters, which is not correct.

Also conversion of those invalid uuid strings to uuid objects succeed, while they should return an error: Invalid uuid string value 'the uuid string'

Reproducible: Always

Steps to Reproduce:

select sys.isauuid('e31960fb-dc8b-452d-ab30-b342723e7565XYZ') as fals;
select sys.isauuid('e31960fbdc8b452dab30b342723e7565XYZ') as fals;
select sys.isauuid('00000000-0000-0000-0000-000000000000XYZ') as fals;
select sys.isauuid('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFFXYZ') as fals;
select sys.isauuid('00000000000000000000000000000000XYZ') as fals;
select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFXYZ') as fals;
select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!') as fals;

select cast('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid) as uuid_val;
select convert('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!', uuid) as uuid_val;
select uuid'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid_val;

Actual Results:

sql>select sys.isauuid('e31960fb-dc8b-452d-ab30-b342723e7565XYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('e31960fbdc8b452dab30b342723e7565XYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('00000000-0000-0000-0000-000000000000XYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFFXYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('00000000000000000000000000000000XYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFXYZ') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!') as fals;
+-------+
| fals |
+=======+
| true |
+-------+
1 tuple
sql>
sql>select cast('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid) as uuid_val;
+--------------------------------------+
| uuid_val |
+======================================+
| ffffffff-ffff-ffff-ffff-ffffffffffff |
+--------------------------------------+
1 tuple
sql>select convert('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!', uuid) as uuid_val;
+--------------------------------------+
| uuid_val |
+======================================+
| ffffffff-ffff-ffff-ffff-ffffffffffff |
+--------------------------------------+
1 tuple
sql>select uuid'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid_val;
+--------------------------------------+
| uuid_val |
+======================================+
| ffffffff-ffff-ffff-ffff-ffffffffffff |
+--------------------------------------+
1 tuple
sql>

Expected Results:

sql>select sys.isauuid('e31960fb-dc8b-452d-ab30-b342723e7565XYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('e31960fbdc8b452dab30b342723e7565XYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('00000000-0000-0000-0000-000000000000XYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFFXYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('00000000000000000000000000000000XYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFXYZ') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>select sys.isauuid('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!') as fals;
+-------+
| fals |
+=======+
| false |
+-------+
1 tuple
sql>
sql>select cast('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid) as uuid_val;
Invalid UUID string value 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!'
sql>select convert('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!', uuid) as uuid_val;
Invalid UUID string value 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!'
sql>select uuid'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!' as uuid_val;
Invalid UUID string value 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF %$@!'
sql>

Comment 27934

Date: 2020-07-23 09:07:27 +0200
From: @sjoerdmullender

Test and fix in changesets 445de75f570a and 510d6756bd0b