May 2018 - developers - lists.mariadb.org

Re: [Maria-developers] [Maria-discuss] Query ambiguity
by Karthick Subramanian 17 May '18

17 May '18

Check whether you really need a comma here: *metrics_5.id <http://metrics_5.id/>,* *(SELECT * Also in your screen shot, AS 'LT CPC`, there is no comma and you use derived table in SELECT and trying to use JOIN in the FROM using this. Sorry to say this, I think you can either break this logic into small simple chunks and use SP to derive the logic in case you are not much comfortable writing a complex SQL. I think there is lot of syntax errors on it. On Thu, May 17, 2018 at 10:05 AM, Dev C <chauhan.devsur(a)gmail.com> wrote: > Here is the full code and picture on what is required. > > SELECT > campaigns_5.name AS `Campaign Name`, > adgroups_5.name AS `Adgroup Name`, > STNegData1W.query, > keywords_5.matchType AS `Match Type`, > Sum(metrics_5.clicks) AS `LT Clicks`, > Sum(metrics_5.costs) AS `LT Spend`, > Sum(metrics_5.orders) AS `LT Orders`, > metrics_5.sales AS `LT Sales`, > If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) > AS `LT ACOS`, > If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC` > FROM (((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = > campaigns_5.campaignId) INNER JOIN adgroups_5 ON keywords_5.adGroupId = > adgroups_5.adGroupId) INNER JOIN STNegData1W ON keywords_5.keywordId = > STNegData1W.keywordId) INNER JOIN metrics_5 ON STNegData1W.metricId = *metrics_5.id > <http://metrics_5.id>,* > *(SELECT * > STNegData4W.query, > STNegData4W.metricId, > STNegData4W.keywordId, > metrics_5.impressions > FROM > (SELECT searchterm_5.query, searchterm_5.metricId, > searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders` > FROM searchterm_5 INNER JOIN metrics_5 ON searchterm_5.metricId = > metrics_5.id > GROUP BY searchterm_5.query, searchterm_5.metricId, > searchterm_5.keywordId, metrics_5.created_at > HAVING (((Sum(metrics_5.orders))=0) AND ((metrics_5.created_at) > > DATE_SUB('2018-05-10',INTERVAL 28 DAY)))) AS STNegData4W > INNER JOIN > metrics_5 ON STNegData4W.metricId = metrics_5.id > GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId, > metrics_5.impressions, metrics_5.created_at > HAVING (((metrics_5.impressions)>0) AND ((metrics_5.created_at)> > DATE_SUB('2018-05-10', INTERVAL 7 DAY)))) AS STNegData1W > GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, > keywords_5.matchType, metrics_5.sales, campaigns_5.user_market_id, Right( > campaigns_5.name,4) > HAVING (((Sum(metrics_5.clicks))>5) AND ((campaigns_5.user_market_id)=12) > AND ((Right(campaigns_5.name,4))="PFUS")) > > On Thu, May 17, 2018 at 4:06 PM, Pantelis Theodosiou <ypercube(a)gmail.com> > wrote: > >> What Rhys said, please post queries as text, not as images. >> >> The issue is likely due to the way you structured your query. You have >> put a derived table in a random place (in the SELECT list). >> >> Please try to read about CTEs. It will help you design those complex >> queries better and make them more readable and easy to edit: >> https://mariadb.com/kb/en/library/with/ >> >> Best regards >> >> Pantelis Theodosiou >> >> On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell(a)swisscom.com> wrote: >> >>> You're not making a lot of sense here. Please explain the "ambiguity". >>> That's a fairly complex query so you should include as text. >>> >>> >>> >>> "Does not work" <- In the image. It helps if you include error message. >>> >>> >>> >>> *From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= >>> swisscom.com(a)lists.launchpad.net] *On Behalf Of *Dev C >>> *Sent:* 17 May 2018 06:41 >>> *To:* maria-discuss(a)lists.launchpad.net; Maria Developers < >>> maria-developers(a)lists.launchpad.net> >>> *Subject:* [Maria-discuss] Query ambiguity >>> >>> >>> >>> Hello >>> >>> >>> >>> I have query ambiguity as attached. Please help me on how to solve that. >>> >>> >>> >>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~maria-discuss >>> Post to : maria-discuss(a)lists.launchpad.net >>> Unsubscribe : https://launchpad.net/~maria-discuss >>> More help : https://help.launchpad.net/ListHelp >>> >>> >> > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >

1 0

Re: [Maria-developers] [Maria-discuss] Query ambiguity
by Pantelis Theodosiou 17 May '18

17 May '18

What Rhys said, please post queries as text, not as images. The issue is likely due to the way you structured your query. You have put a derived table in a random place (in the SELECT list). Please try to read about CTEs. It will help you design those complex queries better and make them more readable and easy to edit: https://mariadb.com/kb/en/library/with/ Best regards Pantelis Theodosiou On Thu, May 17, 2018 at 11:23 AM, <Rhys.Campbell(a)swisscom.com> wrote: > You're not making a lot of sense here. Please explain the "ambiguity". > That's a fairly complex query so you should include as text. > > > > "Does not work" <- In the image. It helps if you include error message. > > > > *From:* Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= > swisscom.com(a)lists.launchpad.net] *On Behalf Of *Dev C > *Sent:* 17 May 2018 06:41 > *To:* maria-discuss(a)lists.launchpad.net; Maria Developers < > maria-developers(a)lists.launchpad.net> > *Subject:* [Maria-discuss] Query ambiguity > > > > Hello > > > > I have query ambiguity as attached. Please help me on how to solve that. > > > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >

2 1

Re: [Maria-developers] [Maria-discuss] Interval in MYsql query
by Peter Laursen 16 May '18

16 May '18

Use the DATE_ADD( ) or DATE_SUB() function with INTERVAL as explained here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#functi… -- Peter. On Wed, May 16, 2018 at 4:07 PM, Dev C <chauhan.devsur(a)gmail.com> wrote: > Hello > > If I use below query > (metrics_5.created_at)>'5/10/2018'- 28 ) > it gives me some result > but if i use below query > (metrics_5.created_at)>'5/10/2018'- INTERVAL 28 DAY ) > > It does not give me any result, any one know why ? > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >

1 0

[Maria-developers] [question] password input length
by Michal Schorm 16 May '18

16 May '18

Hello everyone, 1) I found out, that passwords longer than 79 characters aren't accepted via password prompt. Is that expected? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566179 -- 2) Another password length cap at 213 characters can be found when using sha256_password authentification plugin. Is that expected too? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566219 -- 3) Different theme: why aren't tests for mariadb-connector-odbc, that can be found on GitHub, packged to the source tarball ? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 2

Re: [Maria-developers] a639eff: MDEV-15813 ASAN use-after-poison in hp_hashnr upon HANDLER READ on a versioned HEAP table.
by Sergei Golubchik 16 May '18

16 May '18

Hi, Alexey! On May 16, Alexey Botchkov wrote: > revision-id: a639eff5940ed4147709511aeaee922fa7095284 (mariadb-10.3.6-193-ga639eff) > parent(s): cd15e764a8a4f3e799f1efaede413c95e3233df6 > committer: Alexey Botchkov > timestamp: 2018-05-16 09:44:22 +0400 > message: > > MDEV-15813 ASAN use-after-poison in hp_hashnr upon HANDLER READ on a versioned HEAP table. > > Check index capabilities before executing HANDLER READ command. > > diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc > index 187a746..02d5bbf 100644 > --- a/sql/sql_handler.cc > +++ b/sql/sql_handler.cc > @@ -660,6 +660,18 @@ mysql_ha_fix_cond_and_key(SQL_HANDLER *handler, > Item *item; > key_part_map keypart_map; > uint key_len; > + const KEY *c_key= table->s->key_info + handler->keyno; > + > + if ((c_key->flags & HA_SPATIAL) || > + c_key->algorithm == HA_KEY_ALG_FULLTEXT || > + (ha_rkey_mode != HA_READ_KEY_EXACT && > + (table->file->index_flags(handler->keyno, 0, TRUE) & > + (HA_READ_NEXT | HA_READ_PREV | HA_READ_RANGE)) == 0)) This is strange. Do you mean that SPATIAL and FULLTEXT indexes have HA_READ_NEXT or HA_READ_PREV or HA_READ_RANGE? Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] MDEV-16094 Crash when using AS OF with a stored function
by Alexander Barkov 15 May '18

