- developers - lists.mariadb.org

[Maria-developers] JSON_TABLE: Why one can't just use Name_resolution_context
by Sergey Petrunia 09 Apr '21

09 Apr '21

Hello, There was this question raised: Why can't JSON_TABLE code just use the current Name_resolution_context objects? == Context == Name_resolution_context object has these two members that specify a subset of tables to use: TABLE_LIST *first_name_resolution_table; TABLE_LIST *last_name_resolution_table; JSON_TABLE code also introduces this one: table_map ignored_tables; With the meaning that name resolution process will not search for the field of interest in the tables listed in the bitmap. == Rationale == For a JSON_TABLE invocation select ... from json_table(ARG, ...) as JT .... the ARG may refer to any table that precedes JT in the from clause. Let's call the set of the tables ALLOWED_DEPS. Can one take a JSON_TABLE invocation and pick first_name_resolution_table=FIRST, last_name_resolution_table=LAST such that ALLOWED_DEPS = { FIRST, SECOND= FIRST->next_name_resolution_table, THIRD= SECOND->next_name_resolution_table, ... LAST } ? I claim that there are cases where this is not possible. == An example == select * from t1, (t2 natural join (json_table('{}', '$' COLUMNS (d for ordinality)) as JT natural join t3 ) ); Here, JT's ALLOWED_DEPS={t1, t2}. Looking at the data structures (each Table name denotes a TABLE_LIST object referring to the table) select_lex.name_resolution_context.first_name_resolution_table= t1 t1->next_name_resolution_table= $join_nest1 $join_nest1->next_name_resolution_table= NULL $join_nest1->nested_join.join_list= { $join_nest2, t2 } t2->next_name_resolution_table= $join_nest2 $join_nest2->next_name_resolution_table= NULL $join_nest2->nested_join.join_list= {t3, JT} JT->next_name_resolution_table= t3 t3->next_name_resolution_table= NULL Apparently there is no single chain that includes t1 and t2. == Possible objections == "But nested (outer) joins do it" Nested outer joins never need to pick a subset that includes only - one of the tables in a NATURAL JOIN - one of the tables outside that NATURAL JOIN. so there's no comparison. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] ee538938345: MDEV-21117: refine the server binlog-based recovery for semisync
by Sergei Golubchik 08 Apr '21

08 Apr '21

Hi, Andrei! On Apr 07, Andrei Elkin wrote: > Sergei, salve. > > I should've asked for clarification to > > >>> + } > >>> + } else > >> > >> add into the else branch something like > >> > >> /* this branch is only for the legacy TC_LOG_MMAP */ > >> compile_time_assert(sizeof(TC_LOG_MMAP) > 1 ); > > > > [x] > > Actually the branch, which is > > } else > if (IF_WSREP((wsrep_emulate_bin_log && > wsrep_is_wsrep_xid(info->list + i) && > x <= wsrep_limit), false) || > (info->commit_list ? > my_hash_search(info->commit_list, (uchar *)&x, sizeof(x)) != 0 : > tc_heuristic_recover == TC_HEURISTIC_RECOVER_COMMIT)) > { > #ifndef DBUG_OFF > int rc= > #endif > hton->commit_by_xid(hton, info->list+i); > > deals with tc_heuristic_recover. > > I am not sure what you meant. I wanted to make sure all TC_LOG_MMAP code is removed when TC_LOG_MMAP itself goes away. But apparenlty I overlooked the heuristic recover path. So this code deals with both heuristic recover and TC_LOG_MMAP. Okay than, forget that comment. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

[Maria-developers] JSON_TABLE: on the question of temp table "cache" in MySQL-8
by Sergey Petrunia 07 Apr '21

07 Apr '21

1 0

Re: [Maria-developers] d221068ae8b: Change CHARSET_INFO character set and collaction names to LEX_CSTRING
by Sergei Golubchik 06 Apr '21

06 Apr '21

