← Back to team overview

maria-developers team mailing list archive

Re: MDEV-26301: Obvious wrong query result

 

Hello Igor,

I'm looking at your latest patch for MDEV-26301 in bb-10.4-igor

and I see an obvious bug in the execution part:

Split-materialized table re-fill is triggered by this code in
sub_select():

    if (join_tab->split_derived_to_update && !end_of_records)
    { 
      table_map tab_map= join_tab->split_derived_to_update;
      for (uint i= 0; tab_map; i++, tab_map>>= 1)
      {
        if (tab_map & 1)
          join->map2table[i]->preread_init_done= false;
      }
    }   

(did you see Table_map_iterator class btw?)

but what if the table in question uses join buffer and so uses sub_select_cache(),
not sub_select()? 

Please find the testcase below:

# 5 values
create table t1(a int, b int);
insert into t1 select seq,seq from seq_1_to_5;

# 5 value groups of size 2 each
create table t2(a int, b int, key(a));
insert into t2
select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;

# 5 value groups of size 3 each
create table t3(a int, b int, key(a));
insert into t3
select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;

analyze table t1,t2,t3 persistent for all;

create table t10 (
  grp_id int,
  col1 int,
  key(grp_id)
);

# 100 groups of 100 values each
insert into t10
select
  A.seq,
  B.seq
from
  seq_1_to_100 A,
  seq_1_to_100 B;

# and X10 multiplier

create table t11 (
  col1 int,
  col2 int
);
insert into t11
select A.seq, A.seq from seq_1_to_10 A;

analyze table t10,t11 persistent for all;

explain
select * from
  (
    (t1 left join t2 on t2.a=t1.b)
    left join t3 on t3.a=t1.b
  ) left join (select grp_id, count(*)
               from t10 left join t11 on t11.col1=t10.col1
               group by grp_id) T on T.grp_id=t1.b;

+------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+
| id   | select_type     | table      | type | possible_keys | key    | key_len | ref     | rows | Extra                                           |
+------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+
|    1 | PRIMARY         | t1         | ALL  | NULL          | NULL   | NULL    | NULL    | 5    |                                                 |
|    1 | PRIMARY         | t2         | ref  | a             | a      | 5       | j1.t1.b | 2    | Using where                                     |
|    1 | PRIMARY         | t3         | ref  | a             | a      | 5       | j1.t1.b | 3    | Using where                                     |
|    1 | PRIMARY         | <derived2> | ref  | key0          | key0   | 5       | j1.t1.b | 10   | Using where                                     |
|    2 | LATERAL DERIVED | t10        | ref  | grp_id        | grp_id | 5       | j1.t1.b | 100  |                                                 |
|    2 | LATERAL DERIVED | t11        | ALL  | NULL          | NULL   | NULL    | NULL    | 10   | Using where; Using join buffer (flat, BNL join) |
+------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+

Ok
Note the query result:

MariaDB [j1]> select * from
    ->   (
    ->     (t1 left join t2 on t2.a=t1.b)
    ->     left join t3 on t3.a=t1.b
    ->   ) left join (select grp_id, count(*)
    ->                from t10 left join t11 on t11.col1=t10.col1
    ->                group by grp_id) T on T.grp_id=t1.b;
+------+------+------+------+------+------+--------+----------+
| a    | b    | a    | b    | a    | b    | grp_id | count(*) |
+------+------+------+------+------+------+--------+----------+
|    1 |    1 |    1 |    1 |    1 |    1 |      1 |      100 |
|    1 |    1 |    1 |    1 |    1 |    2 |      1 |      100 |
|    1 |    1 |    1 |    1 |    1 |    3 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    1 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    2 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    3 |      1 |      100 |
|    2 |    2 |    2 |    1 |    2 |    1 |      2 |      100 |
|    2 |    2 |    2 |    1 |    2 |    2 |      2 |      100 |
|    2 |    2 |    2 |    1 |    2 |    3 |      2 |      100 |
|    2 |    2 |    2 |    2 |    2 |    1 |      2 |      100 |
|    2 |    2 |    2 |    2 |    2 |    2 |      2 |      100 |
|    2 |    2 |    2 |    2 |    2 |    3 |      2 |      100 |
|    3 |    3 |    3 |    1 |    3 |    1 |      3 |      100 |
|    3 |    3 |    3 |    1 |    3 |    2 |      3 |      100 |
|    3 |    3 |    3 |    1 |    3 |    3 |      3 |      100 |
|    3 |    3 |    3 |    2 |    3 |    1 |      3 |      100 |
|    3 |    3 |    3 |    2 |    3 |    2 |      3 |      100 |
|    3 |    3 |    3 |    2 |    3 |    3 |      3 |      100 |
|    4 |    4 |    4 |    1 |    4 |    1 |      4 |      100 |
|    4 |    4 |    4 |    1 |    4 |    2 |      4 |      100 |
|    4 |    4 |    4 |    1 |    4 |    3 |      4 |      100 |
|    4 |    4 |    4 |    2 |    4 |    1 |      4 |      100 |
|    4 |    4 |    4 |    2 |    4 |    2 |      4 |      100 |
|    4 |    4 |    4 |    2 |    4 |    3 |      4 |      100 |
|    5 |    5 |    5 |    1 |    5 |    1 |      5 |      100 |
|    5 |    5 |    5 |    1 |    5 |    2 |      5 |      100 |
|    5 |    5 |    5 |    1 |    5 |    3 |      5 |      100 |
|    5 |    5 |    5 |    2 |    5 |    1 |      5 |      100 |
|    5 |    5 |    5 |    2 |    5 |    2 |      5 |      100 |
|    5 |    5 |    5 |    2 |    5 |    3 |      5 |      100 |
+------+------+------+------+------+------+--------+----------+
30 rows in set (0.015 sec)

