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

FederatedX: error on multi-table UPDATE that uses a non-mergable VIEW

    XMLWordPrintable

Details

    • Unexpected results
    • Q4/2026 Server Maintenance

    Description

      Issue:

      ------
      Multi table update stmt isn't pushed down when view created w/o limit.

      Issue details:

      -------------
      case1) With variable ON , multi table update stmt gets pushed down , since Remote server doesn't have view v1 ,it throws "v1 doesn't exists" error //Expected behaviour

      case2) Same above case but View created w/o limit clause , multi table update succeds // Implies multi table update stmt hasn't pushed down to remote server engine , bug ??

      case1)

      -------------
      create federated table s1(a int);
      insert into s1 values (1),(2),(3);

      set global federated_pushdown=ON;
      drop view if exists v1;
      create view v1 as select * from s1 limit 2;
      UPDATE s1 JOIN v1 SET s1.a = 1 where v1.a <3;
      ERROR 1146 (42S02): Table 'm8.v1' doesn't exist

      Remote server general log:
      --------------------------
      Query update (`m8`.`s1` join `m8`.`v1`) set `m8`.`s1`.`a` = 1 where `v1`.`a` < 3

      MariaDB [m8]> explain UPDATE s1 JOIN v1 SET s1.a = 1 where v1.a <3;
      +------+---------------+-------+------+---------------+------+---------+------+------+-------+
      | id   | select_type   | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
      +------+---------------+-------+------+---------------+------+---------+------+------+-------+
      |    1 | PUSHED UPDATE | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL  |
      +------+---------------+-------+------+---------------+------+---------+------+------+-------+
      

      case2)

      --------------
      insert into s1 values (1),(2),(3);

      set global federated_pushdown=ON;
      drop view if exists v1;
      create view v1 as select * from s1;
      UPDATE s1 JOIN v1 SET s1.a = 1 where v1.a <3; //Succeeds

      Remote server general log:
      -------------------------
      Query UPDATE `s1` SET `a` = 1 WHERE `a` = 2 LIMIT 1
      Query UPDATE `s1` SET `a` = 1 WHERE `a` = 3 LIMIT 1

      Series of update..LIMIT1 stmt , so it's confirmed ,stmt isn't pushed down to remote server.

      MariaDB [m8]> explain UPDATE s1 JOIN v1 SET s1.a = 1 where v1.a <3;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | s1    | ALL  | NULL          | NULL | NULL    | NULL | 3    |             |
      |    1 | SIMPLE      | s1    | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      

      Version used:

      ----------------
      Branch : 13.2-MDEV-39226-direct-multi_table-update-delete
      commit: 5992b3d00bec6bdaee4c7dcd3efd9bb8e39e4911
      Version : 13.1.0

      How to repeat:

      ----------------
      Slave:

      create table s1( a int);
      

      Master: Start server with federated engine enabled

      create table s1(a int)  ENGINE="FEDERATED" CONNECTION='mysql://u3:pass1@127.0.0.1:3306/m8/s1';
      insert into s1 values (1),(2),(3);
       
      set global federated_pushdown=ON;
      drop view if exists v1;
      create view v1 as select * from s1;
      UPDATE s1 JOIN v1 SET s1.a = 1 where v1.a <3; //Notice: Update succeeds i.e it isn't pushed down
      

      Attachments

        Issue Links

          Activity

            People

              bsrikanth Srikanth Bondalapati
              mariadb-pavithrapandith Pavithra Pandith
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 1.9d
                  1.9d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 1d 6.5h Time Not Required
                  1d 6.5h

                  Git Integration

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