Details
Description
The following query returns a different execution plan and a significantly different execution time. The following repro is exactly same as the initial bug report in MENT.
|
|
MariaDB [sysbench]> explain select a.* from sysbench.sbtest1 a left join sysbench2.sbtest1 as b on a.id = b.id where a.id = 1000 order by b.id; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
2 rows in set (0.000 sec) |
|
MariaDB [sysbench]> explain select a.* from sysbench.sbtest1 a left join sysbench2.sbtest1 as b on a.id = b.id where a.id in ( 1000, 1000) order by b.id; |
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|
| 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using temporary; Using filesort | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sysbench.a.id | 1 | Using index | |
+------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|
2 rows in set (0.000 sec) |
|
Attachments
Issue Links
- causes
-
MDEV-29895 prepared view crash server (unit.conc_view)
-
- Closed
-
Activity
The problem was mix of temporary and permanent changes in the conditions:
1. we make copy of condition for future use in the statement memory which include creating the list of AND arguments
2. temporary optimisation change IN to = in the list of the arguments and write a rollback
3. other optimisation remove the AND (I do not heck if it is temporary or permanent, but delete was called for the ist and so memory "poisoned by user")
(but still live in the statement memory)
4. we are trying to rollback user poisoned memory to rollback IN to =
see MDEV-29895
Review input provided on slack and on a call.