| Thread Previous • Date Previous • Date Next • Thread Next |
On 14.09.15 21:59, Sergey Petrunia wrote:
Hi Sanja,
The big comment: I see HAVING being printed for EXPLAIN FORMAT=JSON but not for
ANALYZE FORMAT=JSON.
For example:
MariaDB [test]> explain format=json select count(*) from t1 where t1.a between 0 and 1000 group by b having count(*) > 0\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"having_condition": "(count(0) > 0)",
"filesort": {
"temporary_table": {
"function": "buffer",
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 1000,
"filtered": 100,
"attached_condition": "(t1.a between 0 and 1000)"
}
}
}
}
}
MariaDB [test]> analyze format=json select count(*) from t1 where t1.a between 0 and 1000 group by b having count(*) > 0\G
*************************** 1. row ***************************
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 49.417,
"filesort": {
"r_loops": 1,
"r_total_time_ms": 0.4251,
"r_used_priority_queue": false,
"r_output_rows": 101,
"r_buffer_size": "2Kb",
"temporary_table": {
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 1000,
"r_rows": 1000,
"r_total_time_ms": 18.597,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "(t1.a between 0 and 1000)"
}
}
}
}
}
oops, I'll fix.
I was not the man who made the test suite, usually sotuation is reverse somebody require to keep the original.Smaller comments below. On Wed, Sep 09, 2015 at 04:29:54PM +0200, sanja@xxxxxxxxxxx wrote:revision-id: 8983790df9a16520e69905fc4c35d20f7c935c3e (mariadb-10.1.6-119-g8983790) parent(s): 3fcd84c2891f4d2c881f13b53bc5a2401870b80a committer: Oleksandr Byelkin timestamp: 2015-09-09 16:29:50 +0200 message: MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING Printing non-trivial HAVING added. --- mysql-test/r/explain_json.result | 84 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/explain_json.test | 23 ++++++++++- sql/sql_explain.cc | 15 ++++++- sql/sql_explain.h | 7 +++- sql/sql_select.cc | 2 + 5 files changed, 128 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 0507cee..db2726d 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -799,6 +799,7 @@ EXPLAIN { "query_block": { "select_id": 2, + "having_condition": "trigcond(<is_not_null_test>(t1.a))", "full-scan-on-null_key": { "table": { "table_name": "t1", @@ -1070,3 +1071,86 @@ EXPLAIN } } drop table t1; +# +# MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +# +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 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 +explain format=json select a, max(b) as TOP from t2 group by a having TOP > a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "(TOP > t2.a)", + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10000, + "filtered": 100 + } + } + } + } +} +# HAVING is always TRUE (not printed) +explain format=json select a, max(b) as TOP from t2 group by a having 1<>2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10000, + "filtered": 100 + } + } + } + } +} +# HAVING is always FALSE (intercepted by message) +explain format=json select a, max(b) as TOP from t2 group by a having 1=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible HAVING" + } + } +} +# HAVING is absent +explain format=json select a, max(b) as TOP from t2 group by a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "temporary_table": { + "function": "buffer", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10000, + "filtered": 100 + } + } + } + } +} +drop table t0, t1, t2; diff --git a/mysql-test/t/explain_json.test b/mysql-test/t/explain_json.test index 3e6f34b..0870817 100644 --- a/mysql-test/t/explain_json.test +++ b/mysql-test/t/explain_json.test @@ -278,4 +278,25 @@ explain format=json select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') adrop table t1; -+--echo # +--echo # MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +--echo # +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 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;This seems to be excessive. I don't see any examples using key(a), I don't see a need to populate a table with 1M rows.
But I agree, I'll fix.
+--echo # normal HAVING +explain format=json select a, max(b) as TOP from t2 group by a having TOP > a; +--echo # HAVING is always TRUE (not printed) +explain format=json select a, max(b) as TOP from t2 group by a having 1<>2; +--echo # HAVING is always FALSE (intercepted by message) +explain format=json select a, max(b) as TOP from t2 group by a having 1=2; +--echo # HAVING is absent +explain format=json select a, max(b) as TOP from t2 group by a; +drop table t0, t1, t2; diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 15ee67a..5f60aee 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -862,7 +862,20 @@ void Explain_select::print_explain_json(Explain_query *query, writer->add_member("const_condition"); write_item(writer, exec_const_cond); } - + /* we do not print always TRUE HAVING */This doesn't parse. Did you mean: " Do not print HAVING clause if it always evaluates to TRUE" ?
yes
+ if (having || (having_value == Item::COND_FALSE)) + { + writer->add_member("having_condition"); + if (likely(having)) + write_item(writer, having); + else + { + /* Normally we should not go this branch, left just for safety */ + DBUG_ASSERT(having_value == Item::COND_FALSE); + writer->add_str("0"); + } + } + Filesort_tracker *first_table_sort= NULL; bool first_table_sort_used= false; int started_objects= 0; diff --git a/sql/sql_explain.h b/sql/sql_explain.h index f92a576..434be47 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -209,6 +209,7 @@ class Explain_select : public Explain_basic_join Explain_select(MEM_ROOT *root, bool is_analyze) : Explain_basic_join(root), message(NULL), + having(NULL), having_value(Item::COND_UNDEF), using_temporary(false), using_filesort(false), time_tracker(is_analyze), ops_tracker(is_analyze) @@ -231,7 +232,11 @@ class Explain_select : public Explain_basic_join/* Expensive constant condition */Item *exec_const_cond; - + + /* HAVING condition */ + COND *having; + Item::cond_result having_value; + /* Global join attributes. In tabular form, they are printed on the first row */ bool using_temporary; bool using_filesort; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3f2c2ea..be4b076 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24159,6 +24159,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, xpl_sel->using_filesort= true;xpl_sel->exec_const_cond= exec_const_cond;+ xpl_sel->having= having; + xpl_sel->having_value= having_value;JOIN_TAB* const first_top_tab= join->first_breadth_first_optimization_tab();JOIN_TAB* prev_bush_root_tab= NULL;BR Sergei
| Thread Previous • Date Previous • Date Next • Thread Next |
