April 2017 - developers - lists.mariadb.org

[Maria-developers] Windows line endings in MariaDB sources again (
by Otto Kekäläinen 23 Jul '19

23 Jul '19

Hello! While trying to release 10.0.26 for Debian and Ubuntu I noticed git chokes again on Windows line endings. As our .gitattributes states that by default git should clean up the line endings, why do we keep getting new Windows line endings in new code? mariadb-10.0$ cat .gitattributes # Set the default behavior, in case people don't have core.autocrlf set. * text=auto ... storage/connect/mysql-test/connect/std_data/*.txt -text mariadb-10.0.26$ find . | xargs file | grep CRLF ./storage/connect/mysql-test/connect/std_data/boyswin.txt: ASCII text, with CRLF line terminators ./storage/connect/mysql-test/connect/std_data/expenses.txt: ASCII text, with CRLF line terminators ./storage/connect/mysql-test/connect/std_data/emp.txt: ASCII text, with CRLF line terminators ./mysql-test/std_data/loaddata7.dat: ASCII text, with CRLF line terminators ./mysql-test/r/loadxml.result: ASCII text, with CRLF, LF line terminators ./mysql-test/r/perror-win.result: ASCII text, with CRLF, LF line terminators ./mysql-test/r/mysql_binary_mode.result: ASCII English text, with CRLF, LF line terminators ./mysql-test/r/func_regexp_pcre.result: UTF-8 Unicode C++ program text, with CRLF, CR, LF line terminators ./pcre/testdata/grepoutputN: ASCII text, with CRLF, CR, LF line terminators ./pcre/testdata/greppatN4: ASCII text, with CRLF line terminators This leads for me to: dpkg-source: info: building mariadb-10.0 using existing ./mariadb-10.0_10.0.26.orig.tar.gz dpkg-source: info: local changes detected, the modified files are: mariadb-10.0/storage/connect/mysql-test/connect/std_data/boyswin.txt mariadb-10.0/storage/connect/mysql-test/connect/std_data/emp.txt mariadb-10.0/storage/connect/mysql-test/connect/std_data/expenses.txt dpkg-source: error: aborting due to unexpected upstream changes, see /tmp/mariadb-10.0_10.0.26-1.diff.Yzwwhw Which means I have to do extra work with importing the upstream sources.. - Otto

2 4

Re: [Maria-developers] Mdev-10664 Add statuses about optimistic parallel replication stalls.
by Sachin Setiya 19 Apr '18

19 Apr '18

Hi Kristian! Sorry for late reply , I was on vacations. On Tue, Jan 10, 2017 at 7:31 PM, Kristian Nielsen <knielsen(a)knielsen-hq.org> wrote: > Sachin Setiya <sachin.setiya(a)mariadb.com> writes: > > >> Right, but you still need _some_ way to synchronise multiple threads > > > I used atomic builtins my_atomic_add64_explicit. > > That seems fine (but see comment below). > > > Documentation:- > > > > Slave_DDL_Events:- > > > > Description: Number of DDL statements slave have executed in > > replication channel X. > > Scope: Per Replication Channel > > Data Type: numeric > > Introduced: MariaDB 10.1 > > > > Slave_Non_Transactional_Events:- > > > > Description: Number of Non Transactional "no idea what should be the > > next word " slave have executed in replication channel X. > > Scope: Per Replication Channel > > Data Type: numeric > > Introduced: MariaDB 10.1 > > I checked, the term "event group" is used already multiple places in the > documentation, so I think it can be used here also. An event group can be a > transaction (possibly with multiple transactional DML statements), or a DDL > or non-transactional statement. > > What you are doing is introducing status variables to help understand > optimistic (and aggressive) parallel replication modes. > > In optimistic parallel replication, a non-transactional event group is not > safe to run in parallel with other non-transactional events. So that event > is made to wait for all prior event groups to reach commit state, before > being allowed to execute. However, other transactional events are not > delayed by the non-transactional event group. > > Slave_Non_Transactional_Events counts the occurences of this. > > Then in case of a DDL statement, this can potentially be unsafe to run in > parallel even with other transactional events. So for a DDL, the DDL has to > wait for all prior event groups, _and_ subsequent event groups have to wait > for the DDL. > > Slave_DDL_Events counts these occurences. > > This is what the documentation, and commit messages, should explain, I > think. > > I think the status variables describe the values for > @@default_master_connection?, this should also be explained in the > documentation. > > Why did you decide to put this information into a status variable? > Normally, > slave status is seen in SHOW SLAVE STATUS, which supports showing status > for > a particular connection without using @@default_master_connection. > > Sorry for this , I guess It was a confusion I was looking at Slave_Running this is avaliable in show status as well as show slave status. So I thought for if I want to show some information in SHOW slave status this has to be in show status. And this is wrong. Now we can see non trans events / ddl events in show slave status. Show I remove this vars from Show Status, or it is okay ? > > diff --git a/sql/log_event.h b/sql/log_event.h > > index 90900f6..579607f 100644 > > --- a/sql/log_event.h > > +++ b/sql/log_event.h > > @@ -50,6 +50,7 @@ > > #endif > > > > #include "rpl_gtid.h" > > +#include "my_atomic.h" > > Why include my_atomic.h in the header file, when you only use it in the .cc > file? > Changed. > > > + mysql_mutex_lock(&LOCK_active_mi); > > + if (master_info_index) > > + { > > + mi= master_info_index-> > > + get_master_info(&thd->variables.default_master_connection, > > + Sql_condition::WARN_LEVEL_NOTE); > > + if (mi) > > + tmp= mi->total_ddl_events; > > > + if (mi) > > + tmp= mi->total_non_trans_events; > > Better use an atomic primitive here to read the value. Taking a mutex does > not help synchronise against my_atomic_add64_explicit(). > > Changed. > > + } > > + mysql_mutex_unlock(&LOCK_active_mi); > > + if (mi) > > + *((longlong *)buff)= tmp; > > + else > > + var->type= SHOW_UNDEF; > > + > > + return 0; > > +} > > #endif /* HAVE_REPLICATION */ > > > > static int show_open_tables(THD *thd, SHOW_VAR *var, char *buff, > > > index 9365c06..a474aa5 100644 > > --- a/sql/rpl_mi.h > > +++ b/sql/rpl_mi.h > > @@ -303,6 +303,12 @@ class Master_info : public > Slave_reporting_capability > > > > /* The parallel replication mode. */ > > enum_slave_parallel_mode parallel_mode; > > + > > + /* No of DDL statements */ > > + long total_ddl_events; > > + > > + /* No of non-transactional statements*/ > > + long total_non_trans_events; > > "long" is not the appropriate type here. Certainly not since you are using > my_atomic_add64_explicit(). Use a type that is the same size on all > platforms (and why use a signed type?). > > Changed to uint64. > - Kristian. > Attached a newer version of patch , please review it. -- Regards Sachin

3 7

[Maria-developers] CREATE SEQUENCE is coming
by Michael Widenius 18 Apr '18

18 Apr '18

Hi! For those that are trying to port applications to MariaDB from other databases or need more features than the current AUTO_INCREMENT provides, the following may be of interest: First version of sequence is pushed to bb-10.2-sequence. It should be merged to bb-10.2-compatilbity and then to 10.3 shortly (1-2 weeks). What is working for the moment: CREATE OR REPLACE [TEMPORARY] SEQUENCE [IF NOT EXISTS] name [ INCREMENT [ BY | = ] increment ] [ MINVALUE [=] minvalue | NO MINVALUE ] [ MAXVALUE [=] maxvalue | NO MAXVALUE ] [ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ] ENGINE=xxx COMMENT=".." SELECT NEXT VALUE FOR sequence_name; SELECT NEXTVAL(sequence_name); SELECT PREVIOUS VALUE FOR sequence_name; SELECT LASTVAL(sequence_name); SHOW CREATE SEQUENCE sequence_name; SHOW CREATE TABLE sequence_name; CREATE TABLE sequence-structure ... SEQUENCE=1 ALTER TABLE sequence RENAME TO sequence2; RENAME TABLE sequence RENAME TO sequence2; DROP [TEMPORARY] SEQUENCE sequence_name [IF EXISTS] See https://jira.mariadb.org/browse/MDEV-10139 for progress. See commit messages in bb-10.2-sequence for what is still to be done. Some documentation can be found at: https://mariadb.com/kb/en/mariadb/create-sequence/ This will be improved as the work progress. One of the goals with the SEQUENCE implementation is that all old tools, like mysqldump, should work unchanged, while still keeping normal usage of sequence standard compatibly. The make this possible, the sequence is currently implemented as a table with a few exclusive properties. The main disadvantage of having sequence as a table is that it uses the same name space as tables. In other words, you can't have a table and a sequence of the same name. The benefit is that sequences shows up in 'show tables', one can create a sequence also with 'create table' and drop it with 'drop table'. One can select from it as from any other table. This ensures that all old tools that works with tables should work with sequences. The special properties for sequence tables are: - A sequence table has always one row. - When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE, one row will be inserted. - Normal table options works for CREATE SEQUENCE. One can use ENGINE=xxx, COMMENT=xxx etc. - If one tries to insert into a sequence table, the single row will be updated. This allows mysqldump to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications can of course also use ALTER SEQUENCE. - Updates to the sequence table will change the single row. - Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The column 'next_value' shows the next value not reserved by the cache. - Truncate on a sequence table will give an error. - Alter table and rename works on sequences. - If one creates a sequence with INCREMENT 0, then the sequence will use auto_increment_increment and auto_increment_offset for the sequence, just like AUTO_INCREMENT. This allows sequences to work reliable in a master-master environment and with Galera. Internally sequence tables are created as a normal table without rollback (InnoDB, Aria and MySAM supports this) with is wrapped by a sequence engine. This allowed me to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if binary log is enabled). MariaDB 10.3 will support both the ANSI SQL and Oracle syntax for creating and accessing sequences. As ANSI SQL doesn't have an easy access to the last generated value, 103 also supports 'PREVIOUS VALUE FOR sequence_name', like IBM DB2, and LASTVAL(sequence_name) as PostgreSQL. I want to thank Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL which gave me ideas and inspiration for this work. I also want to thank Marko Mäkelä for his help in the InnoDB part of the code. There is still a lot of work to fix edge cases, but in the current implementation most major things seams to work... Comments, suggestions or questions? Regards, Monty

2 1

[Maria-developers] Please review MDEV-11292 Item_func_sp::fix_length_and_dec() does not set collation derivation properly
by Alexander Barkov 20 Nov '17

20 Nov '17

Hello Alexey, Please review a patch for MDEV-11292 (for 10.3). Thanks.

2 2

[Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"
by Kristian Nielsen 11 Jul '17

11 Jul '17

I plan to implement MDEV-12179, per-engine mysql.gtid_slave_pos. Here is a description of the high-level design, as a request for comments and/or suggestion for changes. The purpose of this is to fix a serious performance issue in replication when multiple storage engines are used. Every replicated transaction updates the replication GTID position in table mysql.gtid_slave_pos. There is a large overhead if that table is using another storage engine than the rest of the transaction, as then a cross-engine XA transaction needs to be done. But if more than one storage engine is used on a server, some replicated transactions will necessarily end up being cross-engine. This problem becomes more interesting to fix with the new interest in MyRocks. Though it already exists also for eg. people using MyISAM or TokuDB simultaneously with InnoDB, for example. The basic idea is to create multiple copies of the table, eg. mysql.gtid_slave_pos_innodb and mysql.gtid_slave_pos_myrocks. This way, an InnoDB transaction can update the InnoDB version of the table, and similar for other engines. To enable the feature, the DBA would create extra copies of the table for the engines she wants: CREATE TABLE mysql.gtid_slave_pos_myrocks LIKE mysql.gtid_slave_pos; ALTER TABLE mysql.gtid_slave_pos_myrocks ENGINE=myrocks; The server will look for all tables called mysql.gtid_slave_pos* at startup, as well as on each START SLAVE. This method makes migration from earlier versions simple. MariaDB already has the ability to work with multiple redundant rows in the mysql.gtid_slave_pos table. The column "sub_id" provides a version to identify the most recent entry. So the server just needs to read multiple tables (if present), and pick the newest version amongst all rows. Thus, the new feature is compatible with older versions without any schema changes. I think it is better than automatically creating copies of the table for all engines; it is often the case that an engine (eg. TokuDB) is available in the server binary, but the user does not want any tables created in that engine. This means that no speedup is obtained if the user does not create additional mysql.gtid_slave_pos* tables. (However, things should still work fine, thanks to XA synchronising cross-engine transactions). - Kristian.

6 12

[Maria-developers] MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
by Alexander Barkov 16 Jun '17

16 Jun '17

Hello Sergei, Please review a fix for MDEV-10306. Thanks!

4 13

[Maria-developers] MDEV-10142 - bb-10.2-compatibility
by jerome brauge 04 May '17

04 May '17

Hello Alexander, This is the patch for CHR(). Function CHR(n) returns the character having the binary equivalent to n in the database character set. Returns null on null input. Regard, Jérôme. PS : I share this code under terms of MCA. > -----Message d'origine----- > De : Alexander Barkov [mailto:bar@mariadb.org] > Envoyé : mercredi 26 avril 2017 06:12 > À : jerome brauge > Cc : MariaDB Developers > Objet : Re: MDEV-10142 - bb-10.2-compatibility / MDEV-10574 > > Hello Jerome, > > Thanks for working on this! > > On 04/25/2017 11:42 PM, jerome brauge wrote: > > Hello Alexander, > > Some times ago, we have had the below discussion. > > I made a patch following my first idea (it's enough from our point of view) : > replace empty string by null in literals and in make_empty_string. > > I think this patch can be complementary to your suggestion of adding > > an Item_func_eq_oracle (for data inserted in default sql_mode) > > > > This patch also contains following some functions changes: > > - replace ('ab','a',null) returns 'a' instead null > > - trim / ltrim / rtrim returns null when input only contains spaces > > - lpad /rpad can accept 2 args (default padding char is space) and if > > length equals to 0, returns null -substring : if start index is equal > > to 0, act as if it's equals to 1 -add a function chr() as a synonym of > > char() (but accept only one arg) -add a function lengthb() as a > > synonym of lentgh() -change the semantic of length for oracle (where > > length() is the character length and not the byte length) > > > > What do you think about this patch ? > > > > (Perhaps I have to make 2 patchs, one for empty string, and one for > > functions changes with separate test file for each modified function) > > Right, can you please split the patch into pieces? > I suggest even more pieces than two. One patch for one function. > I'll create MDEVs, one MDEV per function and put examples how Oracle > works. > > Can we do it in the following order: > > 1. LPAD / RPAD - making the third parameter optional. > This extension will be good for all sql_modes. > I checked IBM DB2, Oracle PostgreSQL, Firebird - they all have > the third parameter to LPAD/RPAD optional. > > 2. CHR(). > > 3. LENGTH() and LENGTHB(). > > > Then we can continue with REPLACE, TRIM. SUBSTRING in no particular > order, one patch per function. > > > Some general notes: > > > 1. Unify *.yy files > > Please put sql_mode dependent code into LEX rather than *.yy files directly. > We're going to unify the two files soon. See here for details: > MDEV-12518) Unify sql_yacc.yy and sql_yacc_ora.yy > > So instead of doing: > > | SUBSTRING '(' expr ',' expr ',' expr ')' > { > $$= new (thd->mem_root) Item_func_substr(thd, $3, $5, $7); > if ($$ == NULL) > MYSQL_YYABORT; > } > > > Please do something like this in both sql_yacc.yy and sql_yacc_ora.yy: > > > | SUBSTRING '(' expr ',' expr ',' expr ')' > { > if (!($$= Lex->make_item_func_substr(thd, $3, $5, $7))) > MYSQL_YYABORT; > } > > where LEX::make_item_func_substr() is a new method, creating either > Item_func_substr or Item_func_substr_oracle depending on sql_mode. > > The same applies to all other functions, and to text_literal. > > > 2. Don't use Item::is_null() when you call val_str() anyway. > > There are two reasons that: > - Performance > is_null() involves the second val_str() call, which can be resource consuming > in case of complex functions and subselect. > > - Side effect > Expressions can have some side effects. > For example, in case if the argument is a stored function which counts how > many times it was called and stores the counter in a user variable, or writes > something to a table on every execution. > If we do is_null() followed by val_str(), the side effect will done two times. > We need one time only. > > So instead of: > > String *Item_func_replace_oracle::val_str(String *str) { > String * res; > if (res = replace_value(str, > args[1]->is_null() ? &tmp_emtpystr : > rgs[1]->val_str(&tmp_value2), > args[2]->is_null() ? &tmp_emtpystr : > rgs[2]->val_str(&tmp_value3)) > > return (res->length() == 0) ? make_empty_result () : res; > else > return 0; > } > > > Please do something like this: > > String *Item_func_replace_oracle::val_str(String *str) { > String * res; > String *str1= args[1]->val_str(&tmp_value1); > String *str2= args[2]->val_str(&tmp_value2); > if (res= replace_value(str, > str1 ? str1 : &tmp_emptystr, > str2 ? str2 : &tmp_emptystr) > return (res->length() == 0) ? make_empty_result () : res; > else > return 0; > } > > > Thanks! > > > > > > > Best regards, > > Jérôme. > > > >> -----Message d'origine----- > >> De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 23 > >> janvier 2017 12:49 À : jerome brauge Cc : MariaDB Developers Objet : > >> Re: MDEV-10142 - bb-10.2-compatibility / MDEV-10574 > >> > >> Hi Jerome, > >> > >> On 01/18/2017 01:22 PM, jerome brauge wrote: > >>> Hello Alexander, > >>> Sometime ago, when I ask you about plan for MDEV-10574, you replied : > >>> > >>>> The current plan is to do these transformations: > >>>> > >>>> 1. Transform Insert > >>>> - insert values ("") -> insert values (null) > >>>> > >>>> 2. Transform Select > >>>> > >>>> - where v=x => (v <> "" and V=X) > >>>> - where v is null => (v="" or v is null) > >>>> > >>>> We didn't plan to change functions yet. Thanks for bringing this up. > >>>> We'll discuss this. > >>> > >>> I've done some tests just by changing : > >>> - insert an Item_null instead of an Item_string when $1.length==0 in > >>> rule text_literal of sql_yacc_ora.yy > >>> - return null instead of an empty string in > >>> Item_str_func::make_empty_result > >>> > >>> My first tests seem promising. > >>> > >>> Of course this solution does not allow to "see" the records created > >>> with > >> empty strings as null values. > >>> I don't see the importance of being able to do this in a transparent way. > >>> We can explicitly select these row by adding rtrim on these columns. > >>> > >>> If you are interesting, I can begin to write a test to evaluate the > >>> coverage of > >> this solution. > >> > >> I'm afraid this will fix the behavior only for literals. > >> > >> This script: > >> > >> DROP TABLE t1; > >> CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10)); INSERT INTO t1 > VALUES > >> ('',''); SELECT COUNT(*) FROM t1 WHERE a=''; SELECT COUNT(*) FROM t1 > >> WHERE a=b; > >> > >> returns 0 for both SELECT queries in Oracle, and returns 1 for both > >> SELECT queries in MariaDB, Your approach will fix the first SELECT only. > >> The second will still return 1. > >> > >> > >> For now, I'm not sure how to do this correctly. Some ways possible: > >> > >> 1. We could add new classes, e.g. Item_func_eq_oracle as a pair for > >> Item_func_eq, which will handle both empty strings and NULLs inside > >> their > >> val_int() implementations. > >> > >> 2. Or there could be some after-processing on the created Item tree > >> which will replace all things like Item_func_eq to Item_cond_and with > >> Item_func_eq and Item_func_null_predicate passed as arguments. > >> > >> Currently I'm inclined to #1, because we don't have a good mechanism > >> to clone items to implement #2 correctly. We'd need such cloning to > >> pass arguments both to Item_func_eq and Item_func_null_predicate > correctly. > >> Some places in the code don't expect that the same arguments can be > >> passed to two different Item_func instances. > >> > >> On the other hand, implementing #1 will need more changes in the > >> optimizer. > >> > >> We were going to think on this later. > >> > >>> > >>> Best regard. > >>> Jérôme. > >>>

2 6

[Maria-developers] cte page
by Galina Shalygina 04 May '17

04 May '17

мои артиклы

4 5

[Maria-developers] bb-10.2-ext / rpad lpad with 2 args
by jerome brauge 02 May '17

02 May '17

Hi Alexander, This is the patch for rpad/lpad with 2 args (call of make_empty_string() in val_str() is to prepare MDEV-10574). Regard, Jérôme. PS : I share this code under terms of MCA. > -----Message d'origine----- > De : Alexander Barkov [mailto:bar@mariadb.org] > Envoyé : mercredi 26 avril 2017 06:12 > À : jerome brauge > Cc : MariaDB Developers > Objet : Re: MDEV-10142 - bb-10.2-compatibility / MDEV-10574 > > Hello Jerome, > > Thanks for working on this! > > On 04/25/2017 11:42 PM, jerome brauge wrote: > > Hello Alexander, > > Some times ago, we have had the below discussion. > > I made a patch following my first idea (it's enough from our point of view) : > replace empty string by null in literals and in make_empty_string. > > I think this patch can be complementary to your suggestion of adding > > an Item_func_eq_oracle (for data inserted in default sql_mode) > > > > This patch also contains following some functions changes: > > - replace ('ab','a',null) returns 'a' instead null > > - trim / ltrim / rtrim returns null when input only contains spaces > > - lpad /rpad can accept 2 args (default padding char is space) and if > > length equals to 0, returns null -substring : if start index is equal > > to 0, act as if it's equals to 1 -add a function chr() as a synonym of > > char() (but accept only one arg) -add a function lengthb() as a > > synonym of lentgh() -change the semantic of length for oracle (where > > length() is the character length and not the byte length) > > > > What do you think about this patch ? > > > > (Perhaps I have to make 2 patchs, one for empty string, and one for > > functions changes with separate test file for each modified function) > > Right, can you please split the patch into pieces? > I suggest even more pieces than two. One patch for one function. > I'll create MDEVs, one MDEV per function and put examples how Oracle > works. > > Can we do it in the following order: > > 1. LPAD / RPAD - making the third parameter optional. > This extension will be good for all sql_modes. > I checked IBM DB2, Oracle PostgreSQL, Firebird - they all have > the third parameter to LPAD/RPAD optional. > > 2. CHR(). > > 3. LENGTH() and LENGTHB(). > > > Then we can continue with REPLACE, TRIM. SUBSTRING in no particular > order, one patch per function. > > > Some general notes: > > > 1. Unify *.yy files > > Please put sql_mode dependent code into LEX rather than *.yy files directly. > We're going to unify the two files soon. See here for details: > MDEV-12518) Unify sql_yacc.yy and sql_yacc_ora.yy > > So instead of doing: > > | SUBSTRING '(' expr ',' expr ',' expr ')' > { > $$= new (thd->mem_root) Item_func_substr(thd, $3, $5, $7); > if ($$ == NULL) > MYSQL_YYABORT; > } > > > Please do something like this in both sql_yacc.yy and sql_yacc_ora.yy: > > > | SUBSTRING '(' expr ',' expr ',' expr ')' > { > if (!($$= Lex->make_item_func_substr(thd, $3, $5, $7))) > MYSQL_YYABORT; > } > > where LEX::make_item_func_substr() is a new method, creating either > Item_func_substr or Item_func_substr_oracle depending on sql_mode. > > The same applies to all other functions, and to text_literal. > > > 2. Don't use Item::is_null() when you call val_str() anyway. > > There are two reasons that: > - Performance > is_null() involves the second val_str() call, which can be resource consuming > in case of complex functions and subselect. > > - Side effect > Expressions can have some side effects. > For example, in case if the argument is a stored function which counts how > many times it was called and stores the counter in a user variable, or writes > something to a table on every execution. > If we do is_null() followed by val_str(), the side effect will done two times. > We need one time only. > > So instead of: > > String *Item_func_replace_oracle::val_str(String *str) { > String * res; > if (res = replace_value(str, > args[1]->is_null() ? &tmp_emtpystr : > rgs[1]->val_str(&tmp_value2), > args[2]->is_null() ? &tmp_emtpystr : > rgs[2]->val_str(&tmp_value3)) > > return (res->length() == 0) ? make_empty_result () : res; > else > return 0; > } > > > Please do something like this: > > String *Item_func_replace_oracle::val_str(String *str) { > String * res; > String *str1= args[1]->val_str(&tmp_value1); > String *str2= args[2]->val_str(&tmp_value2); > if (res= replace_value(str, > str1 ? str1 : &tmp_emptystr, > str2 ? str2 : &tmp_emptystr) > return (res->length() == 0) ? make_empty_result () : res; > else > return 0; > } > > > Thanks! > > > > > > > Best regards, > > Jérôme. > > > >> -----Message d'origine----- > >> De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 23 > >> janvier 2017 12:49 À : jerome brauge Cc : MariaDB Developers Objet : > >> Re: MDEV-10142 - bb-10.2-compatibility / MDEV-10574 > >> > >> Hi Jerome, > >> > >> On 01/18/2017 01:22 PM, jerome brauge wrote: > >>> Hello Alexander, > >>> Sometime ago, when I ask you about plan for MDEV-10574, you replied : > >>> > >>>> The current plan is to do these transformations: > >>>> > >>>> 1. Transform Insert > >>>> - insert values ("") -> insert values (null) > >>>> > >>>> 2. Transform Select > >>>> > >>>> - where v=x => (v <> "" and V=X) > >>>> - where v is null => (v="" or v is null) > >>>> > >>>> We didn't plan to change functions yet. Thanks for bringing this up. > >>>> We'll discuss this. > >>> > >>> I've done some tests just by changing : > >>> - insert an Item_null instead of an Item_string when $1.length==0 in > >>> rule text_literal of sql_yacc_ora.yy > >>> - return null instead of an empty string in > >>> Item_str_func::make_empty_result > >>> > >>> My first tests seem promising. > >>> > >>> Of course this solution does not allow to "see" the records created > >>> with > >> empty strings as null values. > >>> I don't see the importance of being able to do this in a transparent way. > >>> We can explicitly select these row by adding rtrim on these columns. > >>> > >>> If you are interesting, I can begin to write a test to evaluate the > >>> coverage of > >> this solution. > >> > >> I'm afraid this will fix the behavior only for literals. > >> > >> This script: > >> > >> DROP TABLE t1; > >> CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10)); INSERT INTO t1 > VALUES > >> ('',''); SELECT COUNT(*) FROM t1 WHERE a=''; SELECT COUNT(*) FROM t1 > >> WHERE a=b; > >> > >> returns 0 for both SELECT queries in Oracle, and returns 1 for both > >> SELECT queries in MariaDB, Your approach will fix the first SELECT only. > >> The second will still return 1. > >> > >> > >> For now, I'm not sure how to do this correctly. Some ways possible: > >> > >> 1. We could add new classes, e.g. Item_func_eq_oracle as a pair for > >> Item_func_eq, which will handle both empty strings and NULLs inside > >> their > >> val_int() implementations. > >> > >> 2. Or there could be some after-processing on the created Item tree > >> which will replace all things like Item_func_eq to Item_cond_and with > >> Item_func_eq and Item_func_null_predicate passed as arguments. > >> > >> Currently I'm inclined to #1, because we don't have a good mechanism > >> to clone items to implement #2 correctly. We'd need such cloning to > >> pass arguments both to Item_func_eq and Item_func_null_predicate > correctly. > >> Some places in the code don't expect that the same arguments can be > >> passed to two different Item_func instances. > >> > >> On the other hand, implementing #1 will need more changes in the > >> optimizer. > >> > >> We were going to think on this later. > >> > >>> > >>> Best regard. > >>> Jérôme. > >>>

2 4

[Maria-developers] MDEV-12617 CASE and CASE-alike hybrid functions do not preserve exact data types
by Alexander Barkov 28 Apr '17

28 Apr '17

Hi Sanja, can you please review a patch for MDEV-12617? Thanks!

1 0