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
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) |
|
Add the line about the reviewer at the end of comment for the commit and push into 10.5
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...
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?
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/)