maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

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)

Best regards,
Jérôme.

> -----Message d'origine-----
> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
> 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.
> >

Attachment: empty_strings.diff
Description: empty_strings.diff