MDEV-18880: Optimizer trace prints date in hexadecimal

Introduced a print_key_value function to makes sure that the trace prints data in readable format
for readable characters and the rest of the characters are printed as hexadecimal.

1 files changed, 292 insertions, 6 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 3e4b7fe6e8a..82a2196545d 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1446,7 +1446,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
                       {
                         "index": "id",
                         "covering": true,
-                        "ranges": ["(0x24a20f) <= (a)"],
+                        "ranges": ["(2001-01-04) <= (a)"],
                         "rows": 9,
                         "cost": 2.35
                       }
@@ -1462,7 +1462,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
                     "rows": 9,
                     "cost": 2.35,
                     "key_parts_used_for_access": ["id"],
-                    "ranges": ["(0x24a20f) <= (a)"],
+                    "ranges": ["(2001-01-04) <= (a)"],
                     "chosen": false,
                     "cause": "cost"
                   },
@@ -1624,7 +1624,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
                       {
                         "index": "id",
                         "covering": true,
-                        "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+                        "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
                         "rows": 9,
                         "cost": 2.35
                       }
@@ -1640,7 +1640,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
                     "rows": 9,
                     "cost": 2.35,
                     "key_parts_used_for_access": ["id", "a"],
-                    "ranges": ["(0x24a20f) <= (a) <= (0x24a20f)"],
+                    "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
                     "chosen": false,
                     "cause": "cost"
                   },
@@ -6130,7 +6130,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
                 "index": "start_date",
                 "ranges": 
                 [
-                    "(0x4ac60f,NULL) < (start_date,end_date)"
+                    "(2019-02-10,NULL) < (start_date,end_date)"
                 ],
                 "rowid_ordered": false,
                 "using_mrr": false,
@@ -6214,7 +6214,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
                 "index": "i_b",
                 "ranges": 
                 [
-                    "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+                    "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"
                 ],
                 "rowid_ordered": true,
                 "using_mrr": false,
@@ -6268,4 +6268,290 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
     }
 ]
 drop table t1;
+#
+# MDEV-18880: Optimizer trace prints date in hexadecimal
+#
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	13	const	1	Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\x0A) <= (b) <= (ab\x0A)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3787,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	11	const	1	Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3785,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	13	const	1	Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\x0A) <= (b) <= (ab\x0A)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3787,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1;
+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, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	11	const	1	Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\n) <= (b) <= (ab\n)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3785,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	1003	const	1	Using where
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\x0A) <= (b) <= (ab\x0A)"
+                ],
+                "rowid_ordered": false,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 3.5719,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1;
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(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='ab\n';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	13	const	2	Using index condition
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "i_b",
+                "ranges": 
+                [
+                    "(ab\n) <= (b) <= (ab\n)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 2,
+                "cost": 3.6324,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["start_date"],
+      "key": "start_date",
+      "key_length": "8",
+      "used_key_parts": ["start_date", "end_date"],
+      "rows": 1000,
+      "filtered": 100,
+      "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"
+    }
+  }
+}
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
+[
+    
+    {
+        "range_scan_alternatives": 
+        [
+            
+            {
+                "index": "start_date",
+                "ranges": 
+                [
+                    "(2019-02-10,NULL) < (start_date,end_date)"
+                ],
+                "rowid_ordered": false,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1000,
+                "cost": 1282.2,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1, t0, one_k;
 set optimizer_trace='enabled=off';