Bug #116359 Different results between 8.0.27 and 8.0.31+ when using IN-LIST with TIME type

• Submitted: 15 Oct 2024 15:17
• Modified: 15 Oct 2024 15:59
• Reporter: Chen Wang (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[15 Oct 2024 15:17] Chen Wang

Description:
I found the mtrs result is strange when analyzing the in list regression commit
Bug#32311183: CPU overhead from IN lists much larger in 8.0.22.

    This is a regression from WL#9384, considering rewrite of prepared
    statement preparation and execution. During this work, the rewrite of
    IN predicate emerged to be considerably more complex than expected,
    and it was thus decided to perform only a minor rewrite that meant that
    only values that were true constant values would be used with the
    bisect execution strategy. The consequence of this was that long
    IN lists had to be searched sequentially rather than binary when the
    lists contain values that are constant for an execution (such as
    parameter values). It was however expected that this was a minor
    problem, and indeed it appears that only a few customers have been
    affected by this performance regression.

Test cases in line 137 ./mysql-test/t/func_in_none.test
 62 set @str_one = '1';
 63 set @date_one = '2020-01-01';
 64 set @time_one = '01:01:01';
 65 set @dt_one = '2020-01-01 01:01:01';
 66 set @json_one = '{"i":1, "s":"1"}';

134 SELECT i8 FROM t1
135 WHERE t IN (TIME'01:01:01', TIME'02:02:02', TIME'05:05:05');
136
137 SELECT i8 FROM t1 WHERE t IN (@time_one, @time_two, @time_five);
138 PREPARE ps FROM "SELECT i8 FROM t1 WHERE t IN (?, ?, ?)";
139 EXECUTE ps USING @time_one, @time_two, @time_five;
140 EXECUTE ps USING @null, @time_two, @time_five;
141 EXECUTE ps USING @time_one, @time_two, @time_five;
142 EXECUTE ps USING @null, @null, @null;
143 EXECUTE ps USING @time_one, @time_two, @time_five;

Why the prepare statement didn't return the results as 8.0.27 

Here is the result from 8.0.36
mysql> SELECT i8 FROM t1
    -> WHERE t IN (TIME'01:01:01', TIME'02:02:02', TIME'05:05:05');
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.02 sec)

mysql>
mysql> SELECT i8 FROM t1 WHERE t IN (@time_one, @time_two, @time_five);
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.01 sec)

mysql> PREPARE ps FROM "SELECT i8 FROM t1 WHERE t IN (?, ?, ?)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
Empty set (0.00 sec)

mysql> EXECUTE ps USING @null, @time_two, @time_five;
Empty set (0.00 sec)

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
Empty set (0.00 sec)

mysql> EXECUTE ps USING @null, @null, @null;
Empty set (0.00 sec)

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
Empty set (0.00 sec)

Here is the result from 8.0.27
mysql> SELECT i8 FROM t1 WHERE t IN (@time_one, @time_two, @time_five);
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

mysql> PREPARE ps FROM "SELECT i8 FROM t1 WHERE t IN (?, ?, ?)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

mysql> EXECUTE ps USING @null, @time_two, @time_five;
+------+
| i8   |
+------+
|    2 |
|    5 |
+------+
2 rows in set (0.00 sec)

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

mysql> EXECUTE ps USING @null, @null, @null;
Empty set (0.00 sec)

mysql> EXECUTE ps USING @time_one, @time_two, @time_five;
+------+
| i8   |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

Is it expected results in higher version?

How to repeat:
Use ./mysql-test/t/func_in_none.test

[15 Oct 2024 15:59] MySQL Verification Team

Hi Mr. Wang,

Thank you for your bug report.

However, it is not a bug.

As you can notice yourself, both test and result files have been changed intentionally. Which is why this test passes in all versions and realises ........

A behaviour is changed in the newer releases, because the old one was wrong.

It is hard to find out which bug fix  (not just WL) changed the behaviour, but it was intentionally.

Not a bug.