- developers - lists.mariadb.org

[Maria-developers] With_sum_func_cache
by Alexander Barkov 31 May '18

31 May '18

Hello Sanja, I recently worked on MDEV-16309 and had hard time to find which Item classes in the hierarchy: - have always with_sum_func==true - have always with_sum_func==false - have variable with_sum_func To make it sure, before actually working on MDEV-16309, I had to create a separate working tree and did with Item::with_sum_func the same change that we previously did for Item::with_subselect in: MDEV-14517 Cleanup for Item::with_subselect and Item::has_subquery() (which you reviewed) - I find the code easier to read this way (instead of searching for all possible assignments, one can search who overrides the method) - Also, some Item can become smaller in size. It's pity to throw the patch away. So perhaps we could just apply it. Can you please have a look? Thanks. I the meanwhile I'll create a new MDEV for it (with a similar description to MDEV-14517)

2 2

[Maria-developers] Open and close a TABLE_LIST at random place in code
by Eugene Kosov 31 May '18

31 May '18

Hello. I need to see a table structure while executing some query (DELETE in my case, but there will be more query types in future). I have a TABLE_LIST which came from a parser. Now I'm opening it with open_table_only_view_structure(), examine table structure and close it, trying to prevent any side effects. And there is a lot of them. Current code looks like this: if (open_tables_only_view_structure(thd, table_list, false)) { return true; } // examine table structure close_mysql_tables(thd); for (Sroutine_hash_entry *e = thd->lex->sroutines_list.first;e; e=e->next) e->mdl_request.ticket= NULL; for (TABLE_LIST *it= table_list; it; it= it->next_global) { it->table= NULL; it->reinit_before_use(thd); //it->next_global= NULL; it->derived_result= NULL; it->derived= NULL; it->view= NULL; it->view_tables= NULL; it->view_sctx= NULL; it->file_version= 0; it->mariadb_version= 0; it->updatable_view= 0; it->view_suid= 0; it->derived_type= 0; it->open_type= OT_BASE_ONLY; it->view_creation_ctx= 0; // it->set_table_ref_id(TABLE_REF_NULL, 0); } Is there a way to close a TABLE_LIST gracefully without touching a lot of it's fields? Or even maybe it's possible to open a table in a simpler manner? -- Eugene

2 3

Re: [Maria-developers] [Commits] de722585c1b: MDEV-16225: wrong resultset from query with semijoin=on
by Sergey Petrunia 27 May '18

27 May '18

Hi Varun, On Thu, May 24, 2018 at 03:17:02AM +0530, Varun wrote: > revision-id: de722585c1b2ad2af99bd1749e8566ea77838764 (mariadb-5.5.56-219-gde722585c1b) > parent(s): 1ada4afb0a51f7283b6187a95019ec2cb80c8a0b > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-05-24 03:09:42 +0530 > message: > > MDEV-16225: wrong resultset from query with semijoin=on > > For non-semi-join subquery optimization we do a cost based decision between > Materialisation and IN -> EXIST transformation. The issue in this case is that for IN->EXIST transformation > we run JOIN::reoptimize with the IN->EXISt conditions and we come up with a new query plan. But when we compare > the cost with Materialization, we make the decision to chose Materialization so we need to restore the query plan > for Materilization. The restoring of query plan is not handled correctly. > > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index d6d269a700f..b8d43b9797e 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -23805,6 +23805,22 @@ void JOIN::restore_query_plan(Join_plan_state *restore_from) > } > > } > + else > + { > + /* > + After running JOIN::reoptimize for IN->EXISTS > + transformation we might add elements to the keyuse array, > + The original query plan was that the keyuse array had no elements, so we > + should restore that state here. > + */ > + while(keyuse.elements) > + delete_dynamic_element(&keyuse, 0); I'm wondering, why do we need the above loop and not just delete everything with delete_dynamic(). But if I don't miss anything, this is not significant. Ok to push either the above variant or one with delete_dynamic(). > + for (uint i= 0; i < table_count; i++) > + { > + join_tab[i].keyuse= NULL; > + join_tab[i].checked_keys.clear_all(); > + } > + } > memcpy((uchar*) best_positions, (uchar*) restore_from->best_positions, > sizeof(POSITION) * (table_count + 1)); > /* Restore SJM nests */ > _______________________________________________ > 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

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