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": "1Kb",
      "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": "1Kb",
      "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": 10,
      "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": 10,
      "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": "65",
      "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": "1",
              "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": "65",
                      "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;