[MDEV-32878] Tuple comparison (kp1,kp2)=(1,2) is not sargable for DML queries Created: 2023-11-25  Updated: 2024-01-09

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6, 11.3
Fix Version/s: 11.4

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: optimizer-easy, optimizer-feature


 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)

 Comments   
Comment by Sergei Petrunia [ 2023-11-25 ]

It looks particularly odd with IN:

MariaDB [test]> explain update t1 set d=3333  where (a,b,c) in ((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)
 
 
 
MariaDB [test]> explain update t1 set d=3333  where (a,b,c) in ((1,2,3),(4,5,6));
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | range | a             | a    | 15      | NULL | 2    | Using where |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.002 sec)

Comment by Sergei Petrunia [ 2023-11-25 ]

lstartseva? I'm pretty sure it wasn't fixed but Please check the new versions

Comment by Sergei Petrunia [ 2023-11-25 ]

according to the reporter, MySQL 5.7 is not affected.

Comment by Lena Startseva [ 2023-11-27 ]

psergei, yes, bug is reproducing in v. 11.3

Generated at Thu Feb 08 10:34:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.