From 10b1f4dd093cb2b21aa9ed09dc453bc52d028b3d Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Mon, 23 Jan 2017 13:32:57 +1100 Subject: MDEV-11866: ANALYZE FORMAT=JSON not predicatable for r_total_time_ms/r_buffer_size [0-9]*[.]?[0-9]* wasn't a sufficient regex to cover the %lg used in Json_writer::add_double. Exponent formats where missed. Here we normalize all the replace_regex expressions for ANALYZE FORMAT=JSON into one include file. Signed-off-by: Daniel Black --- mysql-test/include/analyze-format.inc | 3 ++ mysql-test/r/analyze_format_json.result | 50 ++++++++++++------------ mysql-test/t/analyze_format_json.test | 38 +++++++++--------- mysql-test/t/analyze_stmt_orderby.test | 18 ++++----- mysql-test/t/explain_json.test | 10 ++--- mysql-test/t/explain_json_format_partitions.test | 6 +-- mysql-test/t/subselect_cache.test | 4 +- 7 files changed, 66 insertions(+), 63 deletions(-) create mode 100644 mysql-test/include/analyze-format.inc 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 -- cgit v1.2.1