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

Incorrect Join Execution When Controlling Join Buffer Size

    XMLWordPrintable

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

        Activity

          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.