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
-