Merge branch 'grooverdan-10.1-MDEV-11866-ANALYZE-FORMAT=JSON-volatility-normalise' into 10.1bb-10.1-otto

7 files changed, 66 insertions, 63 deletions

diff --git a/mysql-test/include/analyze-format.inc b/mysql-test/include/analyze-format.inc
new file mode 100644
index 00000000000..65e61b81582
--- /dev/null
+++ b/mysql-test/include/analyze-format.inc
@@ -0,0 +1,3 @@
+# The time on ANALYSE FORMAT=JSON is rather variable
+
+--replace_regex /("(r_total_time_ms|r_buffer_size)": )[^, \n]*/\1"REPLACED"/
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 69930bc0215..78892711dc4 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -487,14 +487,14 @@ ANALYZE
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "volatile parameter": "REPLACED",
+    "r_total_time_ms": "REPLACED",
     "having_condition": "(TOP > a)",
     "filesort": {
       "r_loops": 1,
-      "volatile parameter": "REPLACED",
+      "r_total_time_ms": "REPLACED",
       "r_used_priority_queue": false,
       "r_output_rows": 0,
-      "volatile parameter": "REPLACED",
+      "r_buffer_size": "REPLACED",
       "temporary_table": {
         "table": {
           "table_name": "t2",
@@ -502,7 +502,7 @@ ANALYZE
           "r_loops": 1,
           "rows": 256,
           "r_rows": 256,
-          "volatile parameter": "REPLACED",
+          "r_total_time_ms": "REPLACED",
           "filtered": 100,
           "r_filtered": 100
         }
@@ -517,13 +517,13 @@ ANALYZE
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "volatile parameter": "REPLACED",
+    "r_total_time_ms": "REPLACED",
     "filesort": {
       "r_loops": 1,
-      "volatile parameter": "REPLACED",
+      "r_total_time_ms": "REPLACED",
       "r_used_priority_queue": false,
       "r_output_rows": 256,
-      "volatile parameter": "REPLACED",
+      "r_buffer_size": "REPLACED",
       "temporary_table": {
         "table": {
           "table_name": "t2",
@@ -531,7 +531,7 @@ ANALYZE
           "r_loops": 1,
           "rows": 256,
           "r_rows": 256,
-          "volatile parameter": "REPLACED",
+          "r_total_time_ms": "REPLACED",
           "filtered": 100,
           "r_filtered": 100
         }
@@ -557,13 +557,13 @@ ANALYZE
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "volatile parameter": "REPLACED",
+    "r_total_time_ms": "REPLACED",
     "filesort": {
       "r_loops": 1,
-      "volatile parameter": "REPLACED",
+      "r_total_time_ms": "REPLACED",
       "r_used_priority_queue": false,
       "r_output_rows": 256,
-      "volatile parameter": "REPLACED",
+      "r_buffer_size": "REPLACED",
       "temporary_table": {
         "table": {
           "table_name": "t2",
@@ -571,7 +571,7 @@ ANALYZE
           "r_loops": 1,
           "rows": 256,
           "r_rows": 256,
-          "volatile parameter": "REPLACED",
+          "r_total_time_ms": "REPLACED",
           "filtered": 100,
           "r_filtered": 100
         }
@@ -593,14 +593,14 @@ ANALYZE
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "volatile parameter": "REPLACED",
+    "r_total_time_ms": "REPLACED",
     "table": {
       "table_name": "t1",
       "access_type": "ALL",
       "r_loops": 1,
       "rows": 2,
       "r_rows": 2,
-      "volatile parameter": "REPLACED",
+      "r_total_time_ms": "REPLACED",
       "filtered": 100,
       "r_filtered": 100
     },
@@ -612,7 +612,7 @@ ANALYZE
         "r_loops": 1,
         "rows": 2,
         "r_rows": 2,
-        "volatile parameter": "REPLACED",
+        "r_total_time_ms": "REPLACED",
         "filtered": 100,
         "r_filtered": 100
       },
@@ -625,14 +625,14 @@ ANALYZE
         "query_block": {
           "select_id": 2,
           "r_loops": 1,
-          "volatile parameter": "REPLACED",
+          "r_total_time_ms": "REPLACED",
           "table": {
             "table_name": "t1",
             "access_type": "ALL",
             "r_loops": 1,
             "rows": 2,
             "r_rows": 2,
-            "volatile parameter": "REPLACED",
+            "r_total_time_ms": "REPLACED",
             "filtered": 100,
             "r_filtered": 100
           }
@@ -646,7 +646,7 @@ ANALYZE
         "r_loops": 1,
         "rows": 2,
         "r_rows": 2,
-        "volatile parameter": "REPLACED",
+        "r_total_time_ms": "REPLACED",
         "filtered": 100,
         "r_filtered": 100
       },
@@ -678,19 +678,19 @@ ANALYZE
   "query_block": {
     "select_id": 1,
     "r_loops": 1,
-    "volatile parameter": "REPLACED",
+    "r_total_time_ms": "REPLACED",
     "filesort": {
       "r_loops": 1,
-      "volatile parameter": "REPLACED",
+      "r_total_time_ms": "REPLACED",
       "r_used_priority_queue": false,
       "r_output_rows": 0,
-      "volatile parameter": "REPLACED",
+      "r_buffer_size": "REPLACED",
       "filesort": {
         "r_loops": 1,
-        "volatile parameter": "REPLACED",
+        "r_total_time_ms": "REPLACED",
         "r_used_priority_queue": false,
         "r_output_rows": 0,
-        "volatile parameter": "REPLACED",
+        "r_buffer_size": "REPLACED",
         "temporary_table": {
           "temporary_table": {
             "table": {
@@ -699,7 +699,7 @@ ANALYZE
               "r_loops": 1,
               "rows": 2,
               "r_rows": 2,
-              "volatile parameter": "REPLACED",
+              "r_total_time_ms": "REPLACED",
               "filtered": 100,
               "r_filtered": 100
             },
@@ -710,7 +710,7 @@ ANALYZE
                 "r_loops": 1,
                 "rows": 2,
                 "r_rows": 2,
-                "volatile parameter": "REPLACED",
+                "r_total_time_ms": "REPLACED",
                 "filtered": 100,
                 "r_filtered": 0,
                 "attached_condition": "(t3.f3 in (1,2))"
diff --git a/mysql-test/t/analyze_format_json.test b/mysql-test/t/analyze_format_json.test
index 807e02d2334..88a907718d9 100644
--- a/mysql-test/t/analyze_format_json.test
+++ b/mysql-test/t/analyze_format_json.test
@@ -9,34 +9,34 @@ create table t0 (a int);
 INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 --echo # r_filtered=30%, because 3 rows match: 0,1,2
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json select * from t0 where a<3;
 
 create table t1 (a int, b int, c int, key(a));
 insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze
 select * from t0, t1 where t1.a=t0.a and t0.a > 9;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json 
 select * from t0, t1 where t1.a=t0.a and t0.a > 9;
 
 analyze
 select * from t0, t1 where t1.a=t0.a and t1.b<4;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t0, t1 where t1.a=t0.a and t1.b<4;
 
 analyze 
 select * from t1 tbl1, t1 tbl2 where tbl1.b<2 and tbl2.b>5;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60 and tbl1.c > tbl2.c;
 
@@ -53,7 +53,7 @@ insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2 (a int, key(a));
 insert into t2 values (0),(1);
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
 
 drop table t1,t2;
@@ -69,7 +69,7 @@ select database();
 connect (con1,localhost,root,,*NO-ONE*);
 connection con1;
 select database();
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json select * from test.t1 where t1.a<5;
 disconnect con1;
 connection default;
@@ -91,15 +91,15 @@ create table t1 (pk int primary key);
 insert into t1 select a from t3;
 alter table t1 add b int;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 update t1 set b=pk;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1 where pk < 10 and b > 4;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 delete from t1 where pk < 10 and b > 4;
 
@@ -127,7 +127,7 @@ create table t2 (key1 int, key2 int, key3 int, key4 int, col1 int,
 insert into t2 select a,a,a,a,a from t3;
 insert into t2 select 15,15,15,15,15 from t4;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1, t2 where (t2.key1 between t1.lb1 and t1.rb1) and
                            (t2.key2 between t1.lb2 and t1.rb2) and
@@ -144,7 +144,7 @@ INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int);
 INSERT INTO t1 select * from t0;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json (select * from t1 tbl1 where a<5) union (select * from t1 tbl2 where a in (2,3));
 
 drop table t0, t1;
@@ -164,16 +164,16 @@ create table t2 (
 );
 insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B;
 --echo # normal HAVING
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 analyze format=json select a, max(b) as TOP from t2 group by a having TOP > a;
 --echo # HAVING is always TRUE (not printed)
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 analyze format=json select a, max(b) as TOP from t2 group by a having 1<>2;
 --echo # HAVING is always FALSE (intercepted by message)
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 analyze format=json select a, max(b) as TOP from t2 group by a having 1=2;
 --echo # HAVING is absent
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 analyze format=json select a, max(b) as TOP from t2 group by a;
 drop table t0, t1, t2;
 
@@ -186,7 +186,7 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (b INT);
 INSERT INTO t2 VALUES (3),(4);
 
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN * FROM t1, t2 WHERE b IN ( SELECT a FROM t1 );
 
 drop table t1,t2;
@@ -203,7 +203,7 @@ INSERT INTO t2 VALUES (2),(3);
 CREATE TABLE t3 (f3 INT) ENGINE=MyISAM;
 INSERT INTO t3 VALUES (3),(4);
 
---replace_regex /"(r_total_time_ms|r_buffer_size)": .*?,/"volatile parameter": "REPLACED",/
+--source include/analyze-format.inc
 ANALYZE FORMAT=JSON 
 SELECT GROUP_CONCAT(f3) AS gc, ( SELECT MAX(f1) FROM t1, t2 WHERE f2 = f3 ) sq
 FROM t2, t3
diff --git a/mysql-test/t/analyze_stmt_orderby.test b/mysql-test/t/analyze_stmt_orderby.test
index a40f34805d1..a1cfb58f31a 100644
--- a/mysql-test/t/analyze_stmt_orderby.test
+++ b/mysql-test/t/analyze_stmt_orderby.test
@@ -22,7 +22,7 @@ explain
 update t2 set b=b+1 order by b limit 5;
 explain format=json
 update t2 set b=b+1 order by b limit 5;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 update t2 set b=b+1 order by b limit 5;
 
@@ -33,7 +33,7 @@ explain
 update t2 set a=a+1 where a<10;
 explain format=json
 update t2 set a=a+1 where a<10;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 update t2 set a=a+1 where a<10;
 
@@ -44,7 +44,7 @@ explain
 delete from t2 order by b limit 5;
 explain format=json
 delete from t2 order by b limit 5;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/  /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/ 
+--source include/analyze-format.inc
 analyze format=json
 delete from t2 order by b limit 5;
 
@@ -55,7 +55,7 @@ explain
 select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
 explain format=json
 select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
 
@@ -67,7 +67,7 @@ explain
 select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
 explain format=json
 select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json 
 select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
 
@@ -86,7 +86,7 @@ select
   c.a
 from t0 a, t0 b, t0 c;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json 
 select MAX(b) from t2 where mod(a,2)=0 group by c;
 
@@ -115,7 +115,7 @@ drop table t3;
 create table t3 (a int, b int);
 insert into t3 select a, 123 from t0;
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select distinct max(t3.b) Q from t0, t3 where t0.a=t3.a group by t0.a order by null;
 
@@ -136,7 +136,7 @@ insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);
 insert into t7 values (3, 3), (2, 2), (1, 1);
 
 --echo # TODO: This ANALYZE output doesn't make it clear what is used for what.
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select count(distinct t5.b) as sum from t5, t6
                      where t5.a=t6.a and t6.b > 0 and t5.a <= 5
@@ -168,7 +168,7 @@ explain
 select col1 f1, col2 f2, col1 f3 from t2 group by f1;
 analyze
 select col1 f1, col2 f2, col1 f3 from t2 group by f1;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select col1 f1, col2 f2, col1 f3 from t2 group by f1;
 drop table t2;
diff --git a/mysql-test/t/explain_json.test b/mysql-test/t/explain_json.test
index 153d85359c9..2ca91c1f200 100644
--- a/mysql-test/t/explain_json.test
+++ b/mysql-test/t/explain_json.test
@@ -219,7 +219,7 @@ create table t1 (a int, b int, c int, d int, key(a,b,c));
 insert into t1 select  A.a, B.a, C.a, D.a from t2 A, t2 B, t2 C, t2 D;
 explain select count(distinct b) from t1 group by a;
 explain format=json select count(distinct b) from t1 group by a;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json select count(distinct b) from t1 group by a;
 drop table t1,t2;
 
@@ -342,7 +342,7 @@ explain
 select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0;
 explain format=json
 select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0;
 
@@ -351,7 +351,7 @@ explain
 select * from t1 left join t2 on t2.pk=t1.a where  t2.pk is null;
 explain format=json
 select * from t1 left join t2 on t2.pk=t1.a where  t2.pk is null;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t1 left join t2 on t2.pk=t1.a where  t2.pk is null;
 
@@ -360,7 +360,7 @@ explain
 select distinct t1.a from t1 join t2 on t2.pk=t1.a;
 explain format=json
 select distinct t1.a from t1 join t2 on t2.pk=t1.a;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select distinct t1.a from t1 join t2 on t2.pk=t1.a;
 drop table t1,t2;
@@ -386,7 +386,7 @@ explain
 select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1);
 explain format=json
 select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1);
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1);
 set optimizer_switch=@tmp_optimizer_switch;
diff --git a/mysql-test/t/explain_json_format_partitions.test b/mysql-test/t/explain_json_format_partitions.test
index f3567797019..4c7d3f165d1 100644
--- a/mysql-test/t/explain_json_format_partitions.test
+++ b/mysql-test/t/explain_json_format_partitions.test
@@ -8,10 +8,10 @@ create table t1 (
 insert into t1 select a from t2;
 explain partitions select * from t1 where a in (2,3,4);
 explain format=json select * from t1 where a in (2,3,4);
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json select * from t1 where a in (2,3,4);
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json update t1 set a=a+10 where a in (2,3,4);
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json delete from t1 where a in (20,30,40);
 drop table t1,t2;
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test
index 21247541fb6..55da0000f13 100644
--- a/mysql-test/t/subselect_cache.test
+++ b/mysql-test/t/subselect_cache.test
@@ -24,10 +24,10 @@ select a, (select d from t2 where b=c) from t1;
 show status like "subquery_cache%";
 show status like '%Handler_read%';
 
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select a, (select d from t2 where b=c) from t1;
---replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+--source include/analyze-format.inc
 analyze format=json
 select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
 explain format=json