Now, let's make the tables not use indexes and use join buffer in the top-level select:


explain
select * from
  (
    (t1 left join t2 use index() on t2.a=t1.b)
    left join t3 use index() on t3.a=t1.b
  ) left join (select grp_id, count(*)
               from t10 left join t11 on t11.col1=t10.col1
               group by grp_id) T on T.grp_id=t1.b;
+------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+
| id   | select_type     | table      | type | possible_keys | key    | key_len | ref     | rows | Extra                                                  |
+------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+
|    1 | PRIMARY         | t1         | ALL  | NULL          | NULL   | NULL    | NULL    | 5    |                                                        |
|    1 | PRIMARY         | t2         | ALL  | NULL          | NULL   | NULL    | NULL    | 50   | Using where; Using join buffer (flat, BNL join)        |
|    1 | PRIMARY         | t3         | ALL  | NULL          | NULL   | NULL    | NULL    | 15   | Using where; Using join buffer (incremental, BNL join) |
|    1 | PRIMARY         | <derived2> | ref  | key0          | key0   | 5       | j1.t1.b | 10   | Using where                                            |
|    2 | LATERAL DERIVED | t10        | ref  | grp_id        | grp_id | 5       | j1.t1.b | 100  |                                                        |
|    2 | LATERAL DERIVED | t11        | ALL  | NULL          | NULL   | NULL    | NULL    | 10   | Using where; Using join buffer (flat, BNL join)        |
+------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+


select * from
  (
    (t1 left join t2 use index() on t2.a=t1.b)
    left join t3 use index() on t3.a=t1.b
  ) left join (select grp_id, count(*)
               from t10 left join t11 on t11.col1=t10.col1
               group by grp_id) T on T.grp_id=t1.b;
+------+------+------+------+------+------+--------+----------+
| a    | b    | a    | b    | a    | b    | grp_id | count(*) |
+------+------+------+------+------+------+--------+----------+
|    1 |    1 |    1 |    1 |    1 |    1 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    1 |      1 |      100 |
|    1 |    1 |    1 |    1 |    1 |    2 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    2 |      1 |      100 |
|    1 |    1 |    1 |    1 |    1 |    3 |      1 |      100 |
|    1 |    1 |    1 |    2 |    1 |    3 |      1 |      100 |
|    2 |    2 |    2 |    1 |    2 |    1 |   NULL |     NULL |
|    2 |    2 |    2 |    2 |    2 |    1 |   NULL |     NULL |
|    2 |    2 |    2 |    1 |    2 |    2 |   NULL |     NULL |
|    2 |    2 |    2 |    2 |    2 |    2 |   NULL |     NULL |
|    2 |    2 |    2 |    1 |    2 |    3 |   NULL |     NULL |
|    2 |    2 |    2 |    2 |    2 |    3 |   NULL |     NULL |
|    3 |    3 |    3 |    1 |    3 |    1 |   NULL |     NULL |
|    3 |    3 |    3 |    2 |    3 |    1 |   NULL |     NULL |
|    3 |    3 |    3 |    1 |    3 |    2 |   NULL |     NULL |
|    3 |    3 |    3 |    2 |    3 |    2 |   NULL |     NULL |
|    3 |    3 |    3 |    1 |    3 |    3 |   NULL |     NULL |
|    3 |    3 |    3 |    2 |    3 |    3 |   NULL |     NULL |
|    4 |    4 |    4 |    1 |    4 |    1 |   NULL |     NULL |
|    4 |    4 |    4 |    2 |    4 |    1 |   NULL |     NULL |
|    4 |    4 |    4 |    1 |    4 |    2 |   NULL |     NULL |
|    4 |    4 |    4 |    2 |    4 |    2 |   NULL |     NULL |
|    4 |    4 |    4 |    1 |    4 |    3 |   NULL |     NULL |
|    4 |    4 |    4 |    2 |    4 |    3 |   NULL |     NULL |
|    5 |    5 |    5 |    1 |    5 |    1 |   NULL |     NULL |
|    5 |    5 |    5 |    2 |    5 |    1 |   NULL |     NULL |
|    5 |    5 |    5 |    1 |    5 |    2 |   NULL |     NULL |
|    5 |    5 |    5 |    2 |    5 |    2 |   NULL |     NULL |
|    5 |    5 |    5 |    1 |    5 |    3 |   NULL |     NULL |
|    5 |    5 |    5 |    2 |    5 |    3 |   NULL |     NULL |
+------+------+------+------+------+------+--------+----------+
30 rows in set (0.008 sec)

Bummer.



BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net