maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #13314
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
