Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2(EOL), 11.4, 11.5(EOL), 11.6(EOL)
Description
Consider:
CREATE TABLE t2 (c INT KEY) ENGINE=InnoDB; |
LOCK TABLE t2 READ; |
SELECT 1 FROM t2 GROUP BY 1; |
Gives:
|
11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized) |
11.2.5-opt>SELECT 1 FROM t2 GROUP BY 1;
|
Empty set (0.001 sec)
|
Yet, with Spider, for:
INSTALL PLUGIN Spider SONAME 'ha_spider.so'; |
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE'',USER'',PASSWORD''); |
CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB; |
CREATE TABLE t2 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t1"'; |
LOCK TABLE t2 READ; |
SELECT 1 FROM t2 GROUP BY 1; |
We see:
|
11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized) |
11.2.5-dbg>SELECT 1 FROM t2 GROUP BY 1;
|
ERROR 1056 (42000): Can't group on 'min(`c`)'
|
And in the error log:
|
11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized) |
2024-07-23 14:22:58 4 [ERROR] Got error 1056 when reading table './test/t2'
|
Attachments
Activity
The following analysis is based on a custom branch (bb-10.5-mdev-34639 bc792266f9b1c83182bbcfc9d8eb256cf11dffe7) with two commits above the 10.5 main branch 872dbec93570bb934473907f5d1fcf74aeee4f85, but the behaviour should be the same at the latter.
The offending query sent to the data node is:
"select min(`c`) from `test`.`t1` group by 1 order by `c`"
|
It is sent in the index_first call to the handler.
One issue is that spider seems oblivious with GROUP BY a column index number:
bool spider_db_check_select_colum_in_group( |
st_select_lex *select_lex,
|
Field *field
|
) {
|
ORDER *group;
|
DBUG_ENTER("spider_db_check_select_colum_in_group"); |
for (group = (ORDER *) select_lex->group_list.first; group; |
group = group->next)
|
{
|
Item *item = *group->item;
|
if (item->type() == Item::FIELD_ITEM) |
{
|
Item_field *item_field = (Item_field *) item;
|
if (item_field->field == field) |
{
|
/* This field can be used directly */ |
DBUG_RETURN(TRUE);
|
}
|
}
|
}
|
DBUG_RETURN(FALSE);
|
}
|
which causes the caller to enter the else branch:
if (select_lex && |
!spider_db_check_select_colum_in_group(select_lex, *field))
|
{
|
# [... 8 lines elided]
|
} else { |
#endif
|
if (str->reserve(field_length + |
/* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + SPIDER_SQL_COMMA_LEN)) |
DBUG_RETURN(HA_ERR_OUT_OF_MEM);
|
mysql_share->append_column_name(str, (*field)->field_index);
|
#ifdef HANDLER_HAS_DIRECT_AGGREGATE
|
}
|
There are other things in the stack that are not clear, including the meaning of spider_select_column_mode which causes entering the else branch in spider_db_append_select_columns() that resulted in calling spider->append_minimum_select_sql_part, as well as the meaning of spider->append_minimum_select_sql_part itself, e.g. whether the minimum means appending min or just appending minimum amount of "select parts" (whatever that means)...
The stack:
0 in spider_db_check_select_colum_in_group of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:9579
|
1 in spider_mbase_handler::append_minimum_select of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9739
|
2 in spider_mbase_handler::append_minimum_select_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9698
|
3 in ha_spider::append_minimum_select_sql_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:13375
|
4 in spider_db_append_select_columns of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:1419
|
5 in ha_spider::index_first_internal of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2599
|
6 in ha_spider::index_first of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2916
|
7 in handler::ha_index_first of /home/ycp/source/mariadb-server/10.5/src/sql/handler.cc:3338
|
MTR Testcase:
--source plugin/spider/spider/include/init_spider.inc--source include/have_innodb.inc# Cleanup11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized)
mysqltest: At line 8: query 'SELECT 1 FROM t2 GROUP BY 1' failed: ER_WRONG_GROUP_FIELD (1056): Can't group on 'min(`c`)'