MySQL Bugs: #117214: inconsistent result of two equivalent query

Bug #117214	inconsistent result of two equivalent query
Submitted:	16 Jan 2025 8:10	Modified:	17 Jan 2025 9:23
Reporter:	chi zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There are three queries, take the first query and its result as the part of the predicate of the second and the third one, these two query should be equivalence, but they produce the different result.

```
CREATE TABLE t32(c0 DOUBLE);
INSERT INTO t32(c0) VALUES (NULL);
INSERT INTO t32(c0) VALUES (1);

SELECT BIT_AND(t32.c0) AS c0 FROM t32 GROUP BY t32.c0; --18446744073709552000, 1
SELECT t32.c0 AS c0 FROM t32 WHERE (((CAST(t32.c0 AS DECIMAL)) >= ALL (SELECT 18446744073709552000 UNION SELECT 1)));
SELECT t32.c0 AS c0 FROM t32 WHERE (((CAST(t32.c0 AS DECIMAL)) >= ALL (SELECT BIT_AND(t32.c0) AS c0 FROM t32 GROUP BY t32.c0))); -- 1
```

How to repeat:
There is an online execute engine that can reproduce this result https://www.db-fiddle.com/f/pZFMkSDfPyaSFwVCwJrWgD/3

Suggested fix:
The second one and the third one should have the same result.

Hi Mr. zhang,

Thank you for your bug report.

However, this is not a bug.

Second and third queries are totally different and we do not see why should they produce the same result.

Not a bug.

Hi, 

The second query is created by replacing the subquery's result with the subquery itself. So I expect they are equivalence. But I sincerely apologize for submitting an incorrect report.

Hi Mr. zhang,

First of all, there is no need to apologise.

Second, GROUP BY and UNION cannot produce the same result, particularly since you did not use GROUP BY properly, due to the missing members in the GROUP BY, as explained in our Reference Manual.

Hi,

May I ask about the specific differences between the results returned by GROUP BY and UNION in this example? Shouldn't they both produce the same results like
+----------------------+
| 18446744073709552000 |
+----------------------+
|           1          |
+----------------------+

Hi Mr. zhang,

You are correct.

This is a verified bug in our Optimiser, in all supported versions, from 8.0 and higher.

Hi,

Thank you very much for confirming this report.

Posted by developer:
 
This is not a bug.
There are implicit conversions between data types that make us
loose precision in the calculations.
This is particularly vulnerable with floating point numbers.

Hi,

Thank you for your double check, I wonder is there any approach to query the type of the result of SELECT query. For example, the type of the result of `SELECT BIT_AND(t32.c0) AS c0 FROM t32 GROUP BY t32.c0;`

If you run the MySQL client, you can use the --column-type-info option.
A cruder way is to precede the SELECT clause with a CREATE TABLE clause and do SHOW CREATE TABLE on the created table afterwards.