- commits - lists.mariadb.org

[Commits] de722585c1b: MDEV-16225: wrong resultset from query with semijoin=on
by varunraiko1803＠gmail.com 27 May '18

27 May '18

revision-id: de722585c1b2ad2af99bd1749e8566ea77838764 (mariadb-5.5.56-219-gde722585c1b) parent(s): 1ada4afb0a51f7283b6187a95019ec2cb80c8a0b author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-24 03:09:42 +0530 message: MDEV-16225: wrong resultset from query with semijoin=on For non-semi-join subquery optimization we do a cost based decision between Materialisation and IN -> EXIST transformation. The issue in this case is that for IN->EXIST transformation we run JOIN::reoptimize with the IN->EXISt conditions and we come up with a new query plan. But when we compare the cost with Materialization, we make the decision to chose Materialization so we need to restore the query plan for Materilization. The restoring of query plan is not handled correctly. --- mysql-test/r/subselect_sj2_mat.result | 57 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj2_mat.test | 42 ++++++++++++++++++++++++++ sql/sql_select.cc | 16 ++++++++++ 3 files changed, 115 insertions(+) diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 835742a3ff4..19aa3bd3f02 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1601,3 +1601,60 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; +# +# MDEV-16225: wrong resultset from query with semijoin=on +# +CREATE TABLE t1 ( +`id` int(10) NOT NULL AUTO_INCREMENT, +`local_name` varchar(64) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index +2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED B ALL PRIMARY NULL NULL NULL 13 Using where +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +drop table t1; diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index cfb6c8c2819..0665cdf68fe 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -303,3 +303,45 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-16225: wrong resultset from query with semijoin=on +--echo # + +CREATE TABLE t1 ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `local_name` varchar(64) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; + +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); + +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d6d269a700f..b8d43b9797e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -23805,6 +23805,22 @@ void JOIN::restore_query_plan(Join_plan_state *restore_from) } } + else + { + /* + After running JOIN::reoptimize for IN->EXISTS + transformation we might add elements to the keyuse array, + The original query plan was that the keyuse array had no elements, so we + should restore that state here. + */ + while(keyuse.elements) + delete_dynamic_element(&keyuse, 0); + for (uint i= 0; i < table_count; i++) + { + join_tab[i].keyuse= NULL; + join_tab[i].checked_keys.clear_all(); + } + } memcpy((uchar*) best_positions, (uchar*) restore_from->best_positions, sizeof(POSITION) * (table_count + 1)); /* Restore SJM nests */

2 1

[Commits] 350ae49: Buildbot pass #3
by psergey＠askmonty.org 25 May '18

25 May '18

revision-id: 350ae49df2b59cf8f4f6cef2f4d1314b6f12bb46 parent(s): dea9767f7ef83234631c09b17f850ae88ba0f2a4 committer: Sergei Petrunia branch nick: 10.2-mariarocks-merge timestamp: 2018-05-25 11:51:15 +0300 message: Buildbot pass #3 --- storage/rocksdb/ha_rocksdb.cc | 1 + 1 file changed, 1 insertion(+) diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 9fb7306..966f81e 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -19,6 +19,7 @@ #endif // another buildbot pass +// and another (pass #3) #define MYSQL_SERVER 1 /* For use of 'PRIu64': */

1 0

[Commits] d347659: An optimization of the code for mdev-11953.
by IgorBabaev 25 May '18

25 May '18

