MySQL Bugs: #115697: for some case MySQL 9.0.1 result mismatch

Bug #115697	for some case MySQL 9.0.1 result mismatch
Submitted:	26 Jul 2024 8:31	Modified:	29 Jul 2024 16:48
Reporter:	ximin liang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	9.0.1, 8.4.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I found a case in file `olap.test`, if executed in mysql client, result is not correct.
But if in mtr-tool, result is correct.
version: 9.0.1-debug

How to repeat:
#
# Bug#27735167: WITH ROLLUP: CRASH IN SET_FIELD_TO_NULL_WITH_CONVERSIONS
#
CREATE TABLE t(a INT,b BLOB);
INSERT INTO t VALUES (1,'a'),(1,'b'),(2,'c'),(3,'d');
SELECT (((@e:=`b`)) NOT BETWEEN 0x0b5f09 AND (CHAR(md5(@pub1),
(CONNECTION_ID()+LEAD(5225.750000,110) RESPECT NULLS OVER(ORDER BY b, a)))))
FROM t GROUP BY b,a WITH ROLLUP;

if execute in mysql client, notice first line result should be 1, no null value:
mysql> SELECT (((@e:=`b`)) NOT BETWEEN 0x0b5f09 AND (CHAR(md5(@pub1), (CONNECTION_ID()+LEAD(5225.750000,110) RESPECT NULLS OVER(ORDER BY b,
a))))) FROM t GROUP BY b,a WITH ROLLUP;
+--------------------------------------------------------------------------------------------------------------------------------------+
| (((@e:=`b`)) NOT BETWEEN 0x0b5f09 AND (CHAR(md5(@pub1), (CONNECTION_ID()+LEAD(5225.750000,110) RESPECT NULLS OVER(ORDER BY b, a))))) |
+--------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                 NULL |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
+--------------------------------------------------------------------------------------------------------------------------------------+

correct result: 
+--------------------------------------------------------------------------------------------------------------------------------------+
| (((@e:=`b`)) NOT BETWEEN 0x0b5f09 AND (CHAR(md5(@pub1), (CONNECTION_ID()+LEAD(5225.750000,110) RESPECT NULLS OVER(ORDER BY b, a))))) |
+--------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
|                                                                                                                                    1 |
+--------------------------------------------------------------------------------------------------------------------------------------+

Hello ximin liang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Posted by developer:
 
Setting user variables inside query expressions is deprecated, cf warning given:

"Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'."

because it is hard to give a reasonable semantics to the operations. Since it will be removed in a future release, this
issue will not be attempted fixed, so rejected.