Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Issue seen in main.show_explain
Trace output:
------------------------
Capture : trace1.txt
replay : trace2.txt![]()
Capture Explain:
-----------
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 14 | | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | Using join buffer (flat, BNL join) | |
| 3 | SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 20 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
|
Replay Explain:
----------------
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 14 | Using where | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | Using join buffer (flat, BNL join) | |
| 3 | SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 20 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+ |
How to repro:
----------------
set optimizer_record_context=ON; |
|
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5); |
|
|
CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES |
(0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8),
|
(4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9);
|
|
|
create table t3 like t2; |
insert into t3 select * from t2; |
|
|
explain
|
SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias |
WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; |
SELECT context INTO DUMPFILE 'context1.txt' FROM INFORMATION_SCHEMA.OPTIMIZER_CONTEXT; |
source context1.txt
|
Attachments
Issue Links
- relates to
-
MDEV-39368 Add mtr --replay-server option to test Optimizer Context Replay
-
- In Progress
-