[Commits] 66ad265: MDEV-7674: ANALYZE shows r_rows=0

Sergei Petrunia psergey at askmonty.org
Sat Mar 7 18:30:19 EET 2015


revision-id: 66ad265f3b58121d5a48c492dff79f48863df7c0
parent(s): 143f5d9172647a43cdcf5c27b8a78f32c9f639c0
committer: Sergei Petrunia
branch nick: 10.1-cp2
timestamp: 2015-03-07 19:30:19 +0300
message:

MDEV-7674: ANALYZE shows r_rows=0

Change r_rows to be double

---
 mysql-test/r/analyze_format_json.result         |   50 ++++++++++--
 mysql-test/r/analyze_stmt.result                |   92 +++++++++++++----------
 mysql-test/r/analyze_stmt_slow_query_log.result |    2 +-
 mysql-test/t/analyze_format_json.test           |   15 ++++
 mysql-test/t/analyze_stmt.test                  |   15 ++++
 sql/sql_class.cc                                |    3 +-
 sql/sql_explain.cc                              |   27 +++----
 sql/sql_explain.h                               |    4 +-
 8 files changed, 142 insertions(+), 66 deletions(-)

diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index b496fe5..fcab200 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -24,7 +24,7 @@ insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
 analyze
 select * from t0, t1 where t1.a=t0.a and t0.a > 9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	0.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	0.00	Using where
 1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	NULL	100.00	NULL	
 analyze format=json 
 select * from t0, t1 where t1.a=t0.a and t0.a > 9;
@@ -61,8 +61,8 @@ EXPLAIN
 analyze
 select * from t0, t1 where t1.a=t0.a and t1.b<4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	Using where
-1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	1	100.00	40.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	1.00	100.00	40.00	Using where
 analyze format=json
 select * from t0, t1 where t1.a=t0.a and t1.b<4;
 EXPLAIN
@@ -99,8 +99,8 @@ EXPLAIN
 analyze 
 select * from t1 tbl1, t1 tbl2 where tbl1.b<2 and tbl2.b>5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	tbl1	ALL	NULL	NULL	NULL	NULL	100	100	100.00	2.00	Using where
-1	SIMPLE	tbl2	ALL	NULL	NULL	NULL	NULL	100	100	100.00	94.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	tbl1	ALL	NULL	NULL	NULL	NULL	100	100.00	100.00	2.00	Using where
+1	SIMPLE	tbl2	ALL	NULL	NULL	NULL	NULL	100	100.00	100.00	94.00	Using where; Using join buffer (flat, BNL join)
 analyze format=json
 select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60;
 EXPLAIN
@@ -170,3 +170,43 @@ EXPLAIN
 }
 drop table t1;
 drop table t0;
