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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.37, 10.0.10
    • 5.5.38, 10.0.12
    • None

    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

          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.

          elenst Elena Stepanova added a comment - 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.

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

          elenst Elena Stepanova added a comment - 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

          sanja Oleksandr Byelkin added a comment - Sent for review
          bjohny Evgenii added a comment -

          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

          bjohny Evgenii added a comment - 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

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

          sanja Oleksandr Byelkin added a comment - 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);
          bjohny Evgenii added a comment -

          Dear Oleksandr,

          Thank you for prompt answer.

          Best.

          bjohny Evgenii added a comment - Dear Oleksandr, Thank you for prompt answer. Best.

          People

            sanja Oleksandr Byelkin
            sandeeppuppala sandeep
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.