|
Could you please add .cnf file(s)? I tried to repeat the issue, but I was getting the expected result.
|
|
z-custom.cnf
with pleasure
|
|
Thank you!
Now I was able to reproduce it - after setting character_set_server=utf8mb4.
It is repeatable on MariaDB 10.3-10.6, 10.2 returned correct results.
|
-- source include/have_innodb.inc
|
|
CREATE TABLE t1 (
|
the_date date NOT NULL
|
, PRIMARY KEY ( the_date ) ###!
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13');
|
|
CREATE TABLE t2 (
|
the_date date NOT NULL,
|
ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
|
cco_stk_ttl int,
|
PRIMARY KEY ( the_date , ptn_id )
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t2 VALUES ('2021-08-11','10002',NULL),('2021-08-11','10741',128),('2021-08-11','11001',4),('2021-08-11','11003',2048),('2021-08-12','10001',4096),('2021-08-12','10002',1),('2021-08-12','10429',256),('2021-08-12','10499',16),('2021-08-12','10580',8),('2021-08-12','10740',32),('2021-08-12','10741',64),('2021-08-12','10771',512),('2021-08-12','11001',2),('2021-08-12','11003',1024);
|
|
CREATE TABLE t3 (
|
id int NOT NULL AUTO_INCREMENT,
|
nsc_id char(5) NOT NULL,
|
dept_id char(4) NOT NULL,
|
district_id char(3) NOT NULL,
|
region_id char(2) NOT NULL,
|
PRIMARY KEY ( id ),
|
UNIQUE KEY dept_district ( dept_id , district_id ),
|
KEY region_id ( dept_id , region_id )
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t3 VALUES (1,'MMD','ADVB','10','1'),(2,'MMD','ADVB','11','1'),(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2');
|
|
CREATE TABLE t4 (
|
dept_id char(4) CHARACTER SET utf8mb3 NOT NULL,
|
ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL,
|
district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0',
|
nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL
|
, PRIMARY KEY ( ptn_id , dept_id ) ###!
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t4 VALUES ('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'),('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'),('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'),('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'),('ADVB','11002','11','MMD');
|
|
|
|
SELECT sql_no_cache org.the_date ,
|
org.org_id ,
|
org.dept_id ,
|
msr. cco_stk_ttl
|
FROM
|
( SELECT cal.the_date ,
|
org.dept_id ,
|
coalesce(org.district_id, org.region_id, 'MMD') AS org_id ,
|
org.district_id ,
|
org.region_id
|
FROM t1 cal
|
CROSS JOIN t3 org
|
WHERE org.nsc_id = 'MMD'
|
AND org.dept_id IN ('ADVB')
|
AND cal.the_date = '2021-08-12'
|
GROUP BY cal.the_date,
|
org.dept_id,
|
org.region_id,
|
org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL
|
OR org.dept_id IS NULL) ) org
|
LEFT JOIN
|
( SELECT sub.the_date ,
|
dis.dept_id ,
|
dis.region_id ,
|
dis.district_id ,
|
sum(sub.cco_stk_ttl) AS cco_stk_ttl
|
FROM t2 sub
|
JOIN t4 org ON org.ptn_id = sub.ptn_id
|
JOIN t3 dis ON dis.dept_id = org.dept_id
|
AND dis.district_id = org.district_id
|
WHERE dis.nsc_id = 'MMD'
|
AND dis.dept_id IN ('ADVB')
|
GROUP BY sub.the_date,
|
dis.dept_id,
|
dis.region_id,
|
dis.district_id WITH ROLLUP ) msr ON msr.the_date = org.the_date
|
AND msr.dept_id <=> org.dept_id
|
AND msr.region_id <=> org.region_id
|
AND msr.district_id <=> org.district_id;
|
|
that query returns:
2021-08-12 10 ADVB 1
|
2021-08-12 10 ADVB 4096
|
2021-08-12 11 ADVB 2
|
2021-08-12 1 ADVB 1
|
2021-08-12 1 ADVB 4098
|
2021-08-12 21 ADVB 96
|
2021-08-12 22 ADVB 256
|
2021-08-12 2 ADVB 352
|
2021-08-12 MMD ADVB 4451
|
without index on t1:
the_date org_id dept_id cco_stk_ttl
|
2021-08-12 10 ADVB 4097
|
2021-08-12 11 ADVB 2
|
2021-08-12 1 ADVB 4099
|
2021-08-12 21 ADVB 96
|
2021-08-12 22 ADVB 256
|
2021-08-12 2 ADVB 352
|
2021-08-12 MMD ADVB 4451
|
if there is no index on t4 (PRIMARY KEY ( ptn_id , dept_id )) :
the_date org_id dept_id cco_stk_ttl
|
2021-08-12 10 ADVB 18033
|
2021-08-12 11 ADVB 12022
|
2021-08-12 1 ADVB 30055
|
2021-08-12 21 ADVB 12022
|
2021-08-12 22 ADVB 6011
|
2021-08-12 2 ADVB 18033
|
2021-08-12 MMD ADVB 48088
|
I do not want to simplify the case as I am afraid to lose smth on the way, with those indexes, etc
|
|
What is temporary workaround for this issue ?
|
|
Trying Alice's testcase.
EXPLAIN:
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 2 | Using where |
|
| 3 | LATERAL DERIVED | sub | ref | PRIMARY | PRIMARY | 3 | org.the_date | 1 | Using where; Using filesort |
|
| 3 | LATERAL DERIVED | org | ref | PRIMARY | PRIMARY | 15 | j2.sub.ptn_id | 1 | Using where |
|
| 3 | LATERAL DERIVED | dis | eq_ref | dept_district,region_id | dept_district | 28 | const,func | 1 | Using index condition; Using where |
|
| 2 | DERIVED | cal | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using filesort |
|
+------+-----------------+------------+--------+-------------------------+---------------+---------+--------------------------------------------------------+------+------------------------------------+
|
Note the "LATERAL DERIVED". Lateral Derived optimization is used to execute subquery that has "WITH ROLLUP". As far as I understand LATERAL DERIVED execution strategy, it is not capable of producing WITH ROLLUP's super-aggregate rows.
|
|
EXPLAIN with no index on t1:
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 5 | Using where |
|
| 3 | DERIVED | sub | ALL | PRIMARY | NULL | NULL | NULL | 14 | Using temporary; Using filesort |
|
| 3 | DERIVED | org | ref | PRIMARY | PRIMARY | 15 | j2.sub.ptn_id | 1 | Using where |
|
| 3 | DERIVED | dis | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | DERIVED | cal | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+------------+------+-------------------------+---------+---------+--------------------------------------------------------+------+-------------------------------------------------+
|
|
|
EXPLAIN if there is no index on t4 (PRIMARY KEY ( ptn_id , dept_id )) :
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 43 | org.the_date,org.dept_id,org.region_id,org.district_id | 2 | Using where |
|
| 3 | LATERAL DERIVED | sub | ref | PRIMARY | PRIMARY | 3 | org.the_date | 1 | Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | dis | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | LATERAL DERIVED | org | ALL | NULL | NULL | NULL | NULL | 9 | Using where; Using join buffer (incremental, BNL join) |
|
| 2 | DERIVED | cal | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
|
| 2 | DERIVED | org | ALL | dept_district,region_id | NULL | NULL | NULL | 4 | Using where; Using filesort |
|
+------+-----------------+------------+-------+-------------------------+---------+---------+--------------------------------------------------------+------+--------------------------------------------------------+
|
LATERAL is still used (although the join order inside the LATERAL is different).
|
|
Query result with no index on t1 is the same as what I get when I put the subqueries' results into temp. tables and join the these temp.tables.
|
|
Changed the alias in the top-level select from org to org2.
Also, materialized one of the derived tables, it's irrelevant.
Result:
https://gist.github.com/spetrunia/38fee60313471877279aafb933452baf
EXPLAIN output:
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"const_condition": "1",
|
"table": {
|
"table_name": "org2",
|
"access_type": "ALL",
|
"rows": 7,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "43",
|
"used_key_parts": ["the_date", "dept_id", "region_id", "district_id"],
|
"ref": [
|
"j2.org2.the_date",
|
"j2.org2.dept_id",
|
"j2.org2.region_id",
|
"j2.org2.district_id"
|
],
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "trigcond(msr.dept_id <=> org2.dept_id and msr.region_id <=> org2.region_id and msr.district_id <=> org2.district_id)",
|
"materialized": {
|
"lateral": 1,
|
"query_block": {
|
"select_id": 2,
|
"read_sorted_file": {
|
"filesort": {
|
"sort_key": "sub.the_date, dis.dept_id, dis.region_id, dis.district_id",
|
"table": {
|
"table_name": "sub",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "3",
|
"used_key_parts": ["the_date"],
|
"ref": ["j2.org2.the_date"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "sub.the_date <=> org2.the_date"
|
}
|
}
|
},
|
"table": {
|
"table_name": "org",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "15",
|
"used_key_parts": ["ptn_id"],
|
"ref": ["j2.sub.ptn_id"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "convert(org.dept_id using utf8mb4) = 'ADVB'"
|
},
|
"table": {
|
"table_name": "dis",
|
"access_type": "eq_ref",
|
"possible_keys": ["dept_district", "region_id"],
|
"key": "dept_district",
|
"key_length": "28",
|
"used_key_parts": ["dept_id", "district_id"],
|
"ref": ["const", "func"],
|
"rows": 1,
|
"filtered": 100,
|
"index_condition": "dis.dept_id = 'ADVB' and dis.district_id = convert(org.district_id using utf8mb4)",
|
"attached_condition": "dis.nsc_id = 'MMD'"
|
}
|
}
|
}
|
}
|
}
|
}
|
|
|
The interesting part is here,:
"attached_condition": "trigcond(msr.dept_id <=> org2.dept_id and msr.region_id <=> org2.region_id and msr.district_id <=> org2.district_id)",
|
"materialized": {
|
"lateral": 1,
|
"query_block": {
|
"select_id": 2,
|
"read_sorted_file": {
|
"filesort": {
|
"sort_key": "sub.the_date, dis.dept_id, dis.region_id, dis.district_id",
|
"table": {
|
"table_name": "sub",
|
....
|
} // Table sub
|
} // filesort
|
}, // read_sorted_file
|
...
|
"table": {
|
"table_name": "dis",
|
....
|
Note that the sort_key uses fields from table "dis", which is joined with the sort result!
This happens because of the remove_const() call:
group_list= remove_const(this, group_list, conds,
|
rollup.state == ROLLUP::STATE_NONE,
|
&simple_group);
|
remove_const() calls const_expression_in_where() here:
if (cond && const_expression_in_where(cond,order->item[0]))
|
{
|
DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
|
continue;
|
}
|
where
order->item[0]= "dis.region_id"
|
cond=
|
is.nsc_id = 'MMD' and dis.dept_id = 'ADVB' and
|
convert(org.dept_id using utf8mb4) = 'ADVB' and
|
dis.district_id = convert(org.district_id using utf8mb4) and
|
dis.dept_id = org2.dept_id and dis.region_id = org2.region_id and
|
dis.district_id = org2.district_id and sub.the_date = org2.the_date and
|
org.ptn_id = sub.ptn_id
|
Note the "dis.region_id = org2.region_id" part in the WHERE.
Looks like it's ok?
|
|
... No, actually this is the reason why we get the wrong query result.
The wrong query output looks like this:
Incorrect Result:
|
+------------+--------+---------+-------------+
|
| the_date | org_id | dept_id | cco_stk_ttl |
|
+------------+--------+---------+-------------+
|
| 2021-08-12 | 10 | ADVB | 1 | WRONG: this and the next row should have been
|
| 2021-08-12 | 10 | ADVB | 4096 | WRONG: ...one row with cco_stk_ttl=4097!
|
|
| 2021-08-12 | 11 | ADVB | 2 |
|
|
| 2021-08-12 | 1 | ADVB | 1 | WRONG: this and the next row should have been
|
| 2021-08-12 | 1 | ADVB | 4098 | WRONG:... one row with cco_stk_ttl=4099!
|
|
| 2021-08-12 | 21 | ADVB | 96 |
|
| 2021-08-12 | 22 | ADVB | 256 |
|
| 2021-08-12 | 2 | ADVB | 352 |
|
| 2021-08-12 | MMD | ADVB | 4451 |
|
+------------+--------+---------+-------------+
|
Debugging how the first row read was obtained, I can see these table reads:
org2(the_date,dept_id,org_id,district_id,region_id)=(2021-08-12,ADVB,10,10,1)
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10001,4096)
|
org(dept_id,ptn_id,district_id)=(ADVB,10001,10)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(1,MMD,ADVB,10,1)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,11001,2)
|
org(dept_id,ptn_id,district_id)=(ADVB,11001,11)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(2,MMD,ADVB,11,1)
|
send_data
|
### ROW1!
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10771,512)
|
org(dept_id,ptn_id,district_id)=(ADVB,10771,23)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10741,64)
|
org(dept_id,ptn_id,district_id)=(ADVB,10741,21)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(3,MMD,ADVB,21,2)
|
send_data
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10740,32)
|
org(dept_id,ptn_id,district_id)=(ADVB,10740,21)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(3,MMD,ADVB,21,2)
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10580,8)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10499,16)
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10429,256)
|
org(dept_id,ptn_id,district_id)=(ADVB,10429,22)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(4,MMD,ADVB,22,2)
|
send_data
|
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,10002,1)
|
org(dept_id,ptn_id,district_id)=(ADVB,10002,10)
|
dis(id,nsc_id,dept_id,district_id,region_id)=(1,MMD,ADVB,10,1)
|
## ^ ROW2!
|
send_data
|
|
sub(the_date,ptn_id,cco_stk_ttl)=(2021-08-12,11003,1024)
|
send_data
|
|
msr(the_date,dept_id,region_id,district_id,cco_stk_ttl)=(2021-08-12,ADVB,1,10,1)
|
msr(the_date,dept_id,region_id,district_id,cco_stk_ttl)=(2021-08-12,ADVB,1,10,4096)
|
|
|
Looking at the above and subquery's GROUP BY clause:
GROUP BY sub.the_date, dis.dept_id, dis.region_id, dis.district_id
|
FIXED FIXED Changes
|
In the considered record combinations, region_id is 1 initially, then it changes to 2, and then back to 1!
This is why two rows from the same group were not grouped together in the query's output.
|
|
But one can clearly see that the table outside the subquery:
org2(the_date,dept_id,org_id,district_id,region_id)=(2021-08-12,ADVB,10,10,1)
|
has the value for region_id and it's 1! Why does the subquery enumerate record combinations that do not have region_id=1 ?
|
|
As shown a few comments above,
join->conds= .... AND dis.region_id = org2.region_id AND ....
|
however, it doesn't get attached to any table. make_join_select() calls make_cond_for_table( retain_ref_cond=false)
it makes this call:
/*
|
If cond is an equality injected for split optimization then
|
a. when retain_ref_cond == false : cond is removed unconditionally
|
(cond that supports ref access is removed by the preceding code)
|
b. when retain_ref_cond == true : cond is removed if it does not
|
support ref access
|
*/
|
if (left_item->type() == Item::FIELD_ITEM &&
|
is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) &&
|
Then, is_eq_cond_injected_for_split_opt looks into join->spl_opt_info->inj_cond_list which has these conditions:
dis.dept_id = org2.dept_id
|
dis.region_id = org2.region_id // Match
|
dis.district_id = org2.district_id
|
sub.the_date = org2.the_date
|
finds the condition there. This causes make_cond_for_table() to discard it.
|
|
A patch that fixes the problem described above:
http://lists.askmonty.org/pipermail/commits/2021-November/014779.html
|
LATERAL DERIVED can run WITH ROLLUP queries.
Suppose, the GROUP BY clause is
GROUP BY col1, col2, ... colN WITH ROLLUP
|
Suppose, LATERAL DERIVED code pushes down a condition on colK, that is, we have "colK=...".
The execution code is able to generate all super-aggregate rows that have summaries for columns col_N where N>K.
Note that the restriction "colK=..." means we are not interested in super-aggregate rows that have summaries over column colK. This also implies that we are not interested in super-aggregate rows that have summaries over any column col_J where J < K.
From this, it follows that LATERAL DERIVED optimization is capable of executing WITH ROLLUP queries.
|
|
igor, please review.
|
|
also pushed to bb-10.3-mdev26337
|
|
Takeaways from yesterday call:
- It is not clear why the suggested patch would NOT work (that is, no counterexamples were provided)
- The participants lack confidence in this patch and consider this fix a feature, not a bugfix.
- Decided to use approach #1: just disable LATERAL DERIVED for WITH ROLLUP queries.
|
|
http://lists.askmonty.org/pipermail/commits/2022-January/014833.html . This Implements the conclusions from the last call. igor, please review.
|
|
Here' the explanation why the current implementation of the split optimization cannot be used with ROLLUP.
Consider tables t1,t2
create table t1 (a int, b int, c int)
|
create table t2 (a int, b int, c int, key (a,b));
|
and the query
select s from t1, (select sum(c) as s from t2 group by a,b) dt where t1.a=dt.a and t1.b=dt.b and t1.a in (1,3,5) and t1.b between 2 and 4;
|
In this case the split optimization can be applied that will return certain rows from dt. These rows can follow in any order. For example first for the group (a=3,b=2), then for the group (a=2,b=2), then for the group (a=5,b=4), then for the group (a=3,b=4). It's obviously that in this case we cannot have one register to calculate sum(c) with the same value of a.
|
|
Filed MDEV-27496 to figure out whether Split Materialized can be used with WITH ROLLUP.
|
|
Pushed the "basic" fix variant which makes Split Materialized not to be used for WITH ROLLUP queries.
|
z-custom.cnf