- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] 3032cd8e91f: step #1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the
by Sergey Petrunia 26 Apr '19

26 Apr '19

Hi Varun, Please mention the MDEV# in the commit comment. Otherwise, the patch is ok to push. On Mon, Apr 22, 2019 at 07:09:45PM +0530, Varun wrote: > revision-id: 3032cd8e91f1e1ead8b6f941e75cd29e473e7eaa (mariadb-10.3.10-283-g3032cd8e91f) > parent(s): f4019f5b3544a18f3ddf32df2c5214c3f8dabdce > author: Varun Gupta > committer: Varun Gupta > timestamp: 2019-04-22 18:19:25 +0530 > message: > > step #1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the > upper select knows that ref access to the table will produce one row. > > --- > mysql-test/main/cte_nonrecursive.result | 8 ++-- > mysql-test/main/derived.result | 54 ++++++++++++++++++++++ > mysql-test/main/derived.test | 30 ++++++++++++ > mysql-test/main/derived_view.result | 2 +- > mysql-test/main/subselect_extra.result | 2 +- > mysql-test/main/subselect_extra_no_semijoin.result | 2 +- > sql/sql_lex.h | 1 + > sql/sql_union.cc | 40 ++++++++++++++++ > sql/table.cc | 20 ++++++++ > 9 files changed, 152 insertions(+), 7 deletions(-) > > diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result > index b846ec2d8ac..d80d34ecc7f 100644 > --- a/mysql-test/main/cte_nonrecursive.result > +++ b/mysql-test/main/cte_nonrecursive.result > @@ -244,7 +244,7 @@ with t as (select distinct a from t1 where b >= 'c') > select * from t as r1, t as r2 where r1.a=r2.a; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where > -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 > +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 > 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary > 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary > explain > @@ -253,7 +253,7 @@ select * from (select distinct a from t1 where b >= 'c') as r1, > where r1.a=r2.a; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where > -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 > +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 > 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary > 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary > # two references to t specified by a query > @@ -369,7 +369,7 @@ select c as a from t2 where c < 4) > select * from t2,t where t2.c=t.a; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 > 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where > 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -381,7 +381,7 @@ select c as a from t2 where c < 4) as t > where t2.c=t.a; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 > 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where > 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result > index f0d0289c1ce..857246d68b4 100644 > --- a/mysql-test/main/derived.result > +++ b/mysql-test/main/derived.result > @@ -1195,3 +1195,57 @@ drop table t1,t2,t3; > # > # End of 10.2 tests > # > +# > +# MDEV-9959: A serious MariaDB server performance bug > +# > +create table t1(a int); > +insert into t1 values (1),(2),(3),(4),(5),(6); > +create table t2(a int, b int,c int); > +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); > +create table t3(a int, b int); > +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); > +table "<derived2>" should have type=ref and rows=1 > +one select in derived table > +with distinct > +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +analyze select * from t1 , ((select distinct t2.a, t2.b from t2 order by c))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 1.00 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +# multiple selects in derived table > +# NO UNION ALL > +analyze select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 6.00 NULL NULL > +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > +a a > +1 1 > +2 2 > +3 3 > +4 4 > +5 5 > +6 6 > +# UNION ALL and EXCEPT > +analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 0.50 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +4 EXCEPT t3 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +NULL UNIT RESULT <unit2,3,4> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL > +select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; > +a a > +3 3 > +4 4 > +6 6 > +drop table t1,t2,t3; > diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test > index 6c51f23c51e..990f955450a 100644 > --- a/mysql-test/main/derived.test > +++ b/mysql-test/main/derived.test > @@ -1032,3 +1032,33 @@ drop table t1,t2,t3; > --echo # > --echo # End of 10.2 tests > --echo # > + > +--echo # > +--echo # MDEV-9959: A serious MariaDB server performance bug > +--echo # > + > +create table t1(a int); > +insert into t1 values (1),(2),(3),(4),(5),(6); > +create table t2(a int, b int,c int); > +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); > +create table t3(a int, b int); > +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); > + > +--echo table "<derived2>" should have type=ref and rows=1 > +--echo one select in derived table > + > +--echo with distinct > +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; > +analyze select * from t1 , ((select distinct t2.a, t2.b from t2 order by c))q where t1.a=q.a; > + > +--echo # multiple selects in derived table > +--echo # NO UNION ALL > +analyze select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > + > +--echo # UNION ALL and EXCEPT > +analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; > + > +select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; > + > +drop table t1,t2,t3; > diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result > index 86dd73f5733..30831e75341 100644 > --- a/mysql-test/main/derived_view.result > +++ b/mysql-test/main/derived_view.result > @@ -1525,7 +1525,7 @@ EXPLAIN > SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) > +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) > 3 DERIVED t2 ALL NULL NULL NULL NULL 6 > 4 UNION t3 ALL NULL NULL NULL NULL 4 > NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL > diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result > index a3a0f1f9a15..dbcf00268c2 100644 > --- a/mysql-test/main/subselect_extra.result > +++ b/mysql-test/main/subselect_extra.result > @@ -409,7 +409,7 @@ EXPLAIN > SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) > +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) > 3 DERIVED t2 ALL NULL NULL NULL NULL 6 > 4 UNION t3 ALL NULL NULL NULL NULL 4 > NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL > diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result > index ec9ddb0452e..49a1431eb9b 100644 > --- a/mysql-test/main/subselect_extra_no_semijoin.result > +++ b/mysql-test/main/subselect_extra_no_semijoin.result > @@ -411,7 +411,7 @@ EXPLAIN > SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 2 Using where > +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 1 Using where > 3 DERIVED t2 ALL NULL NULL NULL NULL 6 > 4 UNION t3 ALL NULL NULL NULL NULL 4 > NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 72ca4ac0b43..4eaec7d062b 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -896,6 +896,7 @@ class st_select_lex_unit: public st_select_lex_node { > bool union_needs_tmp_table(); > > void set_unique_exclude(); > + bool check_distinct_in_union(); > > friend struct LEX; > friend int subselect_union_engine::exec(); > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 87fbbebe4ba..3fb5552c77a 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -2049,3 +2049,43 @@ void st_select_lex_unit::set_unique_exclude() > } > } > } > + > +/** > + @brief > + Check if the derived table is guaranteed to have distinct rows because of > + UNION operations used to populate it. > + > + @detail > + UNION operation removes duplicate rows from its output. That is, a query like > + > + select * from t1 UNION select * from t2 > + > + will not produce duplicate rows in its output, even if table t1 (and/or t2) > + contain duplicate rows. EXCEPT and INTERSECT operations also have this > + property. > + > + On the other hand, UNION ALL operation doesn't remove duplicates. (The SQL > + standard also defines EXCEPT ALL and INTERSECT ALL, but we don't support > + them). > + > + st_select_lex_unit computes its value left to right. That is, if there is > + a st_select_lex_unit object describing > + > + (select #1) OP1 (select #2) OP2 (select #3) > + > + then ((select #1) OP1 (select #2)) is computed first, and OP2 is computed > + second. > + > + How can one tell if st_select_lex_unit is guaranteed to have distinct > + output rows? This depends on whether the last operation was duplicate- > + removing or not: > + - UNION ALL is not duplicate-removing > + - all other operations are duplicate-removing > +*/ > + > +bool st_select_lex_unit::check_distinct_in_union() > +{ > + if (union_distinct && !union_distinct->next_select()) > + return true; > + return false; > +} > diff --git a/sql/table.cc b/sql/table.cc > index 80995abc1f9..c4494c9ae4b 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -7269,6 +7269,26 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, > key_part_info++; > } > > + /* > + For the case when there is a derived table that would give distinct rows, > + the index statistics are passed to the join optimizer to tell that a ref > + access to all the fields of the derived table will produce only one row. > + */ > + > + st_select_lex_unit* derived= pos_in_table_list ? > + pos_in_table_list->derived: NULL; > + if (derived) > + { > + st_select_lex* first= derived->first_select(); > + uint select_list_items= first->get_item_list()->elements; > + if (key_parts == select_list_items) > + { > + if ((!first->is_part_of_union() && (first->options & SELECT_DISTINCT)) || > + derived->check_distinct_in_union()) > + keyinfo->rec_per_key[key_parts - 1]= 1; > + } > + } > + > set_if_bigger(s->max_key_length, keyinfo->key_length); > s->keys++; > return FALSE; > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Big endian issue in libmariadb: test: main.mysql_client_test fails
by REIX, Tony 26 Apr '19

