Description:
The same query return different result due to the optimizer switch(derived_merge). Although the optimizer switch is different, but for the result should be same for the same query.
How to repeat:
mysql> create table t1(double_col double);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(-1.7976931348623157e308);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-------------------------+
| double_col |
+-------------------------+
| -1.7976931348623157e308 |
+-------------------------+
1 row in set (0.00 sec)
mysql> set optimizer_switch="derived_merge=on";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from (select reverse(double_col) from t1) as t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from (select reverse(double_col) from t1) as t;
+-------------------------+
| reverse(double_col) |
+-------------------------+
| 803e7513268431396797.1- |
+-------------------------+
1 row in set (0.00 sec)
mysql> set optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from (select reverse(double_col) from t1) as t;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 2 | DERIVED | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> select * from (select reverse(double_col) from t1) as t;
+------------------------+
| reverse(double_col) |
+------------------------+
| 803e7513268431396797.1 |
+------------------------+
1 row in set (0.00 sec)
