Bug #110281 Incorrect query result

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}

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.

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}

Hi,

No, this is not a bug.

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

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}

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.