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
            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;
            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.
            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
            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

            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.