[MDEV-28491] Uuid. "UPDATE/DELETE" not working "WHERE id IN (SELECT id FROM ..)" Created: 2022-05-06  Updated: 2022-07-03  Resolved: 2022-06-03

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Data Manipulation - Update
Affects Version/s: 10.7.3, 10.8.2, 10.7, 10.8
Fix Version/s: 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Maxim Mandrik Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian GNU/Linux 10

Issue Links:
Duplicate
is duplicated by MDEV-28826 UUID field type + WHERE EXISTS doesn'... Closed
Relates
relates to MDEV-4958 Adding datatype UUID Closed

 Description   

Prepare:

CREATE TABLE companies (id uuid, name varchar(10));
 
INSERT INTO companies (id) values ('7bc95b06-cc6c-11ec-96c5-0242ac130002');
 
 
 
CREATE TABLE divisions (company_id uuid);
 
INSERT INTO divisions (company_id) values ('7bc95b06-cc6c-11ec-96c5-0242ac130002');

Strange results:

// 1 row retrieved

SELECT *
 
FROM companies
 
WHERE id IN (
 
    SELECT company_id FROM divisions
 
);

// not row affected

UPDATE companies
 
SET name = 'value'
 
WHERE id IN (
 
    SELECT company_id FROM divisions
 
);

// not row affected

DELETE FROM companies
 
WHERE id IN (
 
    SELECT company_id FROM divisions
 
);

// 1 row affected

UPDATE companies
 
SET name = 'value'
 
WHERE CAST(id as varchar(100)) IN (
 
    SELECT company_id FROM divisions
 
);

// 1 row affected

DELETE FROM companies
 
WHERE CAST(id as varchar(100)) IN (
 
    SELECT company_id FROM divisions
 
);

// 1 row affected

UPDATE companies
 
SET column_a = 'value'
 
WHERE id IN (
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
);

// 1 row affected

DELETE FROM companies
 
WHERE id IN (
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
);

// not row affected

UPDATE companies
 
SET column_a = 'value'
 
WHERE id IN (
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
    UNION
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
);

// not row affected

DELETE FROM companies
 
WHERE id IN (
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
    UNION
 
    SELECT '7bc95b06-cc6c-11ec-96c5-0242ac130002'
 
);



 Comments   
Comment by Alexander Barkov [ 2022-06-03 ]

The same problem is repeatable with INET6:

CREATE OR REPLACE TABLE t1 (id INET6, name varchar(10));
 
INSERT INTO t1 (id) values ('00::01');
 
 
 
CREATE OR REPLACE TABLE t2 (company_id INET6);
 
INSERT INTO t2 (company_id) values ('00::01');
 
 
 
SELECT * FROM t1 WHERE id IN (SELECT company_id FROM t2);


+------+------+
 
| id   | name |
 
+------+------+
 
| ::1  | NULL |
 
+------+------+


UPDATE t1 SET name = 'value' WHERE id IN (SELECT company_id FROM t2);


Query OK, 0 rows affected (0.001 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0

Comment by Alexander Barkov [ 2022-06-03 ]

Pushed a fix into 10.7.5. Now need to backport to 10.5.

Comment by Alexander Barkov [ 2022-06-03 ]

Pushed a 10.5 specific patch.

This code has diverged between 10.5 and 10.7, therefore two separate patches were needed.

Comment by Gri [ 2022-06-21 ]

When will it be released?

10.7 branch considered to be a production-ready and one can rely on UUID field type as production-ready feature, but this bug is not just a Critical-level bug, it is a Blocker that generally eliminates possibility of using a UUID at all and ruins end-user's applications that rely on this feature.
Subqueries are extensively used by any ORM and it is currently unusable at all.

This bugfix deserves a special release of the 10.7 branch.

Comment by Sergei Golubchik [ 2022-07-03 ]

The release schedule is directly on https://jira.mariadb.org/ — new release is planned for the end of July

Generated at Thu Feb 08 10:01:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.