|
The bug looks similar to MDEV-26013. Now, I think that it might be better to stop using the group by handler for some type of execution plans (but what types?). That might have made the situation simple.
A workaround for this is to add GROUP BY explicitly:
SELECT MIN(c2),c1 FROM sp WHERE c1='00166' GROUP BY c1;
|
|
|
The bug is not reproducible on 10.3 HEAD, but is reproducible on 10.4 HEAD, 10.5 HEAD, and 10.6 HEAD.
|
|
The query executed on the Spider node:
SELECT MIN(c2),c1 FROM auto_test_local.sp WHERE c1='00166'
|
The corresponding queries executed on the data node:
select `c1`,`c2`,`c3` from `auto_test_remote`.`sp` where `c1` >= '00166' order by `c1`,`c2`,`c3` limit 1
|
select t0.`c1` `c1` from `auto_test_remote`.`sp` t0 where (t0.`c1` = '00166')
|
|
|
We can see that the query is processed by the GROUP BY handler from the Extra column.
MariaDB > EXPLAIN SELECT MIN(c2),c1 FROM auto_test_local.sp WHERE c1='00166';
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Storage engine handles GROUP BY |
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|
1 row in set (0.035 sec)
|
|
|
I created a MTR test case: https://github.com/nayuta-yanagisawa/server/commit/06dd7db8f3fa8ca45eae3b87499f0d558ecb3cc1
|
|
From now on, I work on the simplified test case above.
I put a breakpoint of spider_db_mbase::exec_query() and checked backtraces of the two queries executed on the data node:
select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1
|
select t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1)
|
(gdb) p query
|
$12 = 0x7fff98198fe0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1"
|
(gdb) bt
|
#0 spider_db_mbase::exec_query (this=0x7fff98051c70,
|
query=0x7fff98198fe0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1", length=86, quick_mode=3)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:2113
|
#1 0x00007ffff4395600 in spider_db_query (conn=0x7fff98151f90,
|
query=0x7fff98198fe0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1", length=86, quick_mode=3, need_mon=0x7fff98150368)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_conn.cc:694
|
#2 0x00007ffff44a6bd0 in spider_mbase_handler::execute_sql (this=0x7fff981504c0, sql_type=1, conn=0x7fff98151f90, quick_mode=3, need_mon=0x7fff98150368)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14002
|
#3 0x00007ffff44399d1 in ha_spider::index_read_map_internal (this=0x7fff98141f08, buf=0x0, key=0x7ffff47fd7d0 "\001", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT)
|
at /home/vagrant/repo/mariadb-server/storage/spider/ha_spider.cc:2408
|
#4 0x00007ffff443a26d in ha_spider::pre_index_read_map (this=0x7fff98141f08, key=0x7ffff47fd7d0 "\001", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT,
|
use_parallel=false) at /home/vagrant/repo/mariadb-server/storage/spider/ha_spider.cc:2524
|
#5 0x0000555556974030 in ha_partition::handle_pre_scan (this=0x7fff98141678, reverse_order=false, use_parallel=false)
|
at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:7317
|
#6 0x000055555696eddf in ha_partition::common_index_read (this=0x7fff98141678, buf=0x7fff9810d3a0 "\377\001", have_start_key=true)
|
at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:5713
|
#7 0x000055555696e97e in ha_partition::index_read_map (this=0x7fff98141678, buf=0x7fff9810d3a0 "\377\001", key=0x7ffff47fd7d0 "\001", keypart_map=1,
|
find_flag=HA_READ_KEY_OR_NEXT) at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:5584
|
#8 0x0000555556107d41 in handler::ha_index_read_map (this=0x7fff98141678, buf=0x7fff9810d3a0 "\377\001", key=0x7ffff47fd7d0 "\001", keypart_map=1,
|
find_flag=HA_READ_KEY_OR_NEXT) at /home/vagrant/repo/mariadb-server/sql/handler.cc:2937
|
#9 0x00005555562b5b5e in get_index_min_value (table=0x7fff98140810, ref=0x7ffff47fd760, item_field=0x7fff98013a18, range_fl=3, prefix_len=4)
|
at /home/vagrant/repo/mariadb-server/sql/opt_sum.cc:139
|
#10 0x00005555562b6639 in opt_sum_query (thd=0x7fff98000d90, tables=..., all_fields=..., conds=0x7fff98015eb0)
|
at /home/vagrant/repo/mariadb-server/sql/opt_sum.cc:411
|
#11 0x0000555555ddc185 in JOIN::optimize_inner (this=0x7fff98015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:2227
|
#12 0x0000555555dd9fc9 in JOIN::optimize (this=0x7fff98015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:1659
|
#13 0x0000555555de554d in mysql_select (thd=0x7fff98000d90, tables=0x7fff98013e08, wild_num=0, fields=..., conds=0x7fff980146a8, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147486464, result=0x7fff98015308, unit=0x7fff98004cc0, select_lex=0x7fff98013588)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4740
|
#14 0x0000555555dd4e18 in handle_select (thd=0x7fff98000d90, lex=0x7fff98004c00, result=0x7fff98015308, setup_tables_done_option=0)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:436
|
#15 0x0000555555d999d1 in execute_sqlcom_select (thd=0x7fff98000d90, all_tables=0x7fff98013e08) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:6449
|
#16 0x0000555555d9019e in mysql_execute_command (thd=0x7fff98000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:3963
|
#17 0x0000555555d9da03 in mysql_parse (thd=0x7fff98000d90, rawbuf=0x7fff980134d8 "SELECT MIN(b), a FROM tbl_a WHERE a=1", length=37, parser_state=0x7ffff47ff520,
|
is_com_multi=false, is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:7995
|
#18 0x0000555555d89d0b in dispatch_command (command=COM_QUERY, thd=0x7fff98000d90, packet=0x7fff9800acf1 "SELECT MIN(b), a FROM tbl_a WHERE a=1", packet_length=37,
|
is_com_multi=false, is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1857
|
#19 0x0000555555d88571 in do_command (thd=0x7fff98000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1373
|
#20 0x0000555555f1a634 in do_handle_one_connection (connect=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1420
|
#21 0x0000555555f1a2cd in handle_one_connection (arg=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1316
|
#22 0x00007ffff7af7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
|
#23 0x00007ffff76ca293 in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
(gdb) p query
|
$14 = 0x7fff98198f10 "select t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1)"
|
(gdb) bt
|
#0 spider_db_mbase::exec_query (this=0x7fff98051c70, query=0x7fff98198f10 "select t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1)", length=71,
|
quick_mode=3) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:2113
|
#1 0x00007ffff4395600 in spider_db_query (conn=0x7fff98151f90, query=0x7fff98198f10 "select t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1)",
|
length=71, quick_mode=3, need_mon=0x7fff98150368) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_conn.cc:694
|
#2 0x00007ffff44a6bd0 in spider_mbase_handler::execute_sql (this=0x7fff981504c0, sql_type=1, conn=0x7fff98151f90, quick_mode=3, need_mon=0x7fff98150368)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14002
|
#3 0x00007ffff44c06e2 in spider_group_by_handler::init_scan (this=0x7fff98199580) at /home/vagrant/repo/mariadb-server/storage/spider/spd_group_by_handler.cc:1466
|
#4 0x0000555555e5ba03 in Pushdown_query::execute (this=0x7fff98018320, join=0x7fff98015330) at /home/vagrant/repo/mariadb-server/sql/group_by_handler.cc:49
|
#5 0x0000555555e0fd97 in do_select (join=0x7fff98015330, procedure=0x0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:20013
|
#6 0x0000555555de4d4b in JOIN::exec_inner (this=0x7fff98015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4534
|
#7 0x0000555555de3e7a in JOIN::exec (this=0x7fff98015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4316
|
#8 0x0000555555de55e6 in mysql_select (thd=0x7fff98000d90, tables=0x7fff98013e08, wild_num=0, fields=..., conds=0x7fff980146a8, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147486464, result=0x7fff98015308, unit=0x7fff98004cc0, select_lex=0x7fff98013588)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4754
|
#9 0x0000555555dd4e18 in handle_select (thd=0x7fff98000d90, lex=0x7fff98004c00, result=0x7fff98015308, setup_tables_done_option=0)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:436
|
#10 0x0000555555d999d1 in execute_sqlcom_select (thd=0x7fff98000d90, all_tables=0x7fff98013e08) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:6449
|
#11 0x0000555555d9019e in mysql_execute_command (thd=0x7fff98000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:3963
|
#12 0x0000555555d9da03 in mysql_parse (thd=0x7fff98000d90, rawbuf=0x7fff980134d8 "SELECT MIN(b), a FROM tbl_a WHERE a=1", length=37, parser_state=0x7ffff47ff520,
|
is_com_multi=false, is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:7995
|
#13 0x0000555555d89d0b in dispatch_command (command=COM_QUERY, thd=0x7fff98000d90, packet=0x7fff9800acf1 "", packet_length=37, is_com_multi=false,
|
is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1857
|
#14 0x0000555555d88571 in do_command (thd=0x7fff98000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1373
|
#15 0x0000555555f1a634 in do_handle_one_connection (connect=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1420
|
#16 0x0000555555f1a2cd in handle_one_connection (arg=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1316
|
#17 0x00007ffff7af7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
|
#18 0x00007ffff76ca293 in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
|
|
The query optimizer of the Spider node seems to spit the single SELECT with MIN into the two SELECTs. The first SELECT is with ORDER BY + LIMIT and is executed by the usual handler (not by the GROUP BY handler). The second SELECT is simple and is handled by the GROUP BY handler.
The bug doesn't occur if we disable the GROUP BY handler, so we can conclude that the cause of the bug lies around the GROUP BY hanlder.
|
|
Here are queries executed on the data node, when the GROUP BY handler is disabled:
select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1"
|
select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` = 1 order by `b`
|
The backtrace of the second SELECT via the usual handler:
(gdb) p query
|
$11 = 0x7fff9019a8e0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` = 1 order by `b`"
|
(gdb) bt
|
#0 spider_db_mbase::exec_query (this=0x7fff90051c70, query=0x7fff9019a8e0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` = 1 order by `b`", length=73,
|
quick_mode=3) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:2113
|
#1 0x00007ffff4397380 in spider_db_query (conn=0x7fff90153960, query=0x7fff9019a8e0 "select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` = 1 order by `b`",
|
length=73, quick_mode=3, need_mon=0x7fff90151d38) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_conn.cc:694
|
#2 0x00007ffff44a8950 in spider_mbase_handler::execute_sql (this=0x7fff90151e90, sql_type=1, conn=0x7fff90153960, quick_mode=3, need_mon=0x7fff90151d38)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14002
|
#3 0x00007ffff443b751 in ha_spider::index_read_map_internal (this=0x7fff90143928, buf=0x0, key=0x7fff90017c28 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
|
at /home/vagrant/repo/mariadb-server/storage/spider/ha_spider.cc:2408
|
#4 0x00007ffff443bfed in ha_spider::pre_index_read_map (this=0x7fff90143928, key=0x7fff90017c28 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT,
|
use_parallel=false) at /home/vagrant/repo/mariadb-server/storage/spider/ha_spider.cc:2524
|
#5 0x0000555556974030 in ha_partition::handle_pre_scan (this=0x7fff90143098, reverse_order=false, use_parallel=false)
|
at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:7317
|
#6 0x000055555696eddf in ha_partition::common_index_read (this=0x7fff90143098, buf=0x7fff90111dd0 "\377\001", have_start_key=true)
|
at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:5713
|
#7 0x000055555696e97e in ha_partition::index_read_map (this=0x7fff90143098, buf=0x7fff90111dd0 "\377\001", key=0x7fff90017c28 "\001", keypart_map=1,
|
find_flag=HA_READ_KEY_EXACT) at /home/vagrant/repo/mariadb-server/sql/ha_partition.cc:5584
|
#8 0x0000555556107d41 in handler::ha_index_read_map (this=0x7fff90143098, buf=0x7fff90111dd0 "\377\001", key=0x7fff90017c28 "\001", keypart_map=1,
|
find_flag=HA_READ_KEY_EXACT) at /home/vagrant/repo/mariadb-server/sql/handler.cc:2937
|
#9 0x0000555555e12ad8 in join_read_always_key (tab=0x7fff900174b8) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:21335
|
#10 0x0000555555e10e5f in sub_select (join=0x7fff90015330, join_tab=0x7fff900174b8, end_of_records=false)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:20574
|
#11 0x0000555555e10388 in do_select (join=0x7fff90015330, procedure=0x0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:20115
|
#12 0x0000555555de4d4b in JOIN::exec_inner (this=0x7fff90015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4534
|
#13 0x0000555555de3e7a in JOIN::exec (this=0x7fff90015330) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4316
|
#14 0x0000555555de55e6 in mysql_select (thd=0x7fff90000d90, tables=0x7fff90013e08, wild_num=0, fields=..., conds=0x7fff900146a8, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147486464, result=0x7fff90015308, unit=0x7fff90004cc0, select_lex=0x7fff90013588)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4754
|
#15 0x0000555555dd4e18 in handle_select (thd=0x7fff90000d90, lex=0x7fff90004c00, result=0x7fff90015308, setup_tables_done_option=0)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:436
|
#16 0x0000555555d999d1 in execute_sqlcom_select (thd=0x7fff90000d90, all_tables=0x7fff90013e08) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:6449
|
#17 0x0000555555d9019e in mysql_execute_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:3963
|
#18 0x0000555555d9da03 in mysql_parse (thd=0x7fff90000d90, rawbuf=0x7fff900134d8 "SELECT MIN(b), a FROM tbl_a WHERE a=1", length=37, parser_state=0x7ffff47ff520,
|
is_com_multi=false, is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:7995
|
#19 0x0000555555d89d0b in dispatch_command (command=COM_QUERY, thd=0x7fff90000d90, packet=0x7fff9000acf1 "", packet_length=37, is_com_multi=false,
|
is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1857
|
#20 0x0000555555d88571 in do_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1373
|
#21 0x0000555555f1a634 in do_handle_one_connection (connect=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1420
|
#22 0x0000555555f1a2cd in handle_one_connection (arg=0x55555833d430) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1316
|
#23 0x00007ffff7af7609 in start_thread () from /lib/x86_64-linux-gnu/libpt
|
|
|
> The query optimizer of the Spider node seems to spit the single SELECT with MIN into the two SELECTs.
The above statement was not accurate. The first SELECT is issued by get_index_min_value() and should be considered rather as a part of the query optimization.
|
|
The query that would be executed by the GROUP BY handler should be like the following. So, at least ORDER BY clause and LIMIT clause should be passed to the GROUP BY handler via Query struct.
select t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1) order by b limit 1
|
|
|
On 10.4, Query struct, which is used to pass a query to the GROUP BY handler, seems to have no place for the LIMIT clause unless I'm missing something. https://github.com/MariaDB/server/blob/mariadb-10.4.21/sql/group_by_handler.h#L58
Such a place is introduced to 10.5 or above by the following commit: https://github.com/MariaDB/server/commit/ebeb4f93e8de0e6763a55dbf9a265afc32459608
|
|
On 10.5, Query struct has limit attribute, but the bug still reproduces. The value of Query.limit.select_limit_cnt is set to 2^64-1.
(gdb) p *query->limit
|
$3 = {select_limit_cnt = 18446744073709551615, offset_limit_cnt = 0}
|
|
|
What should I do first is to find the place where MIN is optimized away and to find its residue. Then, I need to consider how to pass the residue to storage engines. The discussion on the LIMIT clause above should have been done later.
|
|
The bug does not reproduce on mariadb-10.4.10. So, I run git-bisect between 10.4.10 and 10.4 HEAD and got the following result:
68ceb4b460a784f0d7cd1505e6de04338cbd2bb3 is the first bad commit
|
commit 68ceb4b460a784f0d7cd1505e6de04338cbd2bb3
|
Author: Kentoku SHIBA <kentokushiba@gmail.com>
|
Date: Wed Apr 15 23:19:10 2020 +0900
|
|
MDEV-20502 Queries against spider tables return wrong values for columns following constant declarations.
|
|
When executing a query like "select id, 0 as const, val from ...", there are 3 columns(items) in Query->select at handlerton->create_group_by(). After that, MariaDB makes a temporary table with 2 columns. The skipped items are const item, so fixing Spider to skip const items for items at Query->select.
|
|
.../spider/bugfix/include/mdev_20502_deinit.inc | 11 ++++
|
.../spider/bugfix/include/mdev_20502_init.inc | 25 ++++++++
|
.../mysql-test/spider/bugfix/r/mdev_20502.result | 61 +++++++++++++++++++
|
.../mysql-test/spider/bugfix/t/mdev_20502.cnf | 3 +
|
.../mysql-test/spider/bugfix/t/mdev_20502.test | 71 ++++++++++++++++++++++
|
.../spider/mysql-test/spider/r/timestamp.result | 2 +-
|
storage/spider/spd_db_conn.cc | 3 +
|
storage/spider/spd_db_mysql.cc | 20 +++++-
|
storage/spider/spd_db_oracle.cc | 19 +++++-
|
storage/spider/spd_group_by_handler.cc | 5 ++
|
10 files changed, 216 insertions(+), 4 deletions(-)
|
create mode 100644 storage/spider/mysql-test/spider/bugfix/include/mdev_20502_deinit.inc
|
create mode 100644 storage/spider/mysql-test/spider/bugfix/include/mdev_20502_init.inc
|
create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_20502.result
|
create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_20502.cnf
|
create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_20502.test
|
bisect run success
|
|
|
On the last good commit, I run the test case and collect queries.
The query executed on the Spider node:
SELECT MIN(b), a FROM tbl_a WHERE a=1;
|
The corresponding queries executed on the data node:
select `a`,`b` from `auto_test_remote`.`tbl_a` where `a` >= 1 order by `a`,`b` limit 1
|
select min(t0.`b`) `a`,t0.`a` `a` from `auto_test_remote`.`tbl_a` t0 where (t0.`a` = 1)
|
|
|
The commit 69c86ab was also merged into 10.3, but the bug does not reproduce on 10.3.
|
|
As an experiment, I reverted 69c86ab, and MariaDB worked as intended.
|
|
Item that corresponds to MIN(b) is treated as a constant item, and thus it is skipped.
(gdb) p item->name
|
$7 = {str = 0x7fff94013c90 "MIN(b)", length = 6}
|
(gdb) p item->const_item()
|
$8 = true
|
MariaDB server allows having a non-aggregated column, in a select list, which is not named in GROUP BY clause. So, we cannot simply skip MIN(b) without taking care of this implicit GROUP BY.
A possible solution would automatically supplement GROUP BY clause at query reformation. Another solution, which looks more like workaround, would be to force ONLY_FULL_GROUP_BY.
|
|
serg Please review the following patch: https://github.com/MariaDB/server/commit/df3d7315fce7b3f466fa867db5b59076ad259843
|
|
df3d7315fce7b3f466fa867db5b59076ad259843 is ok to push!
|
|
My fix affected the sequence storage engine. So, I reverted the patch.
|
|
Test case without partitions and partial group by. I assume it's the same problem, because the patch (which was later reverted) fixes it, too.
# This may not work, e.g. for in-source builds, fix the path
|
#--source plugin/spider/spider/include/init_spider.inc
|
--source ../storage/spider/mysql-test/spider/include/init_spider.inc
|
|
SET spider_same_server_link= on;
|
--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
|
|
CREATE TABLE t (a INT, KEY(a));
|
CREATE TABLE t_spider (a INT, KEY(a)) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";
|
INSERT INTO t VALUES (1),(2),(3);
|
|
SELECT MAX(t2.a) FROM t_spider AS t1 JOIN t_spider AS t2 WHERE t1.a <> 0;
|
|
# Cleanup
|
|
DROP TABLE t_spider, t;
|
DROP SERVER s;
|
|
# This may not work, e.g. for in-source builds, fix the path
|
#--source plugin/spider/spider/include/deinit_spider.inc
|
--source ../storage/spider/mysql-test/spider/include/deinit_spider.inc
|
|
10.4 f3f09def
|
MAX(t2.a)
|
3
|
3
|
3
|
3
|
3
|
3
|
3
|
3
|
3
|
Reproducible at least on 10.4+.
|
|
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
|
|
I did some debugging myself and noticed that the row_items at
Protocol::send_result_set_row() contain two rows and understood
the implicit grouping. So I went back to Nayuta's patch, and {{mtr
--suite sql_sequence}} passed (on top of 11.0
be4ac773b02bb8e4ea944f65f7e4fe882855f7fc). So I am not sure what is
the problem with the sequence storage engine Nayuta mentioned. Perhaps
it will show up in CI.
|
|
OK, so the failure is with sequence.group_by, and it only happens
in 10.4.
Naively, the issue is with this line in
do_select(JOIN *join, Procedure *procedure)
JOIN_TAB *last_tab= join->join_tab + join->table_count -
|
join->exec_join_tab_cnt();
|
which was updated in commit 04899dbba585aff687860a7d193202f3218b51bb to
JOIN_TAB *last_tab= join->join_tab + join->exec_join_tab_cnt();
|
That commit is for MDEV-20468 which has affected versions 10.1+, but
was fixed for 10.5 only (why?).
I backported this patch to 10.4, with the help of
37a3d4467e3115f4d4dfcad0a6ee3c23e785f524 (for MDEV-23809) which
added some similar lines later. The backport commit is
04899dbba585aff687860a7d193202f3218b51bb.
And it seems to work, i.e. spider, sql_sequence, sequence suits, as
well as main.subselect* tests all pass. Will follow up on the CI.
|
|
I opened MDEV-33100 to backport MDEV-20468 to 10.4. However, it is
not even clear whether MDEV-20468 is still reproducible to 10.4, as
the commit (8e92d5e5e3842fbb805304f1d0e4f5b1def9a145) does not
contain any testcases, and the description of MDEV-20468 does not
contain any complete testcase either. Given 10.4 will reach EOL in a
few months, perhaps it makes more sense to simply fix this one for
10.5+, if we adopt Nayuta's fix. There are still a couple of things
to check about this fix, though.
|