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

wrong select result because of derived_merge=on (default setting)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.5.15, 10.6.7
    • Fix Version/s: None
    • Labels:
    • Environment:
      Linux servers, Debian Testing VM, Opensuse 15.3 VM, probably irrelevant

      Description

      Using a select from (select) gives wrong results when there is a calculated variable involved.

      create table user(id int auto_increment, lname varchar(10), fname varchar(10), sex varchar(1) default NULL, death int unsigned default NULL, key id(id));
       
      create table period_member(period_id int, user_id int references user(id));
       
      -- for example write result to a table
      create table team_member(team_id int, version int, user_id int references user(id) ON DELETE NO ACTION, pos int not NULL, unique key (team_id,version,user_id), unique key(team_id, version, pos));
       
      -- some demo data
      insert into user(lname, fname, sex) values('BBBBB','Daniel','m'),('CCC','Annika','w'),('CC','Tom','m'),('AAAA','Edward','m'),('AAA','Bernd','m');
      insert into user(lname,fname,sex) values('LLL','Franziska','w'),('C','Emilia','w'),('A','Arne','m'),('BBBB','Chris','m'),('AAA','Arne','m');
      insert into user(lname,fname,sex) values('LLL','Daniel','m'),('CC','Emilia','w'),('AA','Arne','m'),('B','Chris','m'),('AAA','Bruno','m');
      insert into user(lname,fname,sex) values('LL','Danilo','m'),('C','Emil','m'),('AAA','Beate','m'),('BBB','Petra','w'),('A','Claire','w');
       
      insert into period_member values(1,2),(1,4),(1,5),(1,6),(1,7),(1,8);
      insert into period_member values(2,4),(2,5),(2,7),(2,8),(2,1),(2,3),(2,9),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18);
       
       
      -- mariadb wrong, mysql ok
      set @ps:=0;
      select 2,id,lname,fname,(@pos:=@pos+1)
      from (
              select id,lname,fname from user
              join period_member on user.id=period_member.user_id
              where period_member.period_id=2
              and sex='m'
              order by lname,fname
      ) sub
      order by fname,lname;
       
      -- mariadb wrong, mysql ok
      select 2,id,lname,fname,(@pos:=@pos+1)
      from (
              select id,lname,fname from user
              join period_member on user.id=period_member.user_id
              where period_member.period_id=2
              and sex='m'
              order by lname,fname
      ) sub,
      (select @pos:=0) r
      order by fname,lname;
      

      Adding a GROUP BY workarounds the problem, but that appears like an ugly hack to me, dunno.

      -- using 'group by' workaround
      -- mariadb ok, mysql ok
      select 2,id,lname,fname,(@pos:=@pos+1)
      from (
              select id,lname,fname from user
              join period_member on user.id=period_member.user_id
              where period_member.period_id=2
              and sex='m'
              group by id
              order by lname,fname
      ) sub,
      (select @pos:=0) r
      order by fname, lname;
      

      Explicitly turning off derived_merge optimizer_switch hack:

      -- using 'derived_merge=off' hack
      -- mariadb ok, mysql ok
      set optimizer_switch='derived_merge=off';
      select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub, (select @pos:=0) r order by lname, fname;
      

      I stumbled over it when trying to generate prefilled list versions using insert-select constructs and it worked in mysql (5.1 no problem and mysql8.0 too no problem) but not mariadb (10.5.15, 10.6.8 and older versions)

      set @pos:=0; set optimizer_switch='derived_merge=on';  insert into team_member select 1,1,id,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
      set @pos:=0; set optimizer_switch='derived_merge=off'; insert into team_member select 1,2,id,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
       
      select * from team_member left join user on user.id=user_id where version=1 order by pos;
      select * from team_member left join user on user.id=user_id where version=2 order by pos;
      

      Using

      SHOW WARNINGS;
      

      and

      EXPLAIN EXTENDED 
      

      shows the problem:

      set @pos:=0; set optimizer_switch='derived_merge=on'; show warnings; explain extended select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and sex='m' order by lname,fname) sub order by lname, fname;
      Query OK, 0 rows affected (0.000 sec)
       
      Query OK, 0 rows affected (0.001 sec)
       
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                    |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 2 AS `2`,`test`.`user`.`id` AS `id`,`test`.`user`.`lname` AS `lname`,`test`.`user`.`fname` AS `fname`,@pos:=@`pos` + 1 AS `(@pos:=@pos+1)` from `test`.`user` join `test`.`period_member` where `test`.`period_member`.`period_id` = 2 and `test`.`period_member`.`user_id` = `test`.`user`.`id` and `test`.`user`.`sex` = 'm' order by `test`.`user`.`lname`,`test`.`user`.`fname` |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
      | id   | select_type | table         | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra                                        |
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
      |    1 | SIMPLE      | user          | ALL  | id            | NULL    | NULL    | NULL         | 20   |   100.00 | Using where; Using temporary; Using filesort |
      |    1 | SIMPLE      | period_member | ref  | user_id       | user_id | 5       | test.user.id | 1    |   100.00 | Using where                                  |
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+----------------------------------------------+
      2 rows in set, 1 warning (0.001 sec)
      }
      

      set @pos:=0; set optimizer_switch='derived_merge=off'; show warnings; explain extended select 2,id,lname,fname,(@pos:=@pos+1) from (select id,lname,fname from user join period_member on user.id=period_member.user_id where period_member.period_id=2 and
      sex='m' order by lname,fname) sub order by lname, fname;
      Query OK, 0 rows affected (0.001 sec)
       
      Query OK, 0 rows affected (0.000 sec)
       
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                    |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 2 AS `2`,`test`.`user`.`id` AS `id`,`test`.`user`.`lname` AS `lname`,`test`.`user`.`fname` AS `fname`,@pos:=@`pos` + 1 AS `(@pos:=@pos+1)` from `test`.`user` join `test`.`period_member` where `test`.`period_member`.`period_id` = 2 and `test`.`period_member`.`user_id` = `test`.`user`.`id` and `test`.`user`.`sex` = 'm' order by `test`.`user`.`lname`,`test`.`user`.`fname` |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
      | id   | select_type | table         | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra                           |
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
      |    1 | PRIMARY     | <derived2>    | ALL  | NULL          | NULL    | NULL    | NULL         | 20   |   100.00 | Using temporary; Using filesort |
      |    2 | DERIVED     | user          | ALL  | id            | NULL    | NULL    | NULL         | 20   |   100.00 | Using where; Using filesort     |
      |    2 | DERIVED     | period_member | ref  | user_id       | user_id | 5       | test.user.id | 1    |   100.00 | Using where                     |
      +------+-------------+---------------+------+---------------+---------+---------+--------------+------+----------+---------------------------------+
      3 rows in set, 1 warning (0.002 sec)
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            peterdd peterdd
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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