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

LP:675516 - wrong result with join_cache_level = 4, join_cache_hashed, join_cache_incremental, outer_join_with_cache

    XMLWordPrintable

Details

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

    Description

      The following query

      SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;

      returns "7" when executed with (incremental, BNLH join), (flat, BNL join) and (incremental, BNL join) and no rows when executed with join_cache_level=0.

      Test case:

      --source include/have_innodb.inc

      SET SESSION storage_engine='InnoDB';
      SET SESSION join_cache_level = 4;
      SET SESSION optimizer_switch = 'join_cache_hashed=on';
      SET SESSION optimizer_switch = 'join_cache_incremental=on';
      SET SESSION optimizer_switch = 'outer_join_with_cache=on';

      DROP TABLE IF EXISTS M;
      CREATE TABLE M (
      f8 int) ;
      INSERT IGNORE INTO M VALUES ('5');

      DROP TABLE IF EXISTS G;
      CREATE TABLE G (
      f7 int,
      f8 int,
      f6 int) ;
      INSERT IGNORE INTO G VALUES ('7','8','0');

      DROP TABLE IF EXISTS E;
      CREATE TABLE E (
      f7 int,
      f8 int,
      f10 int,
      PRIMARY KEY (f8)) ;
      INSERT IGNORE INTO E VALUES ('2','5','0');

      SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
      EXPLAIN SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
      SET SESSION join_cache_level=0;
      SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;
      EXPLAIN SELECT STRAIGHT_JOIN table1.f7 FROM G AS table1 LEFT JOIN E AS table2 RIGHT JOIN M AS table3 ON table2.f8 = table3.f8 JOIN G AS table4 ON table4.f8 ON table1.f6 = table2.f10 WHERE table2.f7 IS NULL ;

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.