maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hi Alexander,
These 2 select works fine if the insert is doing in Oracle mode (because null values are inserted and null is never equals to null even in default mode).
At this time, I found only one way to create an empty string in oracle mode : insert into t1 values (rpad('x',0,' '), lpad('x',0,' '))

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.
>