- developers - lists.mariadb.org

[Maria-developers] FYI: 10.3 master branch head builds OK Launchpad all architectures
by Otto Kekäläinen 11 Apr '18

11 Apr '18

Hello! Just for the record, I've done some small tweaks to 10.3 so that it now builds perfectly and passes build tests on all Launchpad architectures that I have access to. https://launchpad.net/~mysql-ubuntu/+archive/ubuntu/mariadb-10.3/+build s?build_text=&build_state=all Let's hope it stays like this for the rest of the current 10.3 cycle and will not regress by before it is announced GA.. On buildbot the Debian sid and Ubuntu Bionic builders pass now. There are however some sporadic failures left. http://buildbot.askmonty.org/buildbot/grid?branch=10.3&category=main&ca tegory=experimental On Travis everything else passes apart from one test, which has been consistently failing for some time. Everything was green in January when I wrote the protected branches blog post, so this one is a consistently failing regression. https://travis-ci.org/MariaDB/server/builds/362466962 - Otto

2 5

[Maria-developers] Temporary table bug
by Badrul Chowdhury 09 Apr '18

09 Apr '18

Hi, Is anyone actively working on https://jira.mariadb.org/browse/MDEV-12459? If not, I would like to take a look. -- Thanks, Badrul

2 2

[Maria-developers] Test failure overview for Debian/Ubuntu on 10.3 master branch
by Otto Kekäläinen 05 Apr '18

05 Apr '18

(posting this here so to ensure it is recorded in mailing list archive) On Travis the consistently failing tests are: main.type_datetime_hires main.select_pkeycache main.select main.select_jcl6 main.type_float main.func_str main.type_time_hires main.type_timestamp_hires Log: https://api.travis-ci.org/v3/job/362466968/log.txt On buildbot the consistelty failing tests for Debian/Ubuntu are: galera.galera_sst_mysqldump Log: http://buildbot.askmonty.org/buildbot/builders/kvm-deb-jessie-x86/build s/4033/steps/mtr/logs/stdio and http://buildbot.askmonty.org/buildbot/builders/kvm-deb-trusty-ppc64le/b uilds/4546/steps/mtr/logs/stdio Single ones: encryption.innodb_encryption Log: http://buildbot.askmonty.org/buildbot/builders/kvm-deb-sid-x86/builds/6 560/steps/mtr/logs/stdio rocksdb.drop_index_inplace Log: http://buildbot.askmonty.org/buildbot/builders/kvm-deb-trusty-ppc64le/b uilds/4546/steps/mtr/logs/stdio - Otto

1 0

Re: [Maria-developers] Regarding bugs (mysqldump)
by Vicențiu Ciorbaru 01 Apr '18

01 Apr '18