15 May '18

Hi Sergei, Please review a patch for MDEV-16094. Thanks!

2 2

Re: [Maria-developers] review of MDEV-16101 ADD PARTITION on table partitioned by list does not work …
by Jacob Mathew 14 May '18

14 May '18

Hi Holyfoot, Your fix creates more lists, all of them containing one element, but it does only one reorganization. My fix does a reorganization whenever a 33rd element is processed but creates less lists. I agree that reducing the number of reorganizations to one is a better way to fix the problem. So I will use your fix. Thanks, Jacob Jacob B. Mathew Spider and Server Developer MariaDB Corporation +1 408 655 8999 (mobile) jacob.b.mathew (Skype) jacob.mathew(a)MariaDB.com On Sun, May 13, 2018 at 2:36 PM, Alexey Botchkov <holyfoot(a)mariadb.com> wrote: > Hi, Jacob. > > Your fix: > https://github.com/MariaDB/server/commit/85bbea8dbb9c01defa993e44a5b16c > 43ef851fbd > > I don't like the way your fix work. > So after we get the 32-th item we do the reorganize_into_single_ > field_col_val() > trick that turns one element with 32 values into 32 elements with a single > value. > Then as we get 33-th element, we again put it into that first element, and > do so for > the next 31 element then call the reorganize_ again. > > I think we should just keep the 'one-value' list after the 'reorganize_' > So my fix would be like this: > --- a/sql/partition_info.cc > +++ b/sql/partition_info.cc > @@ -1996,9 +1996,11 @@ part_column_list_val *partition_info::add_column_value(THD > *thd) > into the structure used for 1 column. After this we call > ourselves recursively which should always succeed. > */ > + num_columns= curr_list_object; > if (!reorganize_into_single_field_col_val(thd)) > { > - DBUG_RETURN(add_column_value(thd)); > + if (!init_column_part(thd)) > + DBUG_RETURN(add_column_value(thd)); > } > > > Best regards. > HF > >

1 0

Re: [Maria-developers] 5651e72: MDEV-15465 Server crash or ASAN heap-use-after-free in Item_func_match::cleanup upon using FT search with partitioning.
by Sergei Golubchik 14 May '18

14 May '18

