Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28491

Uuid. "UPDATE/DELETE" not working "WHERE id IN (SELECT id FROM ..)"

    XMLWordPrintable

    Details

      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'
      );
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              maximmandrik Maxim Mandrik
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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