[Commits] 1c6c0e651fc: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly

Varun varunraiko1803 at gmail.com
Fri Apr 5 12:27:30 EEST 2019


revision-id: 1c6c0e651fc3d6c576f2c49c8642b8b54f94f415 (mariadb-10.4.3-162-g1c6c0e651fc)
parent(s): 02d9b048a2ab549a3227a81e15ff2f8c45562a65
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-05 14:39:01 +0530
message:

MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly

Changed the function append_range_all_keyparts to use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges.
Also adjusted to print format for the ranges, now the ranges are printed as:
    (keypart1_min, keypart2_min,..)  OP (keypart1_name,keypart2_name, ..) OP (keypart1_max,keypart2_max, ..)

Also added more tests for range and index merge access for optimizer trace

---
 mysql-test/main/opt_trace.result                   | 276 ++++++++++-
 mysql-test/main/opt_trace.test                     |  57 +++
 mysql-test/main/opt_trace_index_merge.result       | 509 ++++++++++++++++++++-
 mysql-test/main/opt_trace_index_merge.test         | 112 +++++
 .../main/opt_trace_index_merge_innodb.result       |   6 +-
 sql/opt_range.cc                                   | 283 ++++++------
 sql/opt_range.h                                    |   7 +-
 7 files changed, 1080 insertions(+), 170 deletions(-)

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 12d4c713886..ed5881e08fd 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1248,7 +1248,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
                       {
                         "index": "a",
                         "covering": true,
-                        "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+                        "ranges": ["(2,3) <= (b,c) <= (2,3)"],
                         "rows": 8,
                         "cost": 2.2
                       }
@@ -1264,7 +1264,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
                     "rows": 8,
                     "cost": 2.2,
                     "key_parts_used_for_access": ["a", "b", "c"],
-                    "ranges": ["2 <= b <= 2 AND 3 <= c <= 3"],
+                    "ranges": ["(2,3) <= (b,c) <= (2,3)"],
                     "chosen": false,
                     "cause": "cost"
                   },
