Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.24
-
None
-
None
Description
This is copied from the comment on MDEV-32403 ( https://jira.mariadb.org/browse/MDEV-32403?focusedCommentId=284540&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-284540 )
RAND-PROPAGATES-OUT-OF-DERIVED-ISSUE:
A simpler testcase for this
create table t10 ( a int);
|
insert into t10 values (1),(2),(3);
|
create table t15 (a int);
|
insert into t15 values (1),(2);
|
select
|
(select count(*)
|
from (select * from t15 where a < rand()*10) TBL
|
)
|
from t10;
|
EXPLAIN shows UNCACHEABLE SUBQUERY:
+------+----------------------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+----------------------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
| 2 | UNCACHEABLE SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 3 | DERIVED | t15 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
|
+------+----------------------+------------+------+---------------+------+---------+------+------+-------------+
|
When I execute this with breakpoint on entry to JOIN::exec(), I can see
JOIN::exec() select_id=1
|
JOIN::exec() select_id=2
|
JOIN::exec() select_id=3
|
JOIN::exec() select_id=2
|
JOIN::exec() select_id=2
|
Note that select_id=3 is executed once. select_id=2 is executed many times although that doesn't make any sense. This probably should be addressed in a separate MDEV.