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

Wrong result (missing row) with outer_join_with_cache=on, join_cache_level > 0, RIGHT JOIN, HAVING, LIMIT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.4
    • 10.0.6
    • None
    • None

    Description

      SET optimizer_switch = 'outer_join_with_cache=on';
       
      CREATE TABLE t1 (c1 VARCHAR(6)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('s'),('t');
       
      CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('a'),('x');
       
      SET join_cache_level = 1;
      SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
       
      SET join_cache_level = 0;
      SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;

      Result:

      SET join_cache_level = 1;
      SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
      c2
      SET join_cache_level = 0;
      SELECT t2.* FROM t1 RIGHT JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
      c2
      x

      The 2nd result is the correct one.

      The failure happens on current 10.0-base (revno 3733) and 10.0 (revno 3856). I found the revision on 10.0-base when it started happening, it was a merge from 5.5:

      revno: 3645 [merge]
      revision-id: sergii@pisem.net-20130606155128-5mytep9v42626tfs
      committer: Sergei Golubchik <sergii@pisem.net>
      branch nick: 10.0-base
      timestamp: Thu 2013-06-06 17:51:28 +0200
      message:
        5.5 merge

      But on some reason I could not reproduce it on 5.5, even after rolling back to the revision which was merged into 10.0-base.

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.