+#
+# MDEV-7674: ANALYZE shows r_rows=0
+#
+create table t1(a int);
+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);
+analyze format=json select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 10,
+      "r_rows": 10,
+      "filtered": 100,
+      "r_filtered": 100,
+      "attached_condition": "(t1.a is not null)"
+    },
+    "table": {
+      "table_name": "t2",
+      "access_type": "ref",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t1.a"],
+      "r_loops": 10,
+      "rows": 2,
+      "r_rows": 0.2,
+      "filtered": 100,
+      "r_filtered": 100,
+      "using_index": true
+    }
+  }
+}
+drop table t1,t2;
diff --git a/mysql-test/r/analyze_stmt.result b/mysql-test/r/analyze_stmt.result
index 0a42728..5006332 100644
--- a/mysql-test/r/analyze_stmt.result
+++ b/mysql-test/r/analyze_stmt.result
@@ -6,17 +6,17 @@ INSERT INTO t1 select * from t0;
 # Try a few basic selects to see that r_rows and r_filtered columns work
 analyze select * from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	
 analyze select * from t1 where a<5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
 analyze select * from t1 where a>100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	0.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	0.00	Using where
 # ANALYZE DELETE will delete rows:
 analyze delete from t1 where a in (2,3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	30.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
 select * from t1;
 a
 0
@@ -32,7 +32,7 @@ create table t1(a int, b int);
 insert into t1 select a,a from t0;
 analyze update t1 set b=100+b where a in (6,7,8);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	30.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
 select * from t1;
 a	b
 0	0
@@ -51,14 +51,14 @@ create table t1(a int, b int);
 insert into t1 select a,a from t0;
 analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
-2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10	100.00	20.00	Using where
-NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	7	NULL	NULL	
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	7.00	NULL	NULL	
 analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
-2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10	100.00	20.00	Using where
-NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	5	NULL	NULL	
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	5.00	NULL	NULL	
 drop table t1;
 drop table t0;
 # 
@@ -72,12 +72,12 @@ insert into t1 values (1,1),(2,2),(3,3);
 # See .test file for the right values of r_rows and r_filtered.
 analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3	100.00	100.00	
-2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	3	100.00	33.33	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	3.00	100.00	33.33	Using where
 # Try a subquery that is never executed
 analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3	100.00	0.00	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	0.00	Using where
 2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
 drop table t0, t1;
 #
@@ -95,8 +95,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 # These should have filtered=50
 analyze select * from t0, t1 where t0.a<5 and t1.a<5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where; Using join buffer (flat, BNL join)
 explain select * from t0, t1 where t0.a<5 and t1.b=t0.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -104,8 +104,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 # Now, t1 should have filtered=10
 analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	10.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where; Using join buffer (flat, BNL join)
 explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -113,8 +113,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 # Now, t1 should have filtered=10
 analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	50.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	10.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	50.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where; Using join buffer (flat, BNL join)
 # TODO: Check what is counted for "range checked for each record".
 #
 # Test for joins
@@ -127,13 +127,13 @@ from t0 A, t0 B, t0 C;
 # This always has matches, filtered=100%.
 analyze select * from t1,t2 where t2.key1=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	Using where
-1	SIMPLE	t2	ref	key1	key1	5	test.t1.a	1	1	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t2	ref	key1	key1	5	test.t1.a	1	1.00	100.00	100.00	
 # This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?)
 analyze select * from t1,t2 where t2.key2x=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	Using where
-1	SIMPLE	t2	ref	key2x	key2x	5	test.t1.a	1	0	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t2	ref	key2x	key2x	5	test.t1.a	1	0.50	100.00	100.00	
 select * from t1,t2 where t2.key2x=t1.a;
 a	b	key1	key2x	col1
 0	0	0	0	0
@@ -144,8 +144,8 @@ a	b	key1	key2x	col1
 # This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0)
 analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	Using where
-1	SIMPLE	t2	ref	key2x	key2x	5	test.t1.a	1	0	100.00	40.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t2	ref	key2x	key2x	5	test.t1.a	1	0.50	100.00	40.00	Using where
 drop table t0,t1,t2;
 #
 # Check non-merged derived tables
@@ -156,8 +156,8 @@ insert into t0 values
 update t0 set b=b/3;
 analyze select * from (select count(*),max(a),b from t0 group by b) T;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	4	100.00	100.00	
-2	DERIVED	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	100.00	Using temporary; Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	4.00	100.00	100.00	
+2	DERIVED	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using temporary; Using filesort
 drop table t0;
 #
 # Check ORDER/GROUP BY
@@ -167,7 +167,7 @@ insert into t0 values
 (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 analyze select count(*),max(a),b from t0 where a<7 group by b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10	100.00	70.00	Using where; Using temporary; Using filesort
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	70.00	Using where; Using temporary; Using filesort
 drop table t0;
 #
 # Check multi-table UPDATE/DELETE.
@@ -178,12 +178,12 @@ insert into t0 values (0,0),(2,2),(4,4),     (8,8);
 insert into t1 values (0,0),(2,2),      (6,6);
 analyze select * from t0,t1 where t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3	100.00	100.00	
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4	100.00	16.67	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	16.67	Using where; Using join buffer (flat, BNL join)
 analyze update t0,t1 set t1.b=5555 where t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3	100.00	100.00	
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4	100.00	16.67	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	16.67	Using where
 select * from t1;
 a	b
 0	5555
@@ -191,8 +191,8 @@ a	b
 6	6
 analyze delete t1 from t1, t0 where t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3	100.00	100.00	
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4	100.00	16.67	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	16.67	Using where
 select * from t1;
 a	b
 6	6
@@ -240,7 +240,7 @@ create table t1 (i int);
 insert into t1 values (1),(2),(3),(4);
 analyze update t1 set i=8;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	4	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 drop table t1;
 #
 # Check ANALYZE SELECT INTO
@@ -257,7 +257,7 @@ drop table t1;
 create table t1 (i int);
 analyze delete from t1 returning *;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	0	0	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	0	0.00	100.00	100.00	
 drop table t1;
 #
 # MDEV-6396: ANALYZE INSERT/REPLACE is accepted, but does not produce a plan
@@ -283,15 +283,15 @@ create table t1(a int);
 insert into t1 values (1),(2);
 analyze select a from t1 where a <2 into @var;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2	100.00	50.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2.00	100.00	50.00	Using where
 analyze select a from t1 into @var;
 ERROR 42000: Result consisted of more than one row
 analyze insert into t1 select * from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2	100.00	100.00	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2.00	100.00	100.00	Using temporary
 analyze select * into outfile '../../tmp/data1.tmp' from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	4	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 drop table t1;
 #
 # MDEV-7024: Assertion `! is_set()' failed in 
@@ -303,3 +303,15 @@ execute stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 drop table t1;
+#
+# MDEV-7674: ANALYZE shows r_rows=0
+#
+create table t1(a int);
+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);
+analyze select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	0.20	100.00	100.00	Using index
+drop table t1,t2;
diff --git a/mysql-test/r/analyze_stmt_slow_query_log.result b/mysql-test/r/analyze_stmt_slow_query_log.result
index 41ab4fe..7d280e6 100644
--- a/mysql-test/r/analyze_stmt_slow_query_log.result
+++ b/mysql-test/r/analyze_stmt_slow_query_log.result
@@ -10,6 +10,6 @@ drop table t1;
 # explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 # explain: 1	INSERT	t1	ALL	NULL	NULL	NULL	NULL	NULL	NULL	100.00	100.00	NULL
 # explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
-# explain: 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	100.00	30.00	Using where
+# explain: 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
 # explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 # explain: 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
diff --git a/mysql-test/t/analyze_format_json.test b/mysql-test/t/analyze_format_json.test
index 7f7ea3d..1adb693 100644
--- a/mysql-test/t/analyze_format_json.test
+++ b/mysql-test/t/analyze_format_json.test
@@ -36,3 +36,18 @@ select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60 and tbl1.c > tbl2.c
 
 drop table t1;
 drop table t0;
+
+--echo #
+--echo # MDEV-7674: ANALYZE shows r_rows=0
+--echo #
+
+create table t1(a int);
+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);
+
+analyze format=json select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
+
+drop table t1,t2;
+
diff --git a/mysql-test/t/analyze_stmt.test b/mysql-test/t/analyze_stmt.test
index 4da16cf..b64dfe6 100644
--- a/mysql-test/t/analyze_stmt.test
+++ b/mysql-test/t/analyze_stmt.test
@@ -244,3 +244,18 @@ create table t1(a int);
 prepare stmt from "analyze select * from t1";
 execute stmt;
 drop table t1;
