Description:
SET_VAR optimizer hint doesn't recognize decimal point in <value>, as shown in the example below.
I use SET_VAR on secondary_engine_cost_threshold. If the value is "3", it works. If the value is "3.0" or "3.3", optimizer hint doesn't work. And the warning message seem to complain about the decimal point. The decimal point value works in a plain "set" command.
mysql> explain select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from t3;
--------------
explain select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from t3
--------------
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
--------------
show warnings
--------------
+-------+------+-----------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(0) AS `count(*)` from `oltp`.`t3` |
+-------+------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select /*+ SET_VAR(secondary_engine_cost_threshold=3.0) */ count(*) from t3;
--------------
explain select /*+ SET_VAR(secondary_engine_cost_threshold=3.0) */ count(*) from t3
--------------
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
--------------
show warnings
--------------
+---------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '.0) */ count(*) from t3' at line 1 |
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `oltp`.`t3` |
+---------+------+----------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> explain select /*+ SET_VAR(secondary_engine_cost_threshold=3.3) */ count(*) from t3;
--------------
explain select /*+ SET_VAR(secondary_engine_cost_threshold=3.3) */ count(*) from t3
--------------
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
--------------
show warnings
--------------
+---------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '.3) */ count(*) from t3' at line 1 |
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `oltp`.`t3` |
+---------+------+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> set secondary_engine_cost_threshold=3.3;
--------------
set secondary_engine_cost_threshold=3.3
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'secondary_engine_cost_threshold';
--------------
show variables like 'secondary_engine_cost_threshold'
--------------
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| secondary_engine_cost_threshold | 3.300000 |
+---------------------------------+----------+
1 row in set (0.03 sec)
How to repeat:
See description. You can use any table to replace "t3" in my query. It doesn't matter.
