|
Reproducible as described.
|
10.4 fcc9f8b1
|
MariaDB [test]> SELECT IF(COUNT(a.`id` )>=0,'Y','N') FROM t1remote a;
|
ERROR 1054 (42S22): Unknown column '' in 'field list'
|
The resulting broken query:
|
10.4 fcc9f8b1
|
select count(t0.`id`) ``,(if((`` >= 0) , 'Y' , 'N')) `IF(COUNT(a.``id`` )>=0,'Y','N')` from `test`.`t1` t0
|
|
|
I created a test case for the issue and ran it on several MariaDB releases. According to the test results, the issue description seems not to be correct. The bug is reproducible even on mariadb-10.4.13.
--disable_warnings
|
--disable_query_log
|
--disable_result_log
|
--source ../../t/test_init.inc
|
--enable_result_log
|
--enable_query_log
|
--enable_warnings
|
|
--echo
|
--echo this test is for MDEV-25116
|
--echo
|
|
--echo prepare tables
|
|
--connection child2_1
|
CREATE DATABASE auto_test_remote;
|
USE auto_test_remote;
|
|
--disable_query_log
|
CREATE TABLE tbl_a (id INT);
|
--enable_query_log
|
|
--connection master_1
|
CREATE DATABASE auto_test_local;
|
USE auto_test_local;
|
|
--disable_query_log
|
eval CREATE TABLE tbl_a (
|
id INT
|
) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a", srv "s_2_1"';
|
--enable_query_log
|
|
--echo execute select
|
--connection master_1
|
SELECT IF(COUNT(id > 0),'Y','N') FROM tbl_a;
|
|
--echo clean up
|
--connection master_1
|
DROP DATABASE IF EXISTS auto_test_local;
|
--connection child2_1
|
DROP DATABASE IF EXISTS auto_test_remote;
|
> ./mysql-test/mtr spider/regression/e1121.MDEV-25116
|
Logging: /home/vagrant/repo/mariadb-server/mysql-test/mysql-test-run.pl spider/regression/e1121.MDEV-25116
|
vardir: /home/vagrant/repo/mariadb-server/build/mysql-test/var
|
Checking leftover processes...
|
Removing old var directory...
|
Creating var directory '/home/vagrant/repo/mariadb-server/build/mysql-test/var'...
|
Checking supported features...
|
MariaDB Version 10.4.13-MariaDB-debug
|
- SSL connections supported
|
- binaries are debug compiled
|
- binaries built with wsrep patch
|
Collecting tests...
|
Installing system database...
|
|
==============================================================================
|
|
TEST RESULT TIME (ms) or COMMENT
|
--------------------------------------------------------------------------
|
|
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
|
for master_1
|
for child2
|
child2_1
|
child2_2
|
child2_3
|
for child3
|
|
this test is for MDEV-25116
|
|
prepare tables
|
connection child2_1;
|
CREATE DATABASE auto_test_remote;
|
USE auto_test_remote;
|
connection master_1;
|
CREATE DATABASE auto_test_local;
|
USE auto_test_local;
|
execute select
|
connection master_1;
|
SELECT IF(COUNT(id > 0),'Y','N') FROM tbl_a;
|
spider/regression/e1121.MDEV-25116 [ fail ]
|
Test ended at 2021-04-18 11:58:41
|
|
CURRENT_TEST: spider/regression/e1121.MDEV-25116
|
mysqltest: At line 35: query 'SELECT IF(COUNT(id > 0),'Y','N') FROM tbl_a' failed: 1054: Unknown column '' in 'field list'
|
|
- saving '/home/vagrant/repo/mariadb-server/build/mysql-test/var/log/spider/regression/e1121.MDEV-25116/' to '/home/vagrant/repo/mariadb-server/build/mysql-test/var/log/e1121.MDEV-25116/'
|
--------------------------------------------------------------------------
|
The servers were restarted 0 times
|
Spent 0.000 of 5 seconds executing testcases
|
|
Failure: Failed 1/1 tests, 0.00% were successful.
|
|
Failing test(s): spider/regression/e1121.MDEV-25116
|
|
The log files in var/log may give you some hint of what went wrong.
|
|
If you want to report this error, please read first the documentation
|
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
|
|
mysql-test-run: *** ERROR: there were failing test cases
|
|
|
Spider SE rewrites the query, before pushing it down to data nodes, in spider_group_by_handler::init_scan(). This procedure, especially the call of spider_mbase_handler::append_list_item_select_part(), seems to generate the broken query.
1316 if ((error_num = dbton_hdl->append_list_item_select_part(
|
(gdb) p ((spider_mbase_handler*)dbton_hdl)->sql
|
$8 = {mem_calc_inited = true, str = {<Charset> = {
|
m_charset = 0x5555575092a0 <my_charset_utf8_general_ci>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>},
|
Ptr = 0x7fff9814fca0 "select ", str_length = 7}, Alloced_length = 8, extra_alloc = 0, alloced = true, thread_specific = false}, <No data fields>}, id = 59,
|
func_name = 0x7ffff447661b "<unknown>", file_name = 0x7ffff4476258 "/home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc", line_no = 8616,
|
current_alloc_mem = 8, next = 0x0}
|
(gdb) n
|
1317 query.select, NULL, 0, TRUE, fields, SPIDER_SQL_TYPE_SELECT_SQL)))
|
(gdb) p ((spider_mbase_handler*)dbton_hdl)->sql
|
$9 = {mem_calc_inited = true, str = {<Charset> = {
|
m_charset = 0x5555575092a0 <my_charset_utf8_general_ci>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>},
|
Ptr = 0x7fff98052330 "select count((t0.`id` > 0)) ``,(if(`` , _latin1'Y' , _latin1'N')) `IF(COUNT(id > 0),'Y','N')`,", str_length = 93},
|
Alloced_length = 120, extra_alloc = 0, alloced = true, thread_specific = false}, <No data fields>}, id = 59, func_name = 0x7ffff447661b "<unknown>",
|
file_name = 0x7ffff4476258 "/home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc", line_no = 8616, current_alloc_mem = 120, next = 0x0}
|
|
|
The bug is reproducible also on 10.3 and 10.6, but not on 10.2. This is because 10.2 does not push down `IF(COUNT(...))` to a data node.
|
|
The first empty alias name `` is determined by item_name in the following code. A problem is that item_name of COUNT(t0.`id`) is empty (0x0).
int spider_mbase_handler::append_list_item_select(
|
List<Item> *select,
|
...
|
spider_fields *fields
|
) {
|
...
|
while ((item = it++))
|
{
|
...
|
field = *(fields->get_next_field_ptr());
|
if (field)
|
{
|
item_name = SPIDER_field_name_str(field);
|
length = SPIDER_field_name_length(field);
|
} else {
|
item_name = SPIDER_item_name_str(item);
|
length = SPIDER_item_name_length(item);
|
}
|
...
|
}
|
...
|
DBUG_RETURN(0);
|
}
|
|
|
SELECT COUNT(id) FROM tbl_a is rewritten to the following. This seems to be OK.
select count(t0.`id`) `COUNT(id)` from `auto_test_remote`.`tbl_a` t0
|
|
|
Backtraces for these two queries look the same:
SELECT COUNT(id) FROM tbl_a
(gdb) bt
|
#0 spider_mbase_handler::append_list_item_select (this=0x7fff900f41d0, select=0x7fff900126c0, str=0x7fff900f4228, alias=0x0, alias_length=0, use_fields=true, fields=0x7fff9012a1f0)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14425
|
#1 0x00007ffff446b474 in spider_mbase_handler::append_list_item_select_part (this=0x7fff900f41d0, select=0x7fff900126c0, alias=0x0, alias_length=0, use_fields=true,
|
fields=0x7fff9012a1f0, sql_type=1) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14382
|
#2 0x00007ffff4474457 in spider_group_by_handler::init_scan (this=0x7fff9012a620) at /home/vagrant/repo/mariadb-server/storage/spider/spd_group_by_handler.cc:1316
|
#3 0x0000555555d1bbef in Pushdown_query::execute (this=0x7fff90014030, join=0x7fff900123a0) at /home/vagrant/repo/mariadb-server/sql/group_by_handler.cc:49
|
#4 0x0000555555cd30d8 in do_select (join=0x7fff900123a0, procedure=0x0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:19283
|
#5 0x0000555555caa2e7 in JOIN::exec_inner (this=0x7fff900123a0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4142
|
#6 0x0000555555ca96a6 in JOIN::exec (this=0x7fff900123a0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:3936
|
#7 0x0000555555caa9db in mysql_select (thd=0x7fff90000d90, tables=0x7fff90011b80, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
|
select_options=2147486464, result=0x7fff90012378, unit=0x7fff90004c40, select_lex=0x7fff900053d8) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4344
|
#8 0x0000555555c9be3e in handle_select (thd=0x7fff90000d90, lex=0x7fff90004b80, result=0x7fff90012378, setup_tables_done_option=0)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:372
|
#9 0x0000555555c6204d in execute_sqlcom_select (thd=0x7fff90000d90, all_tables=0x7fff90011b80) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:6339
|
#10 0x0000555555c58a21 in mysql_execute_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:3870
|
#11 0x0000555555c663ee in mysql_parse (thd=0x7fff90000d90, rawbuf=0x7fff90011818 "SELECT COUNT(id) FROM tbl_a", length=27, parser_state=0x7ffff47a7580, is_com_multi=false,
|
is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:7870
|
#12 0x0000555555c52aaf in dispatch_command (command=COM_QUERY, thd=0x7fff90000d90, packet=0x7fff90009031 "", packet_length=27, is_com_multi=false, is_next_command=false)
|
at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1852
|
#13 0x0000555555c5144f in do_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1398
|
#14 0x0000555555dd1d82 in do_handle_one_connection (connect=0x555557fe6830) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1403
|
#15 0x0000555555dd1ade in handle_one_connection (arg=0x555557fe6830) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1308
|
#16 0x00007ffff77a3609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
|
#17 0x00007ffff76ca293 in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
SELECT IF(COUNT(id > 0),'Y','N') FROM tbl_a;
(gdb) bt
|
#0 spider_mbase_handler::append_list_item_select (this=0x7fff900f41d0, select=0x7fff90012a78, str=0x7fff900f4228, alias=0x0, alias_length=0, use_fields=true, fields=0x7fff9012a1f0)
|
at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14420
|
#1 0x00007ffff446b474 in spider_mbase_handler::append_list_item_select_part (this=0x7fff900f41d0, select=0x7fff90012a78, alias=0x0, alias_length=0, use_fields=true,
|
fields=0x7fff9012a1f0, sql_type=1) at /home/vagrant/repo/mariadb-server/storage/spider/spd_db_mysql.cc:14382
|
#2 0x00007ffff4474457 in spider_group_by_handler::init_scan (this=0x7fff9012a620) at /home/vagrant/repo/mariadb-server/storage/spider/spd_group_by_handler.cc:1316
|
#3 0x0000555555d1bbef in Pushdown_query::execute (this=0x7fff900145c0, join=0x7fff90012758) at /home/vagrant/repo/mariadb-server/sql/group_by_handler.cc:49
|
#4 0x0000555555cd30d8 in do_select (join=0x7fff90012758, procedure=0x0) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:19283
|
#5 0x0000555555caa2e7 in JOIN::exec_inner (this=0x7fff90012758) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4142
|
#6 0x0000555555ca96a6 in JOIN::exec (this=0x7fff90012758) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:3936
|
#7 0x0000555555caa9db in mysql_select (thd=0x7fff90000d90, tables=0x7fff90011fe0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
|
select_options=2147486464, result=0x7fff90012730, unit=0x7fff90004c40, select_lex=0x7fff900053d8) at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:4344
|
#8 0x0000555555c9be3e in handle_select (thd=0x7fff90000d90, lex=0x7fff90004b80, result=0x7fff90012730, setup_tables_done_option=0)
|
at /home/vagrant/repo/mariadb-server/sql/sql_select.cc:372
|
#9 0x0000555555c6204d in execute_sqlcom_select (thd=0x7fff90000d90, all_tables=0x7fff90011fe0) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:6339
|
#10 0x0000555555c58a21 in mysql_execute_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:3870
|
#11 0x0000555555c663ee in mysql_parse (thd=0x7fff90000d90, rawbuf=0x7fff90011818 "SELECT IF(COUNT(id > 0),'Y','N') FROM tbl_a", length=43, parser_state=0x7ffff47a7580,
|
is_com_multi=false, is_next_command=false) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:7870
|
#12 0x0000555555c52aaf in dispatch_command (command=COM_QUERY, thd=0x7fff90000d90, packet=0x7fff90009031 "", packet_length=43, is_com_multi=false, is_next_command=false)
|
at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1852
|
#13 0x0000555555c5144f in do_command (thd=0x7fff90000d90) at /home/vagrant/repo/mariadb-server/sql/sql_parse.cc:1398
|
#14 0x0000555555dd1d82 in do_handle_one_connection (connect=0x555557fe6830) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1403
|
#15 0x0000555555dd1ade in handle_one_connection (arg=0x555557fe6830) at /home/vagrant/repo/mariadb-server/sql/sql_connect.cc:1308
|
#16 0x00007ffff77a3609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
|
#17 0x00007ffff76ca293 in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
|
|
The latter empty empty `` is generated by spider_db_open_item_field. I guess this should have been processed by spider_db_open_item_ref.
|
|
The reason why it is not processed by spider_db\open_item_ref() is that item_ref->name.str is NULL.
int spider_db_open_item_ref(
|
Item_ref *item_ref,
|
...
|
) {
|
...
|
if (item_ref->ref)
|
{
|
if (
|
(*(item_ref->ref))->type() != Item::CACHE_ITEM &&
|
item_ref->ref_type() != Item_ref::VIEW_REF &&
|
!item_ref->table_name &&
|
item_ref->name.str &&
|
item_ref->alias_name_used
|
){
|
...
|
}
|
}
|
|
|
Anyway, I think that one of the causes of the bug is that the names, field->field_name.str and item_ref->name.str, is empty, while there might be other problems
|
|
My bad. I should have use IF(COUNT(id) > 0,'Y','N') rather than IF(COUNT(id > 0),'Y','N'). However, I think the conclusion is the same.
|
|
> The latter empty empty `` is generated by spider_db_open_item_field.
The latter empty empty `` is appended by spider_db_open_item_ident().
> I guess this should have been processed by spider_db_open_item_ref.
This might not be correct.
|
|
I noticed the problem is much more complex than I first expected because queries like select count(*) as foo, if(foo > 1, 'y', 'n') doesn't work.
|
|
The easiest way to fix the bug is to stop using the group by handler when a query contains Item_ref but this might leads to some performance degradation. https://github.com/MariaDB/server/commit/5f39b365856f65cc06c03dc0d2bcf065d644f9e6
|
|
A sightly better patch: https://github.com/MariaDB/server/commit/77fe31b8c4468511bdf6c5688ab3340c3015a88e
|
|
Another possible way to fix the bug would be to change the timing when the SQL for a data node is generated. I will try to see whether this works.
|
|
holyfoot please review: https://github.com/MariaDB/server/commit/e7b573637206a89722da7f72cbaaf76cbb63a634
|
|
holyfoot I may have come up with another way to fix it. If the server properly set Item::name even to Item_ref, Spider can easily print it. Let me withdraw the patch.
|
|
holyfoot The above idea didn't work. That is because the Item class has a recursive structure and Item::name seems to be given only to the outermost Item.
So, please review the original patch: https://github.com/MariaDB/server/commit/e7b573637206a89722da7f72cbaaf76cbb63a634
|
|
ok to push
|