Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reproducible on MariaDB 5.3-10.0 with subqueries, and everywhere (MySQL 5.1-5.7, MariaDB 5.1-10.0) with MERGE views.
Test case with a subquery:
create table accounts (balance float, accountId varchar(64), primary key (accountId)) engine=MyISAM;
insert into accounts (accountId,balance) values
('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0);
update accounts set balance=(select sum(balance)
from (SELECT balance FROM accounts where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR';
1093: Table 'accounts' is specified twice, both as a target for 'UPDATE' and as a separate source for data
Test case with a MERGE view:
create table accounts (balance float, accountId varchar(64), primary key (accountId)) engine=MyISAM;
insert into accounts (accountId,balance) values
('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0);
create algorithm=MERGE view v as SELECT balance FROM accounts where accountId like 'dealer%';
update accounts set balance=(select sum(balance) from v AS copied) where accountId = 'OPERATOR';
1443: The definition of table 'copied' prevents operation UPDATE on table 'accounts'.
Sent for review
Hi,
Thanks for the report.
As a workaround, please try to set
optimizer_switch='derived_merge=off'
either in the session where you run the upgrade, or globally in your cnf file.