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

Changing join_buffer_size causes different results

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 10.3
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This test case is originally subselect_sj.test

      --echo # 
      --echo # MDEV-5059: Wrong result (missing row) wih semijoin, join_cache_level > 2, LEFT JOIN, ORDER BY
      --echo # 
       
      set join_buffer_size= 1024*1024*2;
      SET join_cache_level = 3;
      set optimizer_search_depth=62;
      set join_buffer_space_limit=2097152;
       
      set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on";
      

      CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,4),(2,5);
       
      CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1,'v'),(7,'s');
       
      CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q');
       
      CREATE TABLE t4 (i4 INT) ENGINE=MyISAM;
      INSERT INTO t4 VALUES (1);
      

      SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
       
      set join_buffer_size=262144;
       
      SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
       
      DROP TABLE t1,t2,t3,t4;
      

      -----------
      The last SELECT produces two different results:

      SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
      pk1     i1      i2      c2      pk3     i3      c3
      1       4       7       s       1       7       g
      1       4       7       s       2       4       p
      1       4       7       s       3       1       q
      1       4       1       v       1       7       g
      1       4       1       v       2       4       p
      1       4       1       v       3       1       q
      

      set join_buffer_size=262144;
      SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 
      WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;
      pk1     i1      i2      c2      pk3     i3      c3
      1       4       7       s       NULL    NULL    NULL
      1       4       1       v       NULL    NULL    NULL
      

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              monty Michael Widenius
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: