MariaDB Development
  1. MariaDB Development
  2. MDEV-389

Wrong result (missing row) with semijoin, join_cache_level>4, LEFT JOIN, GROUP BY, ORDER BY, LIMIT

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 5.5.25, 5.3.7
    • Fix Version/s: 5.5.31
    • Labels:
      None
    • Global Rank:
      594

      Description

      The following query

      SELECT a, COUNT(*) AS cnt 
      FROM t1 LEFT JOIN t2 ON (d = b) 
      WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 
      GROUP BY a ORDER BY a, cnt LIMIT 2
      

      on the test data returns 1 row if it's run with semijoin and join_cache_level > 4, and 2 rows otherwise. 2 rows is the correct result.

      bzr version-info

      revision-id: psergey@askmonty.org-20120705103901-9ero4x0eifk0wtks
      date: 2012-07-05 14:39:01 +0400
      build-date: 2012-07-11 01:54:42 +0400
      revno: 3462
      

      Also reproducible on maria/5.3 revno 3551, and on release builds 5.3.7 and 5.5.25.

      Minimal optimizer_switch:

      semijoin=on
      

      Full optimizer_switch (default):

      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=off
      

      EXPLAIN (with the default optimizer_switch):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	index	a	a	4	NULL	3	100.00	Using where; Using temporary; Using filesort
      1	PRIMARY	t2	ref	c	c	5	test.t1.a	2	100.00	Using index; Start temporary; End temporary
      1	PRIMARY	t2	ref	d	d	2	test.t1.b	2	100.00	Using where; Using index
      Warnings:
      Note	1276	Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a` AS `a`,count(0) AS `cnt` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t2` on(((`test`.`t2`.`d` = `test`.`t1`.`b`) and (`test`.`t1`.`b` is not null))) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` > 'k')) group by `test`.`t1`.`a` order by `test`.`t1`.`a`,count(0) limit 2
      

      Test case:

      SET join_cache_level = 5;
      SET optimizer_switch = 'semijoin=on';
      
      CREATE TABLE t1 (a INT NOT NULL, b CHAR(1), KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4,'p'),(1,'q'),(8,'e');
      
      CREATE TABLE t2 (c INT, d CHAR(1), KEY(c), KEY(d)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (4,'f'),(2,'i'),(5,'h'),(3,'q'),(1,'g');
      
      SELECT a, COUNT(*) AS cnt 
      FROM t1 LEFT JOIN t2 ON (d = b) 
      WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 
      GROUP BY a ORDER BY a, cnt LIMIT 2;
      

      Expected result:

      a	cnt
      1	1
      4	1
      

      Actual result:

      a	cnt
      1	1
      

      Please note that the aggregate function is not required, it just makes the test somewhat more sensible. The query can be modified like this (double a in ORDER BY is not a typo, it is needed for this version of the query):

      SELECT a 
      FROM t1 LEFT JOIN t2 ON (d = b) 
      WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 
      GROUP BY a ORDER BY a, a LIMIT 2;
      

      It still returns 1 row instead of 2.

      Note: I've set it to Minor, because join_cache_level > 4 is not default

        Activity

        Hide
        Patryk Pomykalski added a comment -

        Can't repeat with latest 5.5 (rev 3667)

        Show
        Patryk Pomykalski added a comment - Can't repeat with latest 5.5 (rev 3667)
        Hide
        Sergei Petrunia added a comment -

        In 5.3, the bug is removed by this revision:

        revno: 3592 [merge]
        revision-id: timour@askmonty.org-20121109081120-vdtnonh1u7wpl8ms
        parent: igor@askmonty.org-20121102225916-g7s6hod36vdqckne
        parent: timour@askmonty.org-20121108202605-r3z07g8atnaqkw9v
        committer: timour@askmonty.org
        branch nick: 5.3-merge-new
        timestamp: Fri 2012-11-09 10:11:20 +0200
        message:
        Merge MariaDB 5.1.66 -> 5.2 -> 5.3

        I am unable to tell which particular patch caused it to be fixed.

        Show
        Sergei Petrunia added a comment - In 5.3, the bug is removed by this revision: revno: 3592 [merge] revision-id: timour@askmonty.org-20121109081120-vdtnonh1u7wpl8ms parent: igor@askmonty.org-20121102225916-g7s6hod36vdqckne parent: timour@askmonty.org-20121108202605-r3z07g8atnaqkw9v committer: timour@askmonty.org branch nick: 5.3-merge-new timestamp: Fri 2012-11-09 10:11:20 +0200 message: Merge MariaDB 5.1.66 -> 5.2 -> 5.3 I am unable to tell which particular patch caused it to be fixed.
        Hide
        Sergei Petrunia added a comment -

        In 5.3, the change is because of a different query plan:

        rev-3592:
        id select_type table type possible_keys key key_len ref rows Extra
        1 PRIMARY t1 ALL a NULL NULL NULL 3 Using where; Using temporary; Using filesort
        1 PRIMARY t2 ref c c 5 test.t1.a 2 Using index; Start temporary; End temporary
        1 PRIMARY t2 ref d d 2 test.t1.b 2 Using where; Using index

        rev-3591:
        id select_type table type possible_keys key key_len ref rows Extra
        1 PRIMARY t1 index a a 4 NULL 3 Using where; Using temporary; Using filesort
        1 PRIMARY t2 ref c c 5 test.t1.a 2 Using index; Start temporary; End temporary
        1 PRIMARY t2 ref d d 2 test.t1.b 2 Using where; Using index

        maybe, the bug is still there.

        Show
        Sergei Petrunia added a comment - In 5.3, the change is because of a different query plan: rev-3592: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL a NULL NULL NULL 3 Using where; Using temporary; Using filesort 1 PRIMARY t2 ref c c 5 test.t1.a 2 Using index; Start temporary; End temporary 1 PRIMARY t2 ref d d 2 test.t1.b 2 Using where; Using index rev-3591: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 4 NULL 3 Using where; Using temporary; Using filesort 1 PRIMARY t2 ref c c 5 test.t1.a 2 Using index; Start temporary; End temporary 1 PRIMARY t2 ref d d 2 test.t1.b 2 Using where; Using index maybe, the bug is still there.
        Hide
        Sergei Petrunia added a comment -

        Not repeatable in the latest 5.5 (revno: 3742).

        Show
        Sergei Petrunia added a comment - Not repeatable in the latest 5.5 (revno: 3742).
        Hide
        Sergei Petrunia added a comment -

        For now, added testcase. Feel free to re-open if the problem repeats again

        Show
        Sergei Petrunia added a comment - For now, added testcase. Feel free to re-open if the problem repeats again

          People

          • Assignee:
            Sergei Petrunia
            Reporter:
            Elena Stepanova
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: