- developers - lists.mariadb.org

[Maria-developers] Account locked and USERS view?
by Martijn Tonies (Upscene Productions) 06 Jul '22

06 Jul '22

Hi, Any reason why the ‘account locked’ attribute isn’t included in the USERS view? https://mariadb.com/kb/en/account-locking/ It could be added like this: if(json_value(mysql.global_priv.Priv, '$.account_locked') = 1, 'Y', 'N') AS is_locked With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] MDEV-28881 Server crashes in Dep_analysis_context::create_table_value
by Sergey Petrunia 05 Jul '22

05 Jul '22

Hi Oleg, > commit 812f8cbfb3a7a02705f65d8bb7e30ae2faf13b5d > Author: Oleg Smirnov <olernov(a)gmail.com> > Date: Tue Jun 21 16:20:17 2022 +0700 > > MDEV-28881 Server crashes in Dep_analysis_context::create_table_value > > SELECT_LEX::first_select()->join may be NULL for some scenarios. > This commit adds a check for this. Looking into Jira I can infer that the patch fixes two issues, but the commit comment doesn't say that. Please avoid doing this in the future. Please split the commit into two: - first one fixing the SELECT_LEX::first_select()->join == NULL part - the second fixing the memory management. For the second part, I think it's not a good approach when most of the Table Elimination data resides in a MEM_ROOT with exception of std::set which keeps its data on the heap. I was wondering if there's a way to get STL containers to reside on MEM_ROOT as well, and found that yes there is, and MySQL actually already has it: https://dev.mysql.com/worklog/task/?id=6074 https://github.com/mysql/mysql-server/blob/8.0/sql/mem_root_allocator.h Can we copy mem_root_allocator.h from them (in a separate commit) and then fix memory management here by making std::set use it? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] 9ec0b36ee8b: MDEV-28838 password_reuse_check plugin mixes username and password
by Sergei Golubchik 04 Jul '22

04 Jul '22

Hi, Oleksandr, On Jul 04, Oleksandr Byelkin wrote: > revision-id: 9ec0b36ee8b (mariadb-10.7.4-27-g9ec0b36ee8b) > parent(s): ac0af4ec4ab > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-06-29 17:25:44 +0200 > message: > > MDEV-28838 password_reuse_check plugin mixes username and password > > To prevent the problem of mixing user name and password and > host name and user name we add length of the hostname and user name > to the hash. > > diff --git a/plugin/password_reuse_check/password_reuse_check.c b/plugin/password_reuse_check/password_reuse_check.c > index ff0364ce007..4d07cda878a 100644 > --- a/plugin/password_reuse_check/password_reuse_check.c > +++ b/plugin/password_reuse_check/password_reuse_check.c > @@ -165,9 +166,11 @@ static int validate(const MYSQL_CONST_LEX_STRING *username, > return 1; > } > > - memcpy(buff, hostname->str, hostname->length); > - memcpy(buff + hostname->length, username->str, username->length); > - memcpy(buff + hostname->length + username->length, password->str, > + int2store(buff, hostname->length); > + int2store(buff + 2, username->length); > + memcpy(buff + 4, hostname->str, hostname->length); > + memcpy(buff + 4 + hostname->length, username->str, username->length); > + memcpy(buff + 4 + hostname->length + username->length, password->str, > password->length); why? let's use the standard length,string format, why risk inventing something unusual here for no benefits at all? I'd do like: static char *store_str(char *to, MYSQL_CONST_LEX_STRING from) { int2store(to, from.length); memcpy(to+2, from.str, from.length); return to+2+from.length; } and buff= store_str(buff, hostname); buff= store_str(buff, username); buff= store_str(buff, password); > buff[key_len]= 0; > memset(hash, 0, sizeof(hash)); > @@ -235,7 +238,7 @@ maria_declare_plugin(password_reuse_check) > 0x0100, ^^^ this should be 0x0101 (or 0x0200 as I suggest below) > NULL, > sysvars, > - "1.0", > + "1.1", make it 2.0 please, this is an incompatible change > MariaDB_PLUGIN_MATURITY_GAMMA > } > maria_declare_plugin_end; > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] e9293e31ec6: MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE
by Sergei Golubchik 30 Jun '22

30 Jun '22

Hi, Nikita, This is very good. Just one comment, see below: On Jun 29, Nikita Malyavin wrote: > revision-id: e9293e31ec6 (mariadb-10.6.1-499-ge9293e31ec6) > parent(s): 6cfcb36d027 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2022-06-29 20:16:19 +0300 > message: > > MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE > > ALTER ONLINE TABLE acquires table with TL_READ. Myisam normally acquires > TL_WRITE for DML, which makes it hang until table is freed. > > We deadlock once ALTER upgrades its MDL lock. > > Solution: > Unlock table earlier. We don't need to hold TL_READ once we finished > copying. Relay log replication requires no data locks on `from` table. > > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > index 425de469a7f..90f6283d9df 100644 > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -5734,7 +5734,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > used in the transaction and proceed with execution of the actual > event. > */ > - if (!thd->lock) > + if (!thd->lock && rgi->tables_to_lock_count) > { > /* > Lock_tables() reads the contents of thd->lex, so they must be Instead of adding a second condition to the execution path for every row event in the replication, consider this: - if (!thd->lock) + if (!thd->open_tables) this will work identically for replication, I hope, but will be always false in your case, as you still have the table open. I mean, that's the idea. I run the rpl suite with it, but haven't done any further testing, so not completely sure it'll work. When making this change, don't forget to update the comment right above this if(). Ok to push into bb-10.10-MDEV-16329 after that. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

[Maria-developers] How to know when a FUNCTION is AGGREGATE?
by Martijn Tonies (Upscene Productions) 17 Jun '22

17 Jun '22

Hi all, Since MariaDB 10.3, you can use an AGGREGATE stored function. https://mariadb.com/kb/en/stored-aggregate-functions/ When examining the meta data, how does one distinguish between aggregate and normal stored functions? Can’t find anything in the information_schema about this. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

3 3

Re: [Maria-developers] 49ecf935415: MDEV-27009 Add UCA-14.0.0 collations
by Sergei Golubchik 16 Jun '22

16 Jun '22

Hi, Alexander, On Mar 14, Alexander Barkov wrote: > revision-id: 49ecf935415 (mariadb-10.6.1-335-g49ecf935415) > parent(s): c67789f63c8 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-02-28 14:04:58 +0400 > message: > > MDEV-27009 Add UCA-14.0.0 collations please, list all user visible changes there. Mainly that collations are now decoupled from charsets. New syntax in CREATE TABLE, changes in I_S tables, etc. > diff --git a/mysql-test/include/ctype_utf_uca1400_ids.inc b/mysql-test/include/ctype_utf_uca1400_ids.inc > new file mode 100644 > index 00000000000..09cf49fc0e7 > --- /dev/null > +++ b/mysql-test/include/ctype_utf_uca1400_ids.inc > @@ -0,0 +1,17 @@ file names are confusing. better rename ctype_ucs_uca1400_ids.inc to something like ctype_convert_uca1400_ids and ctype_utf_uca1400_ids to ctype_set_names_uca1400_ids or something like that, to show what they do. > + > +--disable_ps_protocol > +--enable_metadata > +DELIMITER $$; > +FOR rec IN (SELECT COLLATION_NAME > + FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY > + WHERE CHARACTER_SET_NAME=@charset > + AND COLLATION_NAME RLIKE 'uca1400' > + ORDER BY ID) > +DO > + EXECUTE IMMEDIATE CONCAT('SET NAMES ',@charset,' COLLATE ', rec.COLLATION_NAME); > + SELECT rec.COLLATION_NAME; > +END FOR; > +$$ > +DELIMITER ;$$ > +--disable_metadata > +--enable_ps_protocol > diff --git a/include/m_ctype.h b/include/m_ctype.h > index 4c6628b72b3..706764ead2a 100644 > --- a/include/m_ctype.h > +++ b/include/m_ctype.h > @@ -34,7 +34,9 @@ enum loglevel { > extern "C" { > #endif > > -#define MY_CS_NAME_SIZE 32 > +#define MY_CS_CHARACTER_SET_NAME_SIZE 32 > +#define MY_CS_COLLATION_NAME_SIZE 64 That's FULL_COLLATION_NAME_SIZE, right? > + > #define MY_CS_CTYPE_TABLE_SIZE 257 > #define MY_CS_TO_LOWER_TABLE_SIZE 256 > #define MY_CS_TO_UPPER_TABLE_SIZE 256 > @@ -240,6 +242,46 @@ typedef enum enum_repertoire_t > } my_repertoire_t; > > > +/* ID compatibility */ > +typedef enum enum_collation_id_type > +{ > + MY_COLLATION_ID_TYPE_PRECISE= 0, > + MY_COLLATION_ID_TYPE_COMPAT_100800= 1 > +} my_collation_id_type_t; > + > + > +/* Collation name display modes */ > +typedef enum enum_collation_name_mode > +{ > + MY_COLLATION_NAME_MODE_FULL= 0, > + MY_COLLATION_NAME_MODE_CONTEXT= 1 > +} my_collation_name_mode_t; > + > + > +/* Level flags */ > +#define MY_CS_LEVEL_BIT_PRIMARY 0x00 > +#define MY_CS_LEVEL_BIT_SECONDARY 0x01 > +#define MY_CS_LEVEL_BIT_TERTIARY 0x02 > +#define MY_CS_LEVEL_BIT_QUATERNARY 0x03 > + > +#define MY_CS_COLL_LEVELS_S1 (1<<MY_CS_LEVEL_BIT_PRIMARY) > + > +#define MY_CS_COLL_LEVELS_AI_CS (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) > + > +#define MY_CS_COLL_LEVELS_S2 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) > + > +#define MY_CS_COLL_LEVELS_S3 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) | \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) AI_CS and S3 don't seem to be used yet > + > +#define MY_CS_COLL_LEVELS_S4 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) | \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) | \ > + (1<<MY_CS_LEVEL_BIT_QUATERNARY) > + > + > /* Flags for strxfrm */ > #define MY_STRXFRM_LEVEL1 0x00000001 /* for primary weights */ > #define MY_STRXFRM_LEVEL2 0x00000002 /* for secondary weights */ > diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc > index 86c6e9a27f8..9ddd482ad57 100644 > --- a/sql/sql_alter.cc > +++ b/sql/sql_alter.cc > @@ -546,6 +546,7 @@ bool Sql_cmd_alter_table::execute(THD *thd) > > result= mysql_alter_table(thd, &select_lex->db, &lex->name, > &create_info, > + lex->create_info.default_charset_collation, I don't see why you need a new argument here. It's create_info.default_charset_collation, so, mysql_alter_table already gets it in create_info. All other mysql_alter_table invocations also take create_info argument and can get default_charset_collation from there > first_table, > &alter_info, > select_lex->order_list.elements, > diff --git a/sql/sql_partition_admin.cc b/sql/sql_partition_admin.cc > index fb1ae0d5fc7..4188dde252b 100644 > --- a/sql/sql_partition_admin.cc > +++ b/sql/sql_partition_admin.cc > @@ -211,6 +211,7 @@ bool compare_table_with_partition(THD *thd, TABLE *table, TABLE *part_table, > part_table->use_all_columns(); > table->use_all_columns(); > if (unlikely(mysql_prepare_alter_table(thd, part_table, &part_create_info, > + Lex_maybe_default_charset_collation(), Same. Can be in part_create_info > &part_alter_info, &part_alter_ctx))) > { > my_error(ER_TABLES_DIFFERENT_METADATA, MYF(0)); > diff --git a/sql/sql_i_s.h b/sql/sql_i_s.h > index bed2e886718..5ff06d32231 100644 > --- a/sql/sql_i_s.h > +++ b/sql/sql_i_s.h > @@ -162,6 +162,11 @@ class Yesno: public Varchar > { > public: > Yesno(): Varchar(3) { } > + static LEX_CSTRING value(bool val) > + { > + return val ? Lex_cstring(STRING_WITH_LEN("Yes")) : > + Lex_cstring(); > + } eh... please, rename the class from Yesno to something like Yesempty or Yes_or_empty, something that says that the second should not be Lex_cstring(STRING_WITH_LEN("No")) > }; > > > diff --git a/sql/table.cc b/sql/table.cc > index a683a78ff49..c28cb2bd928 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -3491,6 +3493,16 @@ int TABLE_SHARE::init_from_sql_statement_string(THD *thd, bool write, > else > thd->set_n_backup_active_arena(arena, &backup); > > + /* > + THD::reset_db() does not set THD::db_charset, > + so it keeps pointing to the character set and collation > + of the current database, rather than the database of the > + new initialized table. Hmm, is that correct? Could you check other invocation of thd->reset_db()? Perhaps they all need to switch charset? In that case it should be done inside THD::reset_db(). Or may be they have to use mysql_change_db_impl() instead? > + Let's call get_default_db_collation() before reset_db(). > + This forces the db.opt file to be loaded. > + */ > + db_cs= get_default_db_collation(thd, db.str); > + > thd->reset_db(&db); > lex_start(thd); > > @@ -3498,6 +3510,11 @@ int TABLE_SHARE::init_from_sql_statement_string(THD *thd, bool write, > sql_unusable_for_discovery(thd, hton, sql_copy)))) > goto ret; > > + if (!(thd->lex->create_info.default_table_charset= > + thd->lex->create_info.default_charset_collation. > + resolved_to_character_set(db_cs, db_cs))) > + DBUG_RETURN(true); How could this (and similar if()'s in other files) fail? > + > thd->lex->create_info.db_type= hton; > #ifdef WITH_PARTITION_STORAGE_ENGINE > thd->work_part_info= 0; // For partitioning > diff --git a/sql/mysys_charset.h b/sql/mysys_charset.h > new file mode 100644 > index 00000000000..86eaeedd432 > --- /dev/null > +++ b/sql/mysys_charset.h > @@ -0,0 +1,44 @@ > +#ifndef MYSYS_CHARSET > +#define MYSYS_CHARSET > + > +/* Copyright (c) 2021, MariaDB Corporation. > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ > + > + > +#include "my_sys.h" > + > + > +class Charset_loader_mysys: public MY_CHARSET_LOADER > +{ > +public: > + Charset_loader_mysys() > + { > + my_charset_loader_init_mysys(this); > + } > + void raise_unknown_collation_error(const char *name, > + CHARSET_INFO *name_cs) const; > + CHARSET_INFO *get_charset(const char *cs_name, uint cs_flags, myf my_flags); > + CHARSET_INFO *get_exact_collation(const char *name, myf utf8_flag); > + CHARSET_INFO *get_contextually_typed_collation(CHARSET_INFO *cs, > + const char *name); > + CHARSET_INFO *get_contextually_typed_collation(const char *name); > + CHARSET_INFO *get_contextually_typed_collation_or_error(CHARSET_INFO *cs, > + const char *name); > + CHARSET_INFO *find_default_collation(CHARSET_INFO *cs); > + CHARSET_INFO *find_bin_collation_or_error(CHARSET_INFO *cs); > +}; you can have C++ code in mysys too, you know, no need to put it in sql/mysys* > + > +#endif // MYSYS_CHARSET > + > diff --git a/strings/ctype-simple.c b/strings/ctype-simple.c > index b579f0af203..d09dfba86ed 100644 > --- a/strings/ctype-simple.c > +++ b/strings/ctype-simple.c > @@ -1940,13 +1941,26 @@ my_bool my_propagate_complex(CHARSET_INFO *cs __attribute__((unused)), > } > > > +void my_ci_set_strength(struct charset_info_st *cs, uint strength) > +{ > + DBUG_ASSERT(strength > 0 && strength <= MY_STRXFRM_NLEVELS); don't use && in asserts, please create two separate asserts instead: DBUG_ASSERT(strength > 0); DBUG_ASSERT(strength <= MY_STRXFRM_NLEVELS); > + cs->levels_for_order= ((1 << strength) - 1); why do you still use the old concept of "strength"? Why not to use bitmap consistently everywhere? > +} > + > + > +void my_ci_set_level_flags(struct charset_info_st *cs, uint flags) > +{ > + DBUG_ASSERT(flags < (1<<MY_STRXFRM_NLEVELS)); > + cs->levels_for_order= flags; > +} > + > /* > Normalize strxfrm flags > > SYNOPSIS: > my_strxfrm_flag_normalize() > + cs - the CHARSET_INFO pointer > flags - non-normalized flags > - nlevels - number of levels > > NOTES: > If levels are omitted, then 1-maximum is assumed. > diff --git a/sql/handler.h b/sql/handler.h > index 8ad521e189a..1e82f37b1e7 100644 > --- a/sql/handler.h > +++ b/sql/handler.h > @@ -2409,7 +2386,32 @@ struct Table_specification_st: public HA_CREATE_INFO, > { > HA_CREATE_INFO::options= 0; > DDL_options_st::init(); > + default_charset_collation.init(); > + } > + > + bool > + add_alter_list_item_convert_to_charset(const Lex_charset_collation_st &cl) > + { > + /* > + cs cannot be NULL, as sql_yacc.yy translates > + CONVERT TO CHARACTER SET DEFAULT > + to > + CONVERT TO CHARACTER SET <character-set-of-the-current-database> > + TODO: Shouldn't we postpone resolution of DEFAULT until the > + character set of the table owner database is loaded from its db.opt? > + */ > + DBUG_ASSERT(cl.charset_collation()); > + DBUG_ASSERT(!cl.is_contextually_typed_collation()); > + alter_table_convert_to_charset= cl.charset_collation(); > + default_charset_collation.Lex_charset_collation_st::operator=(cl); looks quite ugly. can you do, like, default_charset_collation.set(cl) ? > + used_fields|= (HA_CREATE_USED_CHARSET | HA_CREATE_USED_DEFAULT_CHARSET); > + return false; > } > + bool add_table_option_default_charset(CHARSET_INFO *cs); > + bool add_table_option_default_collation(const Lex_charset_collation_st &cl); > + bool resolve_db_charset_and_collation(THD *thd, > + const LEX_CSTRING &db, > + bool is_alter); > }; > > > diff --git a/strings/ctype-uca1400data.h b/strings/ctype-uca1400data.h > new file mode 100644 > index 00000000000..da95dcfde54 > --- /dev/null > +++ b/strings/ctype-uca1400data.h > @@ -0,0 +1,44151 @@ > +/* > + Generated from allkeys.txt version '14.0.0' > +*/ if it's generated, do you need to check it in? perhaps it should be generated during the build? you've checked in allkeys1400.txt anyway. > +static const uint16 uca1400_p000[]= { /* 0000 (4 weights per char) */ > +0x0000,0x0000,0x0000,0x0000, 0x0000,0x0000,0x0000,0x0000, /* 0000 */ > +0x0000,0x0000,0x0000,0x0000, 0x0000,0x0000,0x0000,0x0000, /* 0002 */ > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 6ca10267187..d115401a855 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -542,6 +542,30 @@ bool LEX::add_alter_list(LEX_CSTRING name, LEX_CSTRING new_name, bool exists) > } > > > +bool LEX::add_alter_list_item_convert_to_charset( > + THD *thd, > + CHARSET_INFO *cs, > + const Lex_charset_collation_st &cl) > +{ > + if (!cs) > + { > + Lex_charset_collation_st tmp; > + tmp.set_charset_collate_default(thd->variables.collation_database); Hmm, what if one is doing ALTER TABLE db.test CHARSET DEFAULT and current db is not `db` but `test` ? > + if (!(cs= tmp.charset_collation())) > + return true; // Should not actually happen assert? > + } > + > + Lex_explicit_charset_opt_collate tmp(cs, false); > + if (tmp.merge_opt_collate_or_error(cl) || > + create_info.add_alter_list_item_convert_to_charset( > + Lex_charset_collation(tmp))) > + return true; > + > + alter_info.flags|= ALTER_CONVERT_TO; > + return false; > +} > + > + > void LEX::init_last_field(Column_definition *field, > const LEX_CSTRING *field_name) > { > @@ -11871,29 +11869,41 @@ CHARSET_INFO *Lex_collation_st::find_default_collation(CHARSET_INFO *cs) > "def" is the upper level CHARACTER SET clause (e.g. of a table) > */ > CHARSET_INFO * > -Lex_collation_st::resolved_to_character_set(CHARSET_INFO *def) const > +Lex_charset_collation_st::resolved_to_character_set(CHARSET_INFO *def) const > { > DBUG_ASSERT(def); > - if (m_type != TYPE_CONTEXTUALLY_TYPED) > - { > - if (!m_collation) > - return def; // Empty - not typed at all > - return m_collation; // Explicitly typed > + > + switch (m_type) { > + case TYPE_EMPTY: > + return def; > + case TYPE_CHARACTER_SET: > + DBUG_ASSERT(m_ci); > + return m_ci; > + case TYPE_COLLATE_EXACT: > + DBUG_ASSERT(m_ci); > + return m_ci; > + case TYPE_COLLATE_CONTEXTUALLY_TYPED: > + break; > } > > // Contextually typed > - DBUG_ASSERT(m_collation); > + DBUG_ASSERT(m_ci); > > - if (m_collation == &my_charset_bin) // CHAR(10) BINARY > - return find_bin_collation(def); > + Charset_loader_mysys loader; > + if (is_contextually_typed_binary_style()) // CHAR(10) BINARY > + return loader.find_bin_collation_or_error(def); > > - if (m_collation == &my_charset_latin1) // CHAR(10) COLLATE DEFAULT > - return find_default_collation(def); > + if (is_contextually_typed_collate_default()) // CHAR(10) COLLATE DEFAULT > + return loader.find_default_collation(def); > + > + const LEX_CSTRING context_name= collation_name_context_suffix(); I'd rather put this in assert, not in if(). Like - if (!strncasecmp(context_name.str, STRING_WITH_LEN("uca1400_"))) + DBUG_ASSERT(!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))); > + if (!strncasecmp(context_name.str, STRING_WITH_LEN("uca1400_"))) > + return loader.get_contextually_typed_collation_or_error(def, > + context_name.str); > > /* > - Non-binary and non-default contextually typed collation. > + Non-binary, non-default, non-uca1400 contextually typed collation. > We don't have such yet - the parser cannot produce this. > - But will have soon, e.g. "uca1400_as_ci". > */ > DBUG_ASSERT(0); > return NULL; > @@ -11944,58 +11972,106 @@ bool Lex_collation_st:: > CHAR(10) BINARY .. COLLATE latin1_bin > CHAR(10) COLLATE uca1400_as_ci .. COLLATE latin1_bin > */ > - if (collation() == &my_charset_latin1 && > - !(cl.collation()->state & MY_CS_PRIMARY)) > + if (is_contextually_typed_collate_default() && > + !cl.charset_collation()->default_flag()) > { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "COLLATE ", "DEFAULT", "COLLATE ", > - cl.collation()->coll_name.str); > + error_conflicting_collations_or_styles(*this, cl); > return true; > } > - if (collation() == &my_charset_bin && > - !(cl.collation()->state & MY_CS_BINSORT)) > + > + if (is_contextually_typed_binary_style() && > + !cl.charset_collation()->binsort_flag()) > { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "", "BINARY", "COLLATE ", cl.collation()->coll_name.str); > + error_conflicting_collations_or_styles(*this, cl); > return true; > } > *this= cl; > return false; > } > > - if (cl.is_contextually_typed_collation()) > - { > + DBUG_ASSERT(0); > + return false; > +} > + > + > +bool > +Lex_explicit_charset_opt_collate:: > + merge_collate_or_error(const Lex_charset_collation_st &cl) > +{ > + DBUG_ASSERT(cl.type() != Lex_charset_collation_st::TYPE_CHARACTER_SET); > + > + switch (cl.type()) { > + case Lex_charset_collation_st::TYPE_EMPTY: > + return false; > + case Lex_charset_collation_st::TYPE_CHARACTER_SET: > + DBUG_ASSERT(0); > + return false; > + case Lex_charset_collation_st::TYPE_COLLATE_EXACT: > /* > - EXPLICIT + CONTEXT > - CHAR(10) COLLATE latin1_bin .. COLLATE DEFAULT - not supported > - CHAR(10) COLLATE latin1_bin .. COLLATE uca1400_as_ci - not yet > + EXPLICIT + EXPLICIT > + CHAR(10) CHARACTER SET latin1 .. COLLATE latin1_bin > + CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) CHARACTER SET latin1 BINARY .. COLLATE latin1_bin > */ > - DBUG_ASSERT(0); // Not possible yet > + if (m_with_collate && m_ci != cl.charset_collation()) > + { > + my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > + "COLLATE ", m_ci->coll_name.str, > + "COLLATE ", cl.charset_collation()->coll_name.str); > + return true; > + } > + if (!my_charset_same(m_ci, cl.charset_collation())) > + { > + my_error(ER_COLLATION_CHARSET_MISMATCH, MYF(0), > + cl.charset_collation()->coll_name.str, m_ci->cs_name.str); > + return true; > + } > + m_ci= cl.charset_collation(); > + m_with_collate= true; > return false; > - } > > - /* > - EXPLICIT + EXPLICIT > - CHAR(10) CHARACTER SET latin1 .. COLLATE latin1_bin > - CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) CHARACTER SET latin1 BINARY .. COLLATE latin1_bin > - */ > - if (type() == TYPE_EXPLICIT && collation() != cl.collation()) > - { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "COLLATE ", collation()->coll_name.str, > - "COLLATE ", cl.collation()->coll_name.str); > - return true; > - } > - if (!my_charset_same(collation(), cl.collation())) > - { > - my_error(ER_COLLATION_CHARSET_MISMATCH, MYF(0), > - cl.collation()->coll_name.str, collation()->cs_name.str); > - return true; > + case Lex_charset_collation_st::TYPE_COLLATE_CONTEXTUALLY_TYPED: > + if (cl.is_contextually_typed_collate_default()) > + { > + /* > + SET NAMES latin1 COLLATE DEFAULT; > + ALTER TABLE t1 CONVERT TO CHARACTER SET latin1 COLLATE DEFAULT; > + */ > + CHARSET_INFO *tmp= Charset_loader_mysys().find_default_collation(m_ci); > + if (!tmp) > + return true; > + m_ci= tmp; > + m_with_collate= true; > + return false; > + } > + else > + { > + /* > + EXPLICIT + CONTEXT > + CHAR(10) COLLATE latin1_bin .. COLLATE DEFAULT not possible yet > + CHAR(10) COLLATE latin1_bin .. COLLATE uca1400_as_ci > + */ > + > + const LEX_CSTRING context_cl_name= cl.collation_name_context_suffix(); > + if (!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))) Like above, better DBUG_ASSERT(!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))) > + { > + CHARSET_INFO *tmp; > + Charset_loader_mysys loader; > + if (!(tmp= loader.get_contextually_typed_collation_or_error(m_ci, > + context_cl_name.str))) > + return true; > + m_with_collate= true; > + m_ci= tmp; > + return false; > + } > + > + DBUG_ASSERT(0); // Not possible yet > + return false; > + } > } > - *this= cl; > + DBUG_ASSERT(0); > return false; > } > > diff --git a/strings/ctype-uca.c b/strings/ctype-uca.c > index b89916f3b20..3e6b4e4ce43 100644 > --- a/strings/ctype-uca.c > +++ b/strings/ctype-uca.c > @@ -30542,7 +30613,7 @@ static const char vietnamese[]= > Myanmar, according to CLDR Revision 8900. > http://unicode.org/cldr/trac/browser/trunk/common/collation/my.xml > */ > -static const char myanmar[]= "[shift-after-method expand][version 5.2.0]" > +static const char myanmar[]= "[shift-after-method expand]" What's going on with myanmar? You removed a version here and added &my_uca_v520 below in its charset_info_st. What does this change mean? > /* Tones */ > "&\\u108C" > "<\\u1037" > @@ -37627,7 +37825,7 @@ struct charset_info_st my_charset_utf32_myanmar_uca_ci= > NULL, /* to_lower */ > NULL, /* to_upper */ > NULL, /* sort_order */ > - NULL, /* uca */ > + &my_uca_v520, /* uca */ What does this change? > NULL, /* tab_to_uni */ > NULL, /* tab_from_uni */ > &my_unicase_unicode520,/* caseinfo */ Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

[Maria-developers] Review #3 for: MDEV-26278 Elimination: consider GROUP BY as unique key
by Sergey Petrunia 13 Jun '22

13 Jun '22

Hi Oleg, Please fix compile errors on this platform: https://buildbot.mariadb.org/#/builders/146/builds/13126/steps/5/logs/stdio also please apply the attached diff that fixes the coding style and wording of comments (input and/or adjustments are welcome). After this, the patch is ok to push. The next steps are to create a branch with name like preview-10.10-mdev26278-table-elimination and to officially submit it for preview tree build and testing. > commit 7275bf4939248959b5017314d6b0393296831c0c (HEAD, origin/bb-10.9-MDEV-26278-look) > Author: Oleg Smirnov <olernov(a)gmail.com> > Date: Thu Feb 17 22:53:37 2022 +0700 > > MDEV-26278 Add functionality to eliminate derived tables from the query > > Elimination of unnecessary tables from SQL queries is already present > in MariaDB. But it only works for regular tables and not for derived ones. > > Imagine we have a view: > CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b > > Due to "GROUP BY a, b" the values of combinations {a, b} are unique, > and this fact can be treated as like derived table "v1" has a unique key > on fields {a, b}. > > Suppose we have a SQL query: > SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b > > 1. Since {v1.a, v1.b} is unique and both these fields are bound to t2, > "v1" is functionally dependent on t2. > This means every record of "t2" will be either joined with > a single record of "v1" or NULL-complemented. > 2. No fields of "v1" are present on the SELECT list > > These two facts allow the server to completely exclude (eliminate) > the derived table "v1" from the query. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

2 1

Re: [Maria-developers] 8c2c0e6079a: MDEV-27265 Improve contraction performance in UCA collations
by Sergei Golubchik 11 Jun '22

11 Jun '22

Hi, Alexander, Why haven't you used HASH? On Jun 11, Alexander Barkov wrote: > revision-id: 8c2c0e6079a (mariadb-10.9.1-13-g8c2c0e6079a) > parent(s): 27fa5c10eff > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-05-26 13:17:34 +0400 > message: > > MDEV-27265 Improve contraction performance in UCA collations > > Adding a hash table for contractions. > > The old code iterated through all items in MY_CONTRACTIONS, > and was much slower, especially for those contractions > in the end of the list. > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] MDEV-26278 Elimination: consider GROUP BY as unique key
by Sergey Petrunia 09 Jun '22

09 Jun '22

Hi Oleg, Please find the review below. > commit 20609d374b3a82c46291ee5e87cfadd26de47817 > Author: Oleg Smirnov <olernov(a)gmail.com> > Date: Thu Feb 17 22:53:37 2022 +0700 > > MDEV-26278 Elimination: consider GROUP BY as unique key Please provide a more verbose description. We should aim for it to provide sufficient info for one to understand what the optimization does and how that is achieved. Some general comments: The patch uses this formatting for multi-line comments: > + /* GROUP BY expression is considered as a synthetic "unique key" > + for the derived table. Add this key as a dependency */ While the coding style and actual MariaDB code would use this: /* GROUP BY expression is considered as a synthetic "unique key" for the derived table. Add this key as a dependency */ Please use this style for all comments. Also, the patch uses the term "synthetic unique key". I think the term is not very good: It can be confused with "synthetic primary key", also I read "synthetic" as something that actually exists, while in our case the key doesn't exist. Would using "Pseudo-key" be better? > diff --git a/sql/table.h b/sql/table.h > index 497502b2d06..fd598556cc3 100644 > --- a/sql/table.h > +++ b/sql/table.h > @@ -2872,6 +2872,9 @@ struct TABLE_LIST > } > } > > + void mark_derived_as_eliminated() { m_is_derived_eliminated= true; } > + bool is_derived_eliminated() const { return m_is_derived_eliminated; } > + So, TABLE_LIST objects are created non-eliminated and then they are eliminated, and that is forever. TABLE_LIST object has the same lifetime as the statement, that is, it will survive multiple executions of a prepared statement. On the other hand, Table Elimination optimization is done in eliminate_tables() call which is invoked for every statement execution. This looks like a dangerous mismatch. I'll continue below > private: > bool prep_check_option(THD *thd, uint8 check_opt_type); > bool prep_where(THD *thd, Item **conds, bool no_where_clause); > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 3bf6d9abf57..02ef5cd7ad7 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -11786,7 +11786,7 @@ bool SELECT_LEX_UNIT::explainable() const > EXPLAIN/ANALYZE unit, when: > (1) if it's a subquery - it's not part of eliminated WHERE/ON clause. > (2) if it's a CTE - it's not hanging (needed for execution) > - (3) if it's a derived - it's not merged > + (3) if it's a derived - it's not merged or eliminated > if it's not 1/2/3 - it's some weird internal thing, ignore it > */ > return item ? > @@ -11795,6 +11795,7 @@ bool SELECT_LEX_UNIT::explainable() const > derived && derived->derived_result && > !with_element->is_hanging_recursive(): // (2) > derived ? > - derived->is_materialized_derived() : // (3) > + derived->is_materialized_derived() && // (3) > + !derived->is_derived_eliminated() : > false; > } I think, there's no need for m_is_derived_eliminated. You can have this check in is_derived_eliminated(): this->derived->table->map & this->outer_select()->join->eliminated_tables Please try doing this. > diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result > index deff0623370..842330f3713 100644 > --- a/mysql-test/main/table_elim.result > +++ b/mysql-test/main/table_elim.result > @@ -704,3 +704,252 @@ LIMIT 1; > PostID Voted > 1 NULL > DROP TABLE t1,t2; > +# > +# MDEV-26278: Table elimination does not work across derived tables > +# > +create table t1 (a int, b int); > +insert into t1 select seq, seq+10 from seq_1_to_10; > +create table t11 ( > +a int not null, > +b int, > +key(a) > +); > +insert into t11 select A.seq, A.seq+B.seq > +from > +seq_1_to_100 A, > +seq_1_to_1000 B; Do you really need 100K rows table? I think 10K rows will be just as good. > diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc > index 8c4720bdec4..fe2155191ca 100644 > --- a/sql/opt_table_elimination.cc > +++ b/sql/opt_table_elimination.cc ... > @@ -278,6 +286,8 @@ class Dep_value_field : public Dep_value > Dep_module_key *key_dep; > /* Otherwise, this and advance */ > uint equality_no; > + > + Dep_module_synthetic_key *synth_key_dep; Please add a comment for the above that we should try that one, too. @@ -447,6 +463,55 @@ const size_t Dep_module::iterator_size= > MY_MAX(Dep_module_expr::iterator_size, Dep_module_key::iterator_size); > > > +/* A synthetic unique key module for a derived table. > + For example, a derived table > + SELECT t11.a, count(*) from t1 LEFT JOIN t2 ON t1.id = t2.fk GROUP BY t11.a > + has unique values in its first field (t11.a) due to GROUP BY expression > + so this can be considered as a unique key for this derived table > +*/ > + > +class Dep_module_synthetic_key : public Dep_module > +{ > +public: > + Dep_module_synthetic_key(Dep_value_table *table_arg, > + std::vector<field_index_t>&& field_indexes) > + : table(table_arg), derived_table_field_indexes(field_indexes) > + { > + unbound_args= static_cast<uint>(field_indexes.size()); > + } > + > + Dep_value_table * table; Coding style: "*table". ... > @@ -1603,7 +1674,73 @@ Dep_value_table *Dep_analysis_context::create_table_value(TABLE *table) > key_list= &(key_dep->next_table_key); > } > } > - return table_deps[table->tablenr]= tbl_dep; > + > + auto select_unit= table_list->get_unit(); > + SELECT_LEX *first_select= nullptr; > + if (select_unit) Please move the code that creates the pseudo-key into a separate function. ... > + > +int Dep_analysis_context::find_field_in_list(List<Item> &fields_list, > + Item *field) Please add a one-line comment describing what function does. > @@ -1786,6 +1941,18 @@ Dep_module* Dep_value_field::get_next_unbound_module(Dep_analysis_context *dac, > } > else > di->key_dep= NULL; > + > + Dep_module_synthetic_key *synth_key_dep= di->synth_key_dep; > + if (synth_key_dep && !synth_key_dep->is_applicable() && > + std::find(synth_key_dep->derived_table_field_indexes.begin(), > + synth_key_dep->derived_table_field_indexes.end(), > + field->field_index) != std::end(synth_key_dep->derived_table_field_indexes)) Please introduce a method like bool Dep_module_synthetic_key::covers_field(int field_index); Will this also allow to make Dep_module_synthetic_key::derived_table_field_indexes a private member? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 1

[Maria-developers] Introduction of Weijun
by Weijun Huang 07 Jun '22

07 Jun '22

Hi everyone! My name is Weijun Huang and I was selected for GSoC to work with MariaDB. I'm from China and I am studying at Technical University of Denmark, majoring in Human-centered Artificial Intelligence. My nickname on Zulip is Weijun Huang, and on Github it's Weijun-H. My project for GSoC in 2022 is called "Add Regression Functions for MariaDB." I've already started to work with MariaDB Server. Now I'm doing the implementation of REGR_SXX, REGR_SYY, and REGR_SXY I'm looking forward to working on this project. It would be an excellent experience for me! Best regard Weijun Huang

1 0