Details
Critical Description
This query consistently causes MariaDB 10.4.15 and 10.5.6 to crash:
SELECT * FROM (
|
SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
|
provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
|
UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
|
bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
|
FROM master_ap2.har_provider provider
|
LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
|
LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
|
GROUP BY provider.guid ) provider
|
WHERE (
|
provider.roa_id IN (7) |
OR
|
(SELECT 1 |
from har_provider_org
|
WHERE provider.guid = har_provider_org.provider_guid
|
AND har_provider_org.roa_id IN (7) |
) )
|
AND (
|
(guid = 'ckgbm10gb009no74dzys39ce8') |
AND descr = 'fe0c79969b89eb69c32aee361e5bef9e' |
AND type = 1) |
ORDER BY `guid` ASC LIMIT 1; |
Minimal configuration as generated by dbdeployer:
[mysqld]
|
user = sami
|
port = 10415 |
socket = /tmp/mysql_sandbox10415.sock
|
basedir = /home/sami/opt/mysql/10.4.15 |
datadir = /home/sami/sandboxes/msb_10_4_15/data
|
tmpdir = /home/sami/sandboxes/msb_10_4_15/tmp
|
pid-file = /home/sami/sandboxes/msb_10_4_15/data/mysql_sandbox10415.pid
|
bind-address = 127.0.0.1 |
report-host=single-10415 |
report-port=10415 |
log-error=/home/sami/sandboxes/msb_10_4_15/data/msandbox.err
|
Steps to repeat: import attached dump of three tables, one empty and two have a single row, then execute above query in the master_ap2 schema. Error log shows:
Thread pointer: 0x55ee82e961c8 |
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went |
terribly wrong...
|
stack_bottom = 0x7f5112fb4e70 thread_stack 0x49000 |
/home/sami/opt/mysql/10.4.15/bin/mysqld(my_print_stacktrace+0x2e)[0x55ee7f4300be] |
/home/sami/opt/mysql/10.4.15/bin/mysqld(handle_fatal_signal+0x30f)[0x55ee7ee40b1f] |
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f51383e4390] |
/home/sami/opt/mysql/10.4.15/bin/mysqld(_ZN10Item_equal7val_intEv+0x91)[0x55ee7ee79231] |
sql/item_cmpfunc.cc:7026(Item_equal::val_int())[0x55ee7ed70110] |
sql/sql_type.cc:4446(Type_handler_int_result::Item_val_bool(Item*) const)[0x55ee7ee76e7a] |
sql/item_cmpfunc.cc:5297(Item_cond_and::val_int())[0x55ee7ec86350] |
sql/sql_select.cc:21748(end_send_group(JOIN*, st_join_table*, bool))[0x55ee7ec9329e] |
sql/sql_select.cc:19957(do_select)[0x55ee7ec935d3] |
sql/sql_select.cc:4256(JOIN::exec())[0x55ee7ec91826] |
sql/sql_select.cc:4689(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*))[0x55ee7ebf90d3] |
sql/sql_derived.cc:1266(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55ee7ebf8c74] |
sql/sql_derived.cc:206(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55ee7ec6f6a9] |
sql/sql_select.cc:13539(st_join_table::preread_init())[0x55ee7ec6f8e8] |
sql/sql_select.cc:20383(sub_select(JOIN*, st_join_table*, bool))[0x55ee7ec933a3] |
sql/sql_select.cc:19956(do_select)[0x55ee7ec935d3] |
sql/sql_select.cc:4256(JOIN::exec())[0x55ee7ec91826] |
sql/sql_select.cc:4689(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*))[0x55ee7ec92387] |
sql/sql_select.cc:422(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55ee7eb277df] |
sql/sql_parse.cc:6356(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ee7ec367fa] |
sql/sql_parse.cc:3889(mysql_execute_command(THD*))[0x55ee7ec3c11c] |
sql/sql_parse.cc:7896(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55ee7ec3e6ae] |
sql/sql_audit.h:169(mysql_audit_general)[0x55ee7ec3fe89] |
sql/sql_parse.cc:1353(do_command(THD*))[0x55ee7ed1ef9a] |
sql/sql_connect.cc:1412(do_handle_one_connection(CONNECT*))[0x55ee7ed1f07d] |
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f51383da6ba] |
x86_64/clone.S:111(clone)[0x7f513723f41d] |
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x55ee82ea4500): SELECT * FROM ( SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr, provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by, UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations, bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime FROM master_ap2.har_provider provider LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id GROUP BY provider.guid ) provider WHERE ( provider.roa_id IN (7) OR (SELECT 1 from har_provider_org WHERE provider.guid = har_provider_org.provider_guid AND har_provider_org.roa_id IN (7) ) ) AND ( (guid = 'ckgbm10gb009no74dzys39ce8') AND descr = 'fe0c79969b89eb69c32aee361e5bef9e' AND type = 1) ORDER BY `guid` ASC LIMIT 1 |
Connection ID (thread ID): 8 |
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 |
|
Attachments
Issue Links
- relates to
-
MDEV-28618 Server crash in /sql/item_cmpfunc.cc:6847 in Item_equal::val_int()
-
- Closed
-
Activity
Hi Leonid,
The failure happens with derived table+where, there are for sure plenty of ways to avoid it. Usually changing config is easier than rewriting the application. And in case you have some similar queries, then they will not crash the server. Otherwise, a temporary table, rewriting the query without a derived table, setting optimizer switch off only for this query, etc,.. whatever you prefer, it is up to you to decide.
Hi Alice,
We changed the logic and removed "descr" and "type" condition in WHERE clause, and that helped avoid the crash. Here the query that worked:
SELECT * FROM (
|
SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
|
provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
|
UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
|
bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
|
FROM master_ap2.har_provider provider
|
LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
|
LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
|
GROUP BY provider.guid ) provider
|
WHERE (
|
provider.roa_id IN (7) |
OR
|
(SELECT 1 |
from har_provider_org
|
WHERE provider.guid = har_provider_org.provider_guid
|
AND har_provider_org.roa_id IN (7) |
) )
|
AND
|
(guid = 'ckgbm10gb009no74dzys39ce8') |
ORDER BY `guid` ASC LIMIT 1; |
There is a separate code that will retrieve "descr" and "type" based on "guid".
Is there any danger of just removing the portion of the query, byt still leaving derived table+WHERE?
Why did the query not cause crashes with just "guid" in the WHERE?
Thank you.
-Leonid
Hi Alice,
In MariaDB 10.4.15, I can also observe a similar behavior
The revised query mentioned by Leonid does not cause a crash in my environment.
I wonder why the problematic query which causes crash and the revised query which has the same query execution plan.
## Problematic query
|
master [localhost:22916] {msandbox} (master_ap2) > explain extended SELECT * FROM (
|
-> SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
|
-> provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
|
-> UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
|
-> bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
|
-> FROM master_ap2.har_provider provider
|
-> LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
|
-> LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
|
-> GROUP BY provider.guid ) provider
|
-> WHERE (
|
-> provider.roa_id IN (7)
|
-> OR
|
-> (SELECT 1
|
-> from har_provider_org
|
-> WHERE provider.guid = har_provider_org.provider_guid
|
-> AND har_provider_org.roa_id IN (7)
|
-> ) )
|
-> AND (
|
-> (guid = 'ckgbm10gb009no74dzys39ce8')
|
-> AND descr = 'fe0c79969b89eb69c32aee361e5bef9e'
|
-> AND type = 1)
|
-> ORDER BY `guid` ASC LIMIT 1;
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 3 | DEPENDENT SUBQUERY | har_provider_org | eq_ref | PRIMARY,provider_index | PRIMARY | 36 | provider.guid,const | 1 | 100.00 | Using index |
|
| 2 | DERIVED | provider | const | PRIMARY | PRIMARY | 32 | const | 1 | 100.00 | |
|
| 2 | DERIVED | har_provider_org | ref | PRIMARY,provider_index | PRIMARY | 32 | const | 1 | 100.00 | Using where; Using index |
|
| 2 | DERIVED | bss | eq_ref | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | Using where |
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
5 rows in set, 2 warnings (0.004 sec)
|
## Revised query
|
master [localhost:22916] {msandbox} (master_ap2) > explain extended SELECT * FROM (
|
-> SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr,
|
-> provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
|
-> UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations,
|
-> bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime
|
-> FROM master_ap2.har_provider provider
|
-> LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid
|
-> LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id
|
-> GROUP BY provider.guid ) provider
|
-> WHERE (
|
-> provider.roa_id IN (7)
|
-> OR
|
-> (SELECT 1
|
-> from har_provider_org
|
-> WHERE provider.guid = har_provider_org.provider_guid
|
-> AND har_provider_org.roa_id IN (7)
|
-> ) )
|
-> AND
|
-> (guid = 'ckgbm10gb009no74dzys39ce8')
|
-> ORDER BY `guid` ASC LIMIT 1;
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 3 | DEPENDENT SUBQUERY | har_provider_org | eq_ref | PRIMARY,provider_index | PRIMARY | 36 | provider.guid,const | 1 | 100.00 | Using index |
|
| 2 | DERIVED | provider | const | PRIMARY | PRIMARY | 32 | const | 1 | 100.00 | |
|
| 2 | DERIVED | har_provider_org | ref | PRIMARY,provider_index | PRIMARY | 32 | const | 1 | 100.00 | Using where; Using index |
|
| 2 | DERIVED | bss | eq_ref | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | Using where |
|
+------+--------------------+------------------+--------+------------------------+---------+---------+---------------------+------+----------+--------------------------+
|
5 rows in set, 2 warnings (0.002 sec)
|
|
Jira is not a support forum, it's for reporting bugs or submitting feature requests, not for asking questions. Please use https://mariadb.zulipchat.com for further questions or ask in our mailing list: maria-discuss@lists.launchpad.net
The following test cases can reproduce the same kind crash in with
set optimizer_switch='condition_pushdown_for_derived=off'; |
(Actually no such setting is needed here.)
For MyISAM:
create table t1 (a int, b int) engine=myisam; |
create table t2 (id int, descr text, tp int) engine=myisam; |
insert into t2 values (3,'b',1); |
select t2.* from t2 left join t1 on t2.id = t1.a |
group by t2.id |
having descr = 'b' and tp = 1; |
drop table t1,t2; |
For InnoDB:
create table t1 (a int, b int) engine=innodb; |
create table t2 (id int primary key, a int, descr text, tp int) engine=innodb; |
insert into t2 values (3,7,'b',1); |
select t2.* from t2 left join t1 on t2.a = t1.a |
where t2.id=3 |
group by t2.a |
having descr = 'b' and tp = 1; |
drop table t1,t2; |
This test case that uses only one table with two rows causes a crash of the same kind as reported ones for both MyISAM and InnoDB:
create table t1 (id int primary key, a int, descr text, tp int) ; |
insert into t1 values (3,7,'b',1); |
insert into t1 values (4,7,'b',1); |
select * from t1 |
where id=3 |
group by t1.a |
having descr = 'b' and tp = 1; |
The crash happens in end_send_group() when the server tries to evaluate the having condition. The condition contains multiple equalities that we can see when executing EXPLAIN EXTENDED:
MariaDB [test]> explain extended
|
-> select * from t1
|
-> where id=3
|
-> group by t1.a
|
-> having descr = 'b' and tp = 1;
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|
1 row in set, 1 warning (0.002 sec)
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select 3 AS `id`,7 AS `a`,'b' AS `descr`,1 AS `tp` from `test`.`t1` where 1 group by 7 having multiple equal('b', 'b') and multiple equal(1, 1) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
This is not good as all multiple equalities are supposed be converted to conjunctions of simple equalities before the execution stage.
The call of substitute_for_best_equal_field() for having really performs this conversion. We can see after the call:
(gdb) p dbug_print_item(having)
|
$30 = 0x5555577e2e60 <dbug_item_print_buf> "'b' = 'b' and 1 = 1"
|
Yet in this block of JOIN::optimize_stage2()
if (having && const_table_map && !having->with_sum_func())
|
{
|
having->update_used_tables();
|
having= having->remove_eq_conds(thd, &select_lex->having_value, true);
|
if (select_lex->having_value == Item::COND_FALSE)
|
{
|
having= new (thd->mem_root) Item_bool(thd, false);
|
zero_result_cause= "Impossible HAVING noticed after reading const tables";
|
error= 0;
|
select_lex->mark_const_derived(zero_result_cause);
|
goto setup_subq_exit;
|
}
|
}
|
having becomes:
(gdb) p dbug_print_item(having)
|
$33 = 0x5555577e2e60 <dbug_item_print_buf> "multiple equal('b', 'b') and multiple equal(1, 1)"
|
The change happens in the function remove_eq_conds(). This function is not supposed to be called after elimination of multiple equalities.
If we remove the call if this function in the above mentioned block the test case works fine and all test pass.
However as a result of removal having is not simplified anymore.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
10.11:
240625 14:59:36 [ERROR] mysqld got signal 11 ;
|
|
|
Server version: 10.11.9-MariaDB-debug-log source revision: 346a0c140278d98a1400ad6d9a4e542b82a41546
|
|
|
sql/signal_handler.cc:238(handle_fatal_signal)[0x55efe2d9d9d6]
|
sigaction.c:0(__restore_rt)[0x7f44be5eb420]
|
sql/item_cmpfunc.cc:7423(Item_equal::val_int())[0x55efe2eb1894]
|
sql/sql_type.cc:5145(Type_handler_int_result::Item_val_bool(Item*) const)[0x55efe2b4665e]
|
sql/item.h:1721(Item::val_bool())[0x55efe20d842c]
|
sql/item_cmpfunc.cc:5633(Item_cond_and::val_int())[0x55efe2ea35c2]
|
sql/sql_select.cc:23819(end_send_group(JOIN*, st_join_table*, bool))[0x55efe263e1b4]
|
sql/sql_select.cc:21842(do_select(JOIN*, Procedure*))[0x55efe26307ab]
|
sql/sql_select.cc:4937(JOIN::exec_inner())[0x55efe25b5ba2]
|
sql/sql_select.cc:4716(JOIN::exec())[0x55efe25b30a2]
|
sql/sql_select.cc:5197(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*))[0x55efe25b7611]
|
sql/sql_derived.cc:1291(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55efe23ae1d1]
|
sql/sql_derived.cc:1065(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55efe23ac54e]
|
sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55efe23a6946]
|
sql/sql_select.cc:2434(JOIN::optimize_inner())[0x55efe259a7ad]
|
sql/sql_select.cc:1935(JOIN::optimize())[0x55efe2594f85]
|
sql/sql_select.cc:5181(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*))[0x55efe25b7420]
|
sql/sql_select.cc:586(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x55efe2586583]
|
sql/sql_parse.cc:6385(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55efe24a790d]
|
sql/sql_parse.cc:3988(mysql_execute_command(THD*, bool))[0x55efe2495b8e]
|
sql/sql_parse.cc:8143(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55efe24b29ea]
|
sql/sql_parse.cc:1897(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55efe2487d68]
|
sql/sql_parse.cc:1408(do_command(THD*, bool))[0x55efe2484aa2]
|
sql/sql_connect.cc:1417(do_handle_one_connection(CONNECT*, bool))[0x55efe29565f1]
|
sql/sql_connect.cc:1321(handle_one_connection)[0x55efe2955f4e]
|
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55efe3592502]
|
nptl/pthread_create.c:478(start_thread)[0x7f44be5df609]
|
|
|
Query (0x6290000e62a8): select * from (select t2.* from t2 left join t1 on t2.id = t1.t2_id
|
group by t2.id) dt
|
where id = 'a' and descr = 'b' and tp = 1
|
oleg.smirnov, did you see the comment by Igor above?
At risk of repeating it:
Item_equal items are created and eliminated:
(gdb) p dbug_print_item(having)
|
$144 = 0x555557ab44a0 <dbug_item_print_buf> "t1.descr = 'b' and t1.tp = 1"
|
having= optimize_cond(this, having, join_list, TRUE, |
&having_value, &having_equal);
|
(gdb) p dbug_print_item(having)
|
$147 = 0x555557ab44a0 <dbug_item_print_buf> "multiple equal('b', t1.descr) and multiple equal(1, t1.tp)"
|
^ here it was created
...
if (having)
|
{
|
having= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, having,
|
having_equal, map2table, false);
|
(gdb) p dbug_print_item(having)
|
$148 = 0x555557ab44a0 <dbug_item_print_buf> "'b' = 'b' and 1 = 1"
|
Here it was eliminated.
But then we arrive here:
/* |
It's necessary to check const part of HAVING cond as
|
there is a chance that some cond parts may become
|
const items after make_join_statistics(for example
|
when Item is a reference to cost table field from
|
outer join).
|
This check is performed only for those conditions
|
which do not use aggregate functions. In such case
|
temporary table may not be used and const condition
|
elements may be lost during further having
|
condition transformation in JOIN::exec.
|
*/
|
|
|
if (having && const_table_map && !having->with_sum_func()) |
{
|
having->update_used_tables();
|
having= having->remove_eq_conds(thd, &select_lex->having_value, true); |
(denote the above as WRONG-REMOVE-EQ-CONDS-CALL)
and somewhere inside this function things go wrong.
Igor above says that
The change happens in the function remove_eq_conds(). This function is not supposed to be called after elimination of multiple equalities.
If we remove the call if this function in the above mentioned block the test case works fine and all test pass.
However as a result of removal having is not simplified anymore.
I'm not yet sure if the fix is to just remove WRONG-REMOVE-EQ-CONDS-CALL or we need to place that call elsewhere...
The call to remove_eq_conds() was introduced by
commit be1d06c8a5f843e775374e5ec148aaee56970bdc
|
Author: Galina Shalygina <galashalygina@gmail.com>
|
Date: Sun May 8 23:04:41 2016 +0300
|
|
|
Merge branch '10.2' into 10.2-mdev9864
|
@@ -1813,6 +1881,31 @@ JOIN::optimize_inner()
|
if (!(select_options & SELECT_DESCRIBE))
|
init_ftfuncs(thd, select_lex, MY_TEST(order));
|
|
+ /*
|
+ It's necessary to check const part of HAVING cond as
|
+ there is a chance that some cond parts may become
|
+ const items after make_join_statisctics(for example
|
+ when Item is a reference to cost table field from
|
+ outer join).
|
+ This check is performed only for those conditions
|
+ which do not use aggregate functions. In such case
|
+ temporary table may not be used and const condition
|
+ elements may be lost during further having
|
+ condition transformation in JOIN::exec.
|
+ */
|
+ if (having && const_table_map && !having->with_sum_func)
|
+ {
|
+ having->update_used_tables();
|
+ having= having->remove_eq_conds(thd, &select_lex->having_value, true);
|
+ if (select_lex->having_value == Item::COND_FALSE)
|
+ {
|
+ having= new (thd->mem_root) Item_int(thd, (longlong) 0,1);
|
+ zero_result_cause= "Impossible HAVING noticed after reading const tables";
|
+ error= 0;
|
+ DBUG_RETURN(0);
|
+ }
|
+ }
|
+ |
If we compare processing of HAVING with processing of WHERE...
Suppose there is a condition which has parts that become constant after we've detected and read the const tables (denote them as PART_TO_REMOVE)...
if the condition is the WHERE condition, then PART_TO_REMOVE is removed by the code in make_join_select(). It calls make_cond_for_table() and PART_TO_REMOVE will be either removed on the spot or put into JOIN::exec_const_cond. Note that remove_eq_conds() is not used in this process.
If the condition is in the HAVING condition... do we need to change remove_eq_conds() call into a having= make_cond_for_table(having, ...) call to get PART_TO_REMOVE to be removed?
Ran the main test suite with this.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index 2ab1736891c..65c01912956 100644
|
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -2971,7 +2971,7 @@ int JOIN::optimize_stage2()
|
if (having && const_table_map && !having->with_sum_func())
|
{
|
having->update_used_tables();
|
- having= having->remove_eq_conds(thd, &select_lex->having_value, true);
|
+ //having= having->remove_eq_conds(thd, &select_lex->having_value, true);
|
if (select_lex->having_value == Item::COND_FALSE)
|
{
|
having= new (thd->mem_root) Item_bool(thd, false); |
it passed...
Add the line about the reviewer at the end of comment for the commit and push into 10.5
Fix pushed in 10.5 (commit)
The initial query doesn't cause a crash in 10.5+ anymore:
MariaDB [master_ap2]> SELECT * FROM ( |
-> SELECT provider.guid, provider.roa_id, provider.policy_guid, provider.filter_guid, provider.contact_user_id, provider.contact_roa_id, provider.name, provider.descr, |
-> provider.type, provider.origin_guid, provider.reference_filter_guid, provider.rca_mode, provider.search_additional_orgs, provider.apps_in_biz, provider.edited_by,
|
-> UNIX_TIMESTAMP(create_date) as create_date, UNIX_TIMESTAMP(edit_date) AS edit_date, GROUP_CONCAT(master_ap2.har_provider_org.roa_id) AS additional_organizations, |
-> bss.d_health as health, bss.d_available as availability, bss.d_risk as risk, UNIX_TIMESTAMP(bss.collection_time) as collectionTime |
-> FROM master_ap2.har_provider provider |
-> LEFT JOIN master_ap2.har_provider_org ON provider.guid = master_ap2.har_provider_org.provider_guid |
-> LEFT JOIN master_biz.biz_service_state bss ON provider.guid = bss.service_id |
-> GROUP BY provider.guid ) provider |
-> WHERE ( |
-> provider.roa_id IN (7) |
-> OR |
-> (SELECT 1 |
-> from har_provider_org |
-> WHERE provider.guid = har_provider_org.provider_guid |
-> AND har_provider_org.roa_id IN (7) |
-> ) )
|
-> AND ( |
-> (guid = 'ckgbm10gb009no74dzys39ce8') |
-> AND descr = 'fe0c79969b89eb69c32aee361e5bef9e' |
-> AND type = 1) |
-> ORDER BY `guid` ASC LIMIT 1; |
+---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ |
| guid | roa_id | policy_guid | filter_guid | contact_user_id | contact_roa_id | name | descr | type | origin_guid | reference_filter_guid | rca_mode | search_additional_orgs | apps_in_biz | edited_by | create_date | edit_date | additional_organizations | health | availability | risk | collectionTime | |
+---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ |
| ckgbm10gb009no74dzys39ce8 | 7 | ckgbji3nb008knt4d57b83n2r | ckgbji3yu00lont4d22tp96sm | NULL | 7 | c9454d783fae7e46f59455bd23611490 | fe0c79969b89eb69c32aee361e5bef9e | 1 | ckfxi252b02ou1fq5odo82ii6 | NULL | 0 | 1 | NULL | NULL | 1602806726 | 1602806726 | NULL | 100 | NULL | 0.0000 | 1602866400 | |
+---------------------------+--------+---------------------------+---------------------------+-----------------+----------------+----------------------------------+----------------------------------+------+---------------------------+-----------------------+----------+------------------------+-------------+-----------+-------------+------------+--------------------------+--------+--------------+--------+----------------+ |
1 row in set (0,003 sec) |
|
For the release notes:
A query could cause a crash if it has a HAVING clause with a
construct tblX.column=column_or_constant and the optimizer was
able to infer that table tblX is a constant table.
Note that HAVING clause may be from the original query or
may come from Condition Pushdown optimization (https://mariadb.com/kb/en/condition-pushdown-into-derived-table-optimization/)
Thanks a lot! I confirm that the test case fails on the current 10.4,10.5 with InnoDB/MyIsam. Not reproducible on 5.5-10.3. Please use
set optimizer_switch='condition_pushdown_for_derived=off';as a temporary workaround.
10.4 7eda55619654b76add275695
Version: '10.4.16-MariaDB-debug-log'201020 9:53:22 [ERROR] mysqld got signal 11 ;Server version: 10.4.16-MariaDB-debug-logstack_bottom = 0x7fa60cb8be50 thread_stack 0x49000/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7fa613680730]sql/item_cmpfunc.cc:7026(Item_equal::val_int())[0x5621ff6ccb89]sql/sql_type.cc:4513(Type_handler_int_result::Item_val_bool(Item*) const)[0x5621ff38dff8]sql/item.h:1462(Item::val_bool())[0x5621ff502518]sql/item_cmpfunc.cc:5297(Item_cond_and::val_int())[0x5621ff501652]sql/sql_select.cc:21739(end_send_group(JOIN*, st_join_table*, bool))[0x5621ff502da7]sql/sql_select.cc:19905(do_select(JOIN*, Procedure*))[0x5621ff459504]sql/sql_select.cc:4478(JOIN::exec_inner())[0x5621ff458bd3]sql/sql_select.cc:4261(JOIN::exec())[0x5621ff456506]sql/sql_select.cc:4697(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*))[0x5621ff4f95bc]sql/sql_derived.cc:1255(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x5621ff4f78bd]sql/sql_derived.cc:1045(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x5621ff502d0e]sql/sql_derived.cc:206(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5621ff4f290d]sql/sql_select.cc:2101(JOIN::optimize_inner())[0x5621ff4b938b]sql/sql_select.cc:1615(JOIN::optimize())[0x5621ff4afa0f]sql/sql_select.cc:4681(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*))[0x5621ff4bd319]sql/sql_select.cc:410(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5621ff4a996b]sql/sql_parse.cc:6355(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5621ff4a8115]sql/sql_parse.cc:3889(mysql_execute_command(THD*))[0x5621ff62ff4b]sql/sql_parse.cc:7896(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5621ff62fc9a]sql/sql_parse.cc:1837(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x562200014a5c]nptl/pthread_create.c:487(start_thread)[0x7fa613675fa3]x86_64/clone.S:97(clone)[0x7fa612c7e4cf]Query (0x7fa5fc013408): select * from (select t2.* from t2 left join t1 on t2.id = t1.t2_idgroup by t2.id) dtwhere id = 'a' and descr = 'b' and tp = 1