MySQL Bugs: #110439: On the Inconsistency of Coalesce Functions and ! Operator

Bug #110439	On the Inconsistency of Coalesce Functions and ! Operator
Submitted:	20 Mar 2023 16:39	Modified:	22 Mar 2023 13:54
Reporter:	Chenglin Tian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
On the Inconsistency of Coalesce Functions

An inconsistency occurred when I used the coalesce function and some simple logical operators simultaneously. The following statement is my problem statement. I expected to obtain a piece of data, but returned an empty set

SELECT t2.c0 AS ref0 FROM t2 WHERE (! ((((+ (COALESCE(t2.c1, t2.c0, 1081424713)))) && (NULL)) || (t2.c1)));

How to repeat:
Table building statement：
CREATE TABLE t0(c0 DECIMAL  COLUMN_FORMAT DYNAMIC, c1 MEDIUMINT(188) ZEROFILL  NULL COLUMN_FORMAT DEFAULT , c2 DECIMAL ZEROFILL  COLUMN_FORMAT DYNAMIC STORAGE DISK UNIQUE KEY COMMENT 'asdf' ) ;
ALTER TABLE t0 ROW_FORMAT REDUNDANT, RENAME t0, DISABLE KEYS, STATS_PERSISTENT DEFAULT, DELAY_KEY_WRITE 1, STATS_AUTO_RECALC 0, ALGORITHM INSTANT, INSERT_METHOD NO;
ALTER TABLE t0 STATS_AUTO_RECALC 1, DROP COLUMN c2, STATS_PERSISTENT DEFAULT, ROW_FORMAT COMPACT, ALGORITHM COPY, DELAY_KEY_WRITE 0, RENAME TO t0, CHECKSUM 0, COMPRESSION 'LZ4', FORCE;
CHECKSUM TABLE t0 EXTENDED;
INSERT DELAYED IGNORE INTO t0(c1, c0) VALUES(-1327289544, 192159833);
TRUNCATE TABLE t0;
INSERT HIGH_PRIORITY IGNORE INTO t0(c1) VALUES(1179557396);
SET SESSION range_optimizer_max_mem_size = 4199116503873264337;
CREATE INDEX i0 ON t0(('5l')) VISIBLE;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database5';
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database5';
INSERT INTO t0(c0) VALUES(NULL);
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database5';
INSERT HIGH_PRIORITY INTO t0(c0) VALUES(NULL), (424610218), (NULL);
SET SESSION max_sp_recursion_depth = 195;
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database5';
INSERT IGNORE INTO t0(c0) VALUES(NULL);
ALTER TABLE t0 STATS_PERSISTENT 0, FORCE;
INSERT IGNORE INTO t0(c0, c1) VALUES(NULL, NULL);
INSERT INTO t0(c0) VALUES(NULL);
INSERT DELAYED IGNORE INTO t0(c1) VALUES('');
ANALYZE  TABLE t0 UPDATE HISTOGRAM ON c0;
INSERT LOW_PRIORITY IGNORE INTO t0(c1) VALUES(5.38826935E8);
ALTER TABLE t0 STATS_AUTO_RECALC 0, DISABLE KEYS, INSERT_METHOD NO, RENAME t2;
ALTER TABLE t2 ;

Problematic statement：
 SELECT t2.c0 AS ref0 FROM t2 WHERE (! ((((+ (COALESCE(t2.c1, t2.c0, 1081424713)))) && (NULL)) || (t2.c1)));
expected：[null]
actual:empty set

Suggested fix:
Perhaps this problem is related to the Coalesce function and! When using operators together, there is a problem optimizing the expression

Hi Mr. Tian,

Thank you for your bug report.

However, we are not able to repeat it.

First of all, you are using an outdated release of our version 8.0.

Your test case, simply, does not run.

For example, INSTANT algorithm is impossible to be executed under those circumstances.

Next, you are using lot's of options that are valid only for MyISAM and MyISAM engine is not longer supported.

Next, we do not see any purpose in your SELECT expression. Operators ! and + are not used according to SQL rules. Hence, you should rewrite your queries.

Last, but not least, you have not explained why do you expect one result over the other.

Can't repeat .....

Hello, we have reorganized the problematic test cases

First of all, the database version we are using is 8.0.29, and the
storage engine for this version is Innodb. We have confirmed that. The
following are our simplified test cases. Some statements submitted
previously have misled you, and we apologize for this.

CREATE TABLE t0(c0 DECIMAL  COLUMN_FORMAT DYNAMIC, c1 MEDIUMINT(188) ZEROFILL  NULL COLUMN_FORMAT DEFAULT , c2 DECIMAL ZEROFILL  COLUMN_FORMAT DYNAMIC STORAGE DISK UNIQUE KEY COMMENT 'asdf' ) ;
INSERT DELAYED IGNORE INTO t0(c1, c0) VALUES(-1327289544, 192159833);
INSERT HIGH_PRIORITY IGNORE INTO t0(c1) VALUES(1179557396);
INSERT INTO t0(c0) VALUES(NULL);
INSERT HIGH_PRIORITY INTO t0(c0) VALUES(NULL), (424610218), (NULL);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c0, c1) VALUES(NULL, NULL);
INSERT INTO t0(c0) VALUES(NULL);
INSERT DELAYED IGNORE INTO t0(c1) VALUES('');
INSERT LOW_PRIORITY IGNORE INTO t0(c1) VALUES(5.38826935E8);
ALTER TABLE t0 STATS_AUTO_RECALC 0, DISABLE KEYS, INSERT_METHOD NO, RENAME t2;

--SELECT t2.c0 AS ref0 FROM t2 WHERE (! (((((COALESCE(t2.c1, t2.c0, 1081424713)))) && (NULL)) || (t2.c1)));
--expected:[192159833,null]
--actual:empty set

For this query, we expect to obtain two rows of data, with the t2.c1 column value being 0. Therefore, we made the following attempt to replace t2.c1 with 0 and found that the expected results can be returned, but there may be inconsistencies when using column references

--SELECT t2.c0 AS ref0 FROM t2 WHERE (! (((((COALESCE(t2.c1, t2.c0, 1081424713)))) && (NULL)) || (0)));
--expected and actual: [192159833,null]

We can obtain the expected results using the following query：

--SELECT t2.c0 AS ref0 FROM t2 WHERE  (((((COALESCE(t2.c1, t2.c0, 1081424713)))) && (NULL)) || (t2.c1));
--expected and actual: [null,null]

We noticed that the column t2.c1 in the result of this query is a non zero number, 1677215 and 1677215, respectively, but adding to this expression the ! operator will cause a problem, so we suspect that this inconsistency is related to column references where the data is 0

Hi Mr. Tian,

The result that you get is an expected result.

You are presuming that NULL and COALESCE, table columns of the various domains, other operators and functions are BOOLEAN , while they are not.

Simply, your query is not written correctly, so the result that you get is expected behaviour.