@@ -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": ["(0x24a20f) <= (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": ["(0x24a20f) <= (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": ["(0x24a20f) <= (a) <= (0x24a20f)"],
                         "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": ["(0x24a20f) <= (a) <= (0x24a20f)"],
                     "chosen": false,
                     "cause": "cost"
                   },
@@ -1856,7 +1856,7 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                     "range_scan_alternatives": [
                       {
                         "index": "a_c",
-                        "ranges": ["1 <= a <= 1"],
+                        "ranges": ["(1) <= (a) <= (1)"],
                         "rowid_ordered": false,
                         "using_mrr": false,
                         "index_only": false,
@@ -1866,7 +1866,7 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                       },
                       {
                         "index": "a_b",
-                        "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"],
+                        "ranges": ["(1,2) <= (a,b) <= (1,2)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": false,
@@ -1885,7 +1885,7 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                       "type": "range_scan",
                       "index": "a_b",
                       "rows": 21,
-                      "ranges": ["1 <= a <= 1 AND 2 <= b <= 2"]
+                      "ranges": ["(1,2) <= (a,b) <= (1,2)"]
                     },
                     "rows_for_plan": 21,
                     "cost_for_plan": 27.445,
@@ -2025,7 +2025,7 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                 "range_scan_alternatives": [
                   {
                     "index": "a_c",
-                    "ranges": ["1 <= a <= 1"],
+                    "ranges": ["(1) <= (a) <= (1)"],
                     "rowid_ordered": false,
                     "using_mrr": false,
                     "index_only": false,
@@ -2044,7 +2044,7 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                   "type": "range_scan",
                   "index": "a_c",
                   "rows": 180,
-                  "ranges": ["1 <= a <= 1"]
+                  "ranges": ["(1) <= (a) <= (1)"]
                 },
                 "rows_for_plan": 180,
                 "cost_for_plan": 231.72,
@@ -2895,7 +2895,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                     "range_scan_alternatives": [
                       {
                         "index": "pk",
-                        "ranges": ["2 <= pk <= 2"],
+                        "ranges": ["(2) <= (pk) <= (2)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": false,
@@ -2906,7 +2906,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                       },
                       {
                         "index": "pk_a",
-                        "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5"],
+                        "ranges": ["(2,5) <= (pk,a) <= (2,5)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": false,
@@ -2917,7 +2917,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                       },
                       {
                         "index": "pk_a_b",
-                        "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"],
+                        "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": true,
@@ -2964,7 +2964,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                       "type": "range_scan",
                       "index": "pk_a_b",
                       "rows": 1,
-                      "ranges": ["2 <= pk <= 2 AND 5 <= a <= 5 AND 1 <= b <= 1"]
+                      "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"]
                     },
                     "rows_for_plan": 1,
                     "cost_for_plan": 1.1793,
@@ -3338,7 +3338,7 @@ explain delete from t0 where t0.a<3	{
           "range_scan_alternatives": [
             {
               "index": "a",
-              "ranges": ["NULL < a < 3"],
+              "ranges": ["(NULL) < (a) < (3)"],
               "rowid_ordered": false,
               "using_mrr": false,
               "index_only": false,
@@ -3354,7 +3354,7 @@ explain delete from t0 where t0.a<3	{
             "type": "range_scan",
             "index": "a",
             "rows": 3,
-            "ranges": ["NULL < a < 3"]
+            "ranges": ["(NULL) < (a) < (3)"]
           },
           "rows_for_plan": 3,
           "cost_for_plan": 5.007,
@@ -3481,7 +3481,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                     "range_scan_alternatives": [
                       {
                         "index": "a",
-                        "ranges": ["NULL < a < 3"],
+                        "ranges": ["(NULL) < (a) < (3)"],
                         "rowid_ordered": false,
                         "using_mrr": false,
                         "index_only": true,
@@ -3500,7 +3500,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                       "type": "range_scan",
                       "index": "a",
                       "rows": 3,
-                      "ranges": ["NULL < a < 3"]
+                      "ranges": ["(NULL) < (a) < (3)"]
                     },
                     "rows_for_plan": 3,
                     "cost_for_plan": 1.407,
@@ -3546,7 +3546,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                     "range_scan_alternatives": [
                       {
                         "index": "a",
-                        "ranges": ["NULL < a < 3"],
+                        "ranges": ["(NULL) < (a) < (3)"],
                         "rowid_ordered": false,
                         "using_mrr": false,
                         "index_only": true,
@@ -3565,7 +3565,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                       "type": "range_scan",
                       "index": "a",
                       "rows": 3,
-                      "ranges": ["NULL < a < 3"]
+                      "ranges": ["(NULL) < (a) < (3)"]
                     },
                     "rows_for_plan": 3,
                     "cost_for_plan": 1.407,
@@ -6034,4 +6034,238 @@ COUNT(*)
 1
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+#
+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 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+explain select * from t1 force index (a_b) where a=2 and b=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_b	a_b	10	const,const	1	Using index
+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": "a_b",
+                "ranges": 
+                [
+                    "(2,4) <= (a,b) <= (2,4)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": true,
+                "rows": 1,
+                "cost": 1.1783,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+explain select * from t1 where a >= 900 and b between 10 and 20;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a_b	a_b	10	NULL	107	Using where; Using index
+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": "a_b",
+                "ranges": 
+                [
+                    "(900,10) <= (a,b)"
+                ],
+                "rowid_ordered": false,
+                "using_mrr": false,
+                "index_only": true,
+                "rows": 107,
+                "cost": 10.955,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t0,t1;
+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 select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	start_date	start_date	8	NULL	1000	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": "start_date",
+                "ranges": 
+                [
+                    "(0x4ac60f,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,one_k;
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+a int not null,
+b int not null,
+c int not null,
+d int not null,
+key a_b_c(a,b,c)
+);
+insert into t1 select a,a, a,a from ten;
+explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a_b_c	a_b_c	8	NULL	4	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": "a_b_c",
+                "ranges": 
+                [
+                    "(1) <= (a) < (4,50)"
+                ],
+                "rowid_ordered": false,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 4,
+                "cost": 6.2648,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table ten,t1;
+# Ported test from MYSQL for ranges involving Binary column
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	17	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": 
+                [
+                    "(0xd95b94336a9946a39cf5b58cfe772d8c) <= (b) <= (0xd95b94336a9946a39cf5b58cfe772d8c)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3797,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i_b	i_b	17	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": 
+                [
+                    "(NULL) <= (b) <= (NULL)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 1,
+                "cost": 2.3797,
+                "chosen": true
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+drop table t1;
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 4ec7c338acd..981a53ac1ad 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -387,4 +387,61 @@ SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba');
 DROP VIEW v1;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+--echo #
+
+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 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+
+explain select * from t1 force index (a_b) where a=2 and b=4;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+explain select * from t1 where a >= 900 and b between 10 and 20;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,one_k;
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+  a int not null,
+  b int not null,
+  c int not null,
+  d int not null,
+  key a_b_c(a,b,c)
+);
+
+insert into t1 select a,a, a,a from ten;
+explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table ten,t1;
+
+--echo # Ported test from MYSQL for ranges involving Binary column
+
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1;
+
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 50daef815d6..b5e68d04615 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -110,7 +110,7 @@ explain select * from t1 where a=1 or b=1	{
                             "range_scan_alternatives": [
                               {
                                 "index": "a",
-                                "ranges": ["1 <= a <= 1"],
+                                "ranges": ["(1) <= (a) <= (1)"],
                                 "rowid_ordered": true,
                                 "using_mrr": false,
                                 "index_only": true,
@@ -126,7 +126,7 @@ explain select * from t1 where a=1 or b=1	{
                             "range_scan_alternatives": [
                               {
                                 "index": "b",
-                                "ranges": ["1 <= b <= 1"],
+                                "ranges": ["(1) <= (b) <= (1)"],
                                 "rowid_ordered": true,
                                 "using_mrr": false,
                                 "index_only": true,
@@ -147,7 +147,7 @@ explain select * from t1 where a=1 or b=1	{
                             "type": "range_scan",
                             "index": "a",
                             "rows": 1,
-                            "ranges": ["1 <= a <= 1"],
+                            "ranges": ["(1) <= (a) <= (1)"],
                             "analyzing_roworder_intersect": {
                               "cause": "too few roworder scans"
                             }
@@ -156,7 +156,7 @@ explain select * from t1 where a=1 or b=1	{
                             "type": "range_scan",
                             "index": "b",
                             "rows": 1,
-                            "ranges": ["1 <= b <= 1"],
+                            "ranges": ["(1) <= (b) <= (1)"],
                             "analyzing_roworder_intersect": {
                               "cause": "too few roworder scans"
                             }
@@ -176,13 +176,13 @@ explain select * from t1 where a=1 or b=1	{
                           "type": "range_scan",
                           "index": "a",
                           "rows": 1,
-                          "ranges": ["1 <= a <= 1"]
+                          "ranges": ["(1) <= (a) <= (1)"]
                         },
                         {
                           "type": "range_scan",
                           "index": "b",
                           "rows": 1,
-                          "ranges": ["1 <= b <= 1"]
+                          "ranges": ["(1) <= (b) <= (1)"]
                         }
                       ]
                     },
@@ -243,3 +243,500 @@ explain select * from t1 where a=1 or b=1	{
 drop table t0,t1;
 set optimizer_trace="enabled=off";
 set @@optimizer_switch= @tmp_opt_switch;
+# More tests added index_merge access
+create table t1
+(
+/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+st_a int not null default 0,
+swt1a int not null default 0,
+swt2a int not null default 0,
+st_b int not null default 0,
+swt1b int not null default 0,
+swt2b int not null default 0,
+/* fields/keys for row retrieval tests */
+key1 int,
+key2 int,
+key3 int,
+key4 int,
+/* make rows much bigger then keys */
+filler1 char (200),
+filler2 char (200),
+filler3 char (200),
+filler4 char (200),
+filler5 char (200),
+filler6 char (200),
+/* order of keys is important */
+key sta_swt12a(st_a,swt1a,swt2a),
+key sta_swt1a(st_a,swt1a),
+key sta_swt2a(st_a,swt2a),
+key sta_swt21a(st_a,swt2a,swt1a),
+key st_a(st_a),
+key stb_swt1a_2b(st_b,swt1b,swt2a),
+key stb_swt1b(st_b,swt1b),
+key st_b(st_b),
+key(key1),
+key(key2),
+key(key3),
+key(key4)
+) ;
+create table t0 as select * from t1;
+# Printing of many insert into t0 values (....) disabled.
+alter table t1 disable keys;
+# Printing of many insert into t1 select .... from t0 disabled.
+# Printing of many insert into t1 (...) values (....) disabled.
+alter table t1 enable keys;
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+#  3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	key1,key2,key3	key1,key2,key3	5,5,5	NULL	2	Using intersect(key1,key2,key3); Using where; Using index
+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": "key1",
+                "ranges": 
+                [
+                    "(100) <= (key1) <= (100)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 2243,
+                "cost": 2862.1,
+                "chosen": true
+            },
+            
+            {
+                "index": "key2",
+                "ranges": 
+                [
+                    "(100) <= (key2) <= (100)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 2243,
+                "cost": 2862.1,
+                "chosen": false,
+                "cause": "cost"
+            },
+            
+            {
+                "index": "key3",
+                "ranges": 
+                [
+                    "(100) <= (key3) <= (100)"
+                ],
+                "rowid_ordered": true,
+                "using_mrr": false,
+                "index_only": false,
+                "rows": 2243,
+                "cost": 2862.1,
+                "chosen": false,
+                "cause": "cost"
+            }
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "intersecting_indexes": 
+            [
+                
+                {
+                    "index": "key1",
+                    "index_scan_cost": 58.252,
+                    "cumulated_index_scan_cost": 58.252,
+                    "disk_sweep_cost": 1923.1,
+                    "cumulative_total_cost": 1981.4,
+                    "usable": true,
+                    "matching_rows_now": 2243,
+                    "intersect_covering_with_this_index": false,
+                    "chosen": true
+                },
+                
+                {
+                    "index": "key2",
+                    "index_scan_cost": 58.252,
+                    "cumulated_index_scan_cost": 116.5,
+                    "disk_sweep_cost": 84.518,
+                    "cumulative_total_cost": 201.02,
+                    "usable": true,
+                    "matching_rows_now": 77.636,
+                    "intersect_covering_with_this_index": false,
+                    "chosen": true
+                },
+                
+                {
+                    "index": "key3",
+                    "index_scan_cost": 58.252,
+                    "cumulated_index_scan_cost": 174.76,
+                    "disk_sweep_cost": 0,
+                    "cumulative_total_cost": 174.76,
+                    "usable": true,
+                    "matching_rows_now": 2.6872,
+                    "intersect_covering_with_this_index": true,
+                    "chosen": true
+                }
+            ],
+            "clustered_pk": 
+            {
+                "clustered_pk_added_to_intersect": false,
+                "cause": "no clustered pk index"
+            },
+            "rows": 2,
+            "cost": 174.76,
+            "covering": true,
+            "chosen": true
+        },
+        "analyzing_index_merge_union": 
+        [
+        ]
+    }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+    
+    {
+        "range_access_plan": 
+        {
+            "type": "index_roworder_intersect",
+            "rows": 2,
+            "cost": 174.76,
+            "covering": true,
+            "clustered_pk_scan": false,
+            "intersect_of": 
+            [
+                
+                {
+                    "type": "range_scan",
+                    "index": "key1",
+                    "rows": 2243,
+                    "ranges": 
+                    [
+                        "(100) <= (key1) <= (100)"
+                    ]
+                },
+                
+                {
+                    "type": "range_scan",
+                    "index": "key2",
+                    "rows": 2243,
+                    "ranges": 
+                    [
+                        "(100) <= (key2) <= (100)"
+                    ]
+                },
+                
+                {
+                    "type": "range_scan",
+                    "index": "key3",
+                    "rows": 2243,
+                    "ranges": 
+                    [
+                        "(100) <= (key3) <= (100)"
+                    ]
+                }
+            ]
+        },
+        "rows_for_plan": 2,
+        "cost_for_plan": 174.76,
+        "chosen": true
+    }
+]
+# ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	154	Using union(intersect(key1,key2),intersect(key3,key4)); 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": 
+        [
+        ],
+        "analyzing_roworder_intersect": 
+        {
+            "cause": "too few roworder scans"
+        },
+        "analyzing_index_merge_union": 
+        [
+            
+            {
+                "indexes_to_merge": 
+                [
+                    
+                    {
+                        "range_scan_alternatives": 
+                        [
+                            
+                            {
+                                "index": "key1",
+                                "ranges": 
+                                [
+                                    "(100) <= (key1) <= (100)"
+                                ],
+                                "rowid_ordered": true,
+                                "using_mrr": false,
+                                "index_only": true,
+                                "rows": 2243,
+                                "cost": 170.53,
+                                "chosen": true
+                            },
+                            
+                            {
+                                "index": "key2",
+                                "ranges": 
+                                [
+                                    "(100) <= (key2) <= (100)"
+                                ],
+                                "rowid_ordered": true,
+                                "using_mrr": false,
+                                "index_only": true,
+                                "rows": 2243,
+                                "cost": 170.53,
+                                "chosen": false,
+                                "cause": "cost"
+                            }
+                        ],
+                        "index_to_merge": "key1",
+                        "cumulated_cost": 170.53
+                    },
+                    
+                    {
+                        "range_scan_alternatives": 
+                        [
+                            
+                            {
+                                "index": "key3",
+                                "ranges": 
+                                [
+                                    "(100) <= (key3) <= (100)"
+                                ],
+                                "rowid_ordered": true,
+                                "using_mrr": false,
+                                "index_only": true,
+                                "rows": 2243,
+                                "cost": 170.53,
+                                "chosen": true
+                            },
+                            
+                            {
+                                "index": "key4",
+                                "ranges": 
+                                [
+                                    "(100) <= (key4) <= (100)"
+                                ],
+                                "rowid_ordered": true,
+                                "using_mrr": false,
+                                "index_only": true,
+                                "rows": 2243,
+                                "cost": 170.53,
+                                "chosen": false,
+                                "cause": "cost"
+                            }
+                        ],
+                        "index_to_merge": "key3",
+                        "cumulated_cost": 341.05
+                    }
+                ],
+                "cost_of_reading_ranges": 341.05,
+                "use_roworder_union": true,
+                "cause": "always cheaper than non roworder retrieval",
+                "analyzing_roworder_scans": 
+                [
+                    
+                    {
+                        "type": "range_scan",
+                        "index": "key1",
+                        "rows": 2243,
+                        "ranges": 
+                        [
+                            "(100) <= (key1) <= (100)"
+                        ],
+                        "analyzing_roworder_intersect": 
+                        {
+                            "intersecting_indexes": 
+                            [
+                                
+                                {
+                                    "index": "key1",
+                                    "index_scan_cost": 58.252,
+                                    "cumulated_index_scan_cost": 58.252,
+                                    "disk_sweep_cost": 1923.1,
+                                    "cumulative_total_cost": 1981.4,
+                                    "usable": true,
+                                    "matching_rows_now": 2243,
+                                    "intersect_covering_with_this_index": false,
+                                    "chosen": true
+                                },
+                                
+                                {
+                                    "index": "key2",
+                                    "index_scan_cost": 58.252,
+                                    "cumulated_index_scan_cost": 116.5,
+                                    "disk_sweep_cost": 84.518,
+                                    "cumulative_total_cost": 201.02,
+                                    "usable": true,
+                                    "matching_rows_now": 77.636,
+                                    "intersect_covering_with_this_index": false,
+                                    "chosen": true
+                                }
+                            ],
+                            "clustered_pk": 
+                            {
+                                "clustered_pk_added_to_intersect": false,
+                                "cause": "no clustered pk index"
+                            },
+                            "rows": 77,
+                            "cost": 201.02,
+                            "covering": false,
+                            "chosen": true
+                        }
+                    },
+                    
+                    {
+                        "type": "range_scan",
+                        "index": "key3",
+                        "rows": 2243,
+                        "ranges": 
+                        [
+                            "(100) <= (key3) <= (100)"
+                        ],
+                        "analyzing_roworder_intersect": 
+                        {
+                            "intersecting_indexes": 
+                            [
+                                
+                                {
+                                    "index": "key3",
+                                    "index_scan_cost": 58.252,
+                                    "cumulated_index_scan_cost": 58.252,
+                                    "disk_sweep_cost": 1923.1,
+                                    "cumulative_total_cost": 1981.4,
+                                    "usable": true,
+                                    "matching_rows_now": 2243,
+                                    "intersect_covering_with_this_index": false,
+                                    "chosen": true
+                                },
+                                
+                                {
+                                    "index": "key4",
+                                    "index_scan_cost": 58.252,
+                                    "cumulated_index_scan_cost": 116.5,
+                                    "disk_sweep_cost": 84.518,
+                                    "cumulative_total_cost": 201.02,
+                                    "usable": true,
+                                    "matching_rows_now": 77.636,
+                                    "intersect_covering_with_this_index": false,
+                                    "chosen": true
+                                }
+                            ],
+                            "clustered_pk": 
+                            {
+                                "clustered_pk_added_to_intersect": false,
+                                "cause": "no clustered pk index"
+                            },
+                            "rows": 77,
+                            "cost": 201.02,
+                            "covering": false,
+                            "chosen": true
+                        }
+                    }
+                ],
+                "index_roworder_union_cost": 386.73,
+                "members": 2,
+                "chosen": true
+            }
+        ]
+    }
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary'))
+[
+    
+    {
+        "range_access_plan": 
+        {
+            "type": "index_roworder_union",
+            "union_of": 
+            [
+                
+                {
+                    "type": "index_roworder_intersect",
+                    "rows": 77,
+                    "cost": 201.02,
+                    "covering": false,
+                    "clustered_pk_scan": false,
+                    "intersect_of": 
+                    [
+                        
+                        {
+                            "type": "range_scan",
+                            "index": "key1",
+                            "rows": 2243,
+                            "ranges": 
+                            [
+                                "(100) <= (key1) <= (100)"
+                            ]
+                        },
+                        
+                        {
+                            "type": "range_scan",
+                            "index": "key2",
+                            "rows": 2243,
+                            "ranges": 
+                            [
+                                "(100) <= (key2) <= (100)"
+                            ]
+                        }
+                    ]
+                },
+                
+                {
+                    "type": "index_roworder_intersect",
+                    "rows": 77,
+                    "cost": 201.02,
+                    "covering": false,
+                    "clustered_pk_scan": false,
+                    "intersect_of": 
+                    [
+                        
+                        {
+                            "type": "range_scan",
+                            "index": "key3",
+                            "rows": 2243,
+                            "ranges": 
+                            [
+                                "(100) <= (key3) <= (100)"
+                            ]
+                        },
+                        
+                        {
+                            "type": "range_scan",
+                            "index": "key4",
+                            "rows": 2243,
+                            "ranges": 
+                            [
+                                "(100) <= (key4) <= (100)"
+                            ]
+                        }
+                    ]
+                }
+            ]
+        },
+        "rows_for_plan": 154,
+        "cost_for_plan": 386.73,
+        "chosen": true
+    }
+]
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge.test b/mysql-test/main/opt_trace_index_merge.test
index d5efaf81db5..73240b6a9e2 100644
--- a/mysql-test/main/opt_trace_index_merge.test
+++ b/mysql-test/main/opt_trace_index_merge.test
@@ -19,3 +19,115 @@ select * from information_schema.OPTIMIZER_TRACE;
 drop table t0,t1;
 set optimizer_trace="enabled=off";
 set @@optimizer_switch= @tmp_opt_switch;
+
+--echo # More tests added index_merge access
+
+--enable_warnings
+create table t1
+(
+  /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+  st_a int not null default 0,
+  swt1a int not null default 0,
+  swt2a int not null default 0,
+
+  st_b int not null default 0,
+  swt1b int not null default 0,
+  swt2b int not null default 0,
+
+  /* fields/keys for row retrieval tests */
+  key1 int,
+  key2 int,
+  key3 int,
+  key4 int,
+
+  /* make rows much bigger then keys */
+  filler1 char (200),
+  filler2 char (200),
+  filler3 char (200),
+  filler4 char (200),
+  filler5 char (200),
+  filler6 char (200),
+
+  /* order of keys is important */
+  key sta_swt12a(st_a,swt1a,swt2a),
+  key sta_swt1a(st_a,swt1a),
+  key sta_swt2a(st_a,swt2a),
+  key sta_swt21a(st_a,swt2a,swt1a),
+  key st_a(st_a),
+  key stb_swt1a_2b(st_b,swt1b,swt2a),
+  key stb_swt1b(st_b,swt1b),
+  key st_b(st_b),
+
+  key(key1),
+  key(key2),
+  key(key3),
+  key(key4)
+) ;
+# Fill table
+create table t0 as select * from t1;
+--disable_query_log
+--echo # Printing of many insert into t0 values (....) disabled.
+let $cnt=1000;
+while ($cnt)
+{ 
+  eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
+  dec $cnt;
+}
+--enable_query_log
+
+alter table t1 disable keys;
+--disable_query_log
+--echo # Printing of many insert into t1 select .... from t0 disabled.
+let $1=4;
+while ($1)
+{ 
+  let $2=4;
+  while ($2)
+  { 
+    let $3=4;
+    while ($3)
+    {
+      eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
+      dec $3;
+    }
+    dec $2;
+  }
+ dec $1;
+}
+
+--echo # Printing of many insert into t1 (...) values (....) disabled.
+# Row retrieval tests
+# -1 is used for values 'out of any range we are using'
+# insert enough rows for index intersection to be used for (key1,key2)
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
+let $cnt=400;
+while ($cnt)
+{
+  eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
+  dec $cnt;
+}
+let $cnt=400;
+while ($cnt)
+{
+  eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
+  dec $cnt;
+}
+--enable_query_log
+alter table t1 enable keys;
+
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+set optimizer_trace='enabled=on';
+
+--echo #  3-way ROR-intersection
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # ROR-union(ROR-intersection, ROR-range)
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+set optimizer_trace="enabled=off";
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 94e9d4f58cc..6a245cc83da 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -116,7 +116,7 @@ explain select * from t1 where pk1 != 0  and key1 = 1	{
                     "range_scan_alternatives": [
                       {
                         "index": "PRIMARY",
-                        "ranges": ["pk1 < 0", "0 < pk1"],
+                        "ranges": ["(pk1) < (0)", "(0) < (pk1)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": false,
@@ -127,7 +127,7 @@ explain select * from t1 where pk1 != 0  and key1 = 1	{
                       },
                       {
                         "index": "key1",
-                        "ranges": ["1 <= key1 <= 1"],
+                        "ranges": ["(1) <= (key1) <= (1)"],
                         "rowid_ordered": true,
                         "using_mrr": false,
                         "index_only": false,
@@ -164,7 +164,7 @@ explain select * from t1 where pk1 != 0  and key1 = 1	{
                       "type": "range_scan",
                       "index": "key1",
                       "rows": 1,
-                      "ranges": ["1 <= key1 <= 1"]
+                      "ranges": ["(1) <= (key1) <= (1)"]
                     },
                     "rows_for_plan": 1,
                     "cost_for_plan": 2.3751,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 5ab3d70214d..fa4e30a1e94 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -431,16 +431,18 @@ static int and_range_trees(RANGE_OPT_PARAM *param,
 static bool remove_nonrange_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree);
 
 static void print_key_value(String *out, const KEY_PART_INFO *key_part,
-                            const uchar *key);
+                            const uchar* key, uint length);
+static void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+                         uint n_keypart, key_part_map keypart_map);
 
 static void append_range_all_keyparts(Json_writer_array *range_trace,
-                                      String *range_string,
-                                      String *range_so_far, const SEL_ARG *keypart,
+                                      PARAM param, uint idx,
+                                      SEL_ARG *keypart,
                                       const KEY_PART_INFO *key_parts);
 
 static
-void append_range(String *out, const KEY_PART_INFO *key_parts,
-                  const uchar *min_key, const uchar *max_key, const uint flag);
+void append_range(String *out, const KEY_PART_INFO *key_part,
+                          KEY_MULTI_RANGE *range, uint n_key_parts);
 
 
 /*
@@ -2273,10 +2275,7 @@ void TRP_RANGE::trace_basic_info(const PARAM *param,
   // TRP_RANGE should not be created if there are no range intervals
   DBUG_ASSERT(key);
 
-  String range_info;
-  range_info.length(0);
-  range_info.set_charset(system_charset_info);
-  append_range_all_keyparts(&trace_range, NULL, &range_info, key, key_part);
+  append_range_all_keyparts(&trace_range, *param, key_idx, key, key_part);
 }
 
 
@@ -2489,10 +2488,8 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param,
   // can have group quick without ranges
   if (index_tree)
   {
-    String range_info;
-    range_info.set_charset(system_charset_info);
-    append_range_all_keyparts(&trace_range, NULL, &range_info, index_tree,
-                              key_part);
+    append_range_all_keyparts(&trace_range, *param, param_idx,
+                                      index_tree, key_part);
   }
 }
 
@@ -6398,20 +6395,9 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param,
     trace_isect_idx.add("rows", (*cur_scan)->records);
 
     Json_writer_array trace_range(thd, "ranges");
-    for (const SEL_ARG *current= (*cur_scan)->sel_arg->first(); current;
-                                                 current= current->next)
-    {
-      String range_info;
-      range_info.set_charset(system_charset_info);
-      for (const SEL_ARG *part= current; part;
-           part= part->next_key_part ? part->next_key_part : nullptr)
-      {
-        const KEY_PART_INFO *cur_key_part= key_part + part->part;
-        append_range(&range_info, cur_key_part, part->min_value,
-                     part->max_value, part->min_flag | part->max_flag);
-      }
-      trace_range.add(range_info.ptr(), range_info.length());
-    }
+
+    append_range_all_keyparts(&trace_range, *param, (*cur_scan)->idx,
+                             (*cur_scan)->sel_arg->first(), key_part);
   }
 }
 
@@ -7389,9 +7375,6 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
         const KEY &cur_key= param->table->key_info[keynr];
         const KEY_PART_INFO *key_part= cur_key.key_part;
 
-        String range_info;
-        range_info.set_charset(system_charset_info);
-
         index_scan->idx= idx;
         index_scan->keynr= keynr;
         index_scan->key_info= &param->table->key_info[keynr];
@@ -7402,8 +7385,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
         *tree->index_scans_end++= index_scan;
 
         if (unlikely(thd->trace_started()))
-          append_range_all_keyparts(&trace_range, NULL, &range_info, key,
-                                    key_part);
+          append_range_all_keyparts(&trace_range, (*param), idx,
+                                              key, key_part);
         trace_range.end();
 
         trace_idx.add("rowid_ordered", param->is_ror_scan)
@@ -13554,11 +13537,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
         Json_writer_array trace_range(thd, "ranges");
 
         const KEY_PART_INFO *key_part= cur_index_info->key_part;
-
-        String range_info;
-        range_info.set_charset(system_charset_info);
-        append_range_all_keyparts(&trace_range, NULL, &range_info,
-                                  cur_index_tree, key_part);
+        append_range_all_keyparts(&trace_range, *param, cur_param_idx,
+                                          cur_index_tree, key_part);
       }
     }
     cost_group_min_max(table, cur_index_info, cur_used_key_parts,
@@ -15730,12 +15710,17 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
 }
 
 #endif /* !DBUG_OFF */
+
 static
 void append_range(String *out, const KEY_PART_INFO *key_part,
-                  const uchar *min_key, const uchar *max_key, const uint flag)
+                  KEY_MULTI_RANGE *range, uint n_key_parts)
 {
-  if (out->length() > 0)
-    out->append(STRING_WITH_LEN(" AND "));
+  uint flag= range->range_flag;
+  String key_name;
+  key_name.set_charset(system_charset_info);
+  key_part_map keypart_map= range->start_key.length ?
+                            range->start_key.keypart_map :
+                            range->end_key.keypart_map;
 
   if (flag & GEOM_FLAG)
   {
@@ -15744,22 +15729,24 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
       range types, so printing "col < some_geom" doesn't make sense.
       Just print the column name, not operator.
     */
-    out->append(key_part->field->field_name);
+    print_keyparts_name(out, key_part, n_key_parts, keypart_map);
     out->append(STRING_WITH_LEN(" "));
-    print_key_value(out, key_part, min_key);
+    print_key_value(out, key_part, range->start_key.key,
+                    range->start_key.length);
     return;
   }
 
   if (!(flag & NO_MIN_RANGE))
   {
-    print_key_value(out, key_part, min_key);
+    print_key_value(out, key_part, range->start_key.key,
+                    range->start_key.length);
     if (flag & NEAR_MIN)
       out->append(STRING_WITH_LEN(" < "));
     else
       out->append(STRING_WITH_LEN(" <= "));
   }
 
-  out->append(key_part->field->field_name);
+  print_keyparts_name(out, key_part, n_key_parts, keypart_map);
 
   if (!(flag & NO_MAX_RANGE))
   {
@@ -15767,7 +15754,8 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
       out->append(STRING_WITH_LEN(" < "));
     else
       out->append(STRING_WITH_LEN(" <= "));
-    print_key_value(out, key_part, max_key);
+    print_key_value(out, key_part, range->end_key.key,
+                    range->end_key.length);
   }
 }
 
@@ -15775,60 +15763,39 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
 
   Add ranges to the trace
   For ex:
-    query: select * from t1 where a=2 ;
-    and we have an index on a , so we create a range
-      2 <= a <= 2
+    lets say we have an index a_b(a,b)
+    query: select * from t1 where a=2 and b=4 ;
+    so we create a range:
+      (2,4) <= (a,b) <= (2,4)
     this is added to the trace
 */
 
 static void append_range_all_keyparts(Json_writer_array *range_trace,
-                                      String *range_string,
-                                      String *range_so_far, const SEL_ARG *keypart,
+                                      PARAM param, uint idx,
+                                      SEL_ARG *keypart,
                                       const KEY_PART_INFO *key_parts)
 {
-
-  DBUG_ASSERT(keypart);
-  DBUG_ASSERT(keypart && keypart != &null_element);
-
-  // Navigate to first interval in red-black tree
+  SEL_ARG_RANGE_SEQ seq;
+  KEY_MULTI_RANGE range;
+  range_seq_t seq_it;
+  uint flags= 0;
+  RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init,
+                         sel_arg_range_seq_next, 0, 0};
+  KEY *keyinfo= param.table->key_info + param.real_keynr[idx];
+  uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
+  seq.keyno= idx;
+  seq.real_keyno= param.real_keynr[idx];
+  seq.param= ¶m;
+  seq.start= keypart;
   const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
-  const SEL_ARG *keypart_range= keypart->first();
-  const size_t save_range_so_far_length= range_so_far->length();
-
+  seq_it= seq_if.init((void *) &seq, 0, flags);
 
-  while (keypart_range)
+  while (!seq_if.next(seq_it, &range))
   {
-    // Append the current range predicate to the range String
-    switch (keypart->type)
-    {
-      case SEL_ARG::Type::KEY_RANGE:
-        append_range(range_so_far, cur_key_part, keypart_range->min_value,
-                     keypart_range->max_value,
-                     keypart_range->min_flag | keypart_range->max_flag);
-        break;
-      case SEL_ARG::Type::MAYBE_KEY:
-        range_so_far->append("MAYBE_KEY");
-        break;
-      case SEL_ARG::Type::IMPOSSIBLE:
-        range_so_far->append("IMPOSSIBLE");
-        break;
-      default:
-        DBUG_ASSERT(false);
-        break;
-    }
-
-    if (keypart_range->next_key_part &&
-        keypart_range->next_key_part->part ==
-              keypart_range->part + 1 &&
-        keypart_range->is_singlepoint())
-    {
-      append_range_all_keyparts(range_trace, range_string, range_so_far,
-                                keypart_range->next_key_part, key_parts);
-    }
-    else
-      range_trace->add(range_so_far->c_ptr_safe(), range_so_far->length());
-    keypart_range= keypart_range->next;
-    range_so_far->length(save_range_so_far_length);
+    String range_info;
+    range_info.set_charset(system_charset_info);
+    append_range(&range_info, cur_key_part, &range, n_key_parts);
+    range_trace->add(range_info.c_ptr_safe(), range_info.length());
   }
 }
 
@@ -15838,70 +15805,110 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
   @param[out] out          String the key is appended to
   @param[in]  key_part     Index components description
   @param[in]  key          Key tuple
+  @param[in]  used_length  length of the key tuple
 */
+
 static void print_key_value(String *out, const KEY_PART_INFO *key_part,
-                            const uchar *key)
+                            const uchar* key, uint used_length)
 {
+  out->append(STRING_WITH_LEN("("));
   Field *field= key_part->field;
+  StringBuffer<128> tmp(system_charset_info);
+  TABLE *table= field->table;
+  uint store_length;
+  my_bitmap_map *old_sets[2];
+  dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+  const uchar *key_end= key+used_length;
 
-  if (field->flags & BLOB_FLAG)
+  for (; key < key_end; key+=store_length, key_part++)
   {
-    // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
-    if (field->real_maybe_null() && *key)
-      out->append(STRING_WITH_LEN("NULL"));
-    else
-      (field->type() == MYSQL_TYPE_GEOMETRY)
-          ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
-          : out->append(STRING_WITH_LEN("unprintable_blob_value"));
-    return;
-  }
+    field= key_part->field;
+    store_length= key_part->store_length;
+    if (field->flags & BLOB_FLAG)
+    {
+      // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
+      if (field->real_maybe_null() && *key)
+        out->append(STRING_WITH_LEN("NULL"));
+      else
+        (field->type() == MYSQL_TYPE_GEOMETRY)
+            ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
+            : out->append(STRING_WITH_LEN("unprintable_blob_value"));
+      goto next;
+    }
 
-  uint store_length= key_part->store_length;
+    if (field->real_maybe_null())
+    {
+      /*
+        Byte 0 of key is the null-byte. If set, key is NULL.
+        Otherwise, print the key value starting immediately after the
+        null-byte
+      */
+      if (*key)
+      {
+        out->append(STRING_WITH_LEN("NULL"));
+        goto next;
+      }
+      key++;  // Skip null byte
+      store_length--;
+    }
 
-  if (field->real_maybe_null())
-  {
     /*
-      Byte 0 of key is the null-byte. If set, key is NULL.
-      Otherwise, print the key value starting immediately after the
-      null-byte
+      Binary data cannot be converted to UTF8 which is what the
+      optimizer trace expects. If the column is binary, the hex
+      representation is printed to the trace instead.
     */
-    if (*key)
+    if (field->flags & BINARY_FLAG)
     {
-      out->append(STRING_WITH_LEN("NULL"));
-      return;
+      out->append("0x");
+      for (uint i = 0; i < store_length; i++)
+      {
+        out->append(_dig_vec_lower[*(key + i) >> 4]);
+        out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+      }
+      goto next;
     }
-    key++;  // Skip null byte
-    store_length--;
+
+    field->set_key_image(key, key_part->length);
+    if (field->type() == MYSQL_TYPE_BIT)
+      (void)field->val_int_as_str(&tmp, 1);  // may change tmp's charset
+    else
+      field->val_str(&tmp);  // may change tmp's charset
+    out->append(tmp.ptr(), tmp.length(), tmp.charset());
+
+  next:
+    if (key + store_length < key_end)
+      out->append(STRING_WITH_LEN(","));
   }
+  dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+  out->append(STRING_WITH_LEN(")"));
+}
 
-  /*
-    Binary data cannot be converted to UTF8 which is what the
-    optimizer trace expects. If the column is binary, the hex
-    representation is printed to the trace instead.
-  */
-  if (field->flags & BINARY_FLAG)
+/**
+  Print key parts involed in a range
+  @param[out] out          String the key is appended to
+  @param[in]  key_part     Index components description
+  @param[in]  n_keypart    Number of keyparts in index
+  @param[in]  keypart_map  map for keyparts involved in the range
+*/
+
+void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+                         uint n_keypart, key_part_map keypart_map)
+{
+  uint i;
+  out->append(STRING_WITH_LEN("("));
+  bool first_keypart= TRUE;
+  for (i=0; i < n_keypart; key_part++, i++)
   {
-    out->append("0x");
-    for (uint i = 0; i < store_length; i++)
+    if (keypart_map & (1 << i))
     {
-      out->append(_dig_vec_lower[*(key + i) >> 4]);
-      out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+      if (first_keypart)
+        first_keypart= FALSE;
+      else
+        out->append(STRING_WITH_LEN(","));
+      out->append(key_part->field->field_name);
     }
-    return;
+    else
+      break;
   }
-
-  StringBuffer<128> tmp(system_charset_info);
-  TABLE *table= field->table;
-  my_bitmap_map *old_sets[2];
-
-  dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
-  field->set_key_image(key, key_part->length);
-  if (field->type() == MYSQL_TYPE_BIT)
-    (void)field->val_int_as_str(&tmp, 1);  // may change tmp's charset
-  else
-    field->val_str(&tmp);  // may change tmp's charset
-  out->append(tmp.ptr(), tmp.length(), tmp.charset());
-
-  dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+  out->append(STRING_WITH_LEN(")"));
 }
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 2dab90b9f69..5c2a2e39137 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -458,7 +458,9 @@ class SEL_ARG :public Sql_alloc
     SEL_ARG *key_tree= first();
     uint res= key_tree->store_min(key[key_tree->part].store_length,
                                   range_key, *range_key_flag);
-    *range_key_flag|= key_tree->min_flag;
+    // add flags only if a key_part is written to the buffer
+    if (res)
+      *range_key_flag|= key_tree->min_flag;
     if (key_tree->next_key_part &&
 	key_tree->next_key_part->type == SEL_ARG::KEY_RANGE &&
         key_tree->part != last_part &&
@@ -480,7 +482,8 @@ class SEL_ARG :public Sql_alloc
     SEL_ARG *key_tree= last();
     uint res=key_tree->store_max(key[key_tree->part].store_length,
                                  range_key, *range_key_flag);
-    (*range_key_flag)|= key_tree->max_flag;
+    if (res)
+      (*range_key_flag)|= key_tree->max_flag;
     if (key_tree->next_key_part &&
 	key_tree->next_key_part->type == SEL_ARG::KEY_RANGE &&
         key_tree->part != last_part &&


More information about the commits mailing list