MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when optimizer_use_condition_selectivity >2

Now the optimizer trace shows the ranges constructed while getting estimates from EITS

2 files changed, 112 insertions, 2 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index be358e69c47..6da22802cca 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -108,6 +108,7 @@ select * from v1	{
                 "selectivity_for_columns": [
                   {
                     "column_name": "a",
+                    "ranges": ["1 <= a <= 1"],
                     "selectivity_from_histogram": 0.5
                   }
                 ],
@@ -253,6 +254,7 @@ select * from (select * from t1 where t1.a=1)q	{
                 "selectivity_for_columns": [
                   {
                     "column_name": "a",
+                    "ranges": ["1 <= a <= 1"],
                     "selectivity_from_histogram": 0.5
                   }
                 ],
@@ -403,6 +405,7 @@ select * from v2	{
                       "selectivity_for_columns": [
                         {
                           "column_name": "a",
+                          "ranges": ["1 <= a <= 1"],
                           "selectivity_from_histogram": 0.5
                         }
                       ],
@@ -1416,10 +1419,12 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
                 "selectivity_for_columns": [
                   {
                     "column_name": "b",
+                    "ranges": ["2 <= b <= 2"],
                     "selectivity_from_histogram": 0.2891
                   },
                   {
                     "column_name": "c",
+                    "ranges": ["3 <= c <= 3"],
                     "selectivity_from_histogram": 0.2891
                   }
                 ],
@@ -2091,10 +2096,12 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                 "selectivity_for_columns": [
                   {
                     "column_name": "a",
+                    "ranges": ["1 <= a <= 1"],
                     "selectivity_from_histogram": 0.1797
                   },
                   {
                     "column_name": "b",
+                    "ranges": ["2 <= b <= 2"],
                     "selectivity_from_histogram": 0.0156
                   }
                 ],
@@ -3310,10 +3317,12 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                 "selectivity_for_columns": [
                   {
                     "column_name": "a",
+                    "ranges": ["5 <= a <= 5"],
                     "selectivity_from_histogram": 0.1
                   },
                   {
                     "column_name": "b",
+                    "ranges": ["1 <= b <= 1"],
                     "selectivity_from_histogram": 0.1
                   }
                 ],
@@ -8465,5 +8474,90 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
     ]
 ]
 DROP TABLE t1,t2;
-# End of 10.4 tests
+#
+# MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when
+# optimizer_use_condition_selectivity >2
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+SET optimizer_trace=1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	0.22	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
+[
+    
+    [
+        
+        {
+            "column_name": "a",
+            "ranges": 
+            [
+                "1 <= a <= 5"
+            ],
+            "selectivity_from_histogram": 0.0469
+        },
+        
+        {
+            "column_name": "b",
+            "ranges": 
+            [
+                "NULL < b <= 5"
+            ],
+            "selectivity_from_histogram": 0.0469
+        }
+    ]
+]
+EXPLAIN EXTENDED SELECT * from t1 WHERE a != 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` <> 5
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
+[
+    
+    [
+        
+        {
+            "column_name": "a",
+            "ranges": 
+            [
+                "NULL < a < 5",
+                "5 < a"
+            ],
+            "selectivity_from_histogram": 1
+        }
+    ]
+]
+EXPLAIN EXTENDED SELECT * from t1 WHERE b >= 10 and b < 25;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	15.62	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 10 and `test`.`t1`.`b` < 25
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
+[
+    
+    [
+        
+        {
+            "column_name": "b",
+            "ranges": 
+            [
+                "10 <= b < 25"
+            ],
+            "selectivity_from_histogram": 0.1562
+        }
+    ]
+]
+drop table t1;
 set optimizer_trace='enabled=off';
+# End of 10.4 tests
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index d1a8fedc635..8633ed5b020 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -594,6 +594,22 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b;
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 DROP TABLE t1,t2;
 
---echo # End of 10.4 tests
+--echo #
+--echo # MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when
+--echo # optimizer_use_condition_selectivity >2
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+SET optimizer_trace=1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN EXTENDED SELECT * from t1 WHERE a != 5;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN EXTENDED SELECT * from t1 WHERE b >= 10 and b < 25;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
 
 set optimizer_trace='enabled=off';
+--echo # End of 10.4 tests