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

LP:694443 - Wrong result with hash join and join_cache_level=6

    XMLWordPrintable

Details

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

    Description

      In maria-5.3 , the following query:

      SELECT STRAIGHT_JOIN t1.f2 FROM t1 JOIN t2 ON t1.f4 = t2.f5 WHERE ( t2.f1 OR t2.f2 ) AND t2.f3 IS NULL ;

      returns no rows even though 1 row matches the WHERE predicate and is returned by all other plans.

      explain:

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE t1 ALL NULL NULL NULL NULL 2
      1 SIMPLE t2 ref f3 f3 5 const 1 Using where; Using join buffer (flat, BNLH join)

      test case:

      CREATE TABLE t1 (f2 int(11), f4 varchar(10)) ;
      INSERT IGNORE INTO t1 VALUES ('19','time'),('24','juabngoyrt');

      CREATE TABLE t2 (f5 varchar(10), f2 int(11), f1 int(11), f3 int(11), KEY (f3)) ;
      INSERT IGNORE INTO t2 VALUES ('time','4',NULL,NULL);

      SET SESSION join_cache_level=6;
      SET SESSION optimizer_switch='join_cache_bka=off';
      SELECT STRAIGHT_JOIN t1.f2 FROM t1 JOIN t2 ON t1.f4 = t2.f5 WHERE ( t2.f1 OR t2.f2 ) AND t2.f3 IS NULL ;
      EXPLAIN SELECT STRAIGHT_JOIN t1.f2 FROM t1 JOIN t2 ON t1.f4 = t2.f5 WHERE ( t2.f1 OR t2.f2 ) AND t2.f3 IS NULL ;

      SET SESSION join_cache_level=0;
      SET SESSION optimizer_switch='index_condition_pushdown=off';
      SELECT STRAIGHT_JOIN t1.f2 FROM t1 JOIN t2 ON t1.f4 = t2.f5 WHERE ( t2.f1 OR t2.f2 ) AND t2.f3 IS NULL ;

      Even though this particular test case includes join_cache_bka=off, the problematic query plan was also observed without having to force it.

      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.