Details
Critical Description
PoC:
SELECT ( ( ( WITH x AS ( WITH x AS ( SELECT x FROM ( SELECT * FROM ( SELECT 'x' ) x ) x WHERE ( SELECT x * x ORDER BY ( SELECT 1 GROUP BY 1 IN ( SELECT x ) ) ) ) SELECT x FROM x WHERE x ) SELECT * FROM x ) ) ) ; |
docker log:
mariadbd(my_print_stacktrace+0x32)[0x556678eaa7c2]
|
mariadbd(handle_fatal_signal+0x488)[0x556678983cf8]
|
/lib/x86_64-linux-gnu/libc.so.6(+0x42520)[0x7f8dd27eb520]
|
mariadbd(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x31b)[0x5566789ae8ab]
|
mariadbd(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x415)[0x5566789af675]
|
mariadbd(_ZN20Item_direct_view_ref10fix_fieldsEP3THDPP4Item+0x11b)[0x5566789b04ab]
|
mariadbd(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x8c)[0x5566789f4fec]
|
mariadbd(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x139)[0x5566789ce5c9]
|
mariadbd(_ZN4JOIN14optimize_innerEv+0x69b)[0x55667878fb5b]
|
mariadbd(_ZN4JOIN8optimizeEv+0xda)[0x556678790e2a]
|
mariadbd(+0x7ec60c)[0x5566786ca60c]
|
mariadbd(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0x95)[0x5566786c9e35]
|
mariadbd(_ZN4JOIN14optimize_innerEv+0xb27)[0x55667878ffe7]
|
mariadbd(_ZN4JOIN8optimizeEv+0xda)[0x556678790e2a]
|
mariadbd(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x115)[0x5566786eaa55]
|
mariadbd(_ZN4JOIN28optimize_constant_subqueriesEv+0x35)[0x556678889d55]
|
mariadbd(_ZN4JOIN14optimize_innerEv+0x503)[0x55667878f9c3]
|
mariadbd(_ZN4JOIN8optimizeEv+0xda)[0x556678790e2a]
|
mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd1)[0x556678790f21]
|
mariadbd(_Z13handle_selectP3THDP3LEXP13select_resulty+0x154)[0x556678791774]
|
mariadbd(+0x826f55)[0x556678704f55]
|
mariadbd(_Z21mysql_execute_commandP3THDb+0x419e)[0x556678713f0e]
|
mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x556678715237]
|
mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x14bd)[0x556678717a1d]
|
mariadbd(_Z10do_commandP3THDb+0x138)[0x556678719818]
|
mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3bf)[0x5566788413af]
|
mariadbd(handle_one_connection+0x5d)[0x5566788416fd]
|
mariadbd(+0xcd1906)[0x556678baf906]
|
/lib/x86_64-linux-gnu/libc.so.6(+0x94b43)[0x7f8dd283db43]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x44)[0x7f8dd28cebb4]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f8d700130d8): SELECT ( ( ( WITH x AS ( WITH x AS ( SELECT x FROM ( SELECT * FROM ( SELECT 'x' ) x ) x WHERE ( SELECT x * x ORDER BY ( SELECT 1 GROUP BY 1 IN ( SELECT x ) ) ) ) SELECT x FROM x WHERE x ) SELECT * FROM x ) ) )
|
|
|
Connection ID (thread ID): 4
|
Status: NOT_KILLED
|
|
|
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
|
Attachments
Issue Links
- relates to
-
MDEV-29300 Assertion `*ref && (*ref)->fixed()' failed in Item_field::fix_outer_field on SELECT
-
- Confirmed
-
-
MDEV-36374 Item_subselect::used_tables_cache assertion check.
-
- Open
-
Activity
Crashes in this call in JOIN::optimize_inner :
if (select_lex->cond_pushed_into_where) |
{
|
conds= and_conds(thd, conds, select_lex->cond_pushed_into_where);
|
=> if (conds && conds->fix_fields(thd, &conds)) |
SELECT * FROM |
(
|
SELECT * FROM |
(
|
SELECT * FROM |
(SELECT 1 as 'x') dt2 |
) dt1
|
WHERE |
(
|
SELECT x+1 ORDER BY |
(
|
SELECT 1 GROUP BY |
(1 IN (SELECT x)) |
)
|
)
|
) dt
|
WHERE x = 1; |
This issue starts when code from commit ccec9b1de95a is integrated at merge commit 5bada1246de4.
It marks a derived table that was mergeable as now needing to be derived.
Here's a variation of @alice's test case that causes a crash of the same kind on a debug build of the current 10.6 :
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT 1 as x |
) dt2
|
) dt1
|
WHERE ( |
SELECT dt1.x+1 |
ORDER BY ( |
SELECT 1 |
GROUP BY (1 IN (SELECT dt1.x)) |
)
|
) != 0
|
) dt
|
WHERE dt.x>=1; |
The crash happens when the optimizer tries to apply the method fix_fields() to the condition dt.x >= 1 to the where condition of the derived table dt considered as non-mergeable (why it's si I'll explain later). At this moment the specification of dt looks like this:
(gdb) p dbug_print_select(select_lex)
|
$124 = 0x555557bd1a60 <dbug_item_print_buf> "select dt2.x AS x from (select 1 AS x) dt2 where dt2.x + 1 <> 0 and dt2.x >= 1"
|
The type of the left part of the pushed condition is actually REF_ITEM of the class Item_direct_view_ref. So Item_direct_view_ref::fix_fields() is called for it with view->alias pointing to dt1 and (*ref) pointing to the Item_field dt2.x. As this field has not been fixed yet Item_field::fix_fields() is called for it. When the latter is invoked it first calls find_field_in_tables() and we can see that the third parameter of the call context->first_name_resolution_table, for some reason, is equal to null. As an obvious result this call fails to resolve dt2.x after which find_item_in_list() also fails to resolve the field reference and we come to the call of Item_field::fix_outer_field() that causes a crash because of the bug MDEV-32294. Anyway the flow control should not have come to this point and the field reference dt2.x should have been resolved in the call of find_field_in_tables(). The problem appears due to a wrong value of the third parameter of the call.
In 10.5 the above query doesn't cause any crash and returns the expected result:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT 1 as x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (
|
-> SELECT dt1.x+1
|
-> ORDER BY (
|
-> SELECT 1
|
-> GROUP BY (1 IN (SELECT dt1.x))
|
-> )
|
-> ) != 0
|
-> ) dt
|
-> WHERE dt.x>=1;
|
+---+
|
| x |
|
+---+
|
| 1 |
|
+---+
|
The fact is that the derived table is considered as mergeable in 10.5 and we push the condition:
1 >= 1 and 1 + 1 <> 0
|
into
select 1 AS x
|
Here dt2 is materialized while dt1 and dt are merged because both are considered as mergeable. Merging of mergeable derived tables starts from merging the most inner table. In our case it's the table dt1. But before the first merge the order by clause is eliminated. Thus the result of the merge is the select
SELECT dt.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT 1 as x |
) dt2
|
WHERE ( |
SELECT dt2.x+1 |
) != 0
|
) dt
|
WHERE dt.x>=1; |
Indeed in debugger we see:
gdb p dbug_print_select(this)
|
$10 = 0x555557b87600 <dbug_item_print_buf> "select dt2.x AS x from (select dt2.x AS x from ((select 1 AS x) dt2) where dt2.x + 1 <> 0) dt where dt2.x >= 1"
|
Here we have to take into account that the occurrences of dt2.x in the upper select are actually wrapped in Item_direct_view_ref objects for dt.
After the merge of dt we have:
SELECT dt2.x
|
FROM
|
(
|
SELECT 1 as x
|
) dt2
|
WHERE (
|
SELECT dt2.x+1
|
) != 0
|
AND
|
dt2.x>=1;
|
And in debugger we see:
(gdb) p dbug_print_select(select_lex)
|
$18 = 0x555557b87600 <dbug_item_print_buf> "select dt2.x AS x from (select 1 AS x) dt2 where dt2.x >= 1 and dt2.x + 1 <> 0"
|
Now we see that the where condition can be pushed into the materialized derived table. After the push we have
(gdb) p dbug_print_select(select_lex)
|
$19 = 0x555557b87600 <dbug_item_print_buf> "select 1 AS x where 1 >= 1 and 1 + 1 <> 0"
|
and there is no field references that we have to fix the pushed condition
In contrast to 10.5 in 10.6 we have only the first merge. Thus we have to fix the field reference dt2.x of the condition pushed into dt.
Let's modify the test case to force the optimizer not to merge the derived table dt for both 10.6 and 10.5.
# Q1
|
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT 1 as x |
) dt2
|
) dt1
|
WHERE ( |
SELECT dt1.x+1 |
ORDER BY ( |
SELECT 1 |
GROUP BY (1 IN (SELECT dt1.x)) |
)
|
) != 0
|
GROUP BY dt1.x |
) dt
|
WHERE dt.x>=1; |
In 10.6 the query causes the same crash as the previous query. In 10.5 an execution of the query leads to a bogus error message:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT 1 as x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (
|
-> SELECT dt1.x+1
|
-> ORDER BY (
|
-> SELECT 1
|
-> GROUP BY (1 IN (SELECT dt1.x))
|
-> )
|
-> ) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
ERROR 1109 (42S02): Unknown table 'dt2' in on clause
|
Yet in both cases the failure is due to the fact that context->first_name_resolution_table == NULL for the field reference dt2.x from the condition dt2.x>=1 pushed into the derived table dt.
Let' s modify our test case to make it more generic:
CREATE TABLE t1 (a int); |
INSERT INTO t1 VALUES (2), (1), (3), (3), (1); |
# Q2
|
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT t1.a as x FROM t1 WHERE t1.a < 2 GROUP BY t1.a |
) dt2
|
) dt1
|
WHERE ( |
SELECT dt1.x+1 |
ORDER BY ( |
SELECT 1 |
GROUP BY (1 IN (SELECT dt1.x)) |
)
|
) != 0
|
GROUP BY dt1.x |
) dt
|
WHERE dt.x>=1; |
When running this query on 10.5/10.6 we see the same failures as for the previous query.
If we try to get rid of the eliminated order by clause we may come to the following test case:
# Q3
|
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x |
) dt2
|
) dt1
|
WHERE (SELECT 1 WHERE (1 IN (SELECT dt1.x))) != 0 |
GROUP BY dt1.x |
) dt
|
WHERE dt.x>=1; |
In 10.5 this query fails in the same way as the previous query.
In 10.6 we have
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT 1 WHERE (1 IN (SELECT dt1.x))) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'ON'
|
Again in both cases the failure is due to the fact that context->first_name_resolution_table == NULL for the field reference dt2.x from the condition dt2.x>=1 pushed into the derived table dt.
More generalization of the test case brings us to:
CREATE TABLE t2 (a int); |
INSERT INTO t2 VALUES (2), (1); |
# Q4
|
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x |
) dt2
|
) dt1
|
WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 |
GROUP BY dt1.x |
) dt
|
WHERE dt.x>=1; |
In 10.5 we have:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'on clause'
|
In 10.6 we have:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'ON'
|
The reason for the failures is the same as for the previous query.
Note that the following query using table t3 created and populated by the commands
CREATE TABLE t3 (b int); |
INSERT INTO t3 VALUES (1), (2); |
works fine in 10.5 as well as in 10.6:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x FROM t3)) AND t2.a < 2) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
Now let's check how the last failing query is executed in prepared mode.
First check it in 10.5.
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT dt.x
|
"> FROM
|
"> (
|
"> SELECT dt1.x
|
"> FROM
|
"> (
|
"> SELECT dt2.x
|
"> FROM
|
"> (
|
"> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
"> ) dt2
|
"> ) dt1
|
"> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
"> GROUP BY dt1.x
|
"> ) dt
|
"> WHERE dt.x>=1;
|
"> ";
|
Query OK, 0 rows affected (0.002 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'field list'
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'field list'
|
We see that the first and the second execution return the same bogus message and it differs from that returned by conventional execution of the query.
In 10.6 we see a similar picture:
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT dt.x
|
"> FROM
|
"> (
|
"> SELECT dt1.x
|
"> FROM
|
"> (
|
"> SELECT dt2.x
|
"> FROM
|
"> (
|
"> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
"> ) dt2
|
"> ) dt1
|
"> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
"> GROUP BY dt1.x
|
"> ) dt
|
"> WHERE dt.x>=1;
|
"> ";
|
Query OK, 0 rows affected (0.006 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'SELECT'
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'SELECT'
|
In 10.5 prepared query Q1,Q2,Q3 return the same bogus error message for the first and the second executions:
MariaDB [test]> EXECUTE stmt;
|
ERROR 1109 (42S02): Unknown table 'dt2' in field list
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1109 (42S02): Unknown table 'dt2' in field list
|
while in 10.6 prepared Q1 and Q2 crash at the first execution and prepared Q3 returns:
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'SELECT'
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'dt2.x' in 'SELECT'
|
It's interesting that prepared Q5 returns a wrong result for the second execution both in 10.5 and 10.6:
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT dt.x
|
"> FROM
|
"> (
|
"> SELECT dt1.x
|
"> FROM
|
"> (
|
"> SELECT dt2.x
|
"> FROM
|
"> (
|
"> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
"> ) dt2
|
"> ) dt1
|
"> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x FROM t3)) AND t2.a < 2) != 0
|
"> GROUP BY dt1.x
|
"> ) dt
|
"> WHERE dt.x>=1;
|
"> ";
|
Query OK, 0 rows affected (0.027 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.005 sec)
|
|
|
MariaDB [test]> EXECUTE stmt;
|
Empty set (0.002 sec)
|
Let's investigate why we have a problem when fixing dt2.x from the expression dt2.x >=1 pushed into the derived table dt. This dt2.x is a full copy of dt2.x from the item list of the select that we get after having merged dt1. Due to this merge this occurrence of dt2.x is wrapped into an Item_direct_view_ref reference item. Another occurrence of
dt2.x appears after the merge in (SELECT dt1.x). It's also wrapped into an Item_direct_view_ref reference item. Bear in mind that both wrapper reference item points to the same dt2.x in the translation table created for dt1. We see that by the time when the first item Item_direct_view_ref(dt2.x) is used to create a copy of it to replace dt1.x from the expression dt1.x>= 1 to be pushed into dt the context structure of dt2.x from the translation table is already incorrect. Let's check when the pointer to this structure is changed. It's changed in the function:
bool Item_ident::remove_dependence_processor(void * arg)
|
{
|
DBUG_ENTER("Item_ident::remove_dependence_processor");
|
if (get_depended_from() == (st_select_lex *) arg)
|
depended_from= 0;
|
context= &((st_select_lex *) arg)->context;
|
DBUG_RETURN(0);
|
}
|
when we execute the following code:
/*
|
it is single select without tables => possible optimization
|
remove the dependence mark since the item is moved to upper
|
select and is not outer anymore.
|
*/
|
where_item->walk(&Item::remove_dependence_processor, 0,
|
select_lex->outer_select());
|
Here we have:
(gdb) p dbug_print_select(select_lex)
|
$8 = 0x555557b87600 <dbug_item_print_buf> "select dt2.x"
|
(gdb) p dbug_print_item(where_item)
|
$9 = 0x555557b87600 <dbug_item_print_buf> "dt2.x"
|
(gdb) p where_item->type()
|
$10 = Item::REF_ITEM
|
(gdb) p ((Item_ref*)where_item)->ref_type()
|
$11 = Item_ref::VIEW_REF
|
(gdb) p ((Item_direct_view_ref*)where_item)->view->alias
|
$12 = {str = 0x7fff74019b30 "dt1", length = 3}
|
(gdb) p (*(((Item_direct_view_ref*)where_item)->ref))->type()
|
$14 = Item::FIELD_ITEM
|
(gdb) p dbug_print_item(*(((Item_direct_view_ref*)where_item)->ref))
|
$15 = 0x555557b87600 <dbug_item_print_buf> "dt2.x"
|
We see that the statement:
context= &((st_select_lex *) arg)->context;
|
effectively sets the new context of the occurrence dt2.x in the select list. After this change we have the context with:
(gdb) p dbug_print_select(((st_select_lex *) arg)->context.select_lex)
|
$38 = 0x555557b87600 <dbug_item_print_buf> "select t2.a from t2 where 1 in (subquery#6) and t2.a < 2"
|
gdb) p ((st_select_lex *) arg)->context.first_name_resolution_table->alias
|
$39 = {str = 0x7fff7801a8e0 "t2", length = 2}
|
dt2.x cannot be resolved a a field of t2.
The context of Item_direct_view_ref wrapper around dt2.x is also changed and in the same way.
Note that only the occurrence of dt2.x in the IN predicate is an outer reference. And
get_depended_from() != NULL only for this occurrence.
The following implementation of Item_ident::remove_dependence_processor could fix this problem:
bool Item_ident::remove_dependence_processor(void * arg)
|
{
|
DBUG_ENTER("Item_ident::remove_dependence_processor");
|
if (get_depended_from() == (st_select_lex *) arg)
|
{
|
depended_from= 0;
|
context= &((st_select_lex *) arg)->context;
|
}
|
DBUG_RETURN(0);
|
}
|
And indeed with this change we have:
MariaDB [test]> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
-> GROUP BY dt1.x
|
-> ) dt
|
-> WHERE dt.x>=1;
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
and all tests from the main test suite pass, and with --ps-protocol and --view-protocol as well.
Yet the query itself returns the empty set when executed in prepared mode.
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT dt.x
|
"> FROM
|
"> (
|
"> SELECT dt1.x
|
"> FROM
|
"> (
|
"> SELECT dt2.x
|
"> FROM
|
"> (
|
"> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
"> ) dt2
|
"> ) dt1
|
"> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
"> GROUP BY dt1.x
|
"> ) dt
|
"> WHERE dt.x>=1;
|
"> ";
|
Query OK, 0 rows affected (0.002 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
Empty set ( 0.003 sec)
|
Moreover, even for the query to which no pushdown into derived can be applied:
SELECT dt.x
|
FROM
|
(
|
SELECT dt1.x
|
FROM
|
(
|
SELECT dt2.x
|
FROM
|
(
|
SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
) dt2
|
) dt1
|
WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
GROUP BY dt1.x
|
) dt;
|
we see the same wrong result when executing the query in prepared mode:
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT dt.x
|
"> FROM
|
"> (
|
"> SELECT dt1.x
|
"> FROM
|
"> (
|
"> SELECT dt2.x
|
"> FROM
|
"> (
|
"> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
"> ) dt2
|
"> ) dt1
|
"> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
"> GROUP BY dt1.x
|
"> ) dt;
|
"> ";
|
Query OK, 0 rows affected (0.002 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
Empty set (0.003 sec)
|
Let's investigate why it happens.
When processing the EXECUTE command at the call of JOIN::prepare for the query we see:
(gdb) p dbug_print_select(select_lex)
|
$40 = 0x555557b87600 <dbug_item_print_buf> "select dt.x AS x from (select dt1.x AS x from (select dt2.x AS x from (select t1.x AS x from t1 where t1.x < 2 group by t1.x) dt2) dt1 where (subquery#5) <> 0 group by dt1.x) dt"
|
and subquery#5 here looks like this:
(gdb) p dbug_print_select(select_lex)
|
$44 = 0x555557b87600 <dbug_item_print_buf> "select t2.a from t2 where 1 = dt2.x and t2.a < 2"
|
Here dt2.x in the expression "1 = dt2.x" is actually wrapped into an Item_direct_view_ref object. When fix_fields is called for the where condition of subquery#5 it calls fix_fields for the expression. The latter calls fix_fields for Item_direct_view_ref around dt2.x. Note that dt2.x is the item from the translation table created for the mergeable derived table dt1. Because dt2.x is also used in the item list of select containing subquery#5 it has already been fixed and no new fix_fields is called for the occurrence of dt2.x from subquery#5. As a result no Item_field::fix_outer_field is called for this occurrence of dt2.x and the list upper_refs remains empty for the subquery#5. It leads to a wrong re-calculation of used_tables_cache for the subquery in the call of st_select_lex::update_used_tables() at the optimization stage after which the subquery mistakenly is treated as non-correlated.
Opposed to the occurrence of dt2.x from the item list of the select that uses subquery#5 the occurrence of dt2.x in this subquery preserves an Item_direct_view_ref wrapper while such wrapper for the first occurrence is rolled back at the end of processing the PREPARE command. The wrapper around the second occurrence has not been rolled back due to the conversion of the predicate 1 IN (SELECT dt1.x) into the expression 1 = dt1.x done when the PREPARE command was executed.
If we do the conversion of the predicate 1 IN (SELECT dt1.x) into the expression 1 = dt1.x at the first execution of the prepared statement rather than when processing the PREPARE command we'll get an empty result set at the second execution. After having the following results for the procedure:
CREATE PROCEDURE p() |
SELECT dt.x |
FROM |
(
|
SELECT dt1.x |
FROM |
(
|
SELECT dt2.x |
FROM |
(
|
SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x |
) dt2
|
) dt1
|
WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 |
GROUP BY dt1.x |
) dt;
|
MariaDB [test]> CALL p();
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.003 sec)
|
|
|
Query OK, 0 rows affected (0.003 sec)
|
|
|
MariaDB [test]> CALL p();
|
Empty set (0.003 sec)
|
we easily can predict it.
Summing up a full solution for this bug requires a resolution of the problem of wrong re-calculation of used_tables_cache for subqueries with outer references. Such re-calculation has to be done after any permanent transformation. The problem is reported in MDEV-32294.
With the latest patch from https://github.com/MariaDB/server/tree/MDEV-32294-Work_In_Progress
https://github.com/MariaDB/server/commit/f8e2caaa4036cece9368831d15670594a7e2e1d1
We have this
MariaDB [test]> CREATE PROCEDURE p()
|
-> SELECT dt.x
|
-> FROM
|
-> (
|
-> SELECT dt1.x
|
-> FROM
|
-> (
|
-> SELECT dt2.x
|
-> FROM
|
-> (
|
-> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x
|
-> ) dt2
|
-> ) dt1
|
-> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0
|
-> GROUP BY dt1.x
|
-> ) dt;
|
Query OK, 0 rows affected (0.031 sec)
|
|
|
MariaDB [test]> call p();
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.012 sec)
|
|
|
Query OK, 0 rows affected (0.013 sec)
|
|
|
MariaDB [test]> call p();
|
+------+
|
| x |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.009 sec)
|
I'm still investigating some wrong results from queries i've detailed in MDEV-35859
Thank you!
Reproducible as described on 10.6-11.2. Probably ~ to MDEV-29300
) dtVersion: '10.6.16-MariaDB-debug-log'231009 12:54:49 [ERROR] mysqld got signal 11 ;Server version: 10.6.16-MariaDB-debug-log source revision: 8941bdc4748a94c1207723d86a8e46df8ae129b3sql/signal_handler.cc:241(handle_fatal_signal)[0x562bbb08df76]sigaction.c:0(__restore_rt)[0x7f49f1f02420]sql/item.cc:5650(Item_field::fix_outer_field(THD*, Field**, Item**))[0x562bbb115afb]sql/item.cc:6086(Item_field::fix_fields(THD*, Item**))[0x562bbb119661]sql/item.h:1150(Item::fix_fields_if_needed(THD*, Item**))[0x562bba542c7b]sql/item.cc:9159(Item_direct_view_ref::fix_fields(THD*, Item**))[0x562bbb135042]sql/item.h:1150(Item::fix_fields_if_needed(THD*, Item**))[0x562bba542c7b]sql/item_func.cc:350(Item_func::fix_fields(THD*, Item**))[0x562bbb1e9d58]sql/item.h:1150(Item::fix_fields_if_needed(THD*, Item**))[0x562bba542c7b]sql/item.h:1159(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x562bba542cb5]sql/item.h:1164(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x562bba680e41]sql/item_cmpfunc.cc:5046(Item_cond::fix_fields(THD*, Item**))[0x562bbb18ed14]sql/sql_select.cc:2223(JOIN::optimize_inner())[0x562bba8c07a5]sql/sql_select.cc:1868(JOIN::optimize())[0x562bba8bcd21]sql/sql_derived.cc:1045(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x562bba718202]sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x562bba712844]sql/sql_select.cc:2347(JOIN::optimize_inner())[0x562bba8c215a]sql/sql_select.cc:1868(JOIN::optimize())[0x562bba8bcd21]sql/sql_select.cc:5077(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x562bba8de8ae]sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x562bba8ae539]sql/sql_parse.cc:6285(execute_sqlcom_select(THD*, TABLE_LIST*))[0x562bba80ef4e]sql/sql_parse.cc:3961(mysql_execute_command(THD*, bool))[0x562bba7fd91d]sql/sql_parse.cc:8050(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x562bba81a2f1]sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x562bba7effcd]sql/sql_parse.cc:1409(do_command(THD*, bool))[0x562bba7ecd0e]sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x562bbac6d3e1]sql/sql_connect.cc:1320(handle_one_connection)[0x562bbac6cd3e]perfschema/pfs.cc:2203(pfs_spawn_thread)[0x562bbb90d0fa]nptl/pthread_create.c:478(start_thread)[0x7f49f1ef6609]Query (0x62b0000c42a8): SELECT x FROM(SELECT x FROM ( SELECT * FROM ( SELECT 'x' ) x ) dt1WHERE ( SELECT x * x ORDER BY ( SELECT 1 GROUP BY 1 IN ( SELECT x ) ) )) dtWHERE x