Bug #115697 for some case MySQL 9.0.1 result mismatch

• Submitted: 26 Jul 8:31
• Modified: 29 Jul 16:48
• Reporter: ximin liang (OCA)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 9.0.1, 8.4.2
• OS: Any
• CPU Architecture: Any
• Tags: regression

[26 Jul 8:31] ximin liang

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 |
+--------------------------------------------------------------------------------------------------------------------------------------+

[26 Jul 8:46] MySQL Verification Team

Hello ximin liang,

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

regards,
Umesh

[29 Jul 16:48] Dag Wanvik

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.