Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 11.3
Description
Brought up by Gabriel Ciciliani on LinkedIN:
The testcase:
create table t1 ( |
a int, |
b int, |
c int, |
d int, |
key(a,b,c) |
);
|
insert into t1 |
select |
A.seq, B.seq, C.seq, A.seq
|
from
|
seq_1_to_20 A,
|
seq_1_to_20 B,
|
seq_1_to_20 C;
|
SELECT makes use of the condition
MariaDB [test]> explain select * from t1 where (a,b,c)=(1,2,3);
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+-------+
|
| 1 | SIMPLE | t1 | ref | a | a | 15 | const,const,const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+-------+
|
1 row in set (0.002 sec)
|
While UPDATE doesn't:
MariaDB [test]> explain update t1 set d=3333 where (a,b,c)=(1,2,3);
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 8184 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
Looking at the Optimizer Trace for SELECT, I can see that tuple-based equality was converted into multiple equalities:
"join_optimization": {
|
"select_id": 1,
|
"steps": [
|
{
|
"condition_processing": {
|
"condition": "WHERE",
|
"original_condition": "(t1.a,t1.b,t1.c) = (1,2,3)",
|
"steps": [
|
{
|
"transformation": "equality_propagation",
|
"resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b) and multiple equal(3, t1.c)"
|
},
|
and then the range optimizer produced a 3-keypart interval.
For the UPDATE, equality transformation is not performed. I guess, range optimizer is not able to handle the original (a,b,c)=(1,2,3)