diff options
Diffstat (limited to 'mysql-test/main/analyze_format_json.result')
| -rw-r--r-- | mysql-test/main/analyze_format_json.result | 820 |
1 files changed, 820 insertions, 0 deletions
diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result new file mode 100644 index 00000000000..7991379bc55 --- /dev/null +++ b/mysql-test/main/analyze_format_json.result @@ -0,0 +1,820 @@ +drop table if exists t0,t1,t2,t3; +create table t0 (a int); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# r_filtered=30%, because 3 rows match: 0,1,2 +analyze format=json select * from t0 where a<3; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 30, + "attached_condition": "t0.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; +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.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; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "t0.a > 9 and t0.a is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t0.a"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null + } + } +} +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.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; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t0.a is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t0.a"], + "r_loops": 10, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 40, + "attached_condition": "t1.b < 4" + } + } +} +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.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; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tbl1", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 20, + "attached_condition": "tbl1.b < 20" + }, + "block-nl-join": { + "table": { + "table_name": "tbl2", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 60, + "attached_condition": "tbl2.b < 60" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "r_filtered": 100 + } + } +} +analyze format=json +select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60 and tbl1.c > tbl2.c; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tbl1", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 20, + "attached_condition": "tbl1.b < 20" + }, + "block-nl-join": { + "table": { + "table_name": "tbl2", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 60, + "attached_condition": "tbl2.b < 60" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "tbl1.c > tbl2.c", + "r_filtered": 15.833 + } + } +} +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; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "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, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "using_index": true + } + } +} +drop table t1,t2; +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# +# MDEV-7679: ANALYZE crashes when printing WHERE when no default db +# +select database(); +database() +test +connect con1,localhost,root,,*NO-ONE*; +connection con1; +select database(); +database() +NULL +analyze format=json select * from test.t1 where t1.a<5; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 50, + "attached_condition": "test.t1.a < 5" + } + } +} +disconnect con1; +connection default; +drop table t1; +# +# MDEV-7812: ANALYZE FORMAT=JSON UPDATE/DELETE doesnt print +# the r_total_time_ms +# +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; +create table t1 (pk int primary key); +insert into t1 select a from t3; +alter table t1 add b int; +analyze format=json +update t1 set b=pk; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_total_time_ms": "REPLACED", + "table": { + "update": 1, + "table_name": "t1", + "access_type": "ALL", + "rows": 1000, + "r_rows": 1000, + "r_filtered": 100, + "r_total_time_ms": "REPLACED" + } + } +} +analyze format=json +select * from t1 where pk < 10 and b > 4; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "r_loops": 1, + "rows": 11, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 50, + "index_condition": "t1.pk < 10", + "attached_condition": "t1.b > 4" + } + } +} +analyze format=json +delete from t1 where pk < 10 and b > 4; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_total_time_ms": "REPLACED", + "table": { + "delete": 1, + "table_name": "t1", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk"], + "rows": 11, + "r_rows": 10, + "r_filtered": 50, + "r_total_time_ms": "REPLACED", + "attached_condition": "t1.pk < 10 and t1.b > 4" + } + } +} +drop table t1, t3, t2; +# +# MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record +# +create table t3(a int); +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t4(a int); +insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C; +create table t1 (lb1 int, rb1 int, lb2 int, rb2 int, c1 int, c2 int); +insert into t1 values (1,2,10,20,15,15); +insert into t1 values (3,5,10,20,15,15); +insert into t1 values (10,20,10,20,15,15); +insert into t1 values (10,20,1,2,15,15); +insert into t1 values (10,20,10,20,1,3); +create table t2 (key1 int, key2 int, key3 int, key4 int, col1 int, +key(key1), key(key2), key(key3), key(key4)); +insert into t2 select a,a,a,a,a from t3; +insert into t2 select 15,15,15,15,15 from t4; +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 +(t2.key3=t1.c1 OR t2.key4=t1.c2); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "range-checked-for-each-record": { + "keys": ["key1", "key2", "key3", "key4"], + "r_keys": { + "full_scan": 1, + "index_merge": 1, + "range": { + "key1": 2, + "key2": 1, + "key3": 0, + "key4": 0 + } + }, + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["key1", "key2", "key3", "key4"], + "r_loops": 5, + "rows": 1010, + "r_rows": 203.8, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 98.135 + } + } + } +} +drop table t1,t2,t3,t4; +# +# MDEV-7904: ANALYZE FORMAT=JSON SELECT .. UNION SELECT doesn't print r_rows for union output +# +create table t0 (a int); +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; +analyze format=json (select * from t1 tbl1 where a<5) union (select * from t1 tbl2 where a in (2,3)); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 5, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tbl1", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 50, + "attached_condition": "tbl1.a < 5" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tbl2", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 20, + "attached_condition": "tbl2.a in (2,3)" + } + } + } + ] + } + } +} +drop table t0, t1; +# +# MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +a int, +b int, +key (a) +); +insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; +# normal HAVING +analyze format=json select a, max(b) as TOP from t2 group by a having TOP > a; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "having_condition": "TOP > t2.a", + "filesort": { + "sort_key": "t2.a", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 0, + "r_buffer_size": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +# HAVING is always TRUE (not printed) +analyze format=json select a, max(b) as TOP from t2 group by a having 1<>2; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "filesort": { + "sort_key": "t2.a", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 256, + "r_buffer_size": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +# HAVING is always FALSE (intercepted by message) +analyze format=json select a, max(b) as TOP from t2 group by a having 1=2; +ANALYZE +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible HAVING" + } + } +} +# HAVING is absent +analyze format=json select a, max(b) as TOP from t2 group by a; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "filesort": { + "sort_key": "t2.a", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 256, + "r_buffer_size": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 256, + "r_rows": 256, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} +drop table t0, t1, t2; +# +# MDEV-7267: Server crashes in Item_field::print on ANALYZE FORMAT=JSON +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN * FROM t1, t2 WHERE b IN ( SELECT a FROM t1 ); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", + "r_filtered": null + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } +} +drop table t1,t2; +# +# MDEV-8864: Server crash #2 in Item_field::print on ANALYZE FORMAT=JSON +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (f3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(4); +ANALYZE FORMAT=JSON +SELECT GROUP_CONCAT(f3) AS gc, ( SELECT MAX(f1) FROM t1, t2 WHERE f2 = f3 ) sq +FROM t2, t3 +WHERE f3 IN ( 1, 2 ) +GROUP BY sq ORDER BY gc; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "filesort": { + "sort_key": "group_concat(t3.f3 separator ',')", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 0, + "r_buffer_size": "REPLACED", + "temporary_table": { + "filesort": { + "sort_key": "(subquery#2)", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 0, + "r_buffer_size": "REPLACED", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "t3.f3 in (1,2)" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "r_filtered": null + }, + "subqueries": [ + { + "expression_cache": { + "state": "uninitialized", + "r_loops": 0, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 0, + "rows": 2, + "r_rows": null, + "filtered": 100, + "r_filtered": null + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 0, + "rows": 2, + "r_rows": null, + "filtered": 100, + "r_filtered": null + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t2.f2 = t3.f3", + "r_filtered": null + } + } + } + } + ] + } + } + } + } + } +} +drop table t1,t2,t3; +# +# MDEV-13286: Floating point exception in Filesort_tracker::print_json_members(Json_writer*) +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int); +insert into t1 select a,a,a from t0; +create table t2 as select * from t1; +analyze format=json select a, (select t2.b from t2 where t2.a<t1.a order by t2.c limit 1) from t1 where t1.a<0; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "t1.a < 0" + }, + "subqueries": [ + { + "expression_cache": { + "state": "uninitialized", + "r_loops": 0, + "query_block": { + "select_id": 2, + "read_sorted_file": { + "r_rows": null, + "filesort": { + "sort_key": "t2.c", + "r_loops": null, + "r_limit": null, + "r_used_priority_queue": null, + "r_output_rows": null, + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 0, + "rows": 10, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "attached_condition": "t2.a < t1.a" + } + } + } + } + } + } + ] + } +} +drop table t0,t1,t2; |