Hi, Michael! On Apr 01, Michael Widenius wrote: > revision-id: d221068ae8b (mariadb-10.5.2-540-gd221068ae8b) > parent(s): 896c3b0a00a > author: Michael Widenius <michael.widenius(a)gmail.com> > committer: Michael Widenius <michael.widenius(a)gmail.com> > timestamp: 2021-03-24 19:13:42 +0200 > message: > > Change CHARSET_INFO character set and collaction names to LEX_CSTRING > > This change removed 68 strlen() calls from the code. > > The following renames was to ensure we don't use the old names > when merging code from earlier releases, as using the new variables > for print function could result in crashes: > - charset->csname renamed to charset->cs_name > - charset->name renamed to charset->col_name Two comments: 1. please, use coll_name. in our codebase "cs" conventionally means "charset", "col" is usually used for "column", "coll" - for "collation" 2. You changed the client api, as can be seen from > diff --git a/client/mysql.cc b/client/mysql.cc > index e6c7d0f091c..8c64d01539d 100644 > --- a/client/mysql.cc > +++ b/client/mysql.cc > @@ -4719,9 +4719,8 @@ sql_real_connect(char *host,char *database,char *user,char *password, > return -1; // Retryable > } > > - charset_info= get_charset_by_name(mysql.charset->name, MYF(0)); > - > - > + charset_info= get_charset_by_name(IF_EMBEDDED(mysql.charset->col_name.str, > + mysql.charset->name), MYF(0)); > connected=1; > #ifndef EMBEDDED_LIBRARY > mysql_options(&mysql, MYSQL_OPT_RECONNECT, &debug_info_flag); Luckily, C/C is a separate project now, so most clients won't be affected. But anything linking with embedded might be. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] e3971006cca: Give a readable error in mtr if resolve_at_variable fails
by Sergei Golubchik 06 Apr '21

06 Apr '21

Hi, Monty! On Apr 05, Michael Widenius wrote: > revision-id: e3971006cca (mariadb-10.5.2-547-ge3971006cca) > parent(s): ae009435e72 > author: Michael Widenius <michael.widenius(a)gmail.com> > committer: Michael Widenius <michael.widenius(a)gmail.com> > timestamp: 2021-03-24 19:25:09 +0200 > message: > > Give a readable error in mtr if resolve_at_variable fails This change is wrong. Clearly, the || '' part was intentional. Check the history, it was added in the commit commit 897b51db434 Author: Sergei Golubchik <serg(a)mariadb.com> Date: Thu Sep 10 12:12:47 2020 +0200 make S3 tests to run when S3 is statically linked * use the environment variable HA_S3_SO, not a literal ha_s3 in cnf files * make ConfigFactory to support empty option values * update no_s3.result after MDEV-11412 so, apparently, the idea was that you should be able to write plugin-load-add=$HA_S3_SO and it will *not* fail when $HA_S3_SO is not defined, because S3 engine is statically linked in. > diff --git a/mysql-test/lib/My/ConfigFactory.pm b/mysql-test/lib/My/ConfigFactory.pm > index 3249a06256c..5a5b691998c 100644 > --- a/mysql-test/lib/My/ConfigFactory.pm > +++ b/mysql-test/lib/My/ConfigFactory.pm > @@ -347,8 +347,15 @@ sub resolve_at_variable { > or croak "There is no group named '$group_name' that ", > "can be used to resolve '$option_name' for test '$self->{testname}'"; > > - my $value= $from_group->value($option_name) || ''; > - $res .= $before.$value; > + my $value= $from_group->value($option_name); > + if (!defined($value)) > + { > + ::mtr_verbose("group: $group_name option_name: $option_name is undefined"); > + } > + else > + { > + $res .= $before.$value; > + } > } > $res .= $after; > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] 405a89353b2: Don't reset StringBuffers in loops when not needed
by Sergei Golubchik 06 Apr '21

06 Apr '21

