revision-id: 7ac24cafb339fbd2180859db11888fa5e465b1c4 (mariadb-10.3.6-132-g7ac24ca) parent(s): 3f9040085a0de4976f55bc7e4a2fa5fa8d923100 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-09 20:59:03 -0800 message: MDEV-17096 Pushdown of simple derived tables to storage engines Resolved the problem of forming a proper query string for FEDERATEDX. Added test cases. --- .../federated/federatedx_create_handlers.result | 109 ++++++++++++++++++++- .../federated/federatedx_create_handlers.test | 40 +++++++- sql/item_cmpfunc.cc | 9 +- sql/item_subselect.cc | 6 +- sql/mysqld.h | 2 + sql/sql_derived.cc | 26 ++++- sql/table.h | 1 + storage/federatedx/federatedx_pushdown.cc | 7 +- 8 files changed, 183 insertions(+), 17 deletions(-) diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result index fdad44c..937e2b6 100644 --- a/mysql-test/suite/federated/federatedx_create_handlers.result +++ b/mysql-test/suite/federated/federatedx_create_handlers.result @@ -190,7 +190,114 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f 1 PRIMARY t3 ALL NULL NULL NULL NULL 7 7.00 100.00 100.00 1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 0.00 100.00 100.00 2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -DROP TABLE federated.t1, federated.t2; +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 +WHERE id IN (SELECT count(*) +FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; +name name +xxx xxx +EXPLAIN +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 +WHERE id IN (SELECT count(*) +FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 Using temporary +ANALYZE FORMAT=JSON +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 +WHERE id IN (SELECT count(*) +FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 7, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "18", + "used_key_parts": ["name"], + "ref": ["federated.t3.name"], + "r_loops": 7, + "rows": 2, + "r_rows": 0, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "message": "Pushed derived" + }, + "subqueries": [ + { + "query_block": { + "select_id": 3, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 0, + "rows": 7, + "r_rows": null, + "filtered": 100, + "r_filtered": null + } + } + } + } + ] + } + } + } + } +} +SELECT t.id, federated.t3.name +FROM federated.t3, +( SELECT * FROM federated.t1 WHERE id < 3 +UNION +SELECT * FROM federated.t1 WHERE id >= 5) t +WHERE federated.t3.name=t.name; +id name +5 yyy +7 yyy +5 yyy +7 yyy +5 yyy +7 yyy +EXPLAIN +SELECT t.id, federated.t3.name +FROM federated.t3, +( SELECT * FROM federated.t1 WHERE id < 3 +UNION +SELECT * FROM federated.t1 WHERE id >= 5) t +WHERE federated.t3.name=t.name; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> ref key0 key0 18 federated.t3.name 2 +2 PUSHED DERIVED NULL NULL NULL NULL NULL NULL NULL NULL +DROP TABLE federated.t1, federated.t2, federated.t3; connection slave; DROP TABLE federated.t1, federated.t2; connection default; diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test index 0e586da..6577943 100644 --- a/mysql-test/suite/federated/federatedx_create_handlers.test +++ b/mysql-test/suite/federated/federatedx_create_handlers.test @@ -76,6 +76,7 @@ SELECT id FROM federated.t1 WHERE id < 5; ANALYZE SELECT id FROM federated.t1 WHERE id < 5; +--source include/analyze-format.inc ANALYZE FORMAT=JSON SELECT id FROM federated.t1 WHERE id < 5; @@ -106,7 +107,44 @@ SELECT * FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t WHERE federated.t3.name=t.name; -DROP TABLE federated.t1, federated.t2; +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 + WHERE id IN (SELECT count(*) + FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; + +EXPLAIN +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 + WHERE id IN (SELECT count(*) + FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; + +--source include/analyze-format.inc +ANALYZE FORMAT=JSON +SELECT * +FROM federated.t3, (SELECT t1.name FROM federated.t1 + WHERE id IN (SELECT count(*) + FROM federated.t2 GROUP BY name)) t +WHERE federated.t3.name=t.name; + +SELECT t.id, federated.t3.name +FROM federated.t3, + ( SELECT * FROM federated.t1 WHERE id < 3 + UNION + SELECT * FROM federated.t1 WHERE id >= 5) t +WHERE federated.t3.name=t.name; + +EXPLAIN +SELECT t.id, federated.t3.name +FROM federated.t3, + ( SELECT * FROM federated.t1 WHERE id < 3 + UNION + SELECT * FROM federated.t1 WHERE id >= 5) t +WHERE federated.t3.name=t.name; + + +DROP TABLE federated.t1, federated.t2, federated.t3; connection slave; DROP TABLE federated.t1, federated.t2; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 4cd6304..821f51f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1215,8 +1215,13 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg) void Item_in_optimizer::print(String *str, enum_query_type query_type) { - restore_first_argument(); - Item_func::print(str, query_type); + if (query_type & QT_PARSABLE) + args[1]->print(str, query_type); + else + { + restore_first_argument(); + Item_func::print(str, query_type); + } } diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 0ace59f..7aa2ed4 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3272,7 +3272,8 @@ Item_in_subselect::select_in_like_transformer(JOIN *join) void Item_in_subselect::print(String *str, enum_query_type query_type) { - if (test_strategy(SUBS_IN_TO_EXISTS)) + if (test_strategy(SUBS_IN_TO_EXISTS) && + !(query_type & QT_PARSABLE)) str->append(STRING_WITH_LEN("<exists>")); else { @@ -3499,7 +3500,8 @@ Item_allany_subselect::select_transformer(JOIN *join) void Item_allany_subselect::print(String *str, enum_query_type query_type) { - if (test_strategy(SUBS_IN_TO_EXISTS)) + if (test_strategy(SUBS_IN_TO_EXISTS) && + !(query_type & QT_PARSABLE)) str->append(STRING_WITH_LEN("<exists>")); else { diff --git a/sql/mysqld.h b/sql/mysqld.h index 3d056fb..320dedc 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -745,6 +745,8 @@ enum enum_query_type /// SHOW CREATE {VIEW|PROCEDURE|FUNCTION} and other cases where the /// original representation is required, should set this flag. QT_ITEM_ORIGINAL_FUNC_NULLIF= (1 << 7), + /// good for parsing + QT_PARSABLE= (1 << 8), /// This value means focus on readability, not on ability to parse back, etc. QT_EXPLAIN= QT_TO_SYSTEM_CHARSET | diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 9ad22dd..00ecb2c 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -385,7 +385,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if ((derived->dt_handler= derived->find_derived_handler(thd))) + if (derived->dt_handler) { derived->change_refs_to_fields(); derived->set_materialized_derived(); @@ -820,6 +820,24 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) if (derived->is_derived() && derived->is_merged_derived()) first_select->mark_as_belong_to_derived(derived); + derived->dt_handler= derived->find_derived_handler(thd); + if (derived->dt_handler) + { + char query_buff[4096]; + String derived_query(query_buff, sizeof(query_buff), thd->charset()); + derived_query.length(0); + derived->derived->print(&derived_query, + enum_query_type(QT_VIEW_INTERNAL | + QT_ITEM_ORIGINAL_FUNC_NULLIF | + QT_PARSABLE)); + if (!thd->make_lex_string(&derived->derived_spec, + derived_query.ptr(), derived_query.length())) + { + delete derived->dt_handler; + derived->dt_handler= NULL; + } + } + exit: /* Hide "Unknown column" or "Unknown function" error */ if (derived->view) @@ -912,16 +930,14 @@ bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if (derived->is_materialized_derived() && !derived->dt_handler) - derived->dt_handler= derived->find_derived_handler(thd); - if (derived->dt_handler) + if (derived->is_materialized_derived() && derived->dt_handler) { if (!(derived->pushdown_derived= new (thd->mem_root) Pushdown_derived(derived, derived->dt_handler))) { delete derived->dt_handler; derived->dt_handler= NULL; - DBUG_RETURN(1); + DBUG_RETURN(TRUE); } } diff --git a/sql/table.h b/sql/table.h index 7b88344..f7bcdaa 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2137,6 +2137,7 @@ struct TABLE_LIST bool is_derived_with_recursive_reference; bool block_handle_derived; derived_handler *dt_handler; + LEX_CSTRING derived_spec; Pushdown_derived *pushdown_derived; ST_SCHEMA_TABLE *schema_table; /* Information_schema table */ st_select_lex *schema_select_lex; diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc index bfe421c..bae69d4 100644 --- a/storage/federatedx/federatedx_pushdown.cc +++ b/storage/federatedx/federatedx_pushdown.cc @@ -62,7 +62,6 @@ ha_federatedx_derived_handler::~ha_federatedx_derived_handler() {} int ha_federatedx_derived_handler::init_scan() { - char query_buff[4096]; THD *thd; int rc= 0; @@ -77,11 +76,7 @@ int ha_federatedx_derived_handler::init_scan() if ((rc= txn->acquire(share, thd, TRUE, iop))) DBUG_RETURN(rc); - String derived_query(query_buff, sizeof(query_buff), thd->charset()); - derived_query.length(0); - derived->derived->print(&derived_query, QT_ORDINARY); - - if ((*iop)->query(derived_query.ptr(), derived_query.length())) + if ((*iop)->query(derived->derived_spec.str, derived->derived_spec.length)) goto err; stored_result= (*iop)->store_result();