Hi, Alexey! On May 14, Alexey Botchkov wrote: > Hi Sergei, > > I was wrong - this fix is only needed in 10.3, since the FT keys > are available for PARTITION tables. > So going to push it to the 10.3. Is it ok with you? Yes, thanks! Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 4861977ce76: MDEV-14695: Assertion `n < m_size' failed in Bounds_checked_array<Element_type>::operator
by Vicențiu Ciorbaru 14 May '18

14 May '18

Hi Varun! I will take a second look at this but a first minor comment is to not save two characters by shortening fields to flds, it makes reading unnatural. Vicentiu On Sat, 12 May 2018 at 21:45 Varun <varunraiko1803(a)gmail.com> wrote: > revision-id: 4861977ce76761fefb9779a774984b6afa818f2e > (mariadb-10.2.5-636-g4861977ce76) > parent(s): 8b26fea83572cf5c043721b7835c3828937f9c27 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-05-13 00:09:15 +0530 > message: > > MDEV-14695: Assertion `n < m_size' failed in > Bounds_checked_array<Element_type>::operator > > In this issue we hit the assert because we are adding addition fields to > the field JOIN::all_fields list. This > is done because HEAP tables can't index BIT fields so we need to use an > additional hidden field for grouping because later it will be > converted to a LONG field. Original field will remain of the BIT type and > will be returned. This happens when we convert DISTINCT to > GROUP BY. > > The solution is to take into account the number of such hidden fields that > would be added to the field JOIN::all_fields list while calculating > the size of the ref_pointer_array. > > --- > sql/sql_base.cc | 9 ++++++--- > sql/sql_base.h | 4 ++-- > sql/sql_delete.cc | 3 ++- > sql/sql_lex.cc | 8 +++++++- > sql/sql_lex.h | 5 +++++ > sql/sql_select.cc | 4 +++- > sql/sql_union.cc | 1 + > 7 files changed, 26 insertions(+), 8 deletions(-) > > diff --git a/sql/sql_base.cc b/sql/sql_base.cc > index 95ff41cb6f4..81ccfe5e514 100644 > --- a/sql/sql_base.cc > +++ b/sql/sql_base.cc > @@ -6919,7 +6919,7 @@ static bool setup_natural_join_row_types(THD *thd, > > int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, > List<Item> *sum_func_list, > - uint wild_num) > + uint wild_num, uint *hidden_bit_flds) > { > if (!wild_num) > return(0); > @@ -6960,7 +6960,7 @@ int setup_wild(THD *thd, TABLE_LIST *tables, > List<Item> &fields, > else if (insert_fields(thd, ((Item_field*) item)->context, > ((Item_field*) item)->db_name, > ((Item_field*) item)->table_name, &it, > - any_privileges)) > + any_privileges, hidden_bit_flds)) > { > if (arena) > thd->restore_active_arena(arena, &backup); > @@ -7425,7 +7425,7 @@ bool get_key_map_from_key_list(key_map *map, TABLE > *table, > bool > insert_fields(THD *thd, Name_resolution_context *context, const char > *db_name, > const char *table_name, List_iterator<Item> *it, > - bool any_privileges) > + bool any_privileges, uint *hidden_bit_flds) > { > Field_iterator_table_ref field_iterator; > bool found; > @@ -7545,6 +7545,9 @@ insert_fields(THD *thd, Name_resolution_context > *context, const char *db_name, > else > it->after(item); /* Add 'item' to the SELECT list. */ > > + if (item->type() == Item::FIELD_ITEM && item->field_type() == > MYSQL_TYPE_BIT) > + (*hidden_bit_flds)++; > + > #ifndef NO_EMBEDDED_ACCESS_CHECKS > /* > Set privilege information for the fields of newly created views. > diff --git a/sql/sql_base.h b/sql/sql_base.h > index d6063f1b771..8d0dcb2f197 100644 > --- a/sql/sql_base.h > +++ b/sql/sql_base.h > @@ -154,11 +154,11 @@ bool fill_record_n_invoke_before_triggers(THD *thd, > TABLE *table, > enum trg_event_type event); > bool insert_fields(THD *thd, Name_resolution_context *context, > const char *db_name, const char *table_name, > - List_iterator<Item> *it, bool any_privileges); > + List_iterator<Item> *it, bool any_privileges, uint > *hidden_bit_flds); > void make_leaves_list(THD *thd, List<TABLE_LIST> &list, TABLE_LIST > *tables, > bool full_table_list, TABLE_LIST *boundary); > int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, > - List<Item> *sum_func_list, uint wild_num); > + List<Item> *sum_func_list, uint wild_num, uint * > hidden_bit_flds); > bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, > List<Item> &item, enum_mark_columns mark_used_columns, > List<Item> *sum_func_list, List<Item> *pre_fix, > diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc > index 7b7a7e3f804..7bae7736cf3 100644 > --- a/sql/sql_delete.cc > +++ b/sql/sql_delete.cc > @@ -760,7 +760,8 @@ l > select_lex->leaf_tables, FALSE, > DELETE_ACL, SELECT_ACL, TRUE)) > DBUG_RETURN(TRUE); > - if ((wild_num && setup_wild(thd, table_list, field_list, NULL, > wild_num)) || > + if ((wild_num && setup_wild(thd, table_list, field_list, NULL, wild_num, > + &select_lex->hidden_bit_flds)) || > setup_fields(thd, Ref_ptr_array(), > field_list, MARK_COLUMNS_READ, NULL, NULL, 0) || > setup_conds(thd, table_list, select_lex->leaf_tables, conds) || > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 3e36cac96b9..49150c863fe 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -2143,6 +2143,7 @@ void st_select_lex::init_query() > m_agg_func_used= false; > window_specs.empty(); > window_funcs.empty(); > + hidden_bit_flds= 0; > } > > void st_select_lex::init_select() > @@ -2673,6 +2674,10 @@ ulong st_select_lex::get_table_join_options() > > bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) > { > + > + if (!((options & SELECT_DISTINCT) && !group_list)) > + hidden_bit_flds= 0; > + > // find_order_in_list() may need some extra space, so multiply by two. > order_group_num*= 2; > > @@ -2687,7 +2692,8 @@ bool st_select_lex::setup_ref_array(THD *thd, uint > order_group_num) > select_n_reserved + > select_n_having_items + > select_n_where_fields + > - order_group_num) * 5; > + order_group_num + > + hidden_bit_flds) * 5; > if (!ref_pointer_array.is_null()) > { > /* > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 6d281930d1f..8283496604e 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -956,6 +956,11 @@ class st_select_lex: public st_select_lex_node > > /* it is for correct printing SELECT options */ > thr_lock_type lock_type; > + /* > + it counts the number of bit fields in the SELECT list. These are used > when DISTINCT is > + converted to a GROUP BY involving BIT fields. > + */ > + uint hidden_bit_flds; > > void init_query(); > void init_select(); > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 4cd4754596b..83953661ce3 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -799,7 +799,9 @@ JOIN::prepare(TABLE_LIST *tables_init, > select_lex != select_lex->master_unit()->global_parameters()) > real_og_num+= select_lex->order_list.elements; > > - if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num)) > + DBUG_ASSERT(select_lex->hidden_bit_flds == 0); > + if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num, > + &select_lex->hidden_bit_flds)) > DBUG_RETURN(-1); > if (select_lex->setup_ref_array(thd, real_og_num)) > DBUG_RETURN(-1); > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 3fe8a24f8bd..42904e341b6 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -1474,6 +1474,7 @@ bool st_select_lex::cleanup() > } > inner_refs_list.empty(); > exclude_from_table_unique_test= FALSE; > + hidden_bit_flds= 0; > DBUG_RETURN(error); > } > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] MDEV-6111: Optimizer trace: features and milestones
by Sergey Petrunia 11 May '18

11 May '18

Hi Zhzhzoo, Please find below some thoughts about features/milestones in the project. Items at the top of the list should be done first. The list is not necesarrily complete, any comments/additions are welcome. - Optimizer trace collection API (is it different from Json_writer that we have? Does MySQL use the same API to produce EXPLAIN FORMAT=JSON and produce optimizer_trace? Should we follow them?) - Basic optimizer trace collection (just print the query and "hello":"world" instead of trace). Clear the trace data at query start, collect the trace if tracing is switched on (and do not collect if it is off) - INFORMATION_SCHEMA.OPTIMIZER_TRACE table in I_S database. Produce optimizer_trace content for features common with MySQL: = condition rewrite = partition pruning = range analysis = ref/eq_ref analysis (update_ref_and_keys) = (TODO what else?) Produce optimizer_trace content for MariaDB-specific features: = merged semi-join conversion = non-merged semi-join conversion = join condition pushdown = (TODO what else?) - optimizer_trace_limit/optimizer_trace_offset (without this feature, just save one last trace) - Ability to produce either a pretty-printed or one-line optimizer trace - Security = INFORMATION_SCHEMA.OPTIMIZER_TRACE.INSUFFICIENT_PRIVILEGES = Who can view optimizer_trace? = Figure out and implement the necessary security restrictions - Memory limits = Honor the @@optimizer_trace_max_mem_size setting. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1