26 Apr '19

Hi, We are building MariaDB on AIX. The test: main.mysql_client_test fails (after time-out). This is due to little endian code being used though HAVE_BIGENDIAN is correctly set. This is due to: libmariadb/libmariadb/ma_dtoa.c : #if defined(WORDS_BIGENDIAN) || (defined(__FLOAT_WORD_ORDER) && \ (__FLOAT_WORD_ORDER == __BIG_ENDIAN)) #define word0(x) (x)->L[0] #define word1(x) (x)->L[1] #else #define word0(x) (x)->L[1] #define word1(x) (x)->L[0] #endif which results in the #else part (Little Endian) since none of the tested macros are defined. File libmariadb/libmariadb/ma_dtoa.c includes ma_global.h which has: #define HAVE_BIGENDIAN 1 but nothing defined about WORDS_BIGENDIAN . However, probably that some changes were made in the past in ma_global.h, since I see: #ifdef HAVE_BIGENDIAN #endif /* WORDS_BIGENDIAN */ 2 times in this file. I do not master MariaDB, so I do not have a clear idea about how to fix this. I see 3 solutions: a) libmariadb/CMakeLists.txt : Add: SET(HAVE_WORDS_BIGENDIAN TRUE ...) after : TEST_BIG_ENDIAN(HAVE_BIGENDIAN) if it is BigEndian. b) libmariadb/include/ma_global.h : Add: #ifdef HAVE_BIGENDIAN #define WORDS_BIGENDIAN 1 #endif c) libmariadb/libmariadb/ma_dtoa.c : Change: #if defined(WORDS_BIGENDIAN) || (defined(__FLOAT_WORD_ORDER) && \ (__FLOAT_WORD_ORDER == __BIG_ENDIAN)) by: #if defined(HAVE_BIGENDIAN) || defined(WORDS_BIGENDIAN) || (defined(__FLOAT_WORD_ORDER) && \ (__FLOAT_WORD_ORDER == __BIG_ENDIAN)) Please let me know which seems appropriate. Regards, Tony ATOS / Bull SAS ATOS Expert IBM Coop Architect & Technical Leader Office : +33 (0) 4 76 29 72 67 1 rue de Provence - 38432 Échirolles - France www.atos.net<https://mail.ad.bull.net/owa/redir.aspx?C=PvphmPvCZkGrAgHVnWGsdMcDKgzl_dEIs…>

1 0

Re: [Maria-developers] [Commits] 647c22692ad: MDEV-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave
by andrei.elkin＠pp.inet.fi 25 Apr '19

25 Apr '19

