Description:
Subquery executed more times than expected.
How to repeat:
create table t1 (id int primary key);
create table t2 (id int priamry key, col1 int, key index_col1 (col1));
insert into t1 values (1);
insert into t2 values (1, 1);
We execute a SQL:
select t1.id as t1_id from t1 LEFT JOIN t2 ON t2.id = (SELECT t2.id FROM t2 WHERE t2.col1 = t1.id);
Since there is only one records in `t1`, the subquery `(SELECT t2.id FROM t2 WHERE t2.col1 = t1.id)` should also be executed once. However, this subquery is executed three times. We can enable optimzier_trace to verify that this subquery was indeed executed three times.
optimizer_trace:
{
"join_execution": {
"select#": 1,
"steps": [
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
]
}
}
]
}
},
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
]
}
}
]
}
},
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
]
}
}
]
}
}
]
}
}
It can be found that the number of executions of subquery is significantly more than expected.
Suggested fix:
The problem occurs because the subquery is executed every time the `Item_singlerow_subselect::val_int()` function is called.
In the query plan, there will be three calls to this function.
1. construct_lookup: Create new key for lookup
2. Index_lookup::impossible_null_ref: Perform "Late NULLs Filtering"
3. Item_func_eq::val_int: Validate the filter predicate.
These three calls execute the same subquery three times.
Description: Subquery executed more times than expected. How to repeat: create table t1 (id int primary key); create table t2 (id int priamry key, col1 int, key index_col1 (col1)); insert into t1 values (1); insert into t2 values (1, 1); We execute a SQL: select t1.id as t1_id from t1 LEFT JOIN t2 ON t2.id = (SELECT t2.id FROM t2 WHERE t2.col1 = t1.id); Since there is only one records in `t1`, the subquery `(SELECT t2.id FROM t2 WHERE t2.col1 = t1.id)` should also be executed once. However, this subquery is executed three times. We can enable optimzier_trace to verify that this subquery was indeed executed three times. optimizer_trace: { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ ] } } ] } }, { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ ] } } ] } } ] } } It can be found that the number of executions of subquery is significantly more than expected. Suggested fix: The problem occurs because the subquery is executed every time the `Item_singlerow_subselect::val_int()` function is called. In the query plan, there will be three calls to this function. 1. construct_lookup: Create new key for lookup 2. Index_lookup::impossible_null_ref: Perform "Late NULLs Filtering" 3. Item_func_eq::val_int: Validate the filter predicate. These three calls execute the same subquery three times.