+
+--echo #
+--echo # MDEV-7674: ANALYZE shows r_rows=0
+--echo #
+
+create table t1(a int);
+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);
+
+analyze select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
+
+drop table t1,t2;
+
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 37060a6..5b0d04d 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2454,8 +2454,7 @@ void THD::make_explain_field_list(List<Item> &field_list, uint8 explain_flags,
                                                  MYSQL_TYPE_LONGLONG));
   if (is_analyze)
   {
-    field_list.push_back(item= new Item_return_int("r_rows", 10,
-                                                   MYSQL_TYPE_LONGLONG));
+    field_list.push_back(item= new Item_float("r_rows", 0.1234, 10, 4));
     item->maybe_null=1;
   }
 
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index c68f86c..aa16a74 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -298,7 +298,7 @@ int print_explain_row(select_result_sink *result,
                       const char *key_len,
                       const char *ref,
                       ha_rows *rows,
-                      ha_rows *r_rows,
+                      double *r_rows,
                       double r_filtered,
                       const char *extra)
 {
@@ -356,10 +356,7 @@ int print_explain_row(select_result_sink *result,
   if (is_analyze)
   {
     if (r_rows)
-    {
-      item_list.push_back(new Item_int(*r_rows, 
-                                       MY_INT64_NUM_DECIMAL_DIGITS));
-    }
+      item_list.push_back(new Item_float(*r_rows, 2));
     else
       item_list.push_back(item_null);
   }
@@ -472,9 +469,8 @@ int Explain_union::print_explain(Explain_query *query,
   /* `r_rows` */
   if (is_analyze)
   {
-    ha_rows avg_rows= fake_select_lex_tracker.get_avg_rows();
-    item_list.push_back(new Item_int((longlong) (ulonglong) avg_rows,
-                                      MY_INT64_NUM_DECIMAL_DIGITS));
+    double avg_rows= fake_select_lex_tracker.get_avg_rows();
+    item_list.push_back(new Item_float(avg_rows, 2));
   }
 
   /* `filtered` */
@@ -1004,9 +1000,8 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
     }
     else
     {
-      ha_rows avg_rows= tracker.get_avg_rows();
-      item_list.push_back(new Item_int((longlong) (ulonglong) avg_rows,
-                                        MY_INT64_NUM_DECIMAL_DIGITS));
+      double avg_rows= tracker.get_avg_rows();
+      item_list.push_back(new Item_float(avg_rows, 2));
     }
   }
 
@@ -1289,8 +1284,8 @@ void Explain_table_access::print_explain_json(Explain_query *query,
     writer->add_member("r_rows");
     if (tracker.has_scans())
     {
-      ha_rows avg_rows= tracker.get_avg_rows();
-      writer->add_ll(avg_rows);
+      double avg_rows= tracker.get_avg_rows();
+      writer->add_double(avg_rows);
     }
     else
       writer->add_null();
@@ -1756,7 +1751,7 @@ int Explain_update::print_explain(Explain_query *query,
     "Using index condition" is also not possible (which is an unjustified limitation)
   */
   double r_filtered= 100 * tracker.get_filtered_after_where();
-  ha_rows r_rows= tracker.get_avg_rows();
+  double r_rows= tracker.get_avg_rows();
 
   print_explain_row(output, explain_flags, is_analyze,
                     1, /* id */
@@ -1877,8 +1872,8 @@ void Explain_update::print_explain_json(Explain_query *query,
   /* `r_rows` */
   if (is_analyze && tracker.has_scans())
   {
-    ha_rows avg_rows= tracker.get_avg_rows();
-    writer->add_member("r_rows").add_ll(avg_rows);
+    double avg_rows= tracker.get_avg_rows();
+    writer->add_member("r_rows").add_double(avg_rows);
   }
  
   /* UPDATE/DELETE do not produce `filtered` estimate */
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index c4bfa4d..970edf7 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -44,9 +44,9 @@ class Table_access_tracker
 
   bool has_scans() { return (r_scans != 0); }
   ha_rows get_loops() { return r_scans; }
-  ha_rows get_avg_rows()
+  double get_avg_rows()
   {
-    return r_scans ? (ha_rows)rint((double) r_rows / r_scans): 0;
+    return r_scans ? ((double)r_rows / r_scans): 0;
   }
 
   double get_filtered_after_where()


More information about the commits mailing list