Hi Rutuja! Great analysis again and lets share this with the maria-developers mailing list as well. I've tested your proposed change and the change for dump_all_tables_in_db seems to not be necessary. If you look at how dump_all_tables_in_db gets called, it's followed by dump_all_views_in_db. The actual view creation happens in dump_all_views_in_db, while dump_all_tables_in_db will create some dummy tables for views. We create dummy tables first, so as to avoid having to check dependencies within views. There's more details in the comments of why we do that in the code of get_table_structure: /* Create a table with the same name as the view and with columns of the same name in order to satisfy views that depend on this view. The table will be removed when the actual view is created. The properties of each column, are not preserved in this temporary table, because they are not necessary. This will not be necessary once we can determine dependencies between views and can simply dump them in the appropriate order. */ For dump_selected_tables, your proposed change should be sufficient and according to my tests it works. Please submit a pull request for this. Looking forward to having this fixed soon. :) Unrelated to your analysis: There are plenty of edge cases where views won't work: For example: If we have 2 databases and one view uses something from a different database, if you're not careful about the order of things, it's going to fail. To create a "truly" always working dump one would have to create a full dependency graph and then do a topological sort for all objects in the database that depend on the objects we're dumping. This is outside of the scope of this bug, however it could prove to be a "better way to dump stuff". Regards, Vicentiu On Sun, 1 Apr 2018 at 06:28 Rutuja Surve <rutuja.r.surve(a)gmail.com> wrote: > Hello, > Could you review my following approach regarding the mysqldump bug ( > https://jira.mariadb.org/browse/MDEV-15021) > > I went through the code and observed the following: > 1. dump_all_tables_in_db calls dump_table first (for the list of all > tables), then dump_table calls get_view_structure. > 2. get_view_structure dumps the view for the corresponding table to > mysqldump > 3. After this call, dump_all_tables_in_db calls dump_routines_for_db > > dump_selected_tables also behaves in the exact same way as dump_all_tables_in_db, > following steps 1,2,3, except that it does it for a list of given tables > instead of doing it for all tables. > > We want the routines to get dumped before the views for the dump to become > importable. > There are two solutions for this: > 1. Make dump_all_tables_in_db call dump_routines_for_db before it calls > dump_table. This will dump routines first and views later. > This can be done by shifting the call to dump_routines_for_db before the > loop in which dump_table is called. I.E: > Shift : > if (opt_routines && mysql_get_server_version(mysql) >= 50009) > { > DBUG_PRINT("info", ("Dumping routines for database %s", database)); > *dump_routines_for_db*(database); > } > Before this loop: > while ((table= getTableName(0))) > { > char *end= strmov(afterdot, table); > if (include_table((uchar*) hash_key, end - hash_key)) > { > *dump_table*(table,database); > my_free(order_by); > .... > } > } > > We should do this for both dump_all_tables_in_db and dump_selected_tables. > > 2. Approach 2: > As suggested in the jira thread, we can use negative logic to call > dump_table on tables we intend to skip. > This appears to be a workaround. In the place where dump_all_tables_in_db > is called, we'd rather call dump_selected_tables > with a list of tables that don't use the routines we'll be listing after > that. This would fix the issue but is not a good approach. > > If dump_table doesn't alter the values for opt_routines, the first > approach should fix the issue. > > Thanks, > Rutuja > > > > On Fri, Mar 23, 2018 at 10:36 PM, Rutuja Surve <rutuja.r.surve(a)gmail.com> > wrote: > >> Hello, >> With respect to https://jira.mariadb.org/browse/MDEV-15021 : >> From what I understood from the thread, since the routines are placed >> below the tables/views, they cannot be used in views while importing. >> I went through the problematic_dump.sql file that's attached there. What >> does the '@' in the following mean: >> SET @saved_cs_client = @@character_set_client; >> (I'm not familiar with this syntax) >> Also, what does the following piece of code do: (what does create definer >> mean and does this function simply return 1 as there are no other >> statements after begin? This is a routine defined below test_view) >> CREATE DEFINER=`root`@`localhost` FUNCTION `test_function`() RETURNS >> int(11) >> BEGIN >> >> RETURN 1; >> END ;; >> DELIMITER ; >> From my understanding, test_view uses test_function and test_view2 would >> use test_function2. >> In the mysqldump.c file, I went through the dump_routines_for_db >> function. We'd require to modify that in order to fix the bug. >> >> From the beginner bugs list, I found these 2 bugs that are also related >> to mysql dump that I could take up: >> 1. https://jira.mariadb.org/browse/MDEV-9883 >> (Flag for letting mysqldump support the 'auto' option for character set). >> Where in the code base can I find the file where the ' --default-character-set' >> flag is defined?) >> 2. https://jira.mariadb.org/browse/MDEV-8765 >> The solution for this has already been proposed in the thread. For >> mysqldump to not corrupt 4 byte UTF8 data, "change >> include/my_global.h:#define MYSQL_UNIVERSAL_CLIENT_CHARSET to utf8mb4" >> >> Thanks, >> Rutuja >> > >

1 0

Re: [Maria-developers] d975226a0d6: MDEV-15241: make SIGNAL maximum MESSAGE_TEXT length a larger value
by Sergei Golubchik 01 Apr '18

01 Apr '18

Hi, Varun! On Mar 22, Varun wrote: > revision-id: d975226a0d668775062151c9f30676db3d81c93b (mariadb-10.3.0-642-gd975226a0d6) > parent(s): 85ddd9e8ce11bef089ccc6c86745142d09639c14 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-03-22 12:45:55 +0530 > message: > > MDEV-15241: make SIGNAL maximum MESSAGE_TEXT length a larger value > > Increasing the length of MESSAGE_TEXT from 128 to MYSQL_ERRMSG_SIZE > which is the max length of the error message ok to push. Don't forget to document your change, please. > diff --git a/sql/sql_signal.cc b/sql/sql_signal.cc > index 1da8b145151..13dc3db1f10 100644 > --- a/sql/sql_signal.cc > +++ b/sql/sql_signal.cc > @@ -262,12 +262,12 @@ int Sql_cmd_common_signal::eval_signal_informations(THD *thd, Sql_condition *con > } > /* > Enforce that SET MESSAGE_TEXT = <value> evaluates the value > - as VARCHAR(128) CHARACTER SET UTF8. > + as VARCHAR(MYSQL_ERRMSG_SIZE) CHARACTER SET UTF8. > */ > bool truncated; > String utf8_text; > str= set->val_str(& str_value); > - truncated= assign_fixed_string(thd->mem_root, & my_charset_utf8_bin, 128, > + truncated= assign_fixed_string(thd->mem_root, & my_charset_utf8_bin, MYSQL_ERRMSG_SIZE, > & utf8_text, str); > if (truncated) > { Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] MDEV-14929 - AddressSanitizer: memcpy-param-overlap in Field_longstr::compress
by Sergei Golubchik 31 Mar '18

31 Mar '18

Hi, Sergey, > From 6de643a580b4e706e0201927e43a033f5cb98970 Mon Sep 17 00:00:00 2001 > From: Sergey Vojtovich <svoj(a)mariadb.org> > Date: Wed, 21 Mar 2018 13:54:15 +0400 > Subject: [PATCH] MDEV-14929 - AddressSanitizer: memcpy-param-overlap in > Field_longstr::compress > > Handle overlaping "from" and Field_blob_compressed::value for compressed > blobs similarily to regular blobs. > --- > mysql-test/r/column_compression.result | 13 +++++++++++++ > mysql-test/t/column_compression.test | 12 ++++++++++++ > sql/field.cc | 16 +++++++++++----- > 3 files changed, 36 insertions(+), 5 deletions(-) > > diff --git a/sql/field.cc b/sql/field.cc > index 9da65526e145..1b044ec740e2 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -8718,17 +8718,23 @@ int Field_blob_compressed::store(const char *from, size_t length, > { > ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED; > uint to_length= (uint)MY_MIN(max_data_length(), field_charset->mbmaxlen * length + 1); > + String tmp(from, length, cs); > int rc; > > + if (from >= value.ptr() && from <= value.ptr() + value.length() && > + tmp.copy(from, length, cs)) minor style comment: this could be if (from >= value.ptr() && from <= value.end() && tmp.copy()) Bigger question: Field_blob::store() also checks whether charset conversion is necessary. Why is it not applicable here? And, it'd be good to have a test case where from > value.ptr() (with a substring() function). > + goto oom; > + > if (value.alloc(to_length)) > - { > - set_ptr((uint32) 0, NULL); > - return -1; > - } > + goto oom; > > - rc= compress((char*) value.ptr(), &to_length, from, (uint)length, cs); > + rc= compress((char*) value.ptr(), &to_length, tmp.ptr(), (uint) length, cs); > set_ptr(to_length, (uchar*) value.ptr()); > return rc; > + > +oom: > + set_ptr((uint32) 0, NULL); > + return -1; > } Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

[Maria-developers] Patch for pad functions
by jerome brauge 30 Mar '18

30 Mar '18

Hello Alexander, Can you open an MDEV for rpad/lpad (return NULL instead of empty string when sql_mode=oracle) and review this patch ? Regards.

2 1

Re: [Maria-developers] [Commits] c9ae1234814: MDEV-14592: Custom Aggregates Usage Status Variable
by Vicențiu Ciorbaru 26 Mar '18

26 Mar '18

Hi Varun This generally looks good, 2 comments inline. Vicentiu diff --git a/mysql-test/t/custom_aggregate_functions.test b/mysql-test/t/custom_aggregate_functions.test index 20fcc35f39f..a93ec545f01 100644 --- a/mysql-test/t/custom_aggregate_functions.test +++ b/mysql-test/t/custom_aggregate_functions.test @@ -16,7 +16,9 @@ create table t1 (sal int(10),id int(10)); INSERT INTO t1 (sal,id) VALUES (5000,1); INSERT INTO t1 (sal,id) VALUES (2000,1); INSERT INTO t1 (sal,id) VALUES (1000,1); # Please test that this works with subqueries, views, ctes and when calling from # other stored procedures. +show global status like "Feature_custom_aggregate_functions"; select f1(sal) from t1 where id>= 1; +show global status like "Feature_custom_aggregate_functions"; select * from t2; drop table t2; drop function f1; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index d4abdfc614f..7c0390e543f 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -313,6 +313,8 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref) } } aggr_sel->set_agg_func_used(true); + if (sum_func() == SP_AGGREGATE_FUNC) + aggr_sel->set_custom_agg_func_used(true); update_used_tables(); thd->lex->in_sum_func= in_sum_func; return FALSE; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index c7fbf0a594c..75ccda408b1 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -8491,6 +8491,7 @@ SHOW_VAR status_vars[]= { {"Executed_triggers", (char*) offsetof(STATUS_VAR, executed_triggers), SHOW_LONG_STATUS}, {"Feature_check_constraint", (char*) &feature_check_constraint, SHOW_LONG }, {"Feature_delay_key_write", (char*) &feature_files_opened_with_delayed_keys, SHOW_LONG }, Please move this up so the variable names are ordered alphabetically. + {"Feature_custom_aggregate_functions", (char*) offsetof(STATUS_VAR, feature_custom_aggregate_functions), SHOW_LONG_STATUS}, {"Feature_dynamic_columns", (char*) offsetof(STATUS_VAR, feature_dynamic_columns), SHOW_LONG_STATUS}, {"Feature_fulltext", (char*) offsetof(STATUS_VAR, feature_fulltext), SHOW_LONG_STATUS}, {"Feature_gis", (char*) offsetof(STATUS_VAR, feature_gis), SHOW_LONG_STATUS}, On Sat, 9 Dec 2017 at 08:53 Varun <varunraiko1803(a)gmail.com> wrote: > revision-id: c9ae12348143f59e875a52e2eb285c5ed7d7af56 > (mariadb-10.3.0-285-gc9ae1234814) > parent(s): c60095a818dce92838940525899a13a05633d148 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-12-09 12:22:26 +0530 > message: > > MDEV-14592: Custom Aggregates Usage Status Variable > > --- > mysql-test/r/custom_aggregate_functions.result | 6 ++++++ > mysql-test/t/custom_aggregate_functions.test | 2 ++ > sql/item_sum.cc | 2 ++ > sql/mysqld.cc | 1 + > sql/sql_lex.cc | 2 ++ > sql/sql_lex.h | 3 +++ > sql/sql_select.cc | 2 ++ > 7 files changed, 18 insertions(+) > > diff --git a/mysql-test/r/custom_aggregate_functions.result > b/mysql-test/r/custom_aggregate_functions.result > index ca8612ba652..f70675fa4db 100644 > --- a/mysql-test/r/custom_aggregate_functions.result > +++ b/mysql-test/r/custom_aggregate_functions.result > @@ -11,6 +11,9 @@ create table t1 (sal int(10),id int(10)); > INSERT INTO t1 (sal,id) VALUES (5000,1); > INSERT INTO t1 (sal,id) VALUES (2000,1); > INSERT INTO t1 (sal,id) VALUES (1000,1); > +show global status like "Feature_custom_aggregate_functions"; > +Variable_name Value > +Feature_custom_aggregate_functions 0 > select f1(sal) from t1 where id>= 1; > f1(sal) > 0 > @@ -18,6 +21,9 @@ Warnings: > Note 4093 At line 5 in test.f1 > Note 4093 At line 5 in test.f1 > Note 4093 At line 5 in test.f1 > +show global status like "Feature_custom_aggregate_functions"; > +Variable_name Value > +Feature_custom_aggregate_functions 1 > select * from t2; > sal > 5000 > diff --git a/mysql-test/t/custom_aggregate_functions.test > b/mysql-test/t/custom_aggregate_functions.test > index 20fcc35f39f..a93ec545f01 100644 > --- a/mysql-test/t/custom_aggregate_functions.test > +++ b/mysql-test/t/custom_aggregate_functions.test > @@ -16,7 +16,9 @@ create table t1 (sal int(10),id int(10)); > INSERT INTO t1 (sal,id) VALUES (5000,1); > INSERT INTO t1 (sal,id) VALUES (2000,1); > INSERT INTO t1 (sal,id) VALUES (1000,1); > +show global status like "Feature_custom_aggregate_functions"; > select f1(sal) from t1 where id>= 1; > +show global status like "Feature_custom_aggregate_functions"; > select * from t2; > drop table t2; > drop function f1; > diff --git a/sql/item_sum.cc b/sql/item_sum.cc > index d4abdfc614f..7c0390e543f 100644 > --- a/sql/item_sum.cc > +++ b/sql/item_sum.cc > @@ -313,6 +313,8 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref) > } > } > aggr_sel->set_agg_func_used(true); > + if (sum_func() == SP_AGGREGATE_FUNC) > + aggr_sel->set_custom_agg_func_used(true); > update_used_tables(); > thd->lex->in_sum_func= in_sum_func; > return FALSE; > diff --git a/sql/mysqld.cc b/sql/mysqld.cc > index c7fbf0a594c..75ccda408b1 100644 > --- a/sql/mysqld.cc > +++ b/sql/mysqld.cc > @@ -8491,6 +8491,7 @@ SHOW_VAR status_vars[]= { > {"Executed_triggers", (char*) offsetof(STATUS_VAR, > executed_triggers), SHOW_LONG_STATUS}, > {"Feature_check_constraint", (char*) &feature_check_constraint, > SHOW_LONG }, > {"Feature_delay_key_write", (char*) > &feature_files_opened_with_delayed_keys, SHOW_LONG }, > + {"Feature_custom_aggregate_functions", (char*) offsetof(STATUS_VAR, > feature_custom_aggregate_functions), SHOW_LONG_STATUS}, > {"Feature_dynamic_columns", (char*) offsetof(STATUS_VAR, > feature_dynamic_columns), SHOW_LONG_STATUS}, > {"Feature_fulltext", (char*) offsetof(STATUS_VAR, > feature_fulltext), SHOW_LONG_STATUS}, > {"Feature_gis", (char*) offsetof(STATUS_VAR, feature_gis), > SHOW_LONG_STATUS}, > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 6cb065c3985..277469cc0aa 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -2241,6 +2241,7 @@ void st_select_lex::init_query() > select_list_tables= 0; > m_non_agg_field_used= false; > m_agg_func_used= false; > + m_custom_agg_func_used= false; > window_specs.empty(); > window_funcs.empty(); > tvc= 0; > @@ -2278,6 +2279,7 @@ void st_select_lex::init_select() > merged_into= 0; > m_non_agg_field_used= false; > m_agg_func_used= false; > + m_custom_agg_func_used= false; > name_visibility_map= 0; > with_dep= 0; > join= 0; > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 069b75628e3..9b40529d3e2 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -1213,9 +1213,11 @@ class st_select_lex: public st_select_lex_node > */ > bool non_agg_field_used() const { return m_non_agg_field_used; } > bool agg_func_used() const { return m_agg_func_used; } > + bool custom_agg_func_used() const { return m_custom_agg_func_used; } > > void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; } > void set_agg_func_used(bool val) { m_agg_func_used= val; } > + void set_custom_agg_func_used(bool val) { m_custom_agg_func_used= val; } > inline void set_with_clause(With_clause *with_clause); > With_clause *get_with_clause() > { > @@ -1259,6 +1261,7 @@ class st_select_lex: public st_select_lex_node > private: > bool m_non_agg_field_used; > bool m_agg_func_used; > + bool m_custom_agg_func_used; > > /* current index hint kind. used in filling up index_hints */ > enum index_hint_type current_index_hint_type; > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 96aa227b439..c0d7c720910 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -2909,6 +2909,8 @@ bool JOIN::make_aggr_tables_info() > /* Count that we're using window functions. */ > status_var_increment(thd->status_var.feature_window_functions); > } > + if (select_lex->custom_agg_func_used()) > + > status_var_increment(thd->status_var.feature_custom_aggregate_functions); > > fields= curr_fields_list; > // Reset before execution > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] MDEV 10754 - Patch for trim functions
by jerome brauge 26 Mar '18

26 Mar '18

Hello Alexander, Can you review this patch for trim functions for Oracle ? Regards, Jérôme.

2 2

Re: [Maria-developers] [Commits] 2950feb5c09: MDEV-9959: A serious MariaDB server performance bug
by Sergey Petrunia 26 Mar '18

26 Mar '18

Hi Varun, Please find my feedback below. As disucssed before: please also run the MDEV's test case with this patch and post a comment with the result. On Fri, Mar 23, 2018 at 05:17:57PM +0530, Varun wrote: > revision-id: 2950feb5c09033843a6a32c7431421b45d5812dd (mariadb-10.3.0-646-g2950feb5c09) > parent(s): 9ef7ab221d70630155206910de8a6053db097164 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-03-23 17:17:09 +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/mdev9959.result | 64 ++++++++++++++++++++++++++++++++++++++++++++ > mysql-test/t/mdev9959.test | 28 +++++++++++++++++++ > sql/sql_lex.h | 2 ++ > sql/sql_union.cc | 36 +++++++++++++++++++++++++ > sql/table.cc | 26 +++++++++++++++--- > 5 files changed, 153 insertions(+), 3 deletions(-) > > diff --git a/mysql-test/r/mdev9959.result b/mysql-test/r/mdev9959.result > new file mode 100644 > index 00000000000..5269ba9b734 > --- /dev/null > +++ b/mysql-test/r/mdev9959.result > @@ -0,0 +1,64 @@ > +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); > +analyze select * from t1 , ( (select distinct t2.a from t2 group 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 0.83 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort It is not at all clear which parts of the above output matter and which don't, for example, does #rows matter for table t1? Please add a comment (into .test file with --echo # ) describing that table "<derived2>" should have type=ref and rows=1 > +select * from t1 , ( (select distinct t2.a from t2 group by c))q where t1.a=q.a; > +a a > +1 1 > +2 2 > +3 3 > +5 5 > +6 6 > +analyze select * from t1 , ( (select t2.a from t2 group 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 0.83 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +select * from t1 , ( (select t2.a from t2 group by c))q where t1.a=q.a; > +a a > +1 1 > +2 2 > +3 3 > +5 5 > +6 6 > +analyze select * from t1 , ( (select distinct t2.a from t2 group by c) union all (select distinct t2.a from t2 group 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.67 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort What does the above test show? We've got type=ref, #rows = 2 for the derived table. The estimate is correct but where does it come from? As far as I understand this patch, it only sets the estimate rows=1 for derived tables. How do we arrive at the value of 2? (If we would have gotten rows=2 without this patch, what's the point of having the testcase as part of this patch?) > +select * from t1 , ( (select distinct t2.a from t2 group by c) union all (select distinct t2.a from t2 group by c))q where t1.a=q.a; > +a a > +1 1 > +1 1 > +2 2 > +2 2 > +3 3 > +3 3 > +5 5 > +5 5 > +6 6 > +6 6 > +analyze select * from t1 , ( (select distinct t2.a from t2 group by c) union (select distinct t2.a from t2 group by c) union (select t3.a from t3 group 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 2 0.83 100.00 100.00 > +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +4 UNION t3 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort > +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL 5.00 NULL NULL > +select * from t1 , ( (select distinct t2.a from t2 group by c) union (select distinct t2.a from t2 group by c) union (select t3.a from t3 group by b))q where t1.a=q.a; > +a a > +1 1 > +2 2 > +3 3 > +5 5 > +6 6 > +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..77970f363ac > --- /dev/null > +++ b/mysql-test/t/mdev9959.test > @@ -0,0 +1,28 @@ > +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); > + > +# one select in derived table > + > +#with distinct > +analyze select * from t1 , ( (select distinct t2.a from t2 group by c))q where t1.a=q.a; > +select * from t1 , ( (select distinct t2.a from t2 group by c))q where t1.a=q.a; > +# without distinct > +analyze select * from t1 , ( (select t2.a from t2 group by c))q where t1.a=q.a; > +select * from t1 , ( (select t2.a from t2 group by c))q where t1.a=q.a; > + > +# multiple selects in derived table > + > +#UNION ALL > +analyze select * from t1 , ( (select distinct t2.a from t2 group by c) union all (select distinct t2.a from t2 group by c))q where t1.a=q.a; > +select * from t1 , ( (select distinct t2.a from t2 group by c) union all (select distinct t2.a from t2 group by c))q where t1.a=q.a; > + > +#UNION > +analyze select * from t1 , ( (select distinct t2.a from t2 group by c) union (select distinct t2.a from t2 group by c) union (select t3.a from t3 group by b))q where t1.a=q.a; > +select * from t1 , ( (select distinct t2.a from t2 group by c) union (select distinct t2.a from t2 group by c) union (select t3.a from t3 group 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..4128c1dd300 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 we have UNION ALL operation */ This variable is checked but it is never set? > > 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(); > > friend struct LEX; > friend int subselect_union_engine::exec(); > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 857c9a117f5..43ce0418fe5 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -1960,3 +1960,39 @@ void st_select_lex_unit::set_unique_exclude() > } > } > } > + > +/* > + Check if the selects in the derived table can give distinct rows I think "can" is the wrong word here. UNION/INTERSECT/EXCEPT output "can" be distinct if the underlying selects produce distinct sets of rows. What we are checking is whether the output will be distinct regardless of what data is there in the queried 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. Please describe the criteria and provide a couple of examples (either here or in the comments inside the function). > + > + @retval false Distinct rows are not guarenteed > + @retval true Distinct rows are guanrenteed Typos in the above lines. > + > +*/ > + > +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_select) > + { > + if (!is_intersect_present && !union_select->next_select()) > + return true; > + } > + } > + return false; > +} > diff --git a/sql/table.cc b/sql/table.cc > index f42455a8413..c474d2ed4d7 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -7113,9 +7113,29 @@ 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); > - st_select_lex* lex= pos_in_table_list ? pos_in_table_list->derived->first_select(): NULL; > - if (lex && (lex->options & SELECT_DISTINCT)) > - keyinfo->rec_per_key[key_parts-1]=1; > + > + /* > + 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 > + */ > + st_select_lex* first= derived->first_select(); > + if (first && !first->is_part_of_union() && > + first->options & SELECT_DISTINCT) > + keyinfo->rec_per_key[key_parts-1]=1; > + else > + { > + if (check_distinct_in_union()) > + keyinfo->rec_per_key[key_parts-1]=1; > + } I think it would be better if there was a single "if", like so: if ((this is a non-union select with SELECT_DISTINCT || 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