Hi, Monty! On Apr 02, Michael Widenius wrote: > revision-id: 405a89353b2 (mariadb-10.5.2-542-g405a89353b2) > parent(s): 2dd8d472fcc > author: Michael Widenius <michael.widenius(a)gmail.com> > committer: Michael Widenius <michael.widenius(a)gmail.com> > timestamp: 2021-03-24 19:25:08 +0200 > message: > > Don't reset StringBuffers in loops when not needed > > - Moved out creating StringBuffers in loops and instead create them > outside and just reset the buffer if it was not allocated (to avoid > a possible malloc/free for every entry) > > diff --git a/sql/sql_string.h b/sql/sql_string.h > index 29d01779b71..8df50ac5e66 100644 > --- a/sql/sql_string.h > +++ b/sql/sql_string.h > @@ -1045,6 +1045,12 @@ class StringBuffer : public String > { > length(0); > } > + void set_buffer_if_not_allocated(CHARSET_INFO *cs) > + { > + if (is_alloced()) > + Static_binary_string::set(buff, buff_sz); it seems to be doing exactly the opposite of what the name suggests. It sets the buffer *if allocated*. Also, it looks logical to do length(0) here, so that this method would reset the StringBuffer to its initial state. > + set_charset(cs); > + } > }; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

[Maria-developers] MDEV-9504
by Jason Alpers 02 Apr '21

02 Apr '21

Hello, We're hitting the problem in MDEV-9504 ( https://jira.mariadb.org/browse/MDEV-9504) I see from the commit at https://github.com/MariaDB/server/commit/07b8aefe90ca830d2de068f2966cd2288b… that this change was backed out due to test failures. I ran through all the mysql-tests with and without the change and haven't been able to pinpoint a difference though. The reverting commit indicates the correct value for select_limit is only computed when the query plan changes. Any chance someone could provide more detail on what needs to be changed to avoid regression with this fix? If there are sample queries that regress with only the original MDEV-9504 changes, that would be helpful. Thanks in advance! Jase

1 0

Re: [Maria-developers] GSOC21: MDEV-16375 & MDEV-23143
by Hollow Man 02 Apr '21

02 Apr '21

Hi community! I've had my proposal shared with https://drive.google.com/file/d/1sv0qbqt9W-ob3GqxygWwRGurpRS1lCiv/view , hope to get some feedback from the community.   Songlin   ------------------ Original ------------------ From:  "Hollow Man"<hollowman(a)hollowman.ml>; Date:  Thu, Mar 11, 2021 00:17 AM To:  "maria-developers"<maria-developers(a)lists.launchpad.net>; Subject:  GSOC21: MDEV-16375 & MDEV-23143   Hi MariaDB community!    Glad to be here! My github account is @HollowMan6. Though I'm new to MariaDB community, I'm interested in MDEV-16375 & MDEV-23143: Function to normalize a json value & missing a JSON_EQUALS function for this year's GSOC project. Here are my first thoughts on these issues:    I have checked part of the codebase and I think the two issues can be merged into one. First we can create a function named JSON_NORMALIZE to normalize the json, which automatically parses the inputed json document, recursively sorts the keys (for objects) / sorts the numbers (for arrays), removes the spaces, and then return the json document string.    Then we create a function named JSON_EQUALS, which can be used to compare 2 json documents for equality realized by first seperately normalize the two json documents using JSON_NORMALIZE, then the 2 can be compared exactly as binary strings.    I have taken some inspirations from the Item_func_json_keys and json_scan_start for parsing json documents, and I think it's possible to sort the keys using std::map in STL for objects.    That's all for my ideas so far. Please correct me if I made some mistakes, and I'm going to work on my ideas later. Cheers! Hollow Man

3 4

Re: [Maria-developers] JSON_TABLE: On name resolution question
by Sergey Petrunia 02 Apr '21

02 Apr '21

