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
Closed
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
SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT'x'UNIONSELECT'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT'x'AND x GROUPBY x ) ) ) ) ;
Query (0x62b0000a1420): SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) )
Query (0x62b0000a1290): SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) )
CREATETABLE t0 ( c15 INT , c33 INT ) engine=innodb;
INSERTINTO t0 ( c15 ) WITH t1 AS ( SELECT SQRT ( 123 ) NOT REGEXP MOD ( 91 , -121 ) = ALL ( SELECT c15 AS c33 FROM t0 ) AS c49 FROM t0 ) SELECT t1 . c49 IS UNKNOWN AS c59 FROM t1 CROSSJOIN t0 AS t2 WHERE t1 . c49 = + EXISTS ( SELECT -5839312620871436105 AS c17 GROUPBY c49 ) BETWEEN -109 AND CHAR_LENGTH ( 2694839150676403988 ) - - LOWER ( -13 ) ;
Alice Sherepa
added a comment - - edited Test case from MDEV-32311
SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) ) ;
Version: '10.4.32-MariaDB-debug-log'
mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
231003 15:33:13 [ERROR] mysqld got signal 6 ;
Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2
/lib/x86_64-linux-gnu/libc.so.6(+0x22729)[0x7fba7d088729]
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fba7d099fd6]
sql/item_subselect.cc:735(Item_subselect::exec())[0x5622c94590f0]
sql/item_subselect.cc:1404(Item_singlerow_subselect::val_str(String*))[0x5622c945f991]
sql/item.h:1558(Item::str_result(String*))[0x5622c87a6135]
sql/item.cc:9381(Item_direct_view_ref::str_result(String*))[0x5622c92c60e2]
sql/item.cc:10367(Item_cache_str::cache_value())[0x5622c92cf0ee]
sql/item_cmpfunc.cc:1371(Item_in_optimizer::fix_left(THD*))[0x5622c92fb02e]
sql/item_subselect.cc:3340(Item_in_subselect::select_in_like_transformer(JOIN*))[0x5622c9477bff]
sql/item_subselect.cc:2649(Item_in_subselect::select_transformer(JOIN*))[0x5622c947057f]
sql/opt_subselect.cc:742(check_and_do_in_subquery_rewrites(JOIN*))[0x5622c8f410e4]
sql/sql_select.cc:1434(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b1947d]
sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280]
sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3]
sql/item_subselect.cc:3466(Item_in_subselect::fix_fields(THD*, Item**))[0x5622c9479047]
sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd]
sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607]
sql/item.h:975(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5622c88fca05]
sql/sql_base.cc:8545(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5622c88f3e94]
sql/sql_select.cc:744(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*))[0x5622c8b10955]
sql/sql_select.cc:1335(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b180b9]
sql/item_subselect.cc:3804(subselect_single_select_engine::prepare(THD*))[0x5622c947c280]
sql/item_subselect.cc:289(Item_subselect::fix_fields(THD*, Item**))[0x5622c9455cd3]
sql/item.h:966(Item::fix_fields_if_needed(THD*, Item**))[0x5622c87c45cd]
sql/item.h:970(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5622c87c4607]
sql/sql_base.cc:7744(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x5622c88ede03]
sql/sql_select.cc:1330(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5622c8b17d8d]
sql/sql_select.cc:4789(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5622c8b3d98d]
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5622c8b0e922]
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5622c8a7a72c]
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x5622c8a67ea3]
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5622c8a83c07]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5622c8a5a02d]
sql/sql_parse.cc:1378(do_command(THD*))[0x5622c8a56b58]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5622c8e647fd]
sql/sql_connect.cc:1325(handle_one_connection)[0x5622c8e640a1]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5622c9b0e99a]
nptl/pthread_create.c:478(start_thread)[0x7fba7d5b4609]
Query (0x62b0000a1420): SELECT ( ( WITH x ( x ) AS ( SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x ) SELECT x FROM x WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) ) ) )
test from MDEV-32390 :
CREATE TABLE t0 ( c43 DECIMAL ( 31 ) DEFAULT ( 45 ) ) ;
INSERT INTO t0 VALUES ( 13 ) , ( 29 ) ;
ALTER TABLE t0 ADD COLUMN c24 INT AFTER c43 ;
INSERT INTO t0 VALUES ( DEFAULT , DEFAULT ) , ( DEFAULT , DEFAULT ) ;
SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) ) ;
Version: '10.4.32-MariaDB-debug-log'
mysqld: /10.4/src/sql/item_subselect.cc:733: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
231010 16:22:18 [ERROR] mysqld got signal 6 ;
Server version: 10.4.32-MariaDB-debug-log source revision: 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96
/lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5e60504fd6]
sql/item_subselect.cc:735(Item_subselect::exec())[0x56093e7de89e]
sql/item_subselect.cc:1382(Item_singlerow_subselect::val_int())[0x56093e7e4b9f]
sql/item.h:1557(Item::val_int_result())[0x56093db2b0be]
sql/item.cc:9373(Item_direct_view_ref::val_int_result())[0x56093e64b6a3]
sql/item.cc:8380(Item_ref::val_int())[0x56093e640050]
sql/item_cmpfunc.cc:969(Arg_comparator::compare_int_unsigned())[0x56093e67bc65]
sql/item_cmpfunc.h:104(Arg_comparator::compare())[0x56093e6bd64e]
sql/item_cmpfunc.cc:1821(Item_func_ge::val_int())[0x56093e685867]
sql/item_func.cc:763(Item_int_func::val_str(String*))[0x56093e70a2c6]
sql/item_strfunc.cc:2096(Item_func_trim::val_str(String*))[0x56093e7a6539]
sql/item_strfunc.cc:159(Item_str_func::val_int())[0x56093e79383c]
sql/sql_select.cc:22086(end_send(JOIN*, st_join_table*, bool))[0x56093df3f25e]
sql/sql_select.cc:21129(evaluate_join_record(JOIN*, st_join_table*, int))[0x56093df378b9]
sql/sql_select.cc:20902(sub_select(JOIN*, st_join_table*, bool))[0x56093df361ee]
sql/sql_select.cc:20423(do_select(JOIN*, Procedure*))[0x56093df33f94]
sql/sql_select.cc:4605(JOIN::exec_inner())[0x56093dec1adc]
sql/sql_select.cc:4388(JOIN::exec())[0x56093debf10c]
sql/sql_select.cc:4828(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56093dec32e8]
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56093de93de0]
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56093ddffbe4]
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x56093dded35b]
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56093de090bf]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56093dddf4e5]
sql/sql_parse.cc:1378(do_command(THD*))[0x56093dddc010]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x56093e1e9deb]
sql/sql_connect.cc:1325(handle_one_connection)[0x56093e1e968f]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56093ee94274]
nptl/pthread_create.c:478(start_thread)[0x7f5e60a1f609]
Query (0x62b0000a1290): SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) << LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND -108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 ) ) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22 , ':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN -103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) = CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22 WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) )
MDEV-32309
SELECT ( WITH x ( x ) AS ( WITH x ( x ) AS ( SELECT 1 ) SELECT ( SELECT x ) FROM x ) SELECT x FROM x WHERE x IN ( SELECT NULL GROUP BY x ) ) ;
MDEV-32391
CREATE TABLE t0 ( c15 INT , c33 INT ) engine=innodb;
INSERT INTO t0 ( c15 ) WITH t1 AS ( SELECT SQRT ( 123 ) NOT REGEXP MOD ( 91 , -121 ) = ALL ( SELECT c15 AS c33 FROM t0 ) AS c49 FROM t0 ) SELECT t1 . c49 IS UNKNOWN AS c59 FROM t1 CROSS JOIN t0 AS t2 WHERE t1 . c49 = + EXISTS ( SELECT -5839312620871436105 AS c17 GROUP BY c49 ) BETWEEN -109 AND CHAR_LENGTH ( 2694839150676403988 ) - - LOWER ( -13 ) ;
One of the query optimization routines, remove_redundant_subquery_clauses() evaluates whether to remove the group by clause by searching the argument list of the relevant select_lex for references within the group by expression.
A note in the code details when this might occur.
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
1) is actually more restrictive than 'This is a subquery'. The actual test is if the unit containing this select_lex has an attached Item.
This only occurs under limited circumstances.
In one problem query
SELECT 1 FROM t1
WHERE a in
(
SELECT i+1
FROM (SELECT (SELECT 1 FROM t1) AS i FROM t1) dt2
GROUPBY i
);
WHERE a in (expression) causes an Item to be generated for (expression). This is the type of subquery being optimized.
What is happening here is that, because 'i' (which is itself another subquery), doesn't directly appear in the select list (it is under a function +(i,1)) this optimization considers removing the group by expression. As part of this cleanup, because 'i' is a subquery itself, it can be removed.
This causes a problem later on when the "SELECT i+1" part is to be evaluated and this subquery has now been removed.
Another note inside remove_redundant_subquery_clauses() states
Do not remove the item if it is used in select list and then referred
from GROUP BY clause by its name or number. Example:
select (select ... ) as SUBQ ... group by SUBQ
Here SUBQ cannot be removed.
The flag as to whether any order item is in the select list is 'in_field_list', and is populated by find_item_in_list(). Here, a simple top level scan is performed.
The logic is fine, but the search needed extending to find any occurrences of 'i' anywhere in the select list.
Rex Johnston
added a comment - - edited One of the query optimization routines, remove_redundant_subquery_clauses() evaluates whether to remove the group by clause by searching the argument list of the relevant select_lex for references within the group by expression.
A note in the code details when this might occur.
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
1) is actually more restrictive than 'This is a subquery'. The actual test is if the unit containing this select_lex has an attached Item.
This only occurs under limited circumstances.
In one problem query
SELECT 1 FROM t1
WHERE a in
(
SELECT i+1
FROM ( SELECT ( SELECT 1 FROM t1) AS i FROM t1) dt2
GROUP BY i
);
WHERE a in (expression) causes an Item to be generated for (expression). This is the type of subquery being optimized.
What is happening here is that, because 'i' (which is itself another subquery), doesn't directly appear in the select list (it is under a function +(i,1)) this optimization considers removing the group by expression. As part of this cleanup, because 'i' is a subquery itself, it can be removed.
This causes a problem later on when the "SELECT i+1" part is to be evaluated and this subquery has now been removed.
Another note inside remove_redundant_subquery_clauses() states
Do not remove the item if it is used in select list and then referred
from GROUP BY clause by its name or number. Example:
select (select ... ) as SUBQ ... group by SUBQ
Here SUBQ cannot be removed.
The flag as to whether any order item is in the select list is 'in_field_list', and is populated by find_item_in_list(). Here, a simple top level scan is performed.
The logic is fine, but the search needed extending to find any occurrences of 'i' anywhere in the select list.
I see one more objectionable thing in this example. The call
item->walk(Item::eliminate_subselect_processor);
walks into Item_direct_view_ref objects.
A background on what Item_direct_view_ref is: If you have a view column
createview v1 asselect (select ....) as SUBQ from ...
and then use it multiple times:
select v1.SUBQ ... from v1 WHERE v1.SUBQ = ...
then each use of "v1.SUBQ" is a separate Item_direct_view_ref object. All of these objects point to the same Item_subselect() created based on the view definition.
Now, we see that walk(eliminate_subselect_processor) walks into Item_direct_view_ref. This doesn't look like a good idea - we know that one "v1.SUBQ" was eliminated, but what if the others were not?
Sergei Petrunia
added a comment - - edited I see one more objectionable thing in this example. The call
item->walk(Item::eliminate_subselect_processor);
walks into Item_direct_view_ref objects.
A background on what Item_direct_view_ref is: If you have a view column
create view v1 as select ( select ....) as SUBQ from ...
and then use it multiple times:
select v1.SUBQ ... from v1 WHERE v1.SUBQ = ...
then each use of "v1.SUBQ" is a separate Item_direct_view_ref object. All of these objects point to the same Item_subselect() created based on the view definition.
Now, we see that walk(eliminate_subselect_processor) walks into Item_direct_view_ref. This doesn't look like a good idea - we know that one "v1.SUBQ" was eliminated, but what if the others were not?
Generally, it's fairly easy to construct queries where all use of subquery is removed but the subquery is still in the query plan:
createtable t10 (a int , b int);
insertinto t10 values (1,1),(2,2);
createtable t11 like t10;
insertinto t11 select seq, seq from seq_1_to_1000;
explain format=json
select * from t10
where
t10.a=1 or (1>2 and 3> (selectmax(a) from t11));
explain format=json
select * from t10
where
t10.a=1 or (1>2 and 3> (selectmax(a) from t11 where t11.b<t10.b));
Both will show the subquery as part of query plan. Both will show no references to it.
Sergei Petrunia
added a comment - - edited Generally, it's fairly easy to construct queries where all use of subquery is removed but the subquery is still in the query plan:
create table t10 (a int , b int );
insert into t10 values (1,1),(2,2);
create table t11 like t10;
insert into t11 select seq, seq from seq_1_to_1000;
explain format=json
select * from t10
where
t10.a=1 or (1>2 and 3> ( select max (a) from t11));
explain format=json
select * from t10
where
t10.a=1 or (1>2 and 3> ( select max (a) from t11 where t11.b<t10.b));
Both will show the subquery as part of query plan. Both will show no references to it.
... continuing the question of subquery elimination walking into Item_direct_view_ref objects: Here is an example with a different kind of elimination:
createtable t20 (a int, b int);
insertinto t20 select seq, seq from seq_1_to_10;
createtable t21 asselect * from t20;
createview v21 as
select
a, (selectmax(t20.b) from t20 where t20.a=t21.a) as SUBQ
from t21;
createtable t22 (
pk intprimarykey,
a int
);
insertinto t22 select * from t20;
explain
select v21.a, v21.SUBQ from v21 leftjoin t22 on t22.pk=v21.a and v21.SUBQ=t22.a;
EXPLAIN shows the t22 is eliminated with the ON expression:
#3 0x00007ffff5429472 in __GI___assert_fail (assertion=0x555556f11c29 "!eliminated", file=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect. cc", line=786, function=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:101
#4 0x000055555637d8d9 in Item_subselect::exec (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786
#5 0x000055555637fbbd in Item_singlerow_subselect::val_int (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:1473
#6 0x000055555616c5a3 in Type_handler::Item_send_long (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.cc:7604
#7 0x0000555556179a0a in Type_handler_long::Item_send (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.h:5768
#8 0x0000555555d99722 in Item::send (this=0x7fff2001fc20, protocol=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.h:1254
#9 0x00005555562c3ece in Item_ref::send (this=0x7fff20083d60, prot=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.cc:8500
Sergei Petrunia
added a comment - - edited ... continuing the question of subquery elimination walking into Item_direct_view_ref objects: Here is an example with a different kind of elimination:
create table t20 (a int , b int );
insert into t20 select seq, seq from seq_1_to_10;
create table t21 as select * from t20;
create view v21 as
select
a, ( select max (t20.b) from t20 where t20.a=t21.a) as SUBQ
from t21;
create table t22 (
pk int primary key ,
a int
);
insert into t22 select * from t20;
explain
select v21.a, v21.SUBQ from v21 left join t22 on t22.pk=v21.a and v21.SUBQ=t22.a;
EXPLAIN shows the t22 is eliminated with the ON expression:
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | t21 | ALL | NULL | NULL | NULL | NULL | 10 | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
running the query will fail an assertion here:
mysqld: /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
Thread 14 "mysqld" received signal SIGABRT, Aborted.
__GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
51 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) wher
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
#1 0x00007ffff54397f1 in __GI_abort () at abort.c:79
#2 0x00007ffff54293fa in __assert_fail_base (fmt=0x7ffff55b06c0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556f11c29 "! eliminated", file=file@entry=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc", line=line@entry=786, function=function@entry=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:92
#3 0x00007ffff5429472 in __GI___assert_fail (assertion=0x555556f11c29 "!eliminated", file=0x555556f11a48 "/home/psergey/dev-git2/10.6-dbg/sql/item_subselect. cc", line=786, function=0x555556f13980 <Item_subselect::exec()::__PRETTY_FUNCTION__> "virtual bool Item_subselect::exec()") at assert.c:101
#4 0x000055555637d8d9 in Item_subselect::exec (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:786
#5 0x000055555637fbbd in Item_singlerow_subselect::val_int (this=0x7fff2001fc20) at /home/psergey/dev-git2/10.6-dbg/sql/item_subselect.cc:1473
#6 0x000055555616c5a3 in Type_handler::Item_send_long (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.cc:7604
#7 0x0000555556179a0a in Type_handler_long::Item_send (this=0x555557b4b540 <type_handler_slong>, item=0x7fff2001fc20, protocol=0x7fff20001320, buf=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/sql_type.h:5768
#8 0x0000555555d99722 in Item::send (this=0x7fff2001fc20, protocol=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.h:1254
#9 0x00005555562c3ece in Item_ref::send (this=0x7fff20083d60, prot=0x7fff20001320, buffer=0x7ffff4070ad0) at /home/psergey/dev-git2/10.6-dbg/sql/item.cc:8500
Note that there are actually two kinds of subquery elimination:
1. Item::eliminate_subselect_processor
Introduced in 2010 by Timour, commit 18ad3bdc2fa
This removes the subquery from the st_select_lex tree.
Called by
st_select_lex_unit::prepare_join
remove_redundant_subquery_clauses
2. Item::mark_as_eliminated_processor
Introduced in 2009 by me, commit 9e65634b233
This just sets Item_subselect::eliminated=true.
Called by eliminate_tables().
Sergei Petrunia
added a comment - Note that there are actually two kinds of subquery elimination:
1. Item::eliminate_subselect_processor
Introduced in 2010 by Timour, commit 18ad3bdc2fa
This removes the subquery from the st_select_lex tree.
Called by
st_select_lex_unit::prepare_join
remove_redundant_subquery_clauses
2. Item::mark_as_eliminated_processor
Introduced in 2009 by me, commit 9e65634b233
This just sets Item_subselect::eliminated=true.
Called by eliminate_tables().
Will also need to check what happens on attempt to move this to the optimization phase.
Sergei Petrunia
added a comment - - edited Doing some tests: 10.6- MDEV-28621 -no-elimination.
Will also need to check what happens on attempt to move this to the optimization phase.
"Let's mark everything that's still reachable at query end"
turned out to result in a quite intrusive patch.
There are constant subqueries that are computed and then removed from
any clause.
There is code that "moves" Item_subselect* pointers from one select to
another without updating SELECT_LEX* structures
There is code in JOIN::optimize_constant_subqueries() to handle subqueries that are not reachable through Item trees are present in SELECT_LEX graph:
if (!subquery_predicate->fixed())
{
/*
This subquery was excluded as part of some expression so it is
invisible from all prepared expression.
*/
next_unit= un->next_unit();
etc etc.
Sergei Petrunia
added a comment - Ok the approach of
"Let's mark everything that's still reachable at query end"
turned out to result in a quite intrusive patch.
There are constant subqueries that are computed and then removed from
any clause.
There is code that "moves" Item_subselect* pointers from one select to
another without updating SELECT_LEX* structures
There is code in JOIN::optimize_constant_subqueries() to handle subqueries that are not reachable through Item trees are present in SELECT_LEX graph:
if (!subquery_predicate->fixed())
{
/*
This subquery was excluded as part of some expression so it is
invisible from all prepared expression.
*/
next_unit= un->next_unit();
etc etc.
Sergei Petrunia
added a comment - Trying a different approach: this seems to be much less intrusive:
commit b33f744ea58124c38aa97b597ddd48547777be9e (HEAD -> bb-10.6-MDEV-28621-v3-eliminate-carefully)
Author: Sergei Petrunia <sergey@mariadb.com>
Date: Mon Mar 25 13:53:55 2024 +0300
MDEV-28621: group by optimization incorrectly removing subquery
Try a different approach at removal: remove only "local" subqueries
for which we can't find references in other parts of the query.
(Doesn't handle the Table Elimination case yet, but it's trivial to extend it to)
So far it fixes testcases for MDEV-28621 MDEV-32311 MDEV-32390 MDEV-32309 MDEV-32391 MDEV-28620 MDEV-30842 MDEV-28622
bb-10.6-MDEV-28621-delayed-elimination implements marking a unit to be eliminated but not actually eliminating it until the end of the top level optimize. If we mark something as to be eliminated then try and evaluate it elsewhere, it unmarks this unit for elimination. There are 2nd execution complications that show up as slightly ugly flag checks in item methods, but the idea can work.
Rex Johnston
added a comment - bb-10.6- MDEV-28621 -delayed-elimination implements marking a unit to be eliminated but not actually eliminating it until the end of the top level optimize. If we mark something as to be eliminated then try and evaluate it elsewhere, it unmarks this unit for elimination. There are 2nd execution complications that show up as slightly ugly flag checks in item methods, but the idea can work.
arguments why the approach of bb-10.6-MDEV-28621-v3-eliminate-carefully is better than the approach of bb-10.6-MDEV-28621-delayed-elimination:
It is much nicer when a rewrite is an atomic action. We have the GROUP BY clause and/or a subquery, then Baam and it is completely gone.
The approach where there is a state where the subquery is still there but is being removed adds a lot of complexity.
One may point out that ...-eliminate-carefully does Item tree walks which take CPU cycles. My reply is that the walking is done when there's a redundant ORDER BY clause so walking is not on the common code path.
Sergei Petrunia
added a comment - - edited arguments why the approach of bb-10.6- MDEV-28621 -v3-eliminate-carefully is better than the approach of bb-10.6- MDEV-28621 -delayed-elimination :
It is much nicer when a rewrite is an atomic action. We have the GROUP BY clause and/or a subquery, then Baam and it is completely gone.
The approach where there is a state where the subquery is still there but is being removed adds a lot of complexity.
One may point out that ...-eliminate-carefully does Item tree walks which take CPU cycles. My reply is that the walking is done when there's a redundant ORDER BY clause so walking is not on the common code path.
This is what bb-10.6-MDEV-28621-v3-eliminate-carefully implements.
The idea is:
When we're about to eliminate the ORDER BY clause:
Locate the subqueries there
For each subquery, check if number of references from the
ORDER BY clause (there can be multiple) is equal to the
number of references from this SELECT's select_list.
If it is equal, there are no other references and it can be eliminated.
Advantage of this approach:
All the code is used only inside remove_redundant_subquery_clauses().
There is no logic outside.
Item_subselect objects should have reference counters.
Reference counter is incremented at fix_fields() stage. Whenever we
start referring from somewhere to a select list entry $SEL_LIST_COL,
we walk $SEL_LIST_COL and increment reference counters for all
Item_subselect objects we encounter.
When we eliminate a clause, we walk it and decrement usage counters.
When a usage counter of a subselect reaches zero, it is eliminated.
Advantage:
this is a more general architecture.
Disadvantage (SergeP's opinion):
maintaining reference counters may be complex, particularly when Item_subselect objects are "wrapped" inside multiple Item_direct_view_ref objects.
Sergei Petrunia
added a comment - Takeaways from yesterday's call:
There are two approaches to fixing this
"Count references when eliminating"
This is what bb-10.6- MDEV-28621 -v3-eliminate-carefully implements.
The idea is:
When we're about to eliminate the ORDER BY clause:
Locate the subqueries there
For each subquery, check if number of references from the
ORDER BY clause (there can be multiple) is equal to the
number of references from this SELECT's select_list.
If it is equal, there are no other references and it can be eliminated.
Advantage of this approach:
All the code is used only inside remove_redundant_subquery_clauses().
There is no logic outside.
"Reference counters"
This is what Johnston is trying to implement.
(my recollection from the call):
Item_subselect objects should have reference counters.
Reference counter is incremented at fix_fields() stage. Whenever we
start referring from somewhere to a select list entry $SEL_LIST_COL,
we walk $SEL_LIST_COL and increment reference counters for all
Item_subselect objects we encounter.
When we eliminate a clause, we walk it and decrement usage counters.
When a usage counter of a subselect reaches zero, it is eliminated.
Advantage:
this is a more general architecture.
Disadvantage (SergeP's opinion):
maintaining reference counters may be complex, particularly when Item_subselect objects are "wrapped" inside multiple Item_direct_view_ref objects.
psergei, i've added the test case (and more) to bb-10.4-MDEV-28621-reference-counter please have a look.
Counters are updated during object construction and name resolution, so overhead should be minimal.
Rex Johnston
added a comment - psergei , i've added the test case (and more) to bb-10.4- MDEV-28621 -reference-counter please have a look.
Counters are updated during object construction and name resolution, so overhead should be minimal.
Input piece #1: I was trying to see how the code would behave for ORDER BY subquery_we_can_eliminate, subquery_we_cannot_eliminate so I've modified the previous example I've posted and ran it:
createtable t1 (a int, b int, c int);
insertinto t1 select seq, seq, seq from seq_1_to_10;
createtable t2 asselect * from t1;
createtable t20 asselect * from t1;
createtable t21 asselect * from t1;
createtable t3 asselect * from t1;
createview v2 as
select
a, b,
(selectmax(c) from t20 where t20.a<=t2.a) as SUBQ1,
(selectmax(c) from t21 where t21.a<=t2.a) as SUBQ2
from t2;
explain
select
a, a in (select a from v2 where a>3 and v2.subq2>=0 groupby v2.subq1, v2.subq2)
from
t1
it crashes at
Thread 37 "mysqld" received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffdcad9700 (LWP 19440)]
0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510, las
(gdb) wher
#0 0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510,
#1 0x0000555555e07c3f in JOIN::create_postjoin_aggr_table (this=0x7ffee4068350, tab=0x7ffee4070218, table_fields=0x7ffee4068670, table_group=0x7ffee4017458, save_sum_fields=false, di
#2 0x0000555555e06176 in JOIN::make_aggr_tables_info (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3540
#3 0x0000555555e049e8 in JOIN::optimize_stage2 (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3164
#4 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
#5 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
#6 0x0000555555d7f3fd in st_select_lex::optimize_unflattened_subqueries (this=0x7ffee40155e0, const_only=false) at /home/psergey/dev-git2/10.4-look2/sql/sql_lex.cc:4347
#7 0x0000555555fb3f72 in JOIN::optimize_unflattened_subqueries (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/opt_subselect.cc:5610
#8 0x0000555555e04295 in JOIN::optimize_stage2 (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2998
#9 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
#10 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
#11 0x0000555555e0b157 in mysql_select (thd=0x7ffee4000d50, tables=0x7ffee4018208, wild_num=0, fields=@0x7ffee4015740: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffe
I don't really understand what happens or why it crashes there.
Is this related to this fix at all? Well if I go into remove_redundant_subquery_clauses() and prevent elimination by setting can_eliminate=false with debugger, the crash doesn't occur. I think it's a sufficiently strong indication that the problem might be related to this patch.
Sergei Petrunia
added a comment - - edited Hi Johnston
Input piece #1: I was trying to see how the code would behave for ORDER BY subquery_we_can_eliminate, subquery_we_cannot_eliminate so I've modified the previous example I've posted and ran it:
create table t1 (a int , b int , c int );
insert into t1 select seq, seq, seq from seq_1_to_10;
create table t2 as select * from t1;
create table t20 as select * from t1;
create table t21 as select * from t1;
create table t3 as select * from t1;
create view v2 as
select
a, b,
( select max (c) from t20 where t20.a<=t2.a) as SUBQ1,
( select max (c) from t21 where t21.a<=t2.a) as SUBQ2
from t2;
explain
select
a, a in ( select a from v2 where a>3 and v2.subq2>=0 group by v2.subq1, v2.subq2)
from
t1
it crashes at
Thread 37 "mysqld" received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffdcad9700 (LWP 19440)]
0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510, las
(gdb) wher
#0 0x0000555555e329d7 in create_tmp_table (thd=0x7ffee4000d50, param=0x7ffee40760e8, fields=@0x7ffee4068670: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffee406b510,
#1 0x0000555555e07c3f in JOIN::create_postjoin_aggr_table (this=0x7ffee4068350, tab=0x7ffee4070218, table_fields=0x7ffee4068670, table_group=0x7ffee4017458, save_sum_fields=false, di
#2 0x0000555555e06176 in JOIN::make_aggr_tables_info (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3540
#3 0x0000555555e049e8 in JOIN::optimize_stage2 (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:3164
#4 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
#5 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4068350) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
#6 0x0000555555d7f3fd in st_select_lex::optimize_unflattened_subqueries (this=0x7ffee40155e0, const_only=false) at /home/psergey/dev-git2/10.4-look2/sql/sql_lex.cc:4347
#7 0x0000555555fb3f72 in JOIN::optimize_unflattened_subqueries (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/opt_subselect.cc:5610
#8 0x0000555555e04295 in JOIN::optimize_stage2 (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2998
#9 0x0000555555e02191 in JOIN::optimize_inner (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:2450
#10 0x0000555555dff89e in JOIN::optimize (this=0x7ffee4067a38) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:1763
#11 0x0000555555e0b157 in mysql_select (thd=0x7ffee4000d50, tables=0x7ffee4018208, wild_num=0, fields=@0x7ffee4015740: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7ffe
I don't really understand what happens or why it crashes there.
Is this related to this fix at all? Well if I go into remove_redundant_subquery_clauses() and prevent elimination by setting can_eliminate=false with debugger, the crash doesn't occur. I think it's a sufficiently strong indication that the problem might be related to this patch.
a, b, (selectmax(c) from t20 where t20.a<=t2.a) as SUBQ1
from t2;
explain
select
a, a in (select a from v2 where a>3 groupby v2.subq1)
from t1;
shows selects #1 and #2.
But if copy-paste the VIEW definition into the main query:
explain
select
a, a in (select a
from
(select
a, b, (selectmax(c) from t20 where t20.a<=t2.a) as SUBQ1
from t2
) as v2
groupby v2.subq1
)
from t1;
it shows selects #1, #2, #4.
UPD: one can achieve the same with a nested VIEW:
-- Original query. Subquery is eliminated:
explain
select
a, a in (select a from v2 where a>3 groupby v2.SUBQ1)
from t1;
-- Wrap v2 into v3
createview v3 as
select
a, b, SUBQ1 as SUBQ1A
from v2;
-- Same query against v3:
explain
select
a, a in (select a from v3 where a>3 groupby v3.SUBQ1A)
from t1;
Sergei Petrunia
added a comment - - edited Another observation is about VIEWs. The patch handles the example from mdev28621-remaining-case.test
...
create view v2 as
select
a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1
from t2;
explain
select
a, a in ( select a from v2 where a>3 group by v2.subq1)
from t1;
shows selects #1 and #2.
But if copy-paste the VIEW definition into the main query:
explain
select
a, a in ( select a
from
( select
a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1
from t2
) as v2
group by v2.subq1
)
from t1;
it shows selects #1, #2, #4.
UPD: one can achieve the same with a nested VIEW:
-- Original query. Subquery is eliminated:
explain
select
a, a in ( select a from v2 where a>3 group by v2.SUBQ1)
from t1;
-- Wrap v2 into v3
create view v3 as
select
a, b, SUBQ1 as SUBQ1A
from v2;
-- Same query against v3:
explain
select
a, a in ( select a from v3 where a>3 group by v3.SUBQ1A)
from t1;
Actually, we go into the if(eliminated) branch on the first execution (and only there). The rewrite itself seems to be done on PREPARE even for prepared statements. I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?
Sergei Petrunia
added a comment - This piece looks scary:
@@ -266,7 +272,12 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
*/
}
- eliminated= FALSE;
+ if (eliminated) // 2nd execution
+ {
+ fixed= 1;
+ goto end;
+ }
+
Actually, we go into the if(eliminated) branch on the first execution (and only there). The rewrite itself seems to be done on PREPARE even for prepared statements. I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?
Sergei Petrunia
added a comment -
diff --git a/sql/item.cc b/sql/item.cc
index 07463b202f9..1b324456d3b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6085,6 +6085,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
select->context_analysis_place == IN_GROUP_BY &&
alias_name_used ? *rf->ref : rf);
+ if ((*res)->type() == Item::SUBSELECT_ITEM)
+ ((Item_subselect*)(*res))->reference_count++;
+
/*
We can not "move" aggregate function in the place where
its arguments are not defined.
Why does this modify the top-level item instead of doing the walk() call?
I took the testcase that uses it, made the top-level to be multiplication:
create table t1 (a int , b int , c int );
insert into t1 select seq, seq, seq from seq_1_to_10;
create table t2 as select * from t1;
create table t20 as select * from t1;
select a, a in
(
select
(
select max (c) from t20 where t20.a<=t2.a
)*2 as SUBQ1 from t2 group by SUBQ1+1
)
and it fails assert like here:
Thread 36 "mysqld" hit Breakpoint 2, Item_field::fix_fields (this=0x7ffee80187e0, thd=0x7ffee8000d50, reference=0x7ffee8018a18) at /home/psergey/dev-git2/10.4-look2/sql/item.cc:6089
(gdb) p *res
$7 = (Item_func_mul *) 0x7ffee8017fa0
### if branch not taken
(gdb) c
Continuing.
mysqld: /home/psergey/dev-git2/10.4-look2/sql/item_subselect.cc:757: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
I don't really understand what happens or why it crashes there.
What is happening is that the temporary table being created still has the pointers to the reference to the eliminated item.
for (; cur_group ; cur_group= cur_group->next, key_part_info++)
{
Field *field=(*cur_group->item)->get_tmp_table_field();
here field is null for the eliminated item.
There are a few ways we could fix this. We could fabricate another group list for this join, or chuck in an ugly test like this
for (; cur_group ; cur_group= cur_group->next, key_part_info++)
{
if (((*cur_group->item)->type() == Item::REF_ITEM) &&
Field *field=(*cur_group->item)->get_tmp_table_field();
Which solves this test case. Thoughts?
EDIT, much better to correct join->group_list i think.
Rex Johnston
added a comment - - edited I don't really understand what happens or why it crashes there.
What is happening is that the temporary table being created still has the pointers to the reference to the eliminated item.
for (; cur_group ; cur_group= cur_group->next, key_part_info++)
{
Field *field=(*cur_group->item)->get_tmp_table_field();
here field is null for the eliminated item.
There are a few ways we could fix this. We could fabricate another group list for this join, or chuck in an ugly test like this
for (; cur_group ; cur_group= cur_group->next, key_part_info++)
{
if (((*cur_group->item)->type() == Item::REF_ITEM) &&
((*((Item_ref *)(*cur_group->item))-> ref )->type() ==
Item::SUBSELECT_ITEM) &&
((Item_subselect *)(*((Item_ref *)(*cur_group->item))-> ref ))->
eliminated )
continue ;
Field *field=(*cur_group->item)->get_tmp_table_field();
Which solves this test case. Thoughts?
EDIT, much better to correct join->group_list i think.
if one defines v2 with another column after the subquery, i.e.
createview v2 asselect a, b, (selectmax(c) from t20 where t20.a<=t2.a) as SUBQ1, a+b as c from t2;
then
explain
select
a, a in (select a from v2 where a>3 groupby v2.SUBQ1)
from t1;
we also see an additional "DEPENDENT SUBQUERY" in the explain output.
Rex Johnston
added a comment - UPD: one can achieve the same with a nested VIEW:
if one defines v2 with another column after the subquery, i.e.
create view v2 as select a, b, ( select max (c) from t20 where t20.a<=t2.a) as SUBQ1, a+b as c from t2;
then
explain
select
a, a in ( select a from v2 where a>3 group by v2.SUBQ1)
from t1;
we also see an additional "DEPENDENT SUBQUERY" in the explain output.
Actually, we go into the if(eliminated) branch on the first execution (and only there).
The rewrite itself seems to be done on PREPARE even for prepared statements.
I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?
Test query :
prepare s from'select a, a in (select a from v2 where a>3 group by v2.SUBQ1) from t1';
execute s;
during check_prepared_statement() as part of "prepare", remove_redundant_subqueries() evaluates the select_lex associated with
select t2.a from v2 where t2.a > 3 groupby (subquery#4)
subquery#4 is referred to only once here, so is removed (from the item_list, join->all_fields and join->group_list and marked eliminated).
During execution, setup_fields() once again initiates a fix_field cascade of calls, rippling through prepare, setup_fields on subquery#4, Item::fix_fields_if_needed() is called once again on this eliminated item.
Of course there is now no unit associated with this item and during prepare on the subselect_engine results in a call to uninitialized memory in Item_subselect::fix_fields()/engine->prepare(thd).
The simplest solution was to check if the item was eliminated and exit Item_subselect::fix_fields() once upper_refs.empty() has been called, which is needed elsewhere.
Rex Johnston
added a comment - - edited Actually, we go into the if(eliminated) branch on the first execution (and only there).
The rewrite itself seems to be done on PREPARE even for prepared statements.
I'm wondering if we could remove this if we've made the rewrite to be done on the first execution...
I also don't understand why we need this now - if the rewrite has removed references, why is fix_fields() still called for the item?
Test query :
prepare s from 'select a, a in (select a from v2 where a>3 group by v2.SUBQ1) from t1' ;
execute s;
during check_prepared_statement() as part of "prepare", remove_redundant_subqueries() evaluates the select_lex associated with
select t2.a from v2 where t2.a > 3 group by (subquery#4)
subquery#4 is referred to only once here, so is removed (from the item_list, join->all_fields and join->group_list and marked eliminated).
During execution, setup_fields() once again initiates a fix_field cascade of calls, rippling through prepare , setup_fields on subquery#4 , Item::fix_fields_if_needed() is called once again on this eliminated item.
Of course there is now no unit associated with this item and during prepare on the subselect_engine results in a call to uninitialized memory in Item_subselect::fix_fields()/engine->prepare(thd) .
The simplest solution was to check if the item was eliminated and exit Item_subselect::fix_fields() once upper_refs.empty() has been called, which is needed elsewhere.
Why does this modify the top-level item instead of doing the walk() call?
Silly me. Good catch, thanks. Fixed in bb-10.4-MDEV-28621-reference-counter
Rex Johnston
added a comment - Why does this modify the top-level item instead of doing the walk() call?
Silly me. Good catch, thanks. Fixed in bb-10.4- MDEV-28621 -reference-counter
Another possible alternative fix: bb-10.6-MDEV-28621-eliminate-in-optimization
EDIT
This patch attempted to remove GROUP BY at the query optimization phase.
It doesn't work in all cases.
The optimizer has code to remove constant items from GROUP BY expression, but that doesn't remove the grouping operation.
Attempting to remove the grouping operation itself causes wrong results on the second execution.
We hit something in Name Resolution code where subquery's references to outside get incorrect attributes.
This query from subselect_elimination.test:
select
a,
a in (select
(selectmax(c) from t20 where t20.a<=t2.a) as SUBQ1
from t2
groupby SUBQ1+1
)
from
t1
here, "group by SUBQ1+1" is removed, and then the second execution starts to assume that (select max(c) from t20 where t20.a<=t2.a is not correlated because t2.a doesn't have OUTER_REF_TABLE_BIT.
I could narrow it down only to Item_field::fix_outer_field. It has a check in select->group_list.elements which is non-zero on the first PS execution and zero on the second.
When it is zero, Item_field::fix_outer_field produces an Item object without OUTER_REF_TABLE_BIT.
Sergei Petrunia
added a comment - - edited Another possible alternative fix: bb-10.6- MDEV-28621 -eliminate-in-optimization
EDIT
This patch attempted to remove GROUP BY at the query optimization phase.
It doesn't work in all cases.
The optimizer has code to remove constant items from GROUP BY expression, but that doesn't remove the grouping operation.
Attempting to remove the grouping operation itself causes wrong results on the second execution.
We hit something in Name Resolution code where subquery's references to outside get incorrect attributes.
This query from subselect_elimination.test:
select
a,
a in ( select
( select max (c) from t20 where t20.a<=t2.a) as SUBQ1
from t2
group by SUBQ1+1
)
from
t1
here, "group by SUBQ1+1" is removed, and then the second execution starts to assume that
(select max(c) from t20 where t20.a<=t2.a is not correlated because t2.a doesn't have OUTER_REF_TABLE_BIT.
I could narrow it down only to Item_field::fix_outer_field . It has a check in select->group_list.elements which is non-zero on the first PS execution and zero on the second.
When it is zero, Item_field::fix_outer_field produces an Item object without OUTER_REF_TABLE_BIT.
Sergei Petrunia
added a comment - also stumbled on this ...
/*
Constant ORDER and/or GROUP expressions that contain subqueries. Such
expressions need to evaluated to verify that the subquery indeed
returns a single row. The evaluation of such expressions is delayed
until query execution.
*/
List<Item> exec_const_order_group_cond;
Introduced in https://github.com/MariaDB/server/commit/620aea4fde7d40f3870bebdcfd66d2b0b556db2f
How does this approach agree with what we're trying to implement in this MDEV...
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.
Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 . UPDATE not needed.