Sachin, howdy. > revision-id: 647c22692ad1fe466eb14b749520cc520807d4fa (mariadb-10.1.38-114-g647c22692ad) > parent(s): 6c5e4c9bc0d9ac30f7ec7ee334630bacb58687ba > author: Sachin > committer: Sachin > timestamp: 2019-04-23 17:58:03 +0530 > message: > > MDEV-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave > > Prototype Commit. In next commit could you please brief about the problem and attempted solution. > > Delay throwing of error in parsing stage untill we know the result from rpl > filter. I think you were on the right track implementing this deferred error/warning issuing. Thanks for attempting it! There's a new THD::"flag" for that purpose. I suggest we place it inside struct LEX_MASTER_INFO to set pretty similarly to how the patch does. Secondly, let blocks in sql_yacc.yy that call error/warning functions that gets deferred (because of the slave thread) be invoked (if they will be) in mysql_execute_command() in verbatim. That means to turn the blocks into functions and call them at once or later. Your patch presents two such blocks, I point to them below. For each block I suggest we introduce a flag. The block flag is set when the block gets visited (but not executed 'cos of slave). The "flag" therefore is going to be an array (or enum?). > > --- > mysql-test/suite/rpl/r/rpl_mdev_17588.result | 26 ++++++++++++++++++++++ > mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt | 1 + > mysql-test/suite/rpl/t/rpl_mdev_17588.test | 29 +++++++++++++++++++++++++ > sql/sql_class.h | 1 + > sql/sql_parse.cc | 7 ++++++ > sql/sql_yacc.yy | 12 +++++++--- > 6 files changed, 73 insertions(+), 3 deletions(-) > > diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17588.result b/mysql-test/suite/rpl/r/rpl_mdev_17588.result > new file mode 100644 > index 00000000000..976efde2bdd > --- /dev/null > +++ b/mysql-test/suite/rpl/r/rpl_mdev_17588.result > @@ -0,0 +1,26 @@ > +include/master-slave.inc > +[connection master] > +set sql_log_bin= 0; > +install soname 'ha_tokudb'; > +set sql_log_bin= 1; > +create table t1 (a int) engine=TokuDB; > +create table t2 (a int); > +create table t3 (a int) engine=TokuDB; > +include/wait_for_slave_sql_error.inc [errno=1286] > +show create table t1; > +ERROR 42S02: Table 'test.t1' doesn't exist > +show create table t2; > +Table Create Table > +t2 CREATE TABLE `t2` ( > + `a` int(11) DEFAULT NULL > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show create table t3; > +ERROR 42S02: Table 'test.t3' doesn't exist > +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; > +START SLAVE; > +drop table t1, t2, t3; > +set sql_log_bin= 0; > +uninstall soname 'ha_tokudb'; > +set sql_log_bin= 1; > +CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286'); > +include/rpl_end.inc > diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt > new file mode 100644 > index 00000000000..19497afd22a > --- /dev/null > +++ b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt > @@ -0,0 +1 @@ > +--replicate-do-table=test.t2 --replicate-do-table=test.t3 --sql-mode='NO_ENGINE_SUBSTITUTION' > diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588.test b/mysql-test/suite/rpl/t/rpl_mdev_17588.test > new file mode 100644 > index 00000000000..c636077e4d1 > --- /dev/null > +++ b/mysql-test/suite/rpl/t/rpl_mdev_17588.test > @@ -0,0 +1,29 @@ > +--source include/master-slave.inc > + > +--connection master > +set sql_log_bin= 0; > +install soname 'ha_tokudb'; > +set sql_log_bin= 1; > + > +create table t1 (a int) engine=TokuDB; > +create table t2 (a int); > +create table t3 (a int) engine=TokuDB; > + > +--connection slave > +let $slave_sql_errno= 1286; > +source include/wait_for_slave_sql_error.inc; > +--error ER_NO_SUCH_TABLE > +show create table t1; > +show create table t2; > +--error ER_NO_SUCH_TABLE > +show create table t3; > +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; > +START SLAVE; > +--connection master > +drop table t1, t2, t3; > +set sql_log_bin= 0; > +uninstall soname 'ha_tokudb'; > +set sql_log_bin= 1; > +--sync_slave_with_master > +CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286'); > +--source include/rpl_end.inc > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 6640e02147a..4361700ee6c 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -2802,6 +2802,7 @@ class THD :public Statement, > uint8 password; /* 0, 1 or 2 */ > uint8 failed_com_change_user; > bool slave_thread; > + char *rpl_unavailable_storage_plugin= NULL; > bool extra_port; /* If extra connection */ > > bool no_errors; > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 6649c60f827..5f8c833c76f 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2656,6 +2656,13 @@ mysql_execute_command(THD *thd) > /* we warn the slave SQL thread */ > my_message(ER_SLAVE_IGNORED_TABLE, ER_THD(thd, ER_SLAVE_IGNORED_TABLE), > MYF(0)); > + thd->rpl_unavailable_storage_plugin= NULL; > + DBUG_RETURN(0); > + } > + if (thd->rpl_unavailable_storage_plugin) > + { > + my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(0), thd->rpl_unavailable_storage_plugin); > + thd->rpl_unavailable_storage_plugin= NULL; Here the check would go for each thd->lex.mi.flag[i] when set call "flag_func[i]"; I index flag[] but I don't insist on implementing it like that. Here is the function's body { if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); $$= 0; push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_UNKNOWN_STORAGE_ENGINE, ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE), $1.str); } and a flag function also might need some parameters like $1.str in above. > DBUG_RETURN(0); > } > /* > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 5111f0690ab..0756028c34a 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -2476,7 +2476,7 @@ create: > LEX *lex= thd->lex; > lex->current_select= &lex->select_lex; > if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) && > - !lex->create_info.db_type) > + !lex->create_info.db_type && !thd->rpl_unavailable_storage_plugin) > { > lex->create_info.use_default_db_type(thd); This is the 2nd block which depends on the 1st one. if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) && !lex->create_info.db_type) { lex->create_info.use_default_db_type(thd); if (!thd->slave_thread || !lex->mi.flag[0]) print_warn_using_other_handler(...) } where print_warn_using_other_handler() is { push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_WARN_USING_OTHER_HANDLER, ER_THD(thd, ER_WARN_USING_OTHER_HANDLER), hton_name(lex->create_info.db_type)->str, $5->table.str); } To call it deferredly we seem need two args to memorize (reserve fields too) in Lex->mi. I hope my suggestions make sense and feasible. Cheers, Andrei > push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > @@ -5791,9 +5791,15 @@ storage_engines: > else > { > if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) > - my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); > + { > + if (!thd->slave_thread) > + my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); > + else > + thd->rpl_unavailable_storage_plugin= $1.str; > + } > $$= 0; > - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > + if (!thd->rpl_unavailable_storage_plugin) > + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > ER_UNKNOWN_STORAGE_ENGINE, > ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE), > $1.str); > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] 8ae7a5eb037: MDEV-17260: Memory leaks in mysqlbinlog
by andrei.elkin＠pp.inet.fi 24 Apr '19

24 Apr '19

Sujatha, hello. The patch looks good, I only have a suggestion to the test for better readability, within the quoted patch below. Thanks for a good piece of work! Cheers, Andrei >commit 8ae7a5eb03725441393c353e4e203f80b666c035 >Author: Sujatha Sivakumar <sujatha.sivakumar(a)mariadb.com> >Date: Tue Apr 23 19:47:59 2019 +0530 > > MDEV-17260: Memory leaks in mysqlbinlog > > Problem: > ======== > The mysqlbinlog tool is leaking memory, causing failures in various tests when > compiling and testing with AddressSanitizer or LeakSanitizer like this: > > cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source > make -j$(nproc) > cd mysql-test > ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto > > Analysis: > ========= > Two types of leaks were observed during above execution. > > 1) Leak in Log_event::read_log_event(char const*, unsigned int, char const**, > Format_description_log_event const*, char) > File: sql/log_event.cc:2150 > > For all row based replication events the memory which is allocated during > read_log_event is not freed after the event is processed. The event specific > memory has to be retained only when flashback option is enabled with > mysqlbinlog tool. In this case all the events are retained till the end > statement is received and they are processed in reverse order and they are > destroyed. But in the existing code all events are retained irrespective of > flashback mode. Hence the memory leaks are observed. > > 2) read_remote_annotate_event(unsigned char*, unsigned long, char const**) > File: client/mysqlbinlog.cc:194 > > In general the Annotate event is not printed immediately because all > subsequent rbr-events can be filtered away. Instead it will be printed > together with the first not filtered away Table map or the last rbr will be > processed. While reading remote annotate events memory is allocated for event > buffer and event's temp_buf is made to point to the allocated buffer as shown > below. The TRUE flag is used for doing proper cleanup using free_temp_buf(). > i.e at the time of deletion of annotate event its destructor takes care of > clearing the temp_buf. > > /* > Ensure the event->temp_buf is pointing to the allocated buffer. > (TRUE = free temp_buf on the event deletion) > */ > event->register_temp_buf((char*)event_buf, TRUE); > > But existing code does the following when it receives a remote annotate_event. > > if (remote_opt) > ev->temp_buf= 0; > > That is code immediately sets temp_buf=0, because of which free_temp_buf() > call will return empty handed as it has lost the reference to the allocated > temporary buffer. This results in memory leak > > Fix: > ==== > 1) If not in flashback mode, destroy the memory for events once they are > processed. > > 2) Remove the ev->temp_buf=0 code for remote option. Let the proper cleanup to > be done as part of free_temp_buf(). > >diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc >index cfc05cbf794..3dad4fef67b 100644 >--- a/client/mysqlbinlog.cc >+++ b/client/mysqlbinlog.cc >@@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info) > if (annotate_event) > { > annotate_event->print(result_file, print_event_info); >- delete annotate_event; // the event should not be printed more than once >- annotate_event= 0; >+ free_annotate_event(); > } > } > >@@ -1465,7 +1464,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, > if (print_row_event(print_event_info, ev, e->get_table_id(), > e->get_flags(Rows_log_event::STMT_END_F))) > goto err; >- if (!is_stmt_end) >+ if (opt_flashback && !is_stmt_end) > destroy_evt= FALSE; > break; > } >@@ -1478,7 +1477,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, > if (print_row_event(print_event_info, ev, e->get_table_id(), > e->get_flags(Old_rows_log_event::STMT_END_F))) > goto err; >- if (!is_stmt_end) >+ if (opt_flashback && !is_stmt_end) > destroy_evt= FALSE; > break; > } >@@ -1539,8 +1538,6 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, > } > } > >- if (remote_opt) >- ev->temp_buf= 0; > if (destroy_evt) /* destroy it later if not set (ignored table map) */ > delete ev; > } >diff --git a/mysql-test/suite/binlog/r/flashback.result b/mysql-test/suite/binlog/r/flashback.result >index c96eaebe838..047c032b5df 100644 >--- a/mysql-test/suite/binlog/r/flashback.result >+++ b/mysql-test/suite/binlog/r/flashback.result >@@ -674,6 +674,28 @@ world.city 563256876 > DROP TABLE test.test; > DROP TABLE world.city; > DROP DATABASE world; >+# < CASE 7 > >+# Test Case for MDEV-17260 >+# >+RESET MASTER; >+CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; >+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); >+# 6- Rows must be present >+SELECT COUNT(*) FROM t1; >+COUNT(*) >+6 >+FLUSH LOGS; >+DELETE FROM t1; >+FLUSH LOGS; >+# 0- Rows must be present >+SELECT COUNT(*) FROM t1; >+COUNT(*) >+0 >+# 6- Rows must be present upon restoring from flashback >+SELECT COUNT(*) FROM t1; >+COUNT(*) >+6 >+DROP TABLE t1; > SET binlog_format=statement; > Warnings: > Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT >diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test >index 3fc8c44c60c..fb80669ca51 100644 >--- a/mysql-test/suite/binlog/t/flashback.test >+++ b/mysql-test/suite/binlog/t/flashback.test >@@ -335,8 +335,29 @@ DROP TABLE test.test; > DROP TABLE world.city; > DROP DATABASE world; > >-## Clear >+--echo # < CASE 7 > >+--echo # Test Case for MDEV-17260 >+--echo # >+RESET MASTER; >+ >+CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; >+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); >+--echo # 6- Rows must be present >+SELECT COUNT(*) FROM t1; SELECT COUNT(*) as six FROM t1; would read better though it's far from being the optimal automation. (The optimal one clearly must also count 'six' or whatever number itself, as well as bail out with an error when count(*) won't match the expected # - sure not for the current patch). >+FLUSH LOGS; >+DELETE FROM t1; >+FLUSH LOGS; >+--echo # 0- Rows must be present >+SELECT COUNT(*) FROM t1; >+--exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql >+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" >+ >+--echo # 6- Rows must be present upon restoring from flashback >+SELECT COUNT(*) FROM t1; > >+DROP TABLE t1; >+ >+## Clear > SET binlog_format=statement; > --error ER_FLASHBACK_NOT_SUPPORTED > SET GLOBAL binlog_format=statement; >

1 0

Re: [Maria-developers] 70c5ab5658d: Move XID_STATE::rm_error to XID_cache_element
by Sergei Golubchik 24 Apr '19

24 Apr '19

Hi, Sergey! On Apr 24, Sergey Vojtovich wrote: > revision-id: 70c5ab5658d (mariadb-10.4.4-29-g70c5ab5658d) > parent(s): c5b1799435b > author: Sergey Vojtovich <svoj(a)mariadb.org> > committer: Sergey Vojtovich <svoj(a)mariadb.org> > timestamp: 2019-04-20 14:45:16 +0400 > message: > > Move XID_STATE::rm_error to XID_cache_element It's somewhat confusing. What if it's an internal 2PC and some engine's rollback returns an error? The error won't be stored in xid_state.rm_error anymore. Basically, you're changing the semantics of rm_error from "an error that happened during rollback" to "an error that happened during rollback or an external XA transaction". Mention this change explicitly in the commit comment, please. > Also removed redundant xid reset from THD::init_for_queries(). Must've > been done already either by THD::transaction constructor or by > THD::cleanup(). > > Part of MDEV-7974 - backport fix for mysql bug#12161 (XA and binlog) > > diff --git a/sql/sql_class.h b/sql/sql_class.h > index c68628be65d..e7584a0d825 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -2606,13 +2606,8 @@ class THD: public THD_count, /* this must be first */ > DBUG_ENTER("thd::cleanup"); rename to "THD::st_transaction::cleanup" please > changed_tables= 0; > savepoints= 0; > - /* > - If rm_error is raised, it means that this piece of a distributed > - transaction has failed and must be rolled back. But the user must > - rollback it explicitly, so don't start a new distributed XA until > - then. > - */ > - if (!xid_state.rm_error) > + /* xid_cache_delete() resets xid of explicitly started XA transaction */ > + if (!xid_state.is_explicit_XA()) > xid_state.xid.null(); > free_root(&mem_root,MYF(MY_KEEP_PREALLOC)); > DBUG_VOID_RETURN; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] C API prepared statements parameters for IN (x, x, x, ...)
by Simon Walter 24 Apr '19

24 Apr '19

I want to add support for this or sponsor it - if it is really true that this is not possible with the current c API. I have been told to do: SELECT x FROM x WHERE id IN (?, ?); Where the content of the parenthesis are not dynamic. Rather than going at it brute force on my own, which is my tendency, I want to hear what other developers think and if someone, perhaps a core dev, wants to, I would like to sponsor it. Sorry for the short email. It's late here in Japan. I have been thinking about this for months. Until now I have been using all sorts of work-arounds, but I think that dealing with the core issue will be a better use of my time/resources. More experienced developers may state reasons why this is not possible. Though as you may be able to tell, I can see how it could work. Of course I may be not seeing how everyone else is doing it. From what I can tell (lots code grepping, asking on mailing lists), noone in doing it. In my opinion, that is a shame. Because an, AFAICT, an IN clause means one disk read. The application I am working on make extensive use of IN clauses for reading many records in one call. Some may call this an micro-optimization. If that is really so, please show me that thousands of individual calls to a prepared statement like: SELECT x FROM x WHERE id = x; is as efficient as one call to: SELECT x FROM x WHERE id IN (x, x, x, ...); Background: I am using APR Util and have asked a few people about this and it boils down to the underlying drivers not supporting it. I would ultimately like to code a solution for pgsql as well. Mariadb is where I would like to start however. Then add support for it in APR. Suggesting I use sprintf to create dynamic queries means that I need to write my own sprintf, because as you know, va_arg etc. An array is actually dynamic - not va_arg - reading from file etc. Please feel free to tell me I am stupid, suggest efficient alternatives, tell me that it is actually supported (point me to the docs), etc. SSDs and such are great. Though, I still think it is a shame to be that lazy. No, I do not think the world will overheat because of our lazy programming. I have to throw that in there with the current political climate. No pun intended. ;) Thanks, Simon PS I do not work for any stock trading companies. The project I am working on is opensource. I just want to engineer something very nice.

1 1

[Maria-developers] mysql-proxy to maxscale migration
by Witold Filipczyk 24 Apr '19

24 Apr '19

Hi, I have simple lua script: cdb = require("cdb") function read_auth() local db = cdb.open("/etc/mysql/proxy/data.cdb") local value = db:get(proxy.connection.client.username) db:close() if value == nil then proxy.response.type = proxy.MYSQLD_PACKET_ERR proxy.response.errmsg = "Access denied" return proxy.PROXY_SEND_RESULT end end Format of data: username1 1 username2 1 and so on. Unfortunately after upgrade from mariadb-10.1 to mariadb-10.3 on buster mysql-proxy is defunct. Cannot get "mysql prompt". I tried to replace mysql-proxy by maxscale-2.3.6, but without success. Failed to load users from server 'server1': Lost connection to MySQL server during query Aborted connection 8578832 to db: 'mysql' user: 'debian-sys-maint' host: 'localhost' (Got an error writing communication packets) Note that there is a few hundreds thousands users in mysql.user, but in data.cdb only a few hundreds. Could you point me to the right place, where to find info how to fix mysql-proxy, or configure maxscale, or some alternative to both? Witold Filipczyk

2 1

[Maria-developers] Please review a patch for MDEV-19284 and MDEV-19285 (instant ALTER)
by Alexander Barkov 23 Apr '19

23 Apr '19

Hi Marko and Eugene, please review a patch fixing: - MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data - MDEV-19285 INSTANT ALTER from ascii_general_ci to latin1_general_ci produces currupt data Thanks!

3 8

[Maria-developers] SELECT INTO OUTFILE + LOAD DATA for a mixture of character and binary data
by Alexander Barkov 22 Apr '19

22 Apr '19

Hi Kenneth, I just closed this issue as "not a bug": MDEV-13361 Regression: LOAD DATA INFILE utf8 enconding issues Can you please document that when doing "SELECT INTO OUTFILE" followed by "LOAD DATA INFILE" for a mixture of text and binary data, one should use "CHARACTER SET binary" in both statements. This is needed to avoid escape sequences to be interpreted in a wrong way at "LOAD DATA" type. Thanks.

1 0

Re: [Maria-developers] [Commits] 167a96b5157: MDEV-9959: A serious MariaDB server performance bug
by Sergey Petrunia 20 Apr '19

20 Apr '19

On Tue, Mar 27, 2018 at 04:48:10PM +0530, Varun wrote: > revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5 (mariadb-10.3.0-644-g167a96b5157) > parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-03-27 16:45:46 +0530 > message: > > MDEV-9959: A serious MariaDB server performance bug > > step#1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the > upper select knows that ref access to the table will produce one row. > > Added handling for multiple selects in the derived table > > --- > mysql-test/r/cte_nonrecursive.result | 8 +-- > mysql-test/r/cte_recursive.result | 6 +- > mysql-test/r/derived_cond_pushdown.result | 82 ++++++++++++------------- > mysql-test/r/derived_view.result | 2 +- > mysql-test/r/join_cache.result | 6 +- > mysql-test/r/mdev9959.result | 46 ++++++++++++++ > mysql-test/r/subselect_extra.result | 2 +- > mysql-test/r/subselect_extra_no_semijoin.result | 2 +- > mysql-test/t/mdev9959.test | 25 ++++++++ > sql/sql_lex.h | 2 + > sql/sql_union.cc | 60 ++++++++++++++++++ > sql/sql_yacc.yy | 3 + > sql/table.cc | 19 ++++++ > 13 files changed, 209 insertions(+), 54 deletions(-) > > diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result > index 32d3c88cc8d..3723e25a494 100644 > --- a/mysql-test/r/derived_cond_pushdown.result > +++ b/mysql-test/r/derived_cond_pushdown.result > @@ -5187,7 +5187,7 @@ explain select * from v2_union as v,t2 where > ((v.a=6) or (v.a=8)) and (v.c>200) and (v.a=t2.a); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 6 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where As agreed on the call: need to check what is the cause of this. Here, the temp table has distinct rows, that is, {a,b,c} are distinct. But ref access only uses the first component, where does rows=1 come from? (if this is how best_access_path computes an estimate for prefix when it only has the estimate for the full key ... fine) > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > 4 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > @@ -5213,7 +5213,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 6, > + "rows": 1, > "filtered": 100, > "attached_condition": "v.c > 200", > "materialized": { > @@ -5358,7 +5358,7 @@ a b c a b c d > explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -5382,7 +5382,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 4, > + "rows": 1, > "filtered": 100, > "attached_condition": "v.c > 6", > "materialized": { > @@ -5476,7 +5476,7 @@ a b c a b c d > explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -5500,7 +5500,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 4, > + "rows": 1, > "filtered": 100, > "attached_condition": "t2.a > 1 or v.b < 20", > "materialized": { > @@ -5561,7 +5561,7 @@ explain select * from v3_union as v,t2 where > (v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -5586,7 +5586,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 4, > + "rows": 1, > "filtered": 100, > "attached_condition": "(v.b = 19 or v.b = 21) and (v.c < 3 or v.c > 600)", > "materialized": { > @@ -5645,7 +5645,7 @@ a b c a b c d > explain select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -5669,7 +5669,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 4, > + "rows": 1, > "filtered": 100, > "attached_condition": "v.b < 20", > "materialized": { > @@ -5752,7 +5752,7 @@ explain select * from v4_union as v,t2 where > (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 4 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -5777,7 +5777,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 4, > + "rows": 1, > "filtered": 100, > "attached_condition": "(t2.a < 3 or v.b < 40) and v.c > 500", > "materialized": { > @@ -7952,7 +7952,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -7976,7 +7976,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "materialized": { > "query_block": { > @@ -8226,7 +8226,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.c>500); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -8250,7 +8250,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c > 500", > "materialized": { > @@ -8317,7 +8317,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -8341,7 +8341,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c > 500", > "materialized": { > @@ -8411,7 +8411,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -8435,7 +8435,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.b > 27 or v1.b < 19", > "materialized": { > @@ -8508,7 +8508,7 @@ explain select * from v1,t2 where > (v1.a=t2.a) and ((v1.c<400) or (v1.c>800)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -8533,7 +8533,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c < 400 or v1.c > 800", > "materialized": { > @@ -8762,7 +8762,7 @@ where > ((d1.a>4) and (d1.c>500))); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -8796,7 +8796,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["b"], > "ref": ["test.t2.b"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "t2.c = 988 and t2.b > 13 or d1.a > 4 and d1.c > 500", > "materialized": { > @@ -8872,7 +8872,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 UNION <derived4> ALL NULL NULL NULL NULL 18 Using where > 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > @@ -8899,7 +8899,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 3, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c > 200", > "materialized": { > @@ -9138,7 +9138,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 4 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > @@ -9163,7 +9163,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 3, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c > 200", > "materialized": { > @@ -9260,7 +9260,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 4 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > @@ -9285,7 +9285,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 3, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c < 200", > "materialized": { > @@ -9498,7 +9498,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where > 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > @@ -9525,7 +9525,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c < 130", > "materialized": { > @@ -9643,7 +9643,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 3 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where > 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > @@ -9671,7 +9671,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 3, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c < 130", > "materialized": { > @@ -10143,7 +10143,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -10167,7 +10167,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.b < 30 and v1.c > 450", > "materialized": { > @@ -10243,7 +10243,7 @@ a b c a b c > explain select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where > 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort > NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL > @@ -10267,7 +10267,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["a"], > "ref": ["test.t2.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.c > 450", > "materialized": { > @@ -10636,7 +10636,7 @@ a b c a b c > explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<3); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where > 2 DERIVED t3 range i1 i1 5 NULL 1 Using index condition > 3 UNION t3 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > @@ -10660,7 +10660,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["b"], > "ref": ["test.t2.b"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "attached_condition": "v1.a < 3", > "materialized": { > @@ -12333,7 +12333,7 @@ EXPLAIN > "key_length": "5", > "used_key_parts": ["b"], > "ref": ["test.t1.a"], > - "rows": 2, > + "rows": 1, > "filtered": 100, > "materialized": { > "query_block": { > diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result > index 85e56ff176e..3a0fde7b053 100644 > --- a/mysql-test/r/derived_view.result > +++ b/mysql-test/r/derived_view.result > @@ -1525,7 +1525,7 @@ EXPLAIN > SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) > +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) Interesting.. here, we potentially could infer that this semi-join can be converted to an inner join (as the subquery may only have one match). But I think this is outside of the scope of this MDEV. > 3 DERIVED t2 ALL NULL NULL NULL NULL 6 > 4 UNION t3 ALL NULL NULL NULL NULL 4 > NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL > diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result > index eea397402ad..cc185b640bc 100644 > --- a/mysql-test/r/join_cache.result > +++ b/mysql-test/r/join_cache.result > @@ -5197,7 +5197,7 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t > WHERE t.a IN (SELECT t2.a FROM t2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary > -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 End temporary > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary > 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary > SELECT * FROM (SELECT DISTINCT * FROM t1) t > WHERE t.a IN (SELECT t2.a FROM t2); > @@ -5208,8 +5208,8 @@ EXPLAIN > SELECT * FROM (SELECT DISTINCT * FROM t1) t > WHERE t.a IN (SELECT t2.a FROM t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 > -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary > +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary > 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary > SELECT * FROM (SELECT DISTINCT * FROM t1) t > WHERE t.a IN (SELECT t2.a FROM t2); > diff --git a/mysql-test/r/mdev9959.result b/mysql-test/r/mdev9959.result > new file mode 100644 > index 00000000000..049e0350cca > --- /dev/null > +++ b/mysql-test/r/mdev9959.result > @@ -0,0 +1,46 @@ > +create table t1(a int); > +insert into t1 values (1),(2),(3),(4),(5),(6); > +create table t2(a int, b int,c int); > +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); > +create table t3(a int, b int); > +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); > +table "<derived2>" should have type=ref and rows=1 > +one select in derived table > +with distinct > +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +# multiple selects in derived table > +# NO UNION ALL > +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select t2.a,t2.b from t2 order by c))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 6.00 NULL NULL > +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > +a a > +1 1 > +2 2 > +3 3 > +4 4 > +5 5 > +6 6 > +# UNION ALL and EXCEPT > +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; > +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra > +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where > +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 0.50 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +4 EXCEPT t3 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 > +NULL UNIT RESULT <unit2,3,4> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL > +select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; > +a a b > +3 3 1 > +4 4 2 > +6 6 2 > +drop table t1,t2,t3; > diff --git a/mysql-test/t/mdev9959.test b/mysql-test/t/mdev9959.test > new file mode 100644 > index 00000000000..36f364261c9 > --- /dev/null > +++ b/mysql-test/t/mdev9959.test > @@ -0,0 +1,25 @@ > +create table t1(a int); > +insert into t1 values (1),(2),(3),(4),(5),(6); > +create table t2(a int, b int,c int); > +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); > +create table t3(a int, b int); > +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); > + > +--echo table "<derived2>" should have type=ref and rows=1 > +--echo one select in derived table > + > +--echo with distinct > +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; > + > +--echo # multiple selects in derived table > +--echo # NO UNION ALL > +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select t2.a,t2.b from t2 order by c))q where t1.a=q.a; > +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; > + > +--echo # UNION ALL and EXCEPT > +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; > + > +select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; > + > +drop table t1,t2,t3; > + > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index f0241a32acf..16d5e55d251 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -761,6 +761,7 @@ class st_select_lex_unit: public st_select_lex_node { > Procedure *last_procedure; /* Pointer to procedure, if such exists */ > > bool columns_are_renamed; > + bool union_all; /* TRUE if there is a UNION ALL operation */ This is not initialized properly. The testcase: create table t10 (a int , b int); insert into t10 values (1,1),(1,1); create table t11 (a int , b int); create table t12 (a int , b int); insert into t11 values (2,2),(2,2); create table t13 (a int , b int); insert into t12 values (3,3),(3,3); create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); explain select * from ten A, (select * from t10 union select * from t11 union select * from t12) T where T.a=A.a; +------+--------------+--------------+------+---------------+------+---------+----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+--------------+------+---------------+------+---------+----------+------+-------------+ | 1 | PRIMARY | A | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.A.a | 1 | | | 2 | DERIVED | t10 | ALL | NULL | NULL | NULL | NULL | 2 | | | 3 | UNION | t11 | ALL | NULL | NULL | NULL | NULL | 2 | | | 4 | UNION | t12 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | UNION RESULT | <union2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+--------------+------+---------------+------+---------+----------+------+-------------+ (gdb) wher #0 st_select_lex_unit::check_distinct_in_union (this=0x7fff700149e0) at /home/psergey/dev-git/10.3-cp/sql/sql_union.cc:2100 #1 0x0000555555d2a693 in TABLE::add_tmp_key (this=0x7fff70062908, key=0, key_parts=1, next_field_no=0x555555c6bf47 <get_next_field_for_derived_key(uchar*)>, arg=0x7fffd98e9230 "X\207\006p\377\177", unique=false) at /home/psergey/dev-git/10.3-cp/sql/table.cc:7270 #2 0x0000555555c6c214 in generate_derived_keys_for_table (keyuse=0x7fff700687b0, count=1, keys=1) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:11091 #3 0x0000555555c6c42a in generate_derived_keys (keyuse_array=0x7fff70018968) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:11166 #4 0x0000555555c60267 in sort_and_filter_keyuse (thd=0x7fff70000d60, keyuse=0x7fff70018968, skip_unprefixed_keyparts=true) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:6403 #5 0x0000555555c5a910 in make_join_statistics (join=0x7fff70018678, tables_list=..., keyuse_array=0x7fff70018968) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:4622 #6 0x0000555555c513d4 in JOIN::optimize_inner (this=0x7fff70018678) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:1888 #7 0x0000555555c4fa73 in JOIN::optimize (this=0x7fff70018678) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:1451 #8 0x0000555555c59638 in mysql_select (thd=0x7fff70000d60, tables=0x7fff70013f68, wild_num=1, fields=..., conds=0x7fff70017d98, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff70017ff0, unit=0x7fff70004c28, select_lex=0x7fff70005398) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:4225 #9 0x0000555555c91d6f in mysql_explain_union (thd=0x7fff70000d60, unit=0x7fff70004c28, result=0x7fff70017ff0) at /home/psergey/dev-git/10.3-cp/sql/sql_select.cc:25866 #10 0x0000555555c15da6 in execute_sqlcom_select (thd=0x7fff70000d60, all_tables=0x7fff70013f68) at /home/psergey/dev-git/10.3-cp/sql/sql_parse.cc:6487 #11 0x0000555555c0c7a5 in mysql_execute_command (thd=0x7fff70000d60) at /home/psergey/dev-git/10.3-cp/sql/sql_parse.cc:3821 #12 0x0000555555c1a0fd in mysql_parse (thd=0x7fff70000d60, rawbuf=0x7fff70013ca8 "explain select * from ten A, (select * from t10 union select * from t11 union select * from t12) T where T.a=A.a", length=112, parser_state=0x7fffd98ea5d0, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.3-cp/sql/sql_parse.cc:8091 (gdb) p union_all $36 = 165 > > void init_query(); > st_select_lex* outer_select(); > @@ -800,6 +801,7 @@ class st_select_lex_unit: public st_select_lex_node { > bool union_needs_tmp_table(); > > void set_unique_exclude(); > + bool check_distinct_in_union(); can this be made 'const'? if yes, please make it > > friend struct LEX; > friend int subselect_union_engine::exec(); > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 857c9a117f5..6dac0cb0458 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -1960,3 +1960,63 @@ void st_select_lex_unit::set_unique_exclude() > } > } > } > + > +/* > + Check if the selects in the derived table can give distinct rows irrespective > + of the data given for the tables. > + > + for example: > + select * from > + ((select t1.a from t1) op (select t2.a from t2) op (select t3.a from t3)); > + the op here being UNION/INTERSECT/EXCEPT > + > + so this function would check if the derived table like the case above > + can give distinct rows or not irrespective of the data in the tables. I think the above is difficult to read. Readability is a matter of opinion, but I would suggest another variant: /* @brief Check if the derived table is guaranteed to have distinct rows because of UNION operations used to populate it. @detail UNION operation removes duplicate rows from its output. That is, a query like select * from t1 UNION select * from t2 will not produce duplicate rows in its output, even if table t1 (and/or t2) contain duplicate rows. EXCEPT and INTERSECT operations also have this property. On the other hand, UNION ALL operation doesn't remove duplicates. (The SQL standard also defines EXCEPT ALL and INTERSECT ALL, but we don't support them). st_select_lex_unit computes its value left to right. That is, if there is a st_select_lex_unit object describing (select #1) OP1 (select #2) OP2 (select #3) then ((select #1) OP1 (select #2)) is computed first, and OP2 is computed second. How can one tell if st_select_lex_unit is guaranteed to have distinct output rows? This depends on whether the last operation was duplicate- removing or not: - UNION ALL is not duplicate-removing - all other operations are duplicate-removing */ Is the above correct? > + > + So what the function is handling: > + - If there is no UNION ALL, we are guarenteed distinct rows. > + > + Example > + ((select t1.a from t1) UNION (select t2.a from t2); > + > + - If there is UNION ALL, we can still guarantee distinct rows if > + the last operation of the selects does not involve UNION ALL or > + INTERSECT is not there . > + > + Example: > + ((select t1.a from t1) UNION ALL (select t2.a from t2) UNION/EXCEPT > + (select t3.a from t3)): guarantees distinct rows, UNION and EXCEPT > + have the same precedence. > + > + Example: > + ((select t1.a from t1) UNION ALL (select t2.a from t2) INTERSECT > + (select t3.a from t3)): does not guarantee distinct rows because > + INTERSECT has higher precedence than UNION so we would evaluate > + the INTERSECT part first and then do UNION ALL, so there we can > + end up with duplicates, so distinct rows are not guaranteed. > + > + @retval false Distinct rows are not guaranteed > + @retval true Distinct rows are guanranteed irrespective of the data > + in the tables > + > +*/ > + > +bool st_select_lex_unit::check_distinct_in_union() > +{ > + bool is_intersect_present=FALSE; > + st_select_lex* first= first_select(); > + for(st_select_lex *sl=first; sl ; sl=sl->next_select()) > + is_intersect_present|= (sl->linkage == INTERSECT_TYPE); > + > + if (!union_all) > + return true; > + else > + { > + if (union_distinct) > + { > + if (!is_intersect_present && !union_distinct->next_select()) > + return true; > + } I'm unable to undestand what kind of check we are making here. According to my variant of the comment above, we should just check whether the last operation (operation made with the output of the last select) was duplicate-removing or not. We seem to be doing something more complex? > + } > + return false; > +} > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 35ec2d29d21..112873e8362 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -571,7 +571,10 @@ bool LEX::add_select_to_union_list(bool is_union_distinct, > current_select; > } > else > + { > DBUG_ASSERT(type == UNION_TYPE); > + current_select->master_unit()->union_all= true; > + } > return FALSE; > } > > diff --git a/sql/table.cc b/sql/table.cc > index 4f90d429ce5..d2938a29ea6 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -7113,6 +7113,25 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, > if (!keyinfo->rec_per_key) > return TRUE; > bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); > + /* > + For the case when there is a derived table that would give distinct rows, > + the index statistics are passed to the join optimizer to tell that > + a ref access to the derived table will produce only one row. > + */ > + > + st_select_lex_unit* derived= pos_in_table_list ? pos_in_table_list->derived: NULL; > + if (derived) > + { > + /* > + This handles the case when we have a single select in the derived table > + */ Does it anymore? Now it is handling the UNION case, too. > + st_select_lex* first= derived->first_select(); > + if ((first && !first->is_part_of_union() && > + first->options & SELECT_DISTINCT) || please put brackets around the condition on the line above as some too-smart compilers now complain about a possible typo. > + derived->check_distinct_in_union()) > + keyinfo->rec_per_key[key_parts-1]=1; > + } > + > keyinfo->read_stats= NULL; > keyinfo->collected_stats= NULL; > BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0