MySQL Bugs: #106198: different resultsets are produced under different optimizer

Bug #106198	different resultsets are produced under different optimizer_switch for same SQL
Submitted:	18 Jan 2022 13:42	Modified:	19 Jan 2022 1:29
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.25	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (x86-64)
Tags:	derived_merge, Optimizer

Description:
Hello,
  While configuring different value for optimizer_switch derived_merge, the same SQL produces different resultsets, and it's obvious that derived_merge optimization causes a bug. 
  However, the key is that there is a rand() function in the subquery.

  Please reference to `How to repeat` for detail.

 

How to repeat:
mysql> set @@session.optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select r a, r b from (select rand()*100 r from mysql.user) limit 1 ;
+-------------------+-------------------+
| a                 | b                 |
+-------------------+-------------------+
| 65.69651613542486 | 65.69651613542486 |
+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> set @@session.optimizer_switch = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select r a, r b from (select rand()*100 r from mysql.user) limit 1 ;
+-------------------+--------------------+
| a                 | b                  |
+-------------------+--------------------+
| 38.20826666262771 | 15.091240326958932 |
+-------------------+--------------------+
1 row in set (0.00 sec)

Hi Mr. Yue,

Thank you for your bug report.

However , what we need is a repeatable test case, WITHOUT usage of the random numbers.

We are waiting on your response ........

Actually, this is expected behaviour.

If you merge the derived table, rand() has to be executed twice !!!!!

Hence, this is not a bug, but it is how rand() is functioning on any relational database. You execute it twice and you get two values.

When derived table is materialised, it is executed only once !!!!!

Definitely, NOT a bug !!!!!

Hello,
  Thanks for your quick response. I'm wondering that if the following behavior is also expected:

```
mysql> create view mysql.tv1 as (select rand()*100 r from mysql.user);
Query OK, 0 rows affected (0.00 sec)

mysql> select r a, r b from (select r from mysql.tv1) limit 1 ;
+--------------------+-------------------+
| a                  | b                 |
+--------------------+-------------------+
| 29.853583061931268 | 70.99904815759422 |
+--------------------+-------------------+
1 row in set (0.01 sec)

```

  That is, I treet the view mysql.tv1 as a table, which has only 1 field r. But when I query the r field from the view mysql.tv1 throught a outer select, I get 2 different values of r, which I think is a conflict with my previous viewpoints.

Hi,

Yes, it is also the same, since here a view is materialised with a derived table.