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

Error while executing an update query that has the same table in a sub-query

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.37, 10.0.10
    • Fix Version/s: 5.5.38, 10.0.12
    • Component/s: None
    • Labels:

      Description

      The following update query gives error 1093 in MariaDB 10.0.10 but the same query works fine with MySQL 5.1.53-community version.

      Query:

      update accounts.accounts set balance=(select -1*sum(balance) from (SELECT balance FROM accounts.accounts where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR';

      Result:

      ERROR 1093 (HY000): Table 'accounts' is specified twice, both as a target for 'UPDATE' and as a separate source for data

      Sample table:

      select * from accounts.accounts;
      +---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+
      | accountTypeId | accountId      | accountName | currency | balance       | creditLimit | version | status | password | createDate          | expiryDate          | extraFields |
      +---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+
      | RESELLER      | dealer-1       |             | CFA      |  199354.00000 |     0.00000 |       8 | Active | NULL     | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL        |
      | RESELLER      | dealer-2       |             | CFA      |       0.00000 |     0.00000 |       0 | Active | NULL     | 2014-04-15 04:56:15 | 2014-04-15 04:56:15 | NULL        |
      | RESELLER      | dealer-3       |             | CFA      |       0.00000 |     0.00000 |       0 | Active | NULL     | 2014-04-15 04:56:16 | 2014-04-15 04:56:16 | NULL        |
      | RESELLER      | dealer-5       | dealer-5    | CFA      |       0.00000 |     0.00000 |       0 | Active | NULL     | 2014-04-16 04:41:50 | 2014-04-16 04:41:50 | NULL        |
      | RESELLER      | FINANCE        |             | CFA      | -200000.00000 |        NULL |       2 | Active | NULL     | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL        |
      | RESELLER      | OPERATOR       |             | CFA      |       0.00000 |        NULL |       0 | Active | NULL     | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL        |
      +---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+

      It appears that same table inside a sub-query is not supported in MariaDB version 10.0.10

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              sandeeppuppala sandeep
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: