Bug #112572 Inconsistent results of SELECT statement with HEX, REVERSE, and RPAD functions

• Submitted: 29 Sep 2023 9:44
• Modified: 29 Sep 2023 12:22
• Reporter: Zuming Jiang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[29 Sep 2023 9:44] Zuming Jiang

Description:
Dear MySQL developers,

I used my fuzzer to test MySQL and found a logic bug that made the MySQL server output inconsistent results.

How to repeat:
*** Set up the database ***

create table t6 (c22 int);
insert into t6 values (16);
insert into t6 values (80);

*** Test Case 1 ***

select * from t6
where HEX(t6.c22) > REVERSE(RPAD(case when true then '+YeL' else '+YeL' end, t6.c22, '7+02o'));

I simplified "case when true then '+YeL' else '+YeL' end" to "'+YeL'", and got Test Case 2:

*** Test Case 2 ***

select * from t6
where HEX(t6.c22) > REVERSE(RPAD('+YeL', t6.c22, '7+02o'));

*** Expected results ***

Test Case 1 and Test Case 2 return the same results.

*** Actual results ***

Test Case 1 and Test Case 2 return inconsistent results.

Test Case 1 return:

+------+
| c22  |
+------+
|   16 |
|   80 |
+------+
2 rows in set (0.01 sec)

Test Case 2 return:

+------+
| c22  |
+------+
|   16 |
+------+
1 row in set (0.00 sec)

*** Note ***

The bug can be reproduced in version 5.5 - 8.0.34.

[29 Sep 2023 12:22] MySQL Verification Team

Hi Mr. Jiang,

Thank you for your bug report.

We repeated your behaviour and we agree that this is a bug.

It is verified for the versions that are now maintained, which are 8.0, 8.1 and higher.

Verified as reported.

[29 Sep 2023 12:22] MySQL Verification Team

Hi Mr. Jiang,

Thank you for your bug report.

We repeated your behaviour and we agree that this is a bug.

It is verified for the versions that are now maintained, which are 8.0, 8.1 and higher.

Verified as reported.