Bug #110281 Incorrect query result

• Submitted: 6 Mar 2023 12:04
• Modified: 9 Mar 2023 3:16
• Reporter: John Jove
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[6 Mar 2023 12:04] John Jove

Description:
The SELECT statement returns incorrect results in the case 1. However, when I try to rewrite IFNULL('0a', 1) to its expected value '0a', the SELECT statement returns correct results as in the case 2.

How to repeat:
Case 1:
CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 VALUES (0);
CREATE INDEX i0 ON t0(c0);
SELECT t0.c0 FROM t0 WHERE IFNULL('0a', 1) IN (t0.c0); -- actual:{}, expected:{0}

Case 2:
CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 VALUES (0);
CREATE INDEX i0 ON t0(c0);
SELECT t0.c0 FROM t0 WHERE '0a' IN (t0.c0); -- actual:{0}, expected:{0}

[7 Mar 2023 13:15] MySQL Verification Team

Hi Mr. Jove,

Thank you for your bug report.

However, this is not a bug.

Simply comparing strings with decimals is against all SQL standards.

Not a bug.

[8 Mar 2023 1:37] John Jove

I think the case 1 is a bug. I try to evaluate the same predicate used in the SELECT statement in case 1 in the following case. The result is expected. That means the SELECT statement in case 1 should return result 0 instead of nothing.

CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 VALUES (0);
CREATE INDEX i0 ON t0(c0);
SELECT IFNULL('0a', 1) IN (t0.c0) FROM t0; -- actual:{1}, expected:{1}

[8 Mar 2023 12:04] MySQL Verification Team

Hi,

No, this is not a bug.

Since '0a' is not NULL, then '0a' is returned.

[9 Mar 2023 3:16] John Jove

Is this an intended behavior in the following case? The same expression IFNULL('0a', 1) IN (t0.c0) is evaluated to different results.

CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 VALUES (0);
CREATE INDEX i0 ON t0(c0);
SELECT t0.c0 FROM t0 WHERE IFNULL('0a', 1) IN (t0.c0); -- {}
SELECT IFNULL('0a', 1) IN (t0.c0) FROM t0; -- {1}

[9 Mar 2023 13:39] MySQL Verification Team

Hi Mr. Jove,

This is totally different thing.

First test case was about a filtering condition, while your last query is a using nested queries, in the SELECT list, where SQL standards stipulate that Boolean must be always returned. Since your row contains 1, you get one row as a return. If you fill the same table with several rows, like 2,3,4, etc, you will get a result set of many rows where only first one will be 1, while the rest will be 0.

Please, this is not a forum for SQL standards, but for reporting bugs and this is not a bug.