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.
Smaller comments below.
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') a
drop 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
On Wed, Sep 09, 2015 at 04:29:54PM +0200, sanja@mariadb.com wrote: populate a table with 1M rows.
I was not the man who made the test suite, usually sotuation is reverse somebody require to keep the original. 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