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

LP:675095 - Less rows returned with 5-way join, join_cache_level=7, outer join and BNL

    XMLWordPrintable

Details

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

    Description

      The following 5-table query

      SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;

      returns less rows then when executed with join_cache_level = 0 . Maria-5.3 crashes with the set_match_flag_if_none assertion.

      Test case:

      SET SESSION join_cache_level = 7;
      SET SESSION optimizer_switch = 'outer_join_with_cache=on';

      DROP TABLE IF EXISTS O;
      CREATE TABLE O (
      f1 int,
      pk int,
      f2 int,
      f4 int) ;
      INSERT IGNORE INTO O VALUES ('0','9','0','2'),('0','1','0','1'),('0','1','0','1'),('0','1','0','7'),('0','1','0','6'),('0','2','0',NULL);

      DROP TABLE IF EXISTS I;
      CREATE TABLE I (
      f2 int,
      f4 int,
      pk int,
      KEY (f4)) ;
      INSERT IGNORE INTO I VALUES ('0',NULL,'1'),('0',NULL,'2'),('0','1','6'),('0','1','7'),('0','0','8');

      SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
      EXPLAIN SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
      SET SESSION join_cache_level = 0;
      SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
      EXPLAIN SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;

      Attachments

        Activity

          People

            igor Igor Babaev
            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.