MySQL Bugs: #101430: Invalid JSON number 2e308 in opt trace for FORCE INDEX

Bug #101430	Invalid JSON number 2e308 in opt trace for FORCE INDEX
Submitted:	3 Nov 2020 9:11	Modified:	16 Nov 2020 9:41
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With FORCE INDEX, the cost of table scan is assigned DBL_MAX. However, the stringified DBL_MAX (2e308) cannot be parsed by RapidJSON. See also

https://github.com/Tencent/rapidjson/issues/710

How to repeat:
set optimizer_trace_max_mem_size=1000000;
SET optimizer_trace="enabled=on";

create table t (a int primary key, b int, c int, key idx_b(b));
explain select count(*) from t force index (idx_b) where b = 2;

SELECT
  JSON_EXTRACT(TRACE, '$.steps[1].join_optimization.steps[4].rows_estimation[0].range_analysis.table_scan')
  as table_scan
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

select json_extract('{"cost": 2e308 }', '$.cost');

The incomplete json snippet of the opt trace above is:

            "rows_estimation": [
              {
                "table": "`t` FORCE INDEX (`idx_b`)",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1,
                    "cost": 2e308
                  },

2e308 caused the following error:

mysql> SELECT JSON_EXTRACT(TRACE, '$.steps[1].join_optimization.steps[4].rows_estimation[0].range_analysis.table_scan')  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Terminate parsing due to Handler error." at position 1942.

Hello Kaiwang,

Thank you for the report and feedback.

regards,
Umesh

See enclosed for a fix. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 2e308.patch (application/octet-stream, text), 1.39 KiB.

With the above patch:

mysql> explain select count(*) from t force index (idx_b) where b = 2;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ref  | idx_b         | idx_b | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

mysql>
mysql> SELECT
    ->   JSON_EXTRACT(TRACE, '$.steps[1].join_optimization.steps[4].rows_estimation[0].range_analysis.table_scan')
    ->   as table_scan
    -> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+---------------------------+
| table_scan                |
+---------------------------+
| {"cost": 2.45, "rows": 1} |
+---------------------------+
1 row in set (0.00 sec)

This is essentially the same underlying problem as reported in bug#94672

Not really. It is already using my_gcvt() to convert numbers to string.

I think this specific issue is due to misplaced trace code leaking internal state. For the detail, please refer to the patch in the previous comment.

BTW, the conversion setting must be wrong, and should be:

--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -367,7 +367,7 @@ Opt_trace_struct &amp;Opt_trace_struct::do_add(const char *key, Item *item) {
 Opt_trace_struct &amp;Opt_trace_struct::do_add(const char *key,
                                            const Cost_estimate &amp;value) {
   char buf[32];  // 32 is enough for digits of a double
-  my_gcvt(value.total_cost(), MY_GCVT_ARG_DOUBLE, FLT_DIG, buf, nullptr);
+  my_gcvt(value.total_cost(), MY_GCVT_ARG_DOUBLE, DBL_DIG, buf, nullptr);
   DBUG_PRINT("opt", ("%s: %s", key, buf));
   stmt-&gt;add(key, buf, strlen(buf), false, false);
   return *this;

However, even with DBL_DIG which gives a reasonable number. It is still not well supported by RapidJSON, as described by the RapidJSON issue.

You are right, I spoke too soon. Bug#94672 has been fixed a long time ago in 8.0 and I updated it accordingly.

However, we seem to be looking at different versions of the code. I'm looking at 8.0.22, which looks different:

Opt_trace_struct &Opt_trace_struct::do_add(const char *key, double val) {
  DBUG_ASSERT(started);
  char buf[32];  // 32 is enough for digits of a double
  /*
    To fit in FLT_DIG digits, my_gcvt rounds DBL_MAX (1.7976931...e308), or
    anything >=1.5e308, to 2e308. But JSON parsers refuse to read 2e308. So,
    lower the number.
  */
  my_gcvt(std::min(1e308, val), MY_GCVT_ARG_DOUBLE, FLT_DIG, buf, nullptr);
  DBUG_PRINT("opt", ("%s: %s", key, buf));
  stmt->add(key, buf, strlen(buf), false, false);
  return *this;
}
...
Opt_trace_struct &Opt_trace_struct::do_add(const char *key,
                                           const Cost_estimate &value) {
  return do_add(key, value.total_cost());
}

Leaving correctness of that code aside, the commit that changed those parts of code seems to address precisely the problem described here?

https://github.com/mysql/mysql-server/commit/f9c801f71d03cd60e5272dfc135cafcd64aa7c1f

"Bug#30266767 OPTIMIZER TRACE CONTAINS INVALID JSON FOR FORCE INDEX QUERIES

A cost of DBL_MAX (1.7976931...e308) is printed in opt trace in a rounded
way: 2e308. JSON parsing libraries can't parse that, it's too big.
Fix: for values bigger than 1.5e308, which are rounded to 2e308,
print 1e308.
"

Yes. The suggested commit (delivered in 8.0.19) covered exactly this issue. Sorry for the outdated issue.

Already fixed in 8.0.19

Duplicate of

Bug#96751 Optimizer trace contains invalid JSON for FORCE INDEX queries