MDEV-28502SIGSEGV in Item_subselect::is_expensive (+ other SIGSEGV's in MS 8.0) and 2x UBSAN: runtime error: member call on null pointer of type 'struct st_select_lex' in Item_subselect::is_expensive and in st_select_lex::next_select
Closed
MDEV-28614Server crash in item_subselect.cc:6898 in Item_subselect::init_expr_cache_tracker(THD*)
Stalled
MDEV-29411SIGSEGV's st_select_lex_unit::set_limit and st_select_lex::get_offset, and Assertion `!eliminated' failed in Item_subselect::exec on SELECT, UBSAN: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit
Confirmed
MDEV-34086MariaDB Server crashes at _ZN10Item_equal7val_intEv
Closed
includes
MDEV-30842Item_subselect::get_cache_parameters and UBSAN member access within null pointer of type 'struct st_select_lex' in Item_subselect::get_cache_parameters on INSERT
Stalled
is duplicated by
MDEV-32309Server crashes at Item_subselect::is_expensive
Closed
MDEV-32311Server crashes at st_select_lex_unit::save_union_explain
Closed
MDEV-32390Segmentation fault at /mariadb-11.3.0/sql/sql_lex.cc:4227
Closed
MDEV-32391Segmentation fault at /mariadb-11.3.0/sql/sql_lex.h:1399
Closed
MDEV-32425Segmentation fault at /mariadb-11.3.0/sql/sql_lex.cc:5998
There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary.
A comment in JOIN::prepare
Permanently remove redundant parts from the query if
1) This is a subquery
2) This is the first time this query is optimized (since the
transformation is permanent
3) Not normalizing a view. Removal should take place when a
query involving a view is optimized, not when the view
is created
implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution.
The very simplest solution I've found lies in bb-10.4-MDEV-28621-no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.
Rex Johnston
added a comment - - edited There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary.
A comment in JOIN::prepare
Permanently remove redundant parts from the query if
1) This is a subquery
2) This is the first time this query is optimized (since the
transformation is permanent
3) Not normalizing a view. Removal should take place when a
query involving a view is optimized, not when the view
is created
implies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution.
The very simplest solution I've found lies in bb-10.4- MDEV-28621 -no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.
about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error.
Trying various databases:
createtable t1 (a int);
insertinto t1 values (1),(2),(3);
createtable t2 (a int, b int);
insertinto t2 values (1,1), (2,2),(2,3);
selectcount(*) from t1 groupby (select b from t2 where a=2);
SQLite 3.39: succeeds.
PostgreSQL 13.7: error
MySQL 8: error
Yugabyte, Timesten: error.
Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY".
I suspect that the optimization "Don't compute constant GROUP BY value" is not common.
Trying something more common:
createtable t1 (a int);
insertinto t1 values (1),(2),(3);
createtable t2 (a int, b int);
insertinto t2 values (1,1), (2,2),(2,3);
with T as
(
select
a,
(select t1.a from t2 where a=2) as b
from t1
)
select a from T;
SQL Server : OK
Oracle: OK
PostgreSQL: OK
SQLite: Ok
MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail.
MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).
Sergei Petrunia
added a comment - On the question in the comment above https://jira.mariadb.org/browse/MDEV-28621?focusedCommentId=284062&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-284062
about whether an SQL processor can drop the subqueries or has to compute them to verify that they do not produce the "Subquery returns more than one row" error.
Trying various databases:
create table t1 (a int );
insert into t1 values (1),(2),(3);
create table t2 (a int , b int );
insert into t2 values (1,1), (2,2),(2,3);
select count (*) from t1 group by ( select b from t2 where a=2);
SQLite 3.39: succeeds.
PostgreSQL 13.7: error
MySQL 8: error
Yugabyte, Timesten: error.
Oracle, SQL Server, DB2 - "subquery is not allowed in GROUP BY".
I suspect that the optimization "Don't compute constant GROUP BY value" is not common.
Trying something more common:
create table t1 (a int );
insert into t1 values (1),(2),(3);
create table t2 (a int , b int );
insert into t2 values (1,1), (2,2),(2,3);
with T as
(
select
a,
( select t1.a from t2 where a=2) as b
from t1
)
select a from T;
SQL Server : OK
Oracle: OK
PostgreSQL: OK
SQLite: Ok
MariaDB 10.6: OK but if I add e.g. "LIMIT 10" the CTE, it starts to fail.
MySQL: error (because CTE is not merged? If I rewrite as derived table, the behavior is same as in MariaDB).
Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.
Roel Van de Paar
added a comment - Please also see this comment showing a UBSAN runtime error: member access within null pointer of type 'struct st_select_lex' in st_select_lex_unit::set_limit, discovered in 11.5 optimized.
MDEV-28621: group by optimization incorrectly removing subquery where…
… subject buried in a function
Workaround patch: Do not remove GROUP BY clause when it has
subquer(ies) in it.
This will be followed by a more extensive fix.
Sergei Petrunia
added a comment -
Getting this pushed:
https://github.com/MariaDB/server/commit/40b3525fcc79aef62ea1ae057ec5687a55c0630b
MDEV-28621: group by optimization incorrectly removing subquery where…
… subject buried in a function
Workaround patch: Do not remove GROUP BY clause when it has
subquer(ies) in it.
This will be followed by a more extensive fix.
MDEV-28502 SIGSEGV in Item_subselect::is_expensive (+ other SIGSEGV's in MS 8.0) and 2x UBSAN: runtime error: member call on null pointer of type 'struct st_select_lex' in Item_subselect::is_expensive and in st_select_lex::next_select
MDEV-34013 Ignoring items instead of removing them.
There seems to be a disagreement in parts of the code as to whether removal of the group by statement is permanent or temporary.
A comment in JOIN::prepare
Permanently remove redundant parts from the query if1) This is a subquery2) This is the first time this query is optimized (since thetransformation is permanent3) Not normalizing a view. Removal should take place when aquery involving a view is optimized, not when the viewis createdimplies that it is permanent, yet in the Item tree, the eliminated flag is reset at the end of execution.
The very simplest solution I've found lies in bb-10.4-
MDEV-28621-no-remove-unit, where we simply reset the group list and do not exclude the removed unit from the query graph.