Bug #110213 An incorrect query result

• Submitted: 25 Feb 2023 14:03
• Modified: 28 Feb 2023 14:09
• Reporter: John Jove
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[25 Feb 2023 14:03] John Jove

Description:
The SELECT statement in the following case returns an incorrect result (an empty result). The expected result should be {1}.

How to repeat:
CREATE TABLE t0(c0 tinyint(1));
INSERT INTO t0 VALUES (1);

SELECT * FROM t0 WHERE IF(0 = (t0.c0) > (-1.194192591E9), RIGHT((~ (NULL)), -1), 1); -- actual: {}, expected: {1}

[25 Feb 2023 14:28] John Jove

I wonder if the symbol = in expression IF(0 = ((t0.c0) > (-1.194192591E9)), null, 1) is an assignment operator or a comparison operator.

From the manual, I learn that symbol = in SET or UPDATE statements is an assignment operator. Does it mean that symbol is a comparison operator for other cases, especially in the above case? If so, it will not be a bug.

[28 Feb 2023 14:09] MySQL Verification Team

Hi Mr. Jove,

Thank you for your bug report.

However, this is not a place for asking questions. MySQL Support is the only service where you can ask questions.

Also, what you provided is not a bug, since the IF statement is ambiguously written. It is not a syntax error, but it is ambiguous.

Not a bug.