[MDEV-32907] spider incorrectly translates sum() query to a column - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Embedded Server, Storage Engine - Spider
Labels:
- spider
- spider-gbh

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

MDEV-32273 Broken queries are passed to the group by handler for execution

Open

Activity

Ascending order - Click to sort in descending order

Iacovos Botsaris created issue - 2023-11-29 13:00

Sergei Golubchik made changes - 2023-11-29 19:53

Field	Original Value	New Value
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	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: {noformat} [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 {noformat} The problem I have encountered, is related with combining the IFNULL() function with the SUM() function. A simple example is the following: {noformat} 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' {noformat} Whereas, while using COALESCE ,on the spider, this works as expected: {noformat}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 \| +--------------+----------------+ {noformat} 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: {code:sql} 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"' {code} And here is the create table statement on the dataNode: {code:sql} 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 {code} Of course, when I run the query directly against the dataNode, it executes successfully: {noformat}SELECT target_1, NVL(SUM(target_2),0) AS TARGET_2 FROM testing.metrics limit 1; +-------------+-----------------+ \| target_1 \| TARGET_2 \| +-------------+-----------------+ \| 5000 \| 30474 \| +--------------+----------------+ {noformat} 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

Sergei Golubchik made changes - 2023-11-29 19:54

Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.2 [ 28603 ]

Yuchen Pei added a comment - 2023-11-30 00:08 - edited

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

evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql

OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');

create table t2 (c int);

create table t1 (c int) ENGINE=Spider

COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"';

insert into t1 values (3), (NULL);

select nvl(sum(c), 0) from t1;

# select sum(c) from t1;

# select sum(nvl(c, 0)) from t1;

drop table t1, t2;

drop server srv;

--disable_query_log

--disable_result_log

--source ../../t/test_deinit.inc

--enable_result_log

--enable_query_log

--echo #

--echo # end of test mdev_32907

--echo #

However, sum(nvl(c, 0)) works, have you tried to swap NVL with SUM in
your case, i.e. SUM(NVL(target_2,0)) ibotsaris?

Yuchen Pei added a comment - 2023-11-30 00:08 - edited 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 evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK" , DATABASE 'test' , user 'root' ); create table t2 (c int ); create table t1 (c int ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv",TABLE "t2"' ; insert into t1 values (3), ( NULL ); select nvl( sum (c), 0) from t1; # select sum (c) from t1; # select sum (nvl(c, 0)) from t1; drop table t1, t2; drop server srv; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc --enable_result_log --enable_query_log --echo # --echo # end of test mdev_32907 --echo # However, sum(nvl(c, 0)) works, have you tried to swap NVL with SUM in your case, i.e. SUM(NVL(target_2,0)) ibotsaris ?

Yuchen Pei made changes - 2023-11-30 00:46

Affects Version/s

10.5 [ 23123 ]

Yuchen Pei made changes - 2023-11-30 00:54

Labels

spider

spider spider-gbh

Yuchen Pei added a comment - 2023-11-30 00:56

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

Yuchen Pei added a comment - 2023-11-30 00:56 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

Yuchen Pei made changes - 2023-11-30 01:41

Summary

MariaDB, SPIDER

spider incorrectly translates sum() query to a column

Yuchen Pei made changes - 2023-11-30 01:42

Fix Version/s

10.5 [ 23123 ]

Yuchen Pei added a comment - 2023-11-30 01:44

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)

Yuchen Pei added a comment - 2023-11-30 01:44 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)

Roel Van de Paar made changes - 2023-11-30 02:11

Component/s		Embedded Server [ 11301 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		11.3 [ 28565 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Affects Version/s		11.2 [ 28603 ]
Affects Version/s		11.3 [ 28565 ]
Affects Version/s		11.4 [ 29301 ]
Affects Version/s	10.11.6 [ 29020 ]
Affects Version/s	11.3.1 [ 29416 ]

Roel Van de Paar made changes - 2023-11-30 02:11

Status

Open [ 1 ]

Confirmed [ 10101 ]

Roel Van de Paar made changes - 2023-11-30 02:12

Environment

OS, Debian11, Docker version 24.0.7, mariaDB official image from Docker hub

Roel Van de Paar added a comment - 2023-11-30 02:12

Confirmed, all versions 10.4+ dbg+opt

Roel Van de Paar added a comment - 2023-11-30 02:12 Confirmed, all versions 10.4+ dbg+opt

Yuchen Pei made changes - 2023-11-30 05:37

Affects Version/s	10.9 [ 26905 ]
Affects Version/s	10.10 [ 27530 ]
Affects Version/s	11.4 [ 29301 ]

Yuchen Pei made changes - 2023-11-30 05:37

Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]

Roel Van de Paar made changes - 2023-11-30 09:28

Affects Version/s

11.4 [ 29301 ]

Iacovos Botsaris added a comment - 2023-11-30 18:33 - edited

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

Iacovos Botsaris added a comment - 2023-11-30 18:33 - edited 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

Yuchen Pei added a comment - 2023-11-30 23:33

> Nevertheless, if I were to rewrite all queries, I would opt for COALESCE.

May I ask why?

Yuchen Pei added a comment - 2023-11-30 23:33 > Nevertheless, if I were to rewrite all queries, I would opt for COALESCE. May I ask why?

Iacovos Botsaris added a comment - 2023-12-02 08:20

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

Iacovos Botsaris added a comment - 2023-12-02 08:20 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

Yuchen Pei added a comment - 2023-12-14 07:39

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.

Yuchen Pei added a comment - 2023-12-14 07:39 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 .

Yuchen Pei made changes - 2023-12-14 07:40

Link

This issue relates to MDEV-32273 [ MDEV-32273 ]

Julien Fritsch made changes - 2024-05-03 14:32

Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.3 [ 28565 ]

Julien Fritsch made changes - 2024-09-10 14:56

Fix Version/s

10.4 [ 22408 ]

Julien Fritsch made changes - 2024-09-10 15:27

Fix Version/s

11.1 [ 28549 ]

Julien Fritsch made changes - 2024-11-22 17:09

Fix Version/s

11.2(EOL) [ 28603 ]

People

Assignee:: Yuchen Pei

Reporter:: Iacovos Botsaris

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-11-29 13:00

Updated:: 2024-11-22 17:09

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration