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

outer join, join buffering, and order by - invalid query plan

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.5, 5.3.12, 5.5.33a
    • Fix Version/s: 5.5.35, 10.0.7, 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      A combination of outer join, join buffering, and order by causes invalid query plans to be generated.

      Test dataset:

      create table t0 (a int primary key) engine=myisam;
      insert into t0 values (1);
       
      create table t1(a int) engine=myisam;
      insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      alter table t1 add b int;
       
      create table t2 like t1;
      insert into t2 select * from t1;

      Query and EXPLAIN:

      explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
       
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t0	system	NULL	NULL	NULL	NULL	1	Using filesort
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)

      The query plan uses both "Using filesort" and "Using join buffer". It will produce the results in the wrong order.

      I'll elaborate why:

      • The query has ORDER BY and so must produce rows in order.
        "Using filesort" means that the first table (t1 in the example) is read by filesort(), which produces the sorted sequence, which is then joined to table t2.
      • However, table t2 uses join buffer, which will break the ordering created by filesort.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: