[MDEV-19201] Escape characters in ranges should be printable in readable format in the optimizer trace Created: 2019-04-06  Updated: 2021-03-19

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: optimizer_trace


 Description   

Here is a test case:
CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
INSERT INTO t1 VALUES (1, 'ab\n');
INSERT INTO t1 VALUES (2, 'ab\n');

set optimizer_trace=1;
EXPLAIN SELECT * FROM t1 WHERE b IN ('ab\n');

select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;

                  "analyzing_range_alternatives": {
 
                    "range_scan_alternatives": [
 
                      {
 
                        "index": "i_b",
 
                        "ranges": ["ab
 
 <= b <= ab
 
"],
 
                        "rowid_ordered": true,
 
                        "using_mrr": false,
 
                        "index_only": false,
 
                        "rows": 2,
 
                        "cost": 3.632,
 
                        "chosen": true
 
                      }
 
                    ],

It would be good to see the range as 'ab\n' <= b <= 'ab\n'

select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;

select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
 
NULL
 
Warnings:
 
Warning	4036	Character disallowed in JSON in argument 1 to function 'json_extract' at position 2582



 Comments   
Comment by Varun Gupta (Inactive) [ 2019-04-10 ]

Another test case

CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(3), INDEX i_b(b));
 
INSERT INTO t1 VALUES (1, 0x010209);
 
INSERT INTO t1 VALUES (2, 0x010220);
 
 
 
set optimizer_trace=1;
 
EXPLAIN SELECT * FROM t1 WHERE b IN (0x010209);
 
EXPLAIN SELECT * FROM t1 WHERE b IN (0x010220);

Generated at Thu Feb 08 08:49:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.