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

Wrong result with optimize_join_buffer_size=on on query with LEFT JOINs and constant table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Cannot Reproduce
    • 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • N/A
    • Optimizer
    • None

    Description

      CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4),(5),(8),(4),(1),(4),(8),(6),(4),(9),(2),(10),(9);
       
      CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1),(2);
       
      CREATE TABLE t3 (f3 INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (10);
       
      CREATE TABLE t4 (f4 INT) ENGINE=MyISAM;
       
      ANALYZE TABLE t1, t2, t3, t4;
       
      set optimizer_switch = 'optimize_join_buffer_size=off';
      SELECT f3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
       
      set optimizer_switch = 'optimize_join_buffer_size=on';
      SELECT f3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
       
      # Cleanup
      DROP TABLE t1, t2, t3, t4;
      

      d2ba9edd Result with optimize_join_buffer_size=off

      f3
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      

      d2ba9edd Result with optimize_join_buffer_size=on

      f3
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL
      10
      NULL
      

      The result with all NULLs is probably correct. In any case, one of them is wrong.

      Attachments

        Issue Links

          Activity

            People

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