[MDEV-28491] Uuid. "UPDATE/DELETE" not working "WHERE id IN (SELECT id FROM ..)" - Jira

XML

Word

Printable

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'

);

is duplicated by

MDEV-28826 UUID field type + WHERE EXISTS doesn't work unless CASTed

relates to

MDEV-4958 Adding datatype UUID

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.