revision-id: d347659fa0ca044074ab44497b1373053ca79f3d (mariadb-10.3.6-7-gd347659) parent(s): 549bcceefd94e8700c9e629bdd2c2f5a73d2459b author: Igor Babaev committer: Igor Babaev timestamp: 2018-05-24 21:41:51 -0700 message: An optimization of the code for mdev-11953. This patch eliminates the usage of the stack last_select_stack in the parser. Now the field st_select_lex::first_nested is used to perform the actions that were implemented with last_select_stack. --- sql/sql_lex.cc | 55 ++++++++++++++++++----------------------------------- sql/sql_lex.h | 36 ++++++++++++++++++----------------- sql/sql_yacc.yy | 47 ++++++++++++++++++++------------------------- sql/sql_yacc_ora.yy | 47 ++++++++++++++++++++------------------------- 4 files changed, 80 insertions(+), 105 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 55e76c7..bd1d780 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -5330,12 +5330,12 @@ LEX::wrap_unit_into_derived(SELECT_LEX_UNIT *unit) DBUG_RETURN(NULL); } -SELECT_LEX *LEX::link_selects_chain_down(SELECT_LEX *sel) +SELECT_LEX *LEX::wrap_select_chain_into_derived(SELECT_LEX *sel) { SELECT_LEX *dummy_select; SELECT_LEX_UNIT *unit; Table_ident *ti; - DBUG_ENTER("LEX::link_selects_chain_down"); + DBUG_ENTER("LEX::wrap_select_chain_into_derived"); if (!(dummy_select= alloc_select(TRUE))) DBUG_RETURN(NULL); @@ -5525,44 +5525,27 @@ bool LEX::push_context(Name_resolution_context *context) } -bool LEX::push_new_select(SELECT_LEX *last) +SELECT_LEX *LEX::create_priority_nest(SELECT_LEX *first_in_nest) { - DBUG_ENTER("LEX::push new_select"); - DBUG_PRINT("info", ("Push last select: %p (%d)", - last, last->select_number)); - bool res= last_select_stack.push_front(last, thd->mem_root); - DBUG_RETURN(res); -} - - -bool check_intersect_prefix(SELECT_LEX* first_in_unit) -{ - SELECT_LEX *sel; - for (sel= first_in_unit->next_select(); - sel && sel->linkage == INTERSECT_TYPE; - sel= sel->next_select()) - ; - return (sel == NULL); + DBUG_ENTER("LEX::create_priority_nest"); + DBUG_ASSERT(first_in_nest->first_nested); + enum sub_select_type wr_unit_type= first_in_nest->linkage; + bool wr_distinct= first_in_nest->distinct; + SELECT_LEX *attach_to= first_in_nest->first_nested; + attach_to->cut_next(); + SELECT_LEX *wrapper= wrap_select_chain_into_derived(first_in_nest); + if (wrapper) + { + first_in_nest->first_nested= NULL; + wrapper->set_linkage_and_distinct(wr_unit_type, wr_distinct); + wrapper->first_nested= attach_to->first_nested; + wrapper->set_master_unit(attach_to->master_unit()); + attach_to->link_neighbour(wrapper); + } + DBUG_RETURN(wrapper); } -SELECT_LEX *LEX::pop_new_select_and_wrap() -{ - DBUG_ENTER("LEX::pop_new_select_and_wrap"); - SELECT_LEX *sel; - SELECT_LEX *last= pop_new_select(); - SELECT_LEX *first= last->next_select(); - last->cut_next(); - enum sub_select_type op= first->linkage; - bool ds= first->distinct; - if (!(sel= link_selects_chain_down(first))) - DBUG_RETURN(NULL); - last->link_neighbour(sel); - sel->set_linkage_and_distinct(op, ds); - sel->set_master_unit(last->master_unit()); - DBUG_RETURN(sel); -} - /** Checks if we need finish "automatic brackets" mode diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f715fdd..2a94d96 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -82,8 +82,6 @@ inline int cmp_unit_op(enum sub_select_type op1, enum sub_select_type op2) return (op1 == INTERSECT_TYPE ? 1 : 0) - (op2 == INTERSECT_TYPE ? 1 : 0); } -bool check_intersect_prefix(SELECT_LEX* first_in_unit); - enum unit_common_op {OP_MIX, OP_UNION, OP_INTERSECT, OP_EXCEPT}; enum enum_view_suid @@ -887,6 +885,23 @@ class Field_pair :public Sql_alloc class st_select_lex: public st_select_lex_node { public: + /* + Currently the field first_nested is used only by parser. + It containa either a reference to the first select + of the nest of selects to which 'this' belongs to, or + in the case of priority jump it contains a reference to + the select to which the priority nest has to be attached to. + If there is no priority jump then the first select of the + nest contains the reference to itself in first_nested. + Example: + select1 union select2 intersect select + Here we have a priority jump at select2. + So select2->first_nested points to select1, + while select3->first_nested points to select2 and + select1->first_nested points to select1. + */ + st_select_lex *first_nested; + Name_resolution_context context; LEX_CSTRING db; Item *where, *having; /* WHERE & HAVING clauses */ @@ -2881,7 +2896,6 @@ struct LEX: public Query_tables_list required a local context, the parser pops the top-most context. */ List<Name_resolution_context> context_stack; - List<SELECT_LEX> last_select_stack; SELECT_LEX *select_stack[MAX_SELECT_NESTING]; uint select_stack_top; @@ -3251,17 +3265,6 @@ struct LEX: public Query_tables_list DBUG_VOID_RETURN; } - bool push_new_select(SELECT_LEX *last); - - SELECT_LEX *pop_new_select() - { - DBUG_ENTER("LEX::pop_new_select"); - SELECT_LEX* last= last_select_stack.pop(); - DBUG_PRINT("info", ("Pop last elect: %p (%d)", - last, last->select_number)); - DBUG_RETURN(last); - } - SELECT_LEX *select_stack_head() { if (likely(select_stack_top)) @@ -3269,7 +3272,6 @@ struct LEX: public Query_tables_list return NULL; } - bool push_select(SELECT_LEX *select_lex) { DBUG_ENTER("LEX::push_select"); @@ -3982,10 +3984,10 @@ struct LEX: public Query_tables_list SELECT_LEX *alloc_select(bool is_select); SELECT_LEX_UNIT *create_unit(SELECT_LEX*); SELECT_LEX *wrap_unit_into_derived(SELECT_LEX_UNIT *unit); - SELECT_LEX *link_selects_chain_down(SELECT_LEX *sel); + SELECT_LEX *wrap_select_chain_into_derived(SELECT_LEX *sel); bool main_select_push(); bool insert_select_hack(SELECT_LEX *sel); - SELECT_LEX *pop_new_select_and_wrap(); + SELECT_LEX *create_priority_nest(SELECT_LEX *first_in_nest); void set_main_unit(st_select_lex_unit *u) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 491874b..7117865 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9182,16 +9182,13 @@ query_primary: query_primary_parens: '(' query_expression_unit { - SELECT_LEX *last= $2->pre_last_parse->next_select(); - int cmp= cmp_unit_op($2->first_select()->next_select()->linkage, - last->linkage); - if (cmp < 0) + SELECT_LEX *first_in_nest= + $2->pre_last_parse->next_select()->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if (!check_intersect_prefix($2->first_select())) - { - if (Lex->pop_new_select_and_wrap() == NULL) - MYSQL_YYABORT; - } + /* There is a priority jump starting from first_in_nest */ + if (Lex->create_priority_nest(first_in_nest) == NULL) + MYSQL_YYABORT; } Lex->push_select($2->fake_select_lex); } @@ -9275,6 +9272,7 @@ query_expression_unit: } sel1->link_neighbour(sel2); sel2->set_linkage_and_distinct($2.unit_type, $2.distinct); + sel2->first_nested= sel1->first_nested= sel1; $$= Lex->create_unit(sel1); $$->pre_last_parse= sel1; if ($$ == NULL) @@ -9298,23 +9296,23 @@ query_expression_unit: int cmp= cmp_unit_op($2.unit_type, last->linkage); if (cmp == 0) { - // do nothing, this part will be just connected + sel1->first_nested= last->first_nested; } else if (cmp > 0) { - // Store beginning and continue to connect parts - if (Lex->push_new_select($1->pre_last_parse)) - MYSQL_YYABORT; + last->first_nested= $1->pre_last_parse; + sel1->first_nested= last; } else /* cmp < 0 */ { - // wrap stored part in a select, then continue to connect parts - if (!check_intersect_prefix($1->first_select())) + SELECT_LEX *first_in_nest= last->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if ((last= Lex->pop_new_select_and_wrap()) == NULL) + /* There is a priority jump starting from first_in_nest */ + if ((last= Lex->create_priority_nest(first_in_nest)) == NULL) MYSQL_YYABORT; - last->set_master_unit($1); } + sel1->first_nested= last->first_nested; } last->link_neighbour(sel1); sel1->set_master_unit($1); @@ -9373,16 +9371,13 @@ query_expression_body: } | query_expression_unit { - SELECT_LEX *last= $1->pre_last_parse->next_select(); - int cmp= cmp_unit_op($1->first_select()->next_select()->linkage, - last->linkage); - if (cmp < 0) + SELECT_LEX *first_in_nest= + $1->pre_last_parse->next_select()->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if (!check_intersect_prefix($1->first_select())) - { - if (Lex->pop_new_select_and_wrap() == NULL) - MYSQL_YYABORT; - } + /* There is a priority jump starting from first_in_nest */ + if (Lex->create_priority_nest(first_in_nest) == NULL) + MYSQL_YYABORT; } Lex->push_select($1->fake_select_lex); } diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 069fa0a..8bf09df 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9000,16 +9000,13 @@ query_primary: query_primary_parens: '(' query_expression_unit { - SELECT_LEX *last= $2->pre_last_parse->next_select(); - int cmp= cmp_unit_op($2->first_select()->next_select()->linkage, - last->linkage); - if (cmp < 0) + SELECT_LEX *first_in_nest= + $2->pre_last_parse->next_select()->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if (!check_intersect_prefix($2->first_select())) - { - if (Lex->pop_new_select_and_wrap() == NULL) - MYSQL_YYABORT; - } + /* There is a priority jump starting from first_in_nest */ + if (Lex->create_priority_nest(first_in_nest) == NULL) + MYSQL_YYABORT; } Lex->push_select($2->fake_select_lex); } @@ -9093,6 +9090,7 @@ query_expression_unit: } sel1->link_neighbour(sel2); sel2->set_linkage_and_distinct($2.unit_type, $2.distinct); + sel2->first_nested= sel1->first_nested= sel1; $$= Lex->create_unit(sel1); $$->pre_last_parse= sel1; if ($$ == NULL) @@ -9116,23 +9114,23 @@ query_expression_unit: int cmp= cmp_unit_op($2.unit_type, last->linkage); if (cmp == 0) { - // do nothing, this part will be just connected + sel1->first_nested= last->first_nested; } else if (cmp > 0) { - // Store beginning and continue to connect parts - if (Lex->push_new_select($1->pre_last_parse)) - MYSQL_YYABORT; + last->first_nested= $1->pre_last_parse; + sel1->first_nested= last; } else /* cmp < 0 */ { - // wrap stored part in a select, then continue to connect parts - if (!check_intersect_prefix($1->first_select())) + SELECT_LEX *first_in_nest= last->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if ((last= Lex->pop_new_select_and_wrap()) == NULL) + /* There is a priority jump starting from first_in_nest */ + if ((last= Lex->create_priority_nest(first_in_nest)) == NULL) MYSQL_YYABORT; - last->set_master_unit($1); } + sel1->first_nested= last->first_nested; } last->link_neighbour(sel1); sel1->set_master_unit($1); @@ -9191,16 +9189,13 @@ query_expression_body: } | query_expression_unit { - SELECT_LEX *last= $1->pre_last_parse->next_select(); - int cmp= cmp_unit_op($1->first_select()->next_select()->linkage, - last->linkage); - if (cmp < 0) + SELECT_LEX *first_in_nest= + $1->pre_last_parse->next_select()->first_nested; + if (first_in_nest->first_nested != first_in_nest) { - if (!check_intersect_prefix($1->first_select())) - { - if (Lex->pop_new_select_and_wrap() == NULL) - MYSQL_YYABORT; - } + /* There is a priority jump starting from first_in_nest */ + if (Lex->create_priority_nest(first_in_nest) == NULL) + MYSQL_YYABORT; } Lex->push_select($1->fake_select_lex); }

1 0

[Commits] dea9767: +// another buildbot pass
by psergey＠askmonty.org 24 May '18

24 May '18

revision-id: dea9767f7ef83234631c09b17f850ae88ba0f2a4 parent(s): 223ff38cd2ad19727f067869d2824cbada97a8d0 committer: Sergei Petrunia branch nick: 10.2-mariarocks-merge timestamp: 2018-05-24 15:00:19 +0300 message: +// another buildbot pass --- storage/rocksdb/ha_rocksdb.cc | 1 + 1 file changed, 1 insertion(+) diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 96bc7da..9fb7306 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -18,6 +18,7 @@ #pragma implementation // gcc: Class implementation #endif +// another buildbot pass #define MYSQL_SERVER 1 /* For use of 'PRIu64': */

1 0

[Commits] 561c366a9e4: cleanup
by Oleksandr Byelkin 24 May '18

24 May '18

revision-id: 561c366a9e440797cc7eb0c1c5fad5d10c498a73 (mariadb-10.3.6-8-g561c366a9e4) parent(s): 24c07973c89244765dc964684c91ce30fc32ac2d author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-05-24 12:35:45 +0200 message: cleanup --- sql/sql_lex.cc | 116 -------------------------------------------------------- sql/sql_lex.h | 4 -- sql/sql_yacc.yy | 89 ------------------------------------------- 3 files changed, 209 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 55e76c7effd..539161f4eaf 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -5396,122 +5396,6 @@ SELECT_LEX *LEX::link_selects_chain_down(SELECT_LEX *sel) DBUG_RETURN(NULL); } -/** - Put given (new) SELECT_LEX level below after currect (last) SELECT - - LAST SELECT -> DUMMY SELECT - | - V - NEW UNIT - | - V - NEW SELECT - - SELECT (*LAST*) ... FROM (SELECT (*NEW*) ... ) - - @param nselect Select to put one level below - - @retval TRUE Error - @retval FALSE OK -*/ - -bool LEX::add_unit_in_brackets(SELECT_LEX *nselect) -{ - DBUG_ENTER("LEX::add_unit_in_brackets"); - SELECT_LEX_UNIT *unit= nselect->master_unit(); - int old_nest_level= nselect->nest_level; - bool distinct= unit->union_distinct == nselect; - if (add_select_to_union_list(distinct, nselect->linkage, 0)) - DBUG_RETURN(TRUE); - - SELECT_LEX *dummy= current_select; - dummy->next= NULL; - if (make_select_in_brackets(current_select, nselect, FALSE)) - DBUG_RETURN(TRUE); - - if (!distinct && nselect->master_unit()->union_distinct) - { - // move distinct pointer - if (unit->union_distinct->master_unit() != unit) - { - unit->union_distinct->master_unit()->union_distinct= unit->union_distinct; - unit->union_distinct= NULL; - } - } - else if (distinct) - unit->union_distinct= NULL;// distinct was moved by add_select_to_union_list - - dummy->set_nest_level(old_nest_level); - DBUG_RETURN(FALSE); -} - - -bool LEX::make_select_in_brackets(SELECT_LEX* dummy_select, - SELECT_LEX *nselect, bool automatic) -{ - DBUG_ENTER("LEX::make_select_in_brackets"); - - int old_nest_level= nselect->nest_level; - dummy_select->automatic_brackets= automatic; - dummy_select->set_linkage(nselect->linkage); - current_select= dummy_select; // for mysql_new_select & Items - - /* stuff dummy SELECT * FROM (...) */ - Name_resolution_context *context= &dummy_select->context; - context->init(); - - /* add SELECT list*/ - Item *item= new (thd->mem_root) - Item_field(thd, context, NULL, NULL, &star_clex_str); - if (item == NULL) - DBUG_RETURN(TRUE); - if (add_item_to_list(thd, item)) - DBUG_RETURN(TRUE); - (dummy_select->with_wild)++; - - nselect->set_linkage(DERIVED_TABLE_TYPE); - SELECT_LEX *sl= nselect; - bool down= TRUE; - do{ - SELECT_LEX *next= sl->next_select(); - if (mysql_new_select(this, down, sl)) - DBUG_RETURN(TRUE); - down= FALSE; - DBUG_ASSERT(sl->outer_select() == dummy_select); - sl= next; - } while (sl); - - current_select= dummy_select; - - SELECT_LEX_UNIT *unit= nselect->master_unit(); - Table_ident *ti= new (thd->mem_root) Table_ident(unit); - if (ti == NULL) - DBUG_RETURN(TRUE); - char buff[10]; - LEX_CSTRING alias; - alias.length= my_snprintf(buff, sizeof(buff), - "__%u", dummy_select->select_number); - alias.str= thd->strmake(buff, alias.length); - if (!alias.str) - DBUG_RETURN(TRUE); - - TABLE_LIST *table_list; - if (!(table_list= dummy_select->add_table_to_list(thd, ti, &alias, - 0, TL_READ, - MDL_SHARED_READ))) - DBUG_RETURN(TRUE); - context->resolve_in_table_list_only(table_list); - dummy_select->add_joined_table(table_list); - - derived_tables|= DERIVED_SUBQUERY; - - if (dummy_select->set_nest_level(old_nest_level)) - DBUG_RETURN(TRUE); - - current_select= nselect; - DBUG_RETURN(FALSE); -} - bool LEX::push_context(Name_resolution_context *context) { DBUG_ENTER("LEX::push_context"); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f715fddc5f0..053444190a9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -3402,9 +3402,6 @@ struct LEX: public Query_tables_list int case_stmt_action_expr(Item* expr); int case_stmt_action_when(Item *when, bool simple); int case_stmt_action_then(); - bool add_select_to_union_list(bool is_union_distinct, - enum sub_select_type type, - bool is_top_level); bool setup_select_in_parentheses(); bool set_trigger_new_row(const LEX_CSTRING *name, Item *val); bool set_trigger_field(const LEX_CSTRING *name1, const LEX_CSTRING *name2, @@ -3958,7 +3955,6 @@ struct LEX: public Query_tables_list SELECT_LEX *exclude_last_select(); SELECT_LEX *exclude_not_first_select(SELECT_LEX *exclude); - bool add_unit_in_brackets(SELECT_LEX *nselect); void check_automatic_up(enum sub_select_type type); bool create_or_alter_view_finalize(THD *thd, Table_ident *table_ident); bool add_alter_view(THD *thd, uint16 algorithm, enum_view_suid suid, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d856570c3de..5f4e615280b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -487,95 +487,6 @@ Item* handle_sql2003_note184_exception(THD *thd, Item* left, bool equal, DBUG_RETURN(result); } -/** - @brief Creates a new SELECT_LEX for a UNION branch. - - Sets up and initializes a SELECT_LEX structure for a query once the parser - discovers a UNION token. The current SELECT_LEX is pushed on the stack and - the new SELECT_LEX becomes the current one. - - @param lex The parser state. - - @param is_union_distinct True if the union preceding the new select - statement uses UNION DISTINCT. - - @param is_top_level This should be @c TRUE if the newly created SELECT_LEX - is a non-nested statement. - - @return <code>false</code> if successful, <code>true</code> if an error was - reported. In the latter case parsing should stop. - */ -bool LEX::add_select_to_union_list(bool is_union_distinct, - enum sub_select_type type, - bool is_top_level) -{ - const char *type_name= (type == INTERSECT_TYPE ? "INTERSECT" : - (type == EXCEPT_TYPE ? "EXCEPT" : "UNION")); - DBUG_ENTER("LEX::add_select_to_union_list"); - /* - Only the last SELECT can have INTO. Since the grammar won't allow INTO in - a nested SELECT, we make this check only when creating a top-level SELECT. - */ - if (is_top_level && result) - { - my_error(ER_WRONG_USAGE, MYF(0), type_name, "INTO"); - DBUG_RETURN(TRUE); - } - if (current_select->order_list.first && !current_select->braces) - { - my_error(ER_WRONG_USAGE, MYF(0), type_name, "ORDER BY"); - DBUG_RETURN(TRUE); - } - - if (current_select->explicit_limit && !current_select->braces) - { - my_error(ER_WRONG_USAGE, MYF(0), type_name, "LIMIT"); - DBUG_RETURN(TRUE); - } - if (current_select->linkage == GLOBAL_OPTIONS_TYPE) - { - thd->parse_error(); - DBUG_RETURN(TRUE); - } - if (!is_union_distinct && (type == INTERSECT_TYPE || type == EXCEPT_TYPE)) - { - my_error(ER_WRONG_USAGE, MYF(0), type_name, "ALL"); - DBUG_RETURN(TRUE); - } - /* - Priority implementation, but also trying to keep things as flat - as possible */ - if (type == INTERSECT_TYPE && - (current_select->linkage != INTERSECT_TYPE && - current_select != current_select->master_unit()->first_select())) - { - /* - This and previous SELECTs should go one level down because of - priority - */ - SELECT_LEX *prev= exclude_last_select(); - if (add_unit_in_brackets(prev)) - DBUG_RETURN(TRUE); - bool res= add_select_to_union_list(is_union_distinct, type, 0); - DBUG_RETURN(res); - } - else - { - check_automatic_up(type); - } - if (mysql_new_select(this, 0, NULL)) - DBUG_RETURN(TRUE); - mysql_init_select(this); - current_select->set_linkage(type); - if (is_union_distinct) /* UNION DISTINCT - remember position */ - { - current_select->master_unit()->union_distinct= - current_select; - } - DBUG_RETURN(FALSE); -} - - /** Create a separate LEX for each assignment if in SP.

1 0

[Commits] 6cf39249150: MDEV-9266 Creating index on temporaray table breaks replication
by sachin 24 May '18

24 May '18

revision-id: 6cf39249150c4f1033f6acde443b5aeb7e20ceff (mariadb-10.0.35-1-g6cf39249150) parent(s): 42fac3241368ad72f8cfef2b8521269e6c173558 author: sachin committer: sachin timestamp: 2018-05-24 14:49:25 +0530 message: MDEV-9266 Creating index on temporaray table breaks replication Problem:- Create index was logged into binlog. Goal:- Operation on temporary table should not be binlog when binlog format is row. Solution:- 1st- We should add CF_FORCE_ORIGINAL_BINLOG_FORMAT when there is ddl on temp table. 2nd- For optimize, analyze and repair we dont check if binlog format is row and this is tmp table, we dont need to log that. --- .../suite/binlog/include/check_binlog_size.inc | 31 +++++++++++ .../suite/binlog/r/binlog_tmp_table_row.result | 41 +++++++++++++++ .../suite/binlog/t/binlog_tmp_table_row.test | 60 ++++++++++++++++++++++ sql/sql_table.cc | 5 ++ 4 files changed, 137 insertions(+) diff --git a/mysql-test/suite/binlog/include/check_binlog_size.inc b/mysql-test/suite/binlog/include/check_binlog_size.inc new file mode 100644 index 00000000000..9df161ec843 --- /dev/null +++ b/mysql-test/suite/binlog/include/check_binlog_size.inc @@ -0,0 +1,31 @@ +# This file runs the query and checks +# whether the size of binlog is increased or not +# If size is changed it issue die command +# Parameters +# $sql_query = query to run + +#Only last row of show binlog events matter +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $initial_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +--eval $sql_query + +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $current_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +if ($initial_binlog_size != $current_binlog_size) +{ + die "Binlog size changed"; +} diff --git a/mysql-test/suite/binlog/r/binlog_tmp_table_row.result b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result new file mode 100644 index 00000000000..3f2ef6d5e64 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result @@ -0,0 +1,41 @@ +RESET MASTER; +#Create table test +create temporary table t1(a int, b int); +#Add index test +create index index_a on t1(a); +#drop index test +drop index index_a on t1; +#Analyze test +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +#Optimize test +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +#Repair test +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +#Check test +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +#Checksum test +checksum table t1; +Table Checksum +test.t1 0 +#Rename test +rename table t1 to temp_t1; +analyze table non_existing; +Table Op Msg_type Msg_text +test.non_existing analyze Error Table 'test.non_existing' doesn't exist +test.non_existing analyze status Operation failed +optimize table non_existing; +Table Op Msg_type Msg_text +test.non_existing optimize Error Table 'test.non_existing' doesn't exist +test.non_existing optimize status Operation failed +repair table non_existing; +Table Op Msg_type Msg_text +test.non_existing repair Error Table 'test.non_existing' doesn't exist +test.non_existing repair status Operation failed diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table_row.test b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test new file mode 100644 index 00000000000..2f409b28012 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test @@ -0,0 +1,60 @@ +# ==== Purpose ==== +# +# Test if statements used temporary tables are not binlogged in the case of +# binlog_format=row +# +# ==== Method ==== +# +# We will see if binlog file size is increased or not, It should be constant for the +# entire period of test. +# +# ==== Related bugs ==== +# +# Mdev-9266 +# +source include/have_log_bin.inc; +source include/have_binlog_format_row.inc; + +RESET MASTER; + +--echo #Create table test +--let $sql_query= create temporary table t1(a int, b int) +--source suite/binlog/include/check_binlog_size.inc + +--echo #Add index test +--let $sql_query= create index index_a on t1(a) +--source suite/binlog/include/check_binlog_size.inc + +--echo #drop index test +--let $sql_query= drop index index_a on t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Analyze test +--let $sql_query= analyze table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Optimize test +--let $sql_query= optimize table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Repair test +--let $sql_query= repair table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Check test +--let $sql_query= check table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Checksum test +--let $sql_query= checksum table t1 +--source suite/binlog/include/check_binlog_size.inc + +--echo #Rename test +--let $sql_query= rename table t1 to temp_t1 +--source suite/binlog/include/check_binlog_size.inc + + +#should not crash the server +analyze table non_existing; +optimize table non_existing; +repair table non_existing; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 8f3468a44db..74cd15f0a49 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -1985,6 +1985,11 @@ int write_bin_log(THD *thd, bool clear_error, char const *query, ulong query_length, bool is_trans) { int error= 0; + TABLE_LIST *first_table= thd->lex->select_lex.table_list.first; + if (!(thd->is_current_stmt_binlog_format_row() && + first_table->table && + first_table->table->s->tmp_table != NO_TMP_TABLE )) + return 0; if (mysql_bin_log.is_open()) { int errcode= 0;

1 0

[Commits] 24c07973c89: fixed all failed tests
by Oleksandr Byelkin 24 May '18

24 May '18

revision-id: 24c07973c89244765dc964684c91ce30fc32ac2d (mariadb-10.3.6-7-g24c07973c89) parent(s): 549bcceefd94e8700c9e629bdd2c2f5a73d2459b author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-05-24 10:06:09 +0200 message: fixed all failed tests --- sql/sql_yacc.yy | 1 + sql/sql_yacc_ora.yy | 13 +------------ 2 files changed, 2 insertions(+), 12 deletions(-) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 491874b3b45..d856570c3de 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13619,6 +13619,7 @@ delete_part2: | HISTORY_SYM delete_single_table opt_delete_system_time { Lex->last_table()->vers_conditions= Lex->vers_conditions; + Lex->pop_select(); //main select } ; diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 069fa0ae615..9574bcd471c 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -3918,12 +3918,7 @@ sp_proc_stmt_return: ; reset_lex_expr: - { - Lex->sphead->reset_lex(thd); - // will be poped in sp_proc_stmt_exit or sp_proc_stmt_continue - if (Lex->main_select_push()) - MYSQL_YYABORT; - } + { Lex->sphead->reset_lex(thd); } expr { $$= $2; } ; @@ -3941,14 +3936,12 @@ sp_proc_stmt_exit: } | EXIT_SYM WHEN_SYM reset_lex_expr { - Lex->pop_select(); //main select pushed in reset_lex_expr if (Lex->sp_exit_statement(thd, $3) || Lex->sphead->restore_lex(thd)) MYSQL_YYABORT; } | EXIT_SYM label_ident WHEN_SYM reset_lex_expr { - Lex->pop_select(); //main select pushed in reset_lex_expr if (Lex->sp_exit_statement(thd, &$2, $4) || Lex->sphead->restore_lex(thd)) MYSQL_YYABORT; @@ -3968,14 +3961,12 @@ sp_proc_stmt_continue: } | CONTINUE_SYM WHEN_SYM reset_lex_expr { - Lex->pop_select(); //main select pushed in reset_lex_expr if (Lex->sp_continue_statement(thd, $3) || Lex->sphead->restore_lex(thd)) MYSQL_YYABORT; } | CONTINUE_SYM label_ident WHEN_SYM reset_lex_expr { - Lex->pop_select(); //main select pushed in reset_lex_expr if (Lex->sp_continue_statement(thd, &$2, $4) || Lex->sphead->restore_lex(thd)) MYSQL_YYABORT; @@ -13541,8 +13532,6 @@ truncate: { LEX* lex= Lex; lex->sql_command= SQLCOM_TRUNCATE; - if (lex->main_select_push()) - MYSQL_YYABORT; lex->alter_info.reset(); lex->builtin_select.options= 0; lex->builtin_select.sql_cache= SELECT_LEX::SQL_CACHE_UNSPECIFIED;

1 0

[Commits] 4203c19: Make rocksdb.issue255 test stable
by psergey＠askmonty.org 23 May '18

23 May '18

revision-id: 4203c19d5a51cde1d56061b11e99300a1ac10155 parent(s): 6fa7330636bee379161edc6dbd5162138cdd4318 committer: Sergei Petrunia branch nick: mysql-5.6-rocksdb-spetrunia timestamp: 2018-05-23 16:15:37 +0300 message: Make rocksdb.issue255 test stable Fix an obvious typo: replace_column should apply to SHOW TABLE STATUS, not to SELECT * FROM t1. --- mysql-test/suite/rocksdb/r/issue255.result | 2 +- mysql-test/suite/rocksdb/t/issue255.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/issue255.result b/mysql-test/suite/rocksdb/r/issue255.result index 797f339..9c6d9af 100644 --- a/mysql-test/suite/rocksdb/r/issue255.result +++ b/mysql-test/suite/rocksdb/r/issue255.result @@ -46,7 +46,7 @@ pk 127 SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ROCKSDB 10 Fixed 2 15 30 0 0 0 127 NULL NULL NULL latin1_swedish_ci NULL +t1 ROCKSDB # Fixed 2 # # # # # 127 NULL NULL NULL latin1_swedish_ci NULL INSERT INTO t1 VALUES (); ERROR 23000: Duplicate entry '127' for key 'PRIMARY' SELECT * FROM t1; diff --git a/mysql-test/suite/rocksdb/t/issue255.test b/mysql-test/suite/rocksdb/t/issue255.test index d75f1e3..588b28f 100644 --- a/mysql-test/suite/rocksdb/t/issue255.test +++ b/mysql-test/suite/rocksdb/t/issue255.test @@ -32,8 +32,8 @@ INSERT INTO t1 VALUES (5); SHOW TABLE STATUS LIKE 't1'; INSERT INTO t1 VALUES (1000); ---replace_column 3 # 6 # 7 # 8 # 9 # 10 # SELECT * FROM t1; +--replace_column 3 # 6 # 7 # 8 # 9 # 10 # SHOW TABLE STATUS LIKE 't1'; --error ER_DUP_ENTRY

1 0

[Commits] f01ae12: MDEV-16262: rocksdb.issue255 test sometimes fails in buildbot
by psergey＠askmonty.org 23 May '18

23 May '18

revision-id: f01ae125a3247289714fe433171b7bd7e65327bf parent(s): 31584c8bb8204b27626fae0127eff4889b8f4383 committer: Sergei Petrunia branch nick: 10.2-r11 timestamp: 2018-05-23 15:24:45 +0300 message: MDEV-16262: rocksdb.issue255 test sometimes fails in buildbot Fix an obvious typo: replace_column should be applied to SHOW TABLE STATUS, not to SELECT * FROM t1. --- storage/rocksdb/mysql-test/rocksdb/r/issue255.result | 2 +- storage/rocksdb/mysql-test/rocksdb/t/issue255.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/issue255.result b/storage/rocksdb/mysql-test/rocksdb/r/issue255.result index 797f339..9c6d9af 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/issue255.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/issue255.result @@ -46,7 +46,7 @@ pk 127 SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 ROCKSDB 10 Fixed 2 15 30 0 0 0 127 NULL NULL NULL latin1_swedish_ci NULL +t1 ROCKSDB # Fixed 2 # # # # # 127 NULL NULL NULL latin1_swedish_ci NULL INSERT INTO t1 VALUES (); ERROR 23000: Duplicate entry '127' for key 'PRIMARY' SELECT * FROM t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/issue255.test b/storage/rocksdb/mysql-test/rocksdb/t/issue255.test index d75f1e3..588b28f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/issue255.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/issue255.test @@ -32,8 +32,8 @@ INSERT INTO t1 VALUES (5); SHOW TABLE STATUS LIKE 't1'; INSERT INTO t1 VALUES (1000); ---replace_column 3 # 6 # 7 # 8 # 9 # 10 # SELECT * FROM t1; +--replace_column 3 # 6 # 7 # 8 # 9 # 10 # SHOW TABLE STATUS LIKE 't1'; --error ER_DUP_ENTRY

1 0

[Commits] 6a04c2a: MDEV-16235 Server crashes in my_utf8_uni or in my_strtod_int
by IgorBabaev 22 May '18

22 May '18

revision-id: 6a04c2a1aa60e67c07d4e6ada63f65cc3b26a5a9 (mariadb-5.5.60-13-g6a04c2a) parent(s): 27a7365f42ce0184a004e09b3bb1cadb868c8f64 author: Igor Babaev committer: Igor Babaev timestamp: 2018-05-22 12:09:05 -0700 message: MDEV-16235 Server crashes in my_utf8_uni or in my_strtod_int upon SELECT .. LIMIT 0 The code must differentiate between a SELECT with contradictory WHERE/HAVING and one with LIMIT 0. Also for the latter printed 'Zero limit' instead of 'Impossible where' in the EXPLAIN output. --- mysql-test/r/limit.result | 16 ++++++++++++++++ mysql-test/r/subselect4.result | 2 +- mysql-test/r/subselect_mat_cost_bugs.result | 2 +- mysql-test/t/limit.test | 14 ++++++++++++++ mysql-test/t/subselect4.test | 2 +- sql/sql_select.cc | 17 +++++++++++++---- 6 files changed, 46 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 176a93c..deb4ca2 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -146,3 +146,19 @@ a 16 DROP TABLE t1; End of 5.1 tests +# +# mdev-16235: SELECT over a table with LIMIT 0 +# +EXPLAIN +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +EXPLAIN +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +help_topic_id name help_category_id description example url +End of 5.5 tests diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 6accc23..8973330 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2512,7 +2512,7 @@ SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; # -# mfrv-14515: Wrong results for tableless query with subquery in WHERE +# mdev-14515: Wrong results for tableless query with subquery in WHERE # and implicit aggregation # create table t1 (i1 int, i2 int); diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index df6b543..d33f148 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -334,7 +334,7 @@ SELECT * FROM t1 WHERE (f1) IN (SELECT f1 FROM t2) LIMIT 0; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Zero limit 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (f1) IN (SELECT f1 FROM t2) diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 4dbe130..668d3b7 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -115,3 +115,17 @@ SELECT a FROM t1 ORDER BY a LIMIT 2 OFFSET 14; DROP TABLE t1; --echo End of 5.1 tests + +--echo # +--echo # mdev-16235: SELECT over a table with LIMIT 0 +--echo # + +EXPLAIN +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; + +EXPLAIN +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; + +--echo End of 5.5 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 2b53b55..2727fd9 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2047,7 +2047,7 @@ SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; --echo # ---echo # mfrv-14515: Wrong results for tableless query with subquery in WHERE +--echo # mdev-14515: Wrong results for tableless query with subquery in WHERE --echo # and implicit aggregation --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1e9f1c0..d6d269a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1136,10 +1136,19 @@ JOIN::optimize() if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) { /* Impossible cond */ - DBUG_PRINT("info", (having_value == Item::COND_FALSE ? - "Impossible HAVING" : "Impossible WHERE")); - zero_result_cause= having_value == Item::COND_FALSE ? - "Impossible HAVING" : "Impossible WHERE"; + if (unit->select_limit_cnt) + { + DBUG_PRINT("info", (having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE")); + zero_result_cause= having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE"; + } + else + { + DBUG_PRINT("info", ("Zero limit")); + zero_result_cause= "Zero limit"; + conds= 0; + } table_count= top_join_tab_count= 0; error= 0; goto setup_subq_exit;

1 0