Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Description
Hello,
I am running a MariaDB Standalone node, which acts as a Spider proxy to multiple back end Data Nodes.
These are the spider configuration options I have in place:
[mysqld]
|
plugin_load_add = "ha_spider"
|
# Spider engine settings
|
spider_skip_default_condition=0
|
spider_quick_mode=0
|
spider_direct_dup_insert=0
|
spider_direct_order_limit=1
|
spider_sts_bg_mode=1
|
spider_crd_sync=2
|
spider_crd_interval=0
|
spider_bka_mode=0
|
spider_bgs_mode=0
|
spider_multi_split_read=1
|
spider_bka_engine=memory
|
spider_index_hint_pushdown=1
|
spider_use_pushdown_udf=1
|
spider_remote_autocommit=1
|
spider_connect_timeout=1800
|
spider_net_read_timeout=1800
|
spider_net_write_timeout=1800
|
spider_local_lock_table=0
|
spider_bulk_update_mode=2
|
spider_general_log=ON
|
spider_log_result_errors=2
|
spider_log_result_error_with_sql=2
|
spider_conn_recycle_mode=1
|
use_stat_tables=preferably
|
spider_auto_increment_mode=2
|
# spider_sync_trx_isolation=
|
# log-error = /var/lib/mysql/error_spider.log
|
# general_log_file = /var/lib/mysql/query_spider.log
|
The problem I have encountered, is related with combining the IFNULL() function with the SUM() function.
A simple example is the following:
MariaDB [testing]> SELECT target_1, NVL(SUM(target_2),0) AS TARGET_2 FROM testing.metrics limit 1;
|
ERROR 1054 (42S22): Unknown column 'SUM(target_2)' in 'field list'
|
Whereas, while using COALESCE ,on the spider, this works as expected:
MariaDB [testing]> SELECT target_1, SUM(COALESCE(CASE WHEN target_2 IS NOT NULL THEN target_2 ELSE 0 END)) AS TARGET_2 FROM testing.metrics limit 1;
|
+-------------+-----------------+
|
| target_1 | TARGET_2 |
|
+-------------+-----------------+
|
| 5000 | 30474 |
|
+--------------+----------------+
|
Essentially, the spider tries to find a column 'SUM(target_2)' instead of evaluating the function.
Here is the create table statement on the Spider:
CREATE TABLE `metrics` ( |
`id` int(3) NOT NULL AUTO_INCREMENT, |
`code` varchar(20) NOT NULL DEFAULT '', |
`description` varchar(64) NOT NULL, |
`target_1` int(5) NOT NULL, |
`target_2` int(5) NOT NULL, |
`target_3` int(5) NOT NULL, |
`target_4` int(5) NOT NULL, |
`target_5` int(3) unsigned DEFAULT 0, |
`branch` varchar(4) NOT NULL, |
`enabled` tinyint(1) NOT NULL DEFAULT 1, |
`datetime_created` timestamp(3) NOT NULL DEFAULT current_timestamp(3), |
`datetime_updated` timestamp(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3), |
PRIMARY KEY (`id`), |
UNIQUE KEY `uq_description_branch` (`description`,`branch`) |
) ENGINE=SPIDER AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNode2", database "testing", table "metrics"' |
And here is the create table statement on the dataNode:
CREATE TABLE `metrics` ( |
`id` int(3) NOT NULL AUTO_INCREMENT, |
`code` varchar(20) NOT NULL DEFAULT '', |
`description` varchar(64) NOT NULL, |
`target_1` int(5) NOT NULL, |
`target_2` int(5) NOT NULL, |
`target_3` int(5) NOT NULL, |
`target_4` int(5) NOT NULL, |
`target_5` int(3) unsigned DEFAULT 0, |
`branch` varchar(4) NOT NULL, |
`enabled` tinyint(1) NOT NULL DEFAULT 1, |
`datetime_created` timestamp(3) NOT NULL DEFAULT current_timestamp(3), |
`datetime_updated` timestamp(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3), |
PRIMARY KEY (`id`), |
UNIQUE KEY `uq_description_branch` (`description`,`branch`) |
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
Of course, when I run the query directly against the dataNode, it executes successfully:
SELECT target_1, NVL(SUM(target_2),0) AS TARGET_2 FROM testing.metrics limit 1;
|
+-------------+-----------------+
|
| target_1 | TARGET_2 |
|
+-------------+-----------------+
|
| 5000 | 30474 |
|
+--------------+----------------+
|
If removing SUM(), or IFNULL(), the query executes; the error occurs in the combination of both, whatever the query.
IFNULL(SUM...) fails in all queries.
charsets and collation are identical across the nodes, if relevant.
Thank you
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
I could reproduce it at 10.5 387b92df97e70680641ad0bcaed83b44373f13c5.
It is caused by the query generated by the group by handler of course:
select sum(t0.`c`) `sum(c)`,(ifnull(`sum(c)` , 0)) `nvl(sum(c), 0)` from `test`.`t2` t0
However, when trying to build in 10.4
cd79f102110a3543bc78ef4bec4dbeadaf1f1a83 I get strange mtr failures
(for any test I tried, including the one in the previous comment, not
just the one below)
Could not execute 'check-testcase' before testcase 'spider/bugfix.mdev_29562' (res: 1):
|
mysqltest: Logging to '/home/ycp/source/mariadb-server/10.4/build/mysql-test/var/tmp/check-mysqld_2_1.log'.
|
mysqltest: Results saved in '/home/ycp/source/mariadb-server/10.4/build/mysql-test/var/tmp/check-mysqld_2_1.result'.
|
=================================================================
|
==313014==ERROR: AddressSanitizer: global-buffer-overflow on address 0x5614259cc098 at pc 0x7ff4d2c5e061 bp 0x7fff379f5180 sp 0x7fff379f4930
|
WRITE of size 64 at 0x5614259cc098 thread T0
|
#0 0x7ff4d2c5e060 in __interceptor_regcomp ../../../../src/libsanitizer/sanitizer_common/sanitizer_common_interceptors.inc:7909
|
#1 0x5614253c79ba in init_re_comp(regex_t*, char const*) /home/ycp/source/mariadb-server/10.4/src/client/mysqltest.cc:9391
|
#2 0x5614253c7adb in init_re /home/ycp/source/mariadb-server/10.4/src/client/mysqltest.cc:9480
|
#3 0x5614253c984e in main /home/ycp/source/mariadb-server/10.4/src/client/mysqltest.cc:9864
|
#4 0x7ff4d26461c9 in __libc_start_call_main ../sysdeps/nptl/libc_start_call_main.h:58
|
#5 0x7ff4d2646284 in __libc_start_main_impl ../csu/libc-start.c:360
|
#6 0x5614253980f0 in _start (/home/ycp/source/mariadb-server/10.4/build/client/mysqltest+0x10e0f0)
|
Hello Yuchen Pei, thank you for the prompt response.
Yes, I did try inverting the functions, and the queries get executed.
Nevertheless, if I were to rewrite all queries, I would opt for COALESCE.
Resolving the issue would be greatly appreciated.
Thank you
> Nevertheless, if I were to rewrite all queries, I would opt for COALESCE.
May I ask why?
Hello,
Just to be consistent across queries - technologies etc.
Majority of queries in our case were written using CASE, so if I were to rewrite everything I would try and standardize it somehow.
Nevertheless, reality is that rewriting all queries is not easily achievable, so again, if this can be resolved it will be greatly appreciated.
Thank you
I did some further analysis. The problem is that at exec stage, when
init_scan() is called on the spider group by handler, the optimizer
has already transformed the query to an invalid one (the `sum(c)`
inside ifnull is an invalid column):
(rr) dbp select_lex
|
$13 = 0x56302c8019c0 <dbug_item_print_buf> "select ifnull(`sum(c)`,0) AS `nvl(sum(c), 0)` from t1"
|
However, at optimizer stage, when the spider group by handler is
created, the query is still ok:
(rr) dbp select_lex
|
$12 = 0x56302c8019c0 <dbug_item_print_buf> "select ifnull(sum(t1.c),0) AS `nvl(sum(c), 0)` from t1"
|
So, this is an example of MDEV-32273.
Hi, thanks for reporting. I could reproduce it in a simple case in
10.11 3e90efe4c98f00e96e081bd1c571ea1e25b06258 with the following
case:
--echo #--echo # MDEV-32907--echo #--disable_query_log--disable_result_log--source ../../t/test_init.inc--enable_result_log--enable_query_log--disable_query_log--disable_result_log--source ../../t/test_deinit.inc--enable_result_log--enable_query_log--echo #--echo # end of test mdev_32907However, sum(nvl(c, 0)) works, have you tried to swap NVL with SUM in
your case, i.e. SUM(NVL(target_2,0)) ibotsaris?