[MDEV-6163] Error while executing an update query that has the same table in a sub-query Created: 2014-04-24  Updated: 2015-11-03  Resolved: 2014-06-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.37, 10.0.10
Fix Version/s: 5.5.38, 10.0.12

Type: Bug Priority: Major
Reporter: sandeep Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: upstream


 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

 Comments   
Comment by Elena Stepanova [ 2014-04-25 ]

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.

Comment by Elena Stepanova [ 2014-04-25 ]

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'.

Comment by Oleksandr Byelkin [ 2014-05-08 ]

Sent for review

Comment by Evgenii [ 2015-11-03 ]

Dear All,

This issue is not resolved. It was checked on MariaDB versions: 10.0.19 and 10.1.8.
Could you please let me know is there a solution to fix this issue.

Below you can find the script for recreating of situation:

create table z_temp_tbl(tbl_id int not null auto_increment primary key,
 
txt_val varchar(20), stat int not null);
 
 
 
insert into z_temp_tbl (txt_val, stat) values 
('v1',0),('v2',1),('v3',4);
 
 
 
update z_temp_tbl set txt_val='t' where tbl_id = (select max(tbl_id) from z_temp_tbl);

Error:

ErrorCode: -2147467259, Number: 1093
 
ErrorMessage: Table 'z_temp_tbl' is specified twice, both as a target for 'UPDATE' and as a separate source for data

The values of optimizer_switch:

select @@optimizer_switch;
 

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

Best,
Evgenii

Comment by Oleksandr Byelkin [ 2015-11-03 ]

You case is quite different. Original test case was about table of subquery in the FROM clause (derived table).

Your problem is not solved in MySQL nor mariaDB.

If you really need it please fill separate bug report/feature request. But I doubts that it will be fixed soon in both servers.

Workaround is to put z_temp_tbl in materialized view or derived table to force it materialization before query execution:

update z_temp_tbl set txt_val='t' where tbl_id = (select * from (select max(tbl_id) from z_temp_tbl) as max);

Comment by Evgenii [ 2015-11-03 ]

Dear Oleksandr,

Thank you for prompt answer.

Best.

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