Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.9, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
-
Red Hat 8
Description
The following procedure crashes the server if it is called twice, even with no data, it succeeds if you add a limit:
CREATE TABLE `TEST` ( |
`objectid` int(11) NOT NULL, |
`submissionid` int(11) DEFAULT NULL, |
`objectname` varchar(255) DEFAULT NULL, |
`objecturl` varchar(1000) DEFAULT NULL, |
`reviewercomment` varchar(1000) DEFAULT NULL, |
`isactive` tinyint(1) DEFAULT NULL, |
`createdon` datetime DEFAULT NULL, |
`createdby` varchar(255) DEFAULT NULL, |
`updatedon` datetime DEFAULT NULL, |
`updatedby` varchar(255) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
|
MariaDB [mytest]> DELIMITER $$
|
MariaDB [mytest]> CREATE OR REPLACE PROCEDURE `testsp_doesnot_works`() |
BEGIN |
SET SESSION group_concat_max_len = 100000000000; |
|
SELECT JSON_EXTRACT( |
IFNULL(
|
(
|
SELECT CONCAT( |
'[', |
GROUP_CONCAT(
|
JSON_OBJECT(
|
'objectId', objectId, |
'objectName', objectName,'objectUrl', objectUrl,'reviewerComment', reviewerComment,'createdOn', DATE_FORMAT(createdOn,"%Y-%m-%dT%H:%i:%sZ") |
)
|
),
|
']' |
)
|
),
|
'[]' |
),
|
'$' |
) AS comments |
FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body; |
END$$ |
Query OK, 0 rows affected (0.004 sec) |
|
|
MariaDB [mytest]> DELIMITER ;
|
It succeeds if you replace:
FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body; |
|
|
with |
|
|
{code:sql}
|
FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST` LIMIT 50 ) AS body; |
Attachments
Activity
The change in Item_field::fix_fields() suggested above fixes both bugs MDEV-24454 and MDEV-25086. However it cannot fix the bug MDEV-25631 that was also fixed by the commit for MDEV-24454. Indeed the bugs MDEV-24454/MDEV-25086 and MDEV-25631 have nothing in common. The first two bug concern second executions of queries with a set function whose only argument is an outer reference to a field from a mergeable view or from a mergeable derived table. The bug MDEV-25631 crashes the server when executing a query with derived table specified as aggregating select from a view. Besides this view has to be specified as select with a subquery containing an outer reference. The following provides such crashing query:
create table t3 (c int); |
insert into t3 values (1); |
create view v3 as select c from t3 where (select t3.c from t3 t) = 1; |
select * from (select sum((select * from v3)) as r) dt; |
Note that if in the above query we use the derived table dt1 specified exactly as v3 we have no problems with the execution of the query:
MariaDB [test]> select * from (select sum((select * from (select c from t3 where (select t3.c from t3 t) = 1) dt1)) as r) dt;
|
+------+
|
| r |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.01 sec)
|
We also have no problems if we use CTE cte instead of dt1:
MariaDB [test]> with cte as (select c from t3 where (select t3.c from t3 t) = 1) select * from (select sum((select * from cte)) as r) dt;
|
+------+
|
| r |
|
+------+
|
| 1 |
|
+------+
|
Yet we immediately have the same kind of crash if we use cte twice like in the query:
with cte as (select c from t3 where (select t3.c from t3 t) = 1) |
select * from (select sum((select * from cte)) as r) dt1 |
union
|
select * from (select sum((select * from cte)) as r) dt2; |
Analysis of the bug MDEV-25631 shows that max_arg_level for sum() is set to 0, while it should be 1. It happens in this piece of code:
if (thd->lex->in_sum_func &&
|
thd->lex->in_sum_func->nest_level >= select->nest_level)
|
{
|
Item::Type ref_type= (*reference)->type();
|
set_if_bigger(thd->lex->in_sum_func->max_arg_level,
|
select->nest_level);
|
...
|
'select' above is the select for the specification of v3. Each view specification is parsed separately with its own LEX and the top select has level 0. Meanwhile the set function sum() is used in the main query and in a select whose nest level is 1. Comparing nest levels of selects belonging to different LEX structures does not make sense. Besides we know that an argument of any set function cannot belong to a view specification.
Because of this a possible update of thd->lex->in_sum_func->max_arg_level may be done only in the case
when
thd->lex == context->select_lex->parent_lex
|
Thus instead of the above code we should use the code:
if (thd->lex->in_sum_func &&
|
thd->lex == context->select_lex->parent_lex &&
|
thd->lex->in_sum_func->nest_level >= select->nest_level)
|
{
|
Item::Type ref_type= (*reference)->type();
|
set_if_bigger(thd->lex->in_sum_func->max_arg_level,
|
select->nest_level);
|
...
|
Similar changes should applied to all other pieces of code where thd->lex->in_sum_func->max_arg_level is updated.
A fix for this bug was pushed into 10.2. It should be merged upstream as it is.
If we return to 10.2 tree just before the commit 3690c549c6e72646ba74f6b4c83813ee4ac3aea4 (see above)
we get the same crash for the second execution of the prepared statement for the query
select (select sum(b) from t1 where a=1) from v1;as for the second execution of the prepared statement for the query