Yuchen Pei added a comment - 2024-11-17 23:30 - edited Reproducible with the following simple case: --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log set spider_same_server_link= 1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK" , DATABASE 'test' , user 'root' ); create table t2 (c varchar (100)); create table t1 (c varchar (100)) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv",TABLE "t2"' ; SELECT DISTINCT c FROM t1 WHERE (c LIKE 'resources/SYSTEM/SCHEDULER_JOBS_FORM/V%' ); drop table t1, t2; drop server srv; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc --enable_result_log --enable_query_log Caused by this change in MDEV-26345 ( order->item_ptr is NULL, but *order->item is an Item_temptable_field ): @@ -14071,7 +14084,7 @@ int spider_mbase_handler::append_group_by( str->q_append(SPIDER_SQL_GROUP_STR, SPIDER_SQL_GROUP_LEN); for (; order; order = order->next) { - if ((error_num = spider_db_print_item_type((*order->item), NULL, spider, + if ((error_num = spider_db_print_item_type(order->item_ptr, NULL, spider, str, alias, alias_length, dbton_id, use_fields, fields))) { DBUG_RETURN(error_num); At first glance, it is strange that query.group_by passed to the spider group by handler is not null, given there's no group by in the query.

Yuchen Pei added a comment - 2024-11-18 00:04 - edited The Query::group_by (and Query::order_by) field in this example is JOIN::group_list : // JOIN::make_aggr_tables_info(): Query query= {&all_fields, ( int ) all_fields.elements - ( int ) fields_list.elements, select_distinct || group_optimized_away, tables_list, conds, group_list, order ? order : group_list, having, &select_lex->master_unit()->lim}; The JOIN::group_list was created using create_distinct_group() , where its item field was assigned somewhere in ref_pointer_array ( group == ord below): ORDER * create_distinct_group(THD *thd, Ref_ptr_array ref_pointer_array, ORDER *order_list, List<Item> &fields, List<Item> &all_fields, bool *all_order_by_fields_used) { // [... 69 lines elided] ord->item= &ref_pointer_array[idx]; // [... 9 lines elided] return group; } The artificially created group_list looks like it is for use of the optimizer only. Since MDEV-26345 (and other similar bugs) where spider group by handler should no longer try to do optimizer's job, but translate the original queries and execute them on the data node. The following patch fixes this bug (see also commit bb-10.6-mdev-35422 ec83f75eb99f1995f6018a2c4f2c14a9ecdc63e7). However we still need to look a bit more into the creation of artificial JOIN::group_list . For example, is it possible that JOIN::group_list is a mixture of original and artificial items? If so then further changes need to be made so that we don't skip real group by items in group by handler query construction. modified sql/sql_select.cc @@ -3647,7 +3647,7 @@ bool JOIN::make_aggr_tables_info() (int) all_fields.elements - (int) fields_list.elements, select_distinct || group_optimized_away, tables_list, conds, - group_list, order ? order : group_list, having, + group_list, order, having, &select_lex->master_unit()->lim}; group_by_handler *gbh= ht->create_group_by(thd, &query); modified storage/spider/spd_db_mysql.cc @@ -14151,7 +14151,7 @@ int spider_mbase_handler::append_group_by( int error_num; DBUG_ENTER("spider_mbase_handler::append_group_by"); DBUG_PRINT("info",("spider this=%p", this)); - if (order) + if (order && order->item_ptr) { if (str->reserve(SPIDER_SQL_GROUP_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM);

Yuchen Pei added a comment - 2024-11-19 23:53 - edited BTW, a workaround would be to disable the spider group by handler. In lower versions such as 10.5 or 10.6 one could achieve that by setting the variable spider_use_handler to 1. For higher versions spider_disable_group_by_handler will do ( MDEV-32238 ).

Yuchen Pei added a comment - 2024-11-27 05:51 - edited The key issue is that certain items get replaced after optimization is done, during the exec stage. In MDEV-26345 , it was JOIN::order::item and JOIN::group_list::item that was replaced with Item_temptable_fields in calls to join->set_items_ref_array(join->items1) . Therefore we use the item_ptr field instead. With the present issue, some optimization causes artificial JOIN::group_list to be created which has NULL item_ptr . Since it happens in the optimization stage, we can use their NULLness as a criterion to skip the creation of the spider group by handler. Question for psergei and sanja : is it possible that JOIN::group_list::item_ptr is not NULL at the end of the optimization stage, but NULL in the exec stage? I assume not. But let me know if it is a possibility, as I'll need to cover this case in the patch I'm sending for review.

Yuchen Pei added a comment - 2024-11-27 05:51 Hi holyfoot , ptal thanks b98d92ef201 upstream/bb-10.5-mdev-35422 MDEV-35422 Fix spider group by handler trying to use fake group by fields

Yuchen Pei added a comment - 2024-11-29 00:17 > Question for Sergei Petrunia and Oleksandr Byelkin: is it possible that JOIN::group_list::item_ptr is not NULL at the end of the optimization stage, but NULL in the exec stage? I assume not. But let me know if it is a possibility, as I'll need to cover this case in the patch I'm sending for review. I don't have an answer yet so I updated the patch to include a check and error reporting for this case to prevent server crash. holyfoot , ptal this instead, thanks: 01c23ff032a upstream/bb-10.5-mdev-35422 MDEV-35422 Fix spider group by handler trying to use fake group by fields

Alexey Botchkov added a comment - 2024-12-02 12:10 ok to push.

Yuchen Pei added a comment - 2024-12-03 00:19 thanks for the review - pushed d0fcac44501b93593219ef2210032d06458d5410 to 10.5