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

Different query result between 10.4.10 and 10.4.11 from materialized subquery with split_materialized=ON

    XMLWordPrintable

    Details

      Description

      Following query get a different result in 10.4.11 from 10.4.10:

      SELECT DISTINCT
      egw_pm_projects.*,
      egw_pm_projects.pm_id AS pm_id,
      BIT_OR(role_acl) AS role_acl
      FROM
      egw_pm_projects
      LEFT JOIN egw_pm_members ON(
      egw_pm_projects.pm_id = egw_pm_members.pm_id AND egw_pm_members.member_uid IN(5, -2, -189, -1, -11)
      )
      LEFT JOIN egw_pm_roles ON egw_pm_members.role_id = egw_pm_roles.role_id
      WHERE
      egw_pm_projects.pm_id IN(
      SELECT
      *
      FROM
      (
      SELECT DISTINCT
      egw_pm_projects.pm_id
      FROM
      egw_pm_projects
      LEFT JOIN egw_pm_members ON(
      egw_pm_projects.pm_id = egw_pm_members.pm_id AND egw_pm_members.member_uid IN(5, -2, -189, -1, -11)
      )
      LEFT JOIN egw_pm_roles ON egw_pm_members.role_id = egw_pm_roles.role_id
      LEFT JOIN egw_pm_extra ON egw_pm_projects.pm_id = egw_pm_extra.pm_id
      WHERE
      pm_status = 'active' AND(
      pm_access = 'anonym' OR pm_access = 'public' AND pm_creator IN(-2,5,6,12,14,18,27,32,70,73,76,101,164,-1,7,13,16,44,62,64,68,69,81,89,116,122,146,169,183,203,381) OR pm_access = 'private' AND pm_creator IN(5) OR role_acl != 0
      ) AND(
      (
      (
      (
      CONCAT(
      CAST(
      COALESCE(egw_pm_projects.pm_number, '') AS CHAR
      ),
      CAST(
      COALESCE(egw_pm_projects.pm_title, '') AS CHAR
      ),
      CAST(
      COALESCE(
      egw_pm_projects.pm_description,
      ''
      ) AS CHAR
      ),
      CAST(
      COALESCE(egw_pm_projects.pm_access, '') AS CHAR
      ),
      CAST(
      COALESCE(egw_pm_projects.pm_status, '') AS CHAR
      ),
      CAST(
      COALESCE(
      egw_pm_projects.pm_accounting_type,
      ''
      ) AS CHAR
      )
      ) LIKE '%test%' OR egw_pm_projects.pm_id IN(
      SELECT
      pm_id
      FROM
      egw_pm_extra
      WHERE
      pm_extra_value LIKE '%test%' AND pm_extra_name IN(
      'comments',
      'project_intern',
      'staatsanwalt',
      'tracker',
      'todo',
      'salesoffer',
      'select-account',
      'Abrechnungsinfo'
      )
      )
      )
      )
      )
      )
      GROUP BY
      egw_pm_projects.pm_id
      ORDER BY
      pm_created
      DESC
      LIMIT 0,
      1000
      ) AS something
      )
      GROUP BY
      egw_pm_projects.pm_id
      ORDER BY
      pm_created
      DESC
      

      When disabling split_materialized optimizer switch the query works correctly.

      Before disabling the switch the explain showed :
      10.4.10(good)

      +------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
      | id   | select_type  | table           | type   | possible_keys | key     | key_len | ref                                    | rows | Extra                                           |
      +------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
      |    1 | PRIMARY      | <subquery2>     | ALL    | distinct_key  | NULL    | NULL    | NULL                                   | 235  | Using temporary; Using filesort                 |
      |    1 | PRIMARY      | egw_pm_projects | eq_ref | PRIMARY       | PRIMARY | 4       | something.pm_id                        | 1    |                                                 |
      |    1 | PRIMARY      | egw_pm_members  | ref    | PRIMARY       | PRIMARY | 4       | something.pm_id                        | 1    | Using where                                     |
      |    1 | PRIMARY      | egw_pm_roles    | ALL    | PRIMARY       | NULL    | NULL    | NULL                                   | 6    | Using where; Using join buffer (flat, BNL join) |
      |    2 | MATERIALIZED | <derived3>      | ALL    | NULL          | NULL    | NULL    | NULL                                   | 235  |                                                 |
      |    3 | DERIVED      | egw_pm_projects | ALL    | PRIMARY       | NULL    | NULL    | NULL                                   | 235  | Using where; Using temporary; Using filesort    |
      |    3 | DERIVED      | egw_pm_members  | ref    | PRIMARY       | PRIMARY | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using where                                     |
      |    3 | DERIVED      | egw_pm_roles    | eq_ref | PRIMARY       | PRIMARY | 4       | egw_pm_projects.egw_pm_members.role_id | 1    | Using where                                     |
      |    3 | DERIVED      | egw_pm_extra    | ref    | PRIMARY       | PRIMARY | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using index                                     |
      |    4 | MATERIALIZED | egw_pm_extra    | ALL    | PRIMARY       | NULL    | NULL    | NULL                                   | 14   | Using where                                     |
      +------+--------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+-------------------------------------------------+
      

      10.4.11 (bad)

      +------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
      | id   | select_type        | table           | type           | possible_keys | key          | key_len | ref                                    | rows | Extra                           |
      +------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
      |    1 | PRIMARY            | egw_pm_projects | index          | PRIMARY       | PRIMARY      | 4       | NULL                                   | 235  | Using temporary; Using filesort |
      |    1 | PRIMARY            | <subquery2>     | eq_ref         | distinct_key  | distinct_key | 4       | func                                   | 1    |                                 |
      |    1 | PRIMARY            | egw_pm_members  | ref            | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using where                     |
      |    1 | PRIMARY            | egw_pm_roles    | eq_ref         | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_members.role_id | 1    | Using where                     |
      |    2 | MATERIALIZED       | <derived3>      | ALL            | NULL          | NULL         | NULL    | NULL                                   | 235  |                                 |
      |    3 | LATERAL DERIVED    | egw_pm_projects | eq_ref         | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using where                     |
      |    3 | LATERAL DERIVED    | egw_pm_members  | ref            | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using where                     |
      |    3 | LATERAL DERIVED    | egw_pm_roles    | eq_ref         | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_members.role_id | 1    | Using where                     |
      |    3 | LATERAL DERIVED    | egw_pm_extra    | ref            | PRIMARY       | PRIMARY      | 4       | egw_pm_projects.egw_pm_projects.pm_id  | 1    | Using index                     |
      |    4 | DEPENDENT SUBQUERY | egw_pm_extra    | index_subquery | PRIMARY       | PRIMARY      | 4       | func                                   | 1    | Using where                     |
      +------+--------------------+-----------------+----------------+---------------+--------------+---------+----------------------------------------+------+---------------------------------+
      

        Attachments

          Activity

            People

            Assignee:
            kjoiner Kyle Joiner
            Reporter:
            kjoiner Kyle Joiner
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated: