[MDEV-23983] Crash on SELECT Created: 2020-10-17  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Sami Ahlroos Assignee: Igor Babaev
Resolution: Unresolved Votes: 2
Labels: not-10.9

Attachments: File dumpmd5_single.sql    
Issue Links:
Relates
relates to MDEV-28618 Server crash in /sql/item_cmpfunc.cc:... Closed

 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



 Comments   
Comment by Alice Sherepa [ 2020-10-20 ]

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. 

create table t1 ( t2_id char(32), id2 int);
 
 
 
create table t2 (id char(32), id2 int, descr text, tp tinyint(1));
 
insert into t2 values ('a',7,'b',1);
 
 
 
#set optimizer_switch='condition_pushdown_for_derived=on';
 
 
 
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;

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-log
 
 
 
stack_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_id
 
group by t2.id) dt
 
where id = 'a' and descr = 'b' and tp = 1

Comment by Alice Sherepa [ 2020-10-22 ]

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.

Comment by Leonid Tsoi [ 2020-10-27 ]

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

Comment by Phong Dinh [ 2020-10-27 ]

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)

Comment by Alice Sherepa [ 2020-10-27 ]

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

Comment by Igor Babaev [ 2020-12-30 ]

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;

Comment by Igor Babaev [ 2020-12-31 ]

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;

Comment by Igor Babaev [ 2020-12-31 ]

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.

Comment by Alice Sherepa [ 2023-09-15 ]

Still reproducible on 10.4-10.6, not on 10.9+

Version: '10.4.32-MariaDB-debug-log'  
230915 16:30:58 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: b1ab4ec4e25b231da77a2683d69366ab5dea4d66
 
 
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x56032c5711d7]
 
sigaction.c:0(__restore_rt)[0x7fa4c4edc420]
 
sql/item_cmpfunc.cc:7111(Item_equal::val_int())[0x56032c67e19c]
 
sql/sql_type.cc:4607(Type_handler_int_result::Item_val_bool(Item*) const)[0x56032c333452]
 
sql/item.h:1474(Item::val_bool())[0x56032baf3d52]
 
sql/item_cmpfunc.cc:5359(Item_cond_and::val_int())[0x56032c66fb5e]
 
sql/sql_select.cc:22243(end_send_group(JOIN*, st_join_table*, bool))[0x56032bf09846]
 
sql/sql_select.cc:20354(do_select(JOIN*, Procedure*))[0x56032befc2f3]
 
sql/sql_select.cc:4605(JOIN::exec_inner())[0x56032be8a54c]
 
sql/sql_select.cc:4388(JOIN::exec())[0x56032be87b78]
 
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*))[0x56032be8bd58]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56032be5c86c]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56032bdc8676]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x56032bdb5ded]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56032bdd1b51]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56032bda7f77]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x56032bda4aa2]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x56032c1b26d7]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x56032c1b1f7b]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x56032ce5c196]
 
nptl/pthread_create.c:478(start_thread)[0x7fa4c4ed0609]
 
 
 
Query (0x62b0000a1290): select * from t1
 
where id=3   
group by t1.a
 
having descr = 'b' and tp = 1

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 09:26:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.