|
The patch in upstream that we are missing:
commit 98b0d40af27051ded465a5ca81fac8e3110cc2c1
|
Author: Manuel Ung <mung@fb.com> Tue Mar 19 16:44:54 2019
|
Committer: Facebook Github Bot <facebook-github-bot@users.noreply.github.com> Fri Apr 5 22:13:46 2019
|
|
Fix error handling in group by plans
|
|
Summary:
|
For certain GROUP BY plans, the QUICK_RANGE_SELECT class is used to iterate through all possible prefixes that form a group. The current iteration process calls read_range_first with both a start and end range, and then iterates until we've moved past the end range. However, the storage engine may choose to honour the end range (because of index condition pushdown, rocksdb iterator bounds, or rocksdb prefix seek mode) and return HA_ERR_KEY_NOT_FOUND or HA_ERR_END_OF_FILE instead. Instead of moving onto the next range, this causes the QUICK_RANGE_SELECT::get_next_prefix to return early without actually advancing onto the next prefix. This can cause an infinite loop in QUICK_GROUP_MIN_MAX_SELECT::get_next, or QUICK_GROUP_MIN_MAX_SELECT::get_next to return HA_ERR_END_OF_FILE too early.
|
|
The fix is to detect if HA_ERR_KEY_NOT_FOUND or HA_ERR_END_OF_FILE is returned, and continue onto the next range instead of returning early.
|
|
Reviewed By: hermanlee
|
|
Differential Revision: D14521084
|
|
fbshipit-source-id: 6963b17081f
|
|
|
create table t2 (
|
a1 char(64),
|
a2 char(64) not null,
|
b char(16),
|
c char(16),
|
d char(16),
|
dummy char(248) default ' '
|
) engine=RocksDB;
|
create index idx_t2_0 on t2 (a1);
|
create index idx_t2_1 on t2 (a1,a2,b,c);
|
create index idx_t2_2 on t2 (a1,a2,b);
|
select
|
a1,a2,b,max(c),min(c)
|
from
|
t1
|
where
|
(a2 = 'a' or a2 = 'b') and (b = 'b')
|
group by a1;
|
In MariaDB and MySQL-8, loose index scan is not applicable. In FB/MySQL-8.0, it is applicable.
Optimizer trace in MySQL-8.0 shows:
"group_index_range": {
|
"potential_group_range_indexes": [
|
{
|
"index": "idx_t1_0",
|
"usable": false,
|
"cause": "not_covering"
|
},
|
{
|
"index": "idx_t1_1",
|
"covering": true,
|
"usable": false,
|
"cause": "nonconst_equality_gap_attribute"
|
},
|
{
|
"index": "idx_t1_2",
|
"usable": false,
|
"cause": "not_covering"
|
}
|
]
|
|
|
Added by this patch:
commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3
|
Author: Manuel Ung <mung@fb.com>
|
Date: Thu Apr 19 23:06:27 2018 -0700
|
|
Enhance group-by loose index scan
|
|
Summary:
|
This enhances the current group by plan so that every "gap attribute" is allowed to have a disjunction of equality predicates. The predicates from different attributes must still be conjunctive to each other though, and this is all enforced in check_key_infix.
|
|
|
Because of the above and MySQL's "Extended GROUP BY" feature, the values of some columns in output results are different.
|
|
Another kind of discrepancy:
mysql> select version();
|
+------------------+
|
| version() |
|
+------------------+
|
| 5.6.35-debug-log |
|
+------------------+
|
1 row in set (0.00 sec)
|
mysql> select a1,a2,b,max(c),min(c) from t2 use index() where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
|
+------+----+------+--------+--------+
|
| a1 | a2 | b | max(c) | min(c) |
|
+------+----+------+--------+--------+
|
| a | a | b | p122 | e112 |
|
| b | a | b | p222 | e212 |
|
| c | a | b | p322 | e312 |
|
| d | a | b | p422 | e412 |
|
| e | a | b | NULL | NULL |
|
+------+----+------+--------+--------+
|
5 rows in set (0.01 sec)
|
mysql> select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1;
|
+------+----+------+--------+--------+
|
| a1 | a2 | b | max(c) | min(c) |
|
+------+----+------+--------+--------+
|
| a | b | b | p122 | e112 |
|
| b | b | b | p222 | e212 |
|
| c | b | b | p322 | e312 |
|
| d | b | b | p422 | e412 |
|
+------+----+------+--------+--------+
|
4 rows in set (0.00 sec)
|
The above shows I get it with upstream FB/MySQL, too?
|
|
Filed the above as a bug against the upstream https://github.com/facebook/mysql-5.6/issues/1036
|
|
Fixed the test in 10.2: https://github.com/MariaDB/server/commit/09a85692a65634cda40214b6693f34bec2f5b73b
|