| Bug #96751 | Optimizer trace contains invalid JSON for FORCE INDEX queries | ||
|---|---|---|---|
| Submitted: | 4 Sep 2019 15:52 | Modified: | 22 Oct 2019 23:02 |
| Reporter: | Sergei Petrunia | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.17-debug | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Sep 2019 6:32]
MySQL Verification Team
Hello Sergei, Thank you for the report and test case. Verified as described. regards, Umesh
[22 Oct 2019 23:02]
Jon Stephens
Documented fix in the MySQL 8.0.19 changelog as follows:
In some cases, such as when a query uses FORCE INDEX, the cost
of reading the table is DBL_MAX; this was rounded up to 2e308
when printed, which is too large for the JSON parser, so that it
was not possible to extract parts of the optimizer trace using a
query such as SELECT JSON_EXTRACT(trace, '$**.table_scan') FROM
INFORMATION_SCHEMA.OPTIMIZER_TRACE. Now in such cases, values
greater than 1.5e308 are rounded down and printed as 1e308
instead.
Closed.
[16 Nov 2020 9:41]
Erlend Dahl
Bug#101430 Invalid JSON number 2e308 in opt trace for FORCE INDEX was marked as a duplicate.
Description: When a query uses FORCE INDEX syntax, the cost of reading the table is DBL_MAX. There are other scenarios where the intermediate cost will be DBL_MAX (printed as 2e308). The issue is, this number confuses the JSON parser, so it's not possible to extract parts of the optimizer trace. How to repeat: create table t10 ( a int, b int, key(a) ); insert into t10 values (1,1),(2,2),(3,3); set optimizer_trace=1; explain select * from t10 force index (a) where a<=2; select JSON_EXTRACT(trace, '$**.table_scan') fromI NFORMATION_SCHEMA.OPTIMIZER_TRACE; ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Terminate parsing due to Handler error." at position 1736. select substring(trace, 1736-50, 250) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "rows": 3, "cost": 2e308 }, "potential_range_indexes": [ { "index": "a", ... Suggested fix: I'm not sure if JSON spec allows DBL_MAX number. So it is not clear for me whether the issue is in JSON parser or in Optimizer Trace writer code. But it would be nice if different parts of the server were in agreement about what is a valid JSON number. (FWIW, in MariaDB the DBL_MAX is printed into trace and can be parsed back)