On Thu, Apr 01, 2021 at 10:14:04PM -0700, Igor Babaev wrote: > Sergey, > > Can you explain this: > > MariaDB [test]> explain format=json select * from t1 as t2 where item_name > in (select item_name from t2 as s, json_table(t2.item_props,'$' columns( > color varchar(100) path '$.color')) as t) and t2.item_name='Jeans'; > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | EXPLAIN > > > > > > > | > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | { > "query_block": { > "select_id": 1, > "table": { > "table_name": "t2", > "access_type": "ALL", > "rows": 2, > "filtered": 100, > "attached_condition": "t2.item_name = 'Jeans'" > }, > "table": { > "table_name": "s", > "access_type": "ALL", > "rows": 2, > "filtered": 100, > "attached_condition": "s.item_name = 'Jeans'" > }, > "table": { > "table_name": "t", > "access_type": "ALL", > "rows": 40, > "filtered": 100, > "table_function": "json_table", > "first_match": "t2" > } > } > } | > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > How do get get s.item_name = 'Jeans' ? > Check out the optimizer trace: First, the subquery is converted into a semi-join: "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "converted_to_semi_join": true } then, there's a multiple equality: "transformation": "equality_propagation", "resulting_condition": "1 and multiple equal('Jeans', t2.item_name, s.item_name)" and then, after substituion: "attaching_conditions_to_tables": { "original_condition": "t2.item_name = 'Jeans' and s.item_name = 'Jeans'", BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] 9abbb589e6d: MDEV-20220: Merge 5.7 P_S replication table 'replication_applier_status_by_worker
by Sergei Golubchik 01 Apr '21

01 Apr '21

Hi, Sujatha! thanks! ok to push. one question below On Apr 01, Sujatha wrote: > revision-id: 9abbb589e6d (mariadb-10.5.2-531-g9abbb589e6d) > parent(s): 04c7d5f1518 > author: Sujatha <sujatha.sivakumar(a)mariadb.com> > committer: Sujatha <sujatha.sivakumar(a)mariadb.com> > timestamp: 2021-03-29 22:57:26 +0530 > message: > > MDEV-20220: Merge 5.7 P_S replication table 'replication_applier_status_by_worker > > Step 3: > ====== > > Preserve worker pool information on either STOP SLAVE/Error. In case STOP > SLAVE is executed worker threads will be gone, hence worker threads will be > unavailable. Querying the table at this stage will give empty rows. To > address this case when worker threads are about to stop, due to an error or > forced stop, create a backup pool and preserve the data which is relevant to > populate performance schema table. Clear the backup pool upon slave start. > > > diff --git a/mysql-test/suite/perfschema/r/dml_handler.result b/mysql-test/suite/perfschema/r/dml_handler.result > index ce4ae6bf9a3..61bbba3189e 100644 > --- a/mysql-test/suite/perfschema/r/dml_handler.result > +++ b/mysql-test/suite/perfschema/r/dml_handler.result > @@ -12,78 +12,81 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be > # For each table in the performance schema, attempt HANDLER...OPEN, > # which should fail with an error 1031, ER_ILLEGAL_HA. > # ... > ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`replication_connection_configuration` doesn't have this option > +SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=57; > +HANDLER performance_schema.replication_applier_status_by_worker OPEN; > +ERROR HY000: Storage engine PERFORMANCE_SCHEMA of the table `performance_schema`.`replication_applier_status_by_worker` doesn't have this option > SELECT TABLE_NAME INTO @table_name FROM table_list WHERE id=56; > diff --git a/mysql-test/suite/perfschema/r/information_schema.result b/mysql-test/suite/perfschema/r/information_schema.result > index 3f4a8cf6336..c0aff9bb956 100644 > --- a/mysql-test/suite/perfschema/r/information_schema.result > +++ b/mysql-test/suite/perfschema/r/information_schema.result > @@ -59,6 +59,7 @@ performance_schema prepared_statements_instances def > performance_schema replication_applier_configuration def > performance_schema replication_applier_status def > performance_schema replication_applier_status_by_coordinator def > +performance_schema replication_applier_status_by_worker def > performance_schema replication_connection_configuration def > performance_schema rwlock_instances def > performance_schema session_account_connect_attrs def etc, etc. many tests showing that this commit adds a new performance_schema.replication_applier_status_by_worker table. but looking at the commit, I don't think it does. incorrectly split commits, perhaps? Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0