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

Incorrect Join Execution When Controlling Join Buffer Size

Details

    Description

      CREATE TABLE `t1` (
      		 
        `c0` int(10) unsigned NOT NULL,
      		 
        PRIMARY KEY (`c0`),
      		 
        KEY `ic3` (`c0`) USING BTREE
      		 
      );
      		 
      INSERT INTO `t1` VALUES (1332945389);
      		 
       
      		 
      CREATE TABLE `t2` (
      		 
        `c0` int(10) unsigned NOT NULL,
      		 
        PRIMARY KEY (`c0`)
      		 
      );
      		 
      INSERT INTO `t2` VALUES (1180244875), (1951338178);
      		 
       
      		 
      mysql> SET SESSION join_buffer_size = 5250229460064350213;
      		 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      		 
       
      		 
      mysql> SET SESSION join_cache_level = 4;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      mysql> SELECT t2.c0 FROM t2  LEFT OUTER JOIN t1 ON t1.c0 = t2.c0  WHERE t1.c0;
      		 
      Empty set (0.00 sec)
      		 
       
      		 
      mysql> SET optimizer_switch='optimize_join_buffer_size=off';
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      mysql> SELECT t2.c0 FROM t2  LEFT OUTER JOIN t1 ON t1.c0 = t2.c0  WHERE t1.c0;
      		 
      +------------+
      		 
      | c0         |
      		 
      +------------+
      		 
      | 1180244875 |
      		 
      | 1951338178 |
      		 
      +------------+
      		 
      2 rows in set (0.00 sec)

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23949 Assertion `0' failed in JOIN_CACHE::get_match_flag_by_pos

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            sugar Xiu Tang created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            Description CREATE TABLE `t1` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`),
              KEY `ic3` (`c0`) USING BTREE
            );
            INSERT INTO `t1` VALUES (1332945389);

            CREATE TABLE `t2` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`)
            );
            INSERT INTO `t2` VALUES (1180244875), (1951338178);

            mysql> SET SESSION join_buffer_size = 5250229460064350213;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

            mysql> SET SESSION join_cache_level = 4;
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            Empty set (0.00 sec)

            mysql> SET optimizer_switch='optimize_join_buffer_size=off';
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            +------------+
            | c0 |
            +------------+
            | 1180244875 |
            | 1951338178 |
            +------------+
            2 rows in set (0.00 sec)
            {noformat}
            CREATE TABLE `t1` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`),
              KEY `ic3` (`c0`) USING BTREE
            );
            INSERT INTO `t1` VALUES (1332945389);

            CREATE TABLE `t2` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`)
            );
            INSERT INTO `t2` VALUES (1180244875), (1951338178);

            mysql> SET SESSION join_buffer_size = 5250229460064350213;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

            mysql> SET SESSION join_cache_level = 4;
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            Empty set (0.00 sec)

            mysql> SET optimizer_switch='optimize_join_buffer_size=off';
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            +------------+
            | c0 |
            +------------+
            | 1180244875 |
            | 1951338178 |
            +------------+
            2 rows in set (0.00 sec)
            {noformat}
            alice Alice Sherepa made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            alice Alice Sherepa made changes -
            Assignee Sergei Petrunia [ psergey ]
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa added a comment -

            Thanks! I repeated on 10.2 53b580a91c12e92726-10.8 as described:

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb;
            		 
            INSERT INTO t1 VALUES (1332945389);
            		 
             
            CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb;
            		 
            INSERT INTO t2 VALUES (1180244875), (1951338178);
            		 
             
            SET SESSION join_buffer_size = 5250229460064350213;
            		 
            SET SESSION join_cache_level = 4;
            		 
             
            		 
            SET optimizer_switch='optimize_join_buffer_size=on';
            		 
            SELECT t2.i FROM t2  LEFT  JOIN t1 ON t1.i = t2.i  WHERE t1.i;
            		 
             
            		 
            SET optimizer_switch='optimize_join_buffer_size=off';
            		 
            SELECT t2.i FROM t2  LEFT  JOIN t1 ON t1.i = t2.i  WHERE t1.i;

            alice Alice Sherepa added a comment - Thanks! I repeated on 10.2 53b580a91c12e92726-10.8 as described: --source include/have_innodb.inc   CREATE TABLE t1 (i int PRIMARY KEY )engine=innodb; INSERT INTO t1 VALUES (1332945389); CREATE TABLE t2 (i int PRIMARY KEY )engine=innodb; INSERT INTO t2 VALUES (1180244875), (1951338178); SET SESSION join_buffer_size = 5250229460064350213; SET SESSION join_cache_level = 4;   SET optimizer_switch= 'optimize_join_buffer_size=on' ; SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i;   SET optimizer_switch= 'optimize_join_buffer_size=off' ; SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i;
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            Weijun Huang Weijun Huang added a comment -

            I checked the bug and found it still exists in 10.11.

            Weijun Huang Weijun Huang added a comment - I checked the bug and found it still exists in 10.11.
            alice Alice Sherepa made changes -
            Labels 11.0-sel
            alice Alice Sherepa made changes -
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.9 [ 26905 ]
            Affects Version/s 10.10 [ 27530 ]
            Affects Version/s 10.11 [ 27614 ]
            psergei Sergei Petrunia added a comment -

            Reproducible for me on latest 10.11:

            commit 70be59913c90e93fe5136d6f6df03c4254aa515d (HEAD -> 10.11, origin/HEAD, origin/10.11)
            		 
            Author: Otto Kekäläinen <otto@kekalainen.net>
            		 
            Date:   Sat Nov 26 18:19:35 2022 -0800

            and on latest 11.0:

            commit b075191ba8598af6aff5549e6e19f6255aef258a
            		 
            Author: Rex <rex.johnston@mariadb.com>
            		 
            Date:   Fri Jan 6 15:03:54 2023 +1200
            		 
             
            		 
                MDEV-30353 Debian additions version fix

            psergei Sergei Petrunia added a comment - Reproducible for me on latest 10.11: commit 70be59913c90e93fe5136d6f6df03c4254aa515d (HEAD -> 10.11, origin/HEAD, origin/10.11) Author: Otto Kekäläinen <otto@kekalainen.net> Date: Sat Nov 26 18:19:35 2022 -0800 and on latest 11.0: commit b075191ba8598af6aff5549e6e19f6255aef258a Author: Rex <rex.johnston@mariadb.com> Date: Fri Jan 6 15:03:54 2023 +1200   MDEV-30353 Debian additions version fix
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            elenst Elena Stepanova made changes -
            Assignee Sergei Petrunia [ psergey ] Michael Widenius [ monty ]
            Description
            {noformat}
            CREATE TABLE `t1` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`),
              KEY `ic3` (`c0`) USING BTREE
            );
            INSERT INTO `t1` VALUES (1332945389);

            CREATE TABLE `t2` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`)
            );
            INSERT INTO `t2` VALUES (1180244875), (1951338178);

            mysql> SET SESSION join_buffer_size = 5250229460064350213;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

            mysql> SET SESSION join_cache_level = 4;
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            Empty set (0.00 sec)

            mysql> SET optimizer_switch='optimize_join_buffer_size=off';
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            +------------+
            | c0 |
            +------------+
            | 1180244875 |
            | 1951338178 |
            +------------+
            2 rows in set (0.00 sec)
            {noformat}
            		{noformat}
            CREATE TABLE `t1` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`),
              KEY `ic3` (`c0`) USING BTREE
            );
            INSERT INTO `t1` VALUES (1332945389);

            CREATE TABLE `t2` (
              `c0` int(10) unsigned NOT NULL,
              PRIMARY KEY (`c0`)
            );
            INSERT INTO `t2` VALUES (1180244875), (1951338178);

            mysql> SET SESSION join_buffer_size = 5250229460064350213;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

            mysql> SET SESSION join_cache_level = 4;
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            Empty set (0.00 sec)

            mysql> SET optimizer_switch='optimize_join_buffer_size=off';
            Query OK, 0 rows affected (0.00 sec)

            mysql> SELECT t2.c0 FROM t2 LEFT OUTER JOIN t1 ON t1.c0 = t2.c0 WHERE t1.c0;
            +------------+
            | c0 |
            +------------+
            | 1180244875 |
            | 1951338178 |
            +------------+
            2 rows in set (0.00 sec)
            {noformat}
            Priority Major [ 3 ] Critical [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            monty Michael Widenius made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            monty Michael Widenius added a comment -

            The problem was that join_buffer_size conflicted with
            join_buffer_space_limit, which caused the query to be run without
            join buffer. However this caused wrong results as the code assumed
            that the hash+join buffer would eliminate all rows in the ON condition.

            Fixed by not using join_buffer_space_limit when
            optimize_join_buffer_size=off. This matches the documentation at
            https://mariadb.com/kb/en/block-based-join-algorithms

            monty Michael Widenius added a comment - The problem was that join_buffer_size conflicted with join_buffer_space_limit, which caused the query to be run without join buffer. However this caused wrong results as the code assumed that the hash+join buffer would eliminate all rows in the ON condition. Fixed by not using join_buffer_space_limit when optimize_join_buffer_size=off. This matches the documentation at https://mariadb.com/kb/en/block-based-join-algorithms
            monty Michael Widenius added a comment -

            Pushed to 10.4 main tree

            monty Michael Widenius added a comment - Pushed to 10.4 main tree
            monty Michael Widenius made changes -
            Fix Version/s 10.4.30 [ 28912 ]
            Fix Version/s 10.5.21 [ 28913 ]
            Fix Version/s 10.6.14 [ 28914 ]
            Fix Version/s 10.8.9 [ 28915 ]
            Fix Version/s 10.9.7 [ 28916 ]
            Fix Version/s 10.10.5 [ 28917 ]
            Fix Version/s 10.11.4 [ 28918 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8.9 [ 28915 ]
            dbart Daniel Bartholomew made changes -
            Fix Version/s 10.4.31 [ 29010 ]
            Fix Version/s 10.5.22 [ 29011 ]
            Fix Version/s 10.6.15 [ 29013 ]
            Fix Version/s 10.9.8 [ 29015 ]
            Fix Version/s 10.10.6 [ 29017 ]
            Fix Version/s 10.11.5 [ 29019 ]
            Fix Version/s 10.4.30 [ 28912 ]
            Fix Version/s 10.5.21 [ 28913 ]
            Fix Version/s 10.6.14 [ 28914 ]
            Fix Version/s 10.9.7 [ 28916 ]
            Fix Version/s 10.10.5 [ 28917 ]
            Fix Version/s 10.11.4 [ 28918 ]
            alice Alice Sherepa made changes -

            People

              monty Michael Widenius
              sugar Xiu Tang
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.