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

Wrong query results with optimizer_switch="split_materialized=on"

    XMLWordPrintable

Details

    Description

      Wrong query results when optimizer_switch="split_materialized=on" optimization is enabled.
      Tested with MariaDB 10.3.22, 10.4.10, 10.4.11, 10.4.12.
      Works correctly with 10.2.31 (no optimization)

      Example

      MySQL:

      CREATE DATABASE test;
      CREATE TABLE test.a (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL);
      CREATE TABLE test.b (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, related_object_id int(11) NOT NULL, is_default int(1) NOT NULL DEFAULT 0, KEY related_object_id (related_object_id) );
      

      Shell:

      for i in {1..1000}; do echo "INSERT INTO test.a(name) VALUES('name$i');"|mysql;done
      for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,0);"|mysql;done
      for i in {1..1000}; do echo "INSERT INTO test.b(related_object_id, is_default) VALUES($i,1);"|mysql;done
      

      Query:

      SELECT
         a.id AS id,
         a.name AS name
      FROM
         a AS a 
      WHERE
         a.id IN 
         (
            SELECT
               subp.id 
            FROM
               (
                  SELECT
                     a.* 
                  FROM
                     a a 
                     LEFT JOIN
                        b AS b
                        ON (a.id = b.related_object_id AND b.is_default = 1) 
                  GROUP BY
                     a.id 
               )
               AS subp 
         )
      
      

      Result:

      +----+-------+
      | id     | name  |
      +----+-------+
      |  1     | name1 |
      +----+-------+
      1 row in set (0.00 sec)
      

      Query plan:

      MariaDB [test]> explain extended SELECT
          ->    a.id AS id,
          ->    a.name AS name
          -> FROM
          ->    a AS a 
          -> WHERE
          ->    a.id IN 
          ->    (
          ->       SELECT
          ->          subp.id 
          ->       FROM
          ->          (
          ->             SELECT
          ->                a.* 
          ->             FROM
          ->                a a 
          ->                LEFT JOIN
          ->                   b AS b
          ->                   ON (a.id = b.related_object_id AND b.is_default = 1) 
          ->             GROUP BY
          ->                a.id 
          ->          )
          ->          AS subp 
          ->    );
      +------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      | id   | select_type     | table       | type   | possible_keys     | key               | key_len | ref       | rows | filtered | Extra       |
      +------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      |    1 | PRIMARY         | a           | ALL    | PRIMARY           | NULL              | NULL    | NULL      | 1000 |   100.00 |             |
      |    1 | PRIMARY         | <subquery2> | eq_ref | distinct_key      | distinct_key      | 4       | func      | 1    |   100.00 |             |
      |    2 | MATERIALIZED    | <derived3>  | ALL    | NULL              | NULL              | NULL    | NULL      | 1000 |   100.00 |             |
      |    3 | LATERAL DERIVED | a           | eq_ref | PRIMARY           | PRIMARY           | 4       | test.a.id | 1    |   100.00 |             |
      |    3 | LATERAL DERIVED | b           | ref    | related_object_id | related_object_id | 4       | test.a.id | 1    |   100.00 | Using where |
      +------+-----------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      5 rows in set, 1 warning (0.00 sec)
      
      

      Turn split_materialized optimization

      SET optimizer_switch="split_materialized=off";
      

      Result:

      |  991 | name991  |
      |  992 | name992  |
      |  993 | name993  |
      |  994 | name994  |
      |  995 | name995  |
      |  996 | name996  |
      |  997 | name997  |
      |  998 | name998  |
      |  999 | name999  |
      | 1000 | name1000 |
      +------+----------+
      1000 rows in set (0.00 sec)
      

      Query plan:

      MariaDB [test]> explain extended SELECT
          ->    a.id AS id,
          ->    a.name AS name
          -> FROM
          ->    a AS a 
          -> WHERE
          ->    a.id IN 
          ->    (
          ->       SELECT
          ->          subp.id 
          ->       FROM
          ->          (
          ->             SELECT
          ->                a.* 
          ->             FROM
          ->                a a 
          ->                LEFT JOIN
          ->                   b AS b
          ->                   ON (a.id = b.related_object_id AND b.is_default = 1) 
          ->             GROUP BY
          ->                a.id 
          ->          )
          ->          AS subp 
          ->    );
      +------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      | id   | select_type  | table       | type   | possible_keys     | key               | key_len | ref       | rows | filtered | Extra       |
      +------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      |    1 | PRIMARY      | a           | ALL    | PRIMARY           | NULL              | NULL    | NULL      | 1000 |   100.00 |             |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key      | distinct_key      | 4       | func      | 1    |   100.00 |             |
      |    2 | MATERIALIZED | <derived3>  | ALL    | NULL              | NULL              | NULL    | NULL      | 1000 |   100.00 |             |
      |    3 | DERIVED      | a           | index  | NULL              | PRIMARY           | 4       | NULL      | 1000 |   100.00 |             |
      |    3 | DERIVED      | b           | ref    | related_object_id | related_object_id | 4       | test.a.id | 1    |   100.00 | Using where |
      +------+--------------+-------------+--------+-------------------+-------------------+---------+-----------+------+----------+-------------+
      5 rows in set, 1 warning (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              sergeij Sergei Jeldosev
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.