maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Jerome,



On 10/05/2018 01:21 PM, jerome brauge wrote:
> Hi Alexander,
> 
> I 've found another difference in behavior of "like" between Mariadb and Oracle :
> 
> Oracle :
> select 'X' from dual where  1 like 10/10;
> select 'X' from dual where 1 like 1.00;
> select 'x' from dual where 1.000 like 1.00;
> 
> returns 'X'  and mariadb return empty result set.
> 
> If numeric types have not the same precision for their decimal part, like operator return false.

Good catch.

I guess this is because Oracle truncates the zero fractional part when
converting a number to char:


> SQL> select cast(10/10 as char) from dual;
>
> C
> -
> 1


while MariaDB preserves zeros:


> MariaDB [test]> select cast(10/10 as char);
> +---------------------+
> | cast(10/10 as char) |
> +---------------------+
> | 1.0000              |
> +---------------------+

So this looks like a problem in CAST, not in LIKE.

> 
> Should we change the "like" operator behavior ? (in another patch ?)

Yes, we can fix CAST in another patch.
I reported the problem in MDEV-17375

Thanks for noticing this!


> 
> Regards .
> 
>> -----Message d'origine-----
>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
>> Envoyé : jeudi 4 octobre 2018 17:29
>> À : jerome brauge
>> Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx)
>> Objet : Re: MDEV-17359
>>
>> Hi Jerome,
>>
>>
>> On 10/04/2018 06:11 PM, jerome brauge wrote:
>>> Hi Alexander,
>>> Thank you very much.
>>> Do you think this these patches will be ready for 10.3.10 GA ?
>>
>> Unfortunately no. It's too late for 10.3.10. But they'll be in 10.3.11.
>>
>> I've pushed MDEV-17374.
>>
>>
>> Now MDEV-17359 can be fixed without any shift/reduce conflicts, just by this
>> two-line change in sql_yacc_ora.yy:
>>
>>
>> one line for LIKE:
>>
>> -        | bit_expr LIKE mysql_concatenation_expr opt_escape
>> +        | bit_expr LIKE bit_expr opt_escape
>>
>>
>> and one line for NOT LIKE:
>>
>>
>> -        | bit_expr not LIKE mysql_concatenation_expr opt_escape
>> +        | bit_expr not LIKE bit_expr opt_escape
>>
>>
>>
>> This is the output after applying this change:
>>
>>
>> SET sql_mode=ORACLE;
>> SELECT 111 LIKE 100+10+1, 'ab' like 'a'||'b';
>>
>> +-------------------+--------------------+
>> | 111 LIKE 100+10+1 | 'ab' like 'a'||'b' |
>> +-------------------+--------------------+
>> |                 1 |                  1 |
>> +-------------------+--------------------+
>> 1 row in set (0.00 sec)
>>
>>
>> Are you willing to make a new patch and extend the tests to cover additive
>> and mutiplicative expressions?
>>
>>
>> Note, it actually enables bit operators as well, which are not supported by
>> Oracle.
>>
>> But I think this should not be harmful.
>>
>>
>> So I think all expressions from "bit_expr" should be covered in the test:
>>
>> |
>> &
>> <<
>>>>
>> ||
>> +
>> -
>> *
>> /
>> DIV
>> MOD
>> ^
>>
>> No needs to test the rules with INTERVAL (this grammar will be gone soon).
>>
>>
>> Thanks!
>>
>>
>>
>>
>>>
>>> Regards.
>>>
>>>> -----Message d'origine-----
>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : jeudi 4
>>>> octobre 2018 16:05 À : jerome brauge Cc : MariaDB Developers
>>>> (maria-developers@xxxxxxxxxxxxxxxxxxx)
>>>> Objet : Re: MDEV-17359
>>>>
>>>> Hi Jerome,
>>>>
>>>> Note, Oracle supports not only concatenation in the pattern, but also
>>>> additive and multiplicative expressions, e.g.:
>>>>
>>>> SELECT 'x' FROM DUAL WHERE '100' LIKE 10*10;
>>>>
>>>> This kind of expressions currently also do not work.
>>>>
>>>> I think that in this rule:
>>>>
>>>>         | bit_expr LIKE mysql_concatenation_expr opt_escape
>>>>           {
>>>>             $$= new (thd->mem_root) Item_func_like(thd, $1, $3, $4,
>>>>                                                    Lex->escape_used);
>>>>             if (unlikely($$ == NULL))
>>>>               MYSQL_YYABORT;
>>>>           }
>>>>
>>>>
>>>> mysql_concatenation_expr should be just replaced to bit_expr.
>>>>
>>>> But this adds a lot of new shift/reduce conflicts because of flaws in
>>>> the current grammar. They need to be fixed before MDEV-17359.
>>>>
>>>>
>>>> I reported these conflicts in:
>>>>
>>>> MDEV-17374 Shift/reduce conflicts because of SOUNDS_SYM,
>> ESCAPE_SYM,
>>>> USER_SYM not given precedence
>>>>
>>>> I'll have a patch soon, then will return to MDEV-17359.
>>>>
>>>> Greetings.
>>>>
>>>>
>>>> On 10/04/2018 09:26 AM, Alexander Barkov wrote:
>>>>> Jerome,
>>>>>
>>>>> Can you please wait. I'll do some more analysis on the subject.
>>>>>
>>>>>
>>>>> On 10/04/2018 09:16 AM, Alexander Barkov wrote:
>>>>>>   Hello Jerome,
>>>>>>
>>>>>> On 10/03/2018 05:33 PM, jerome brauge wrote:
>>>>>>> Hello Alexander,
>>>>>>> Can you review this patch for MDEV-17359 ?
>>>>>>>
>>>>>>> Best regards.
>>>>>>>
>>>>>>
>>>>>>
>>>>>> Thanks for you contribution!
>>>>>>
>>>>>>
>>>>>> I noticed that Oracle also supports concatenation in the ESCAPE clause:
>>>>>>
>>>>>> SELECT 'x' FROM DUAL WHERE 'a' LIKE 'b' ESCAPE ''||'x'||'';
>>>>>>
>>>>>> This can be done separately, or can be addressed in the same patch,
>>>>>> if you wish :)
>>>>>>
>>>>>>
>>>>>> Review comments:
>>>>>>
>>>>>> I've been trying to gradually make the two *.yy files as similar as
>>>>>> possible, to turn them into a single *.yy at the end.
>>>>>> So new changes in the grammar should usually make the two files
>>>>>> look more similar rather than make them diverge.
>>>>>>
>>>>>> However, it seems in case of concatenation and like, having only a
>>>>>> pair of MYSQL_CONCAT_SYM and ORACLE_CONCAT_SYM does not
>> solve
>>>> the
>>>>>> problem. I could not make a quick patch which would converge
>>>>>> sql_yacc.yy and sql_yacc_ora.yy.
>>>>>>
>>>>>> It's likely that we'll have to introduce separate MYSQL_LIKE_SYM
>>>>>> and ORACLE_LIKE_SYM. But I can do it separately later (after more
>>>>>> analysis).
>>>>>>
>>>>>>
>>>>>> I the meanwhile, could you please rename mysql_concatenation_expr
>>>>>> into oracle_concatenation_expr in sql_yacc_ora.yy, so it looks like
>>>>>> this:
>>>>>>
>>>>>>
>>>>>> oracle_concatenation_expr:
>>>>>>            simple_expr
>>>>>>         | oracle_concatenation_expr ORACLE_CONCAT_SYM simple_expr
>>>>>>            {
>>>>>>              $$= new (thd->mem_root)
>>>>>> Item_func_concat_operator_oracle(thd, $1, $3);
>>>>>>              if (unlikely($$ == NULL))
>>>>>>                MYSQL_YYABORT;
>>>>>>            }
>>>>>>         ;
>>>>>>
>>>>>>
>>>>>>
>>>>>> Later, when we join the two *.yy files, we will need both
>>>>>> mysql_concatenation_expr (in its current reduction) and
>>>>>> oracle_concatenation_expr (in your reduction with
>>>> ORACLE_CONCAT_SYM
>>>>>> and Item_func_concat_operator_oracle) .
>>>>>>
>>>>>>
>>>>>> Thanks.
>>>>>>
>>> Ce message et les pièces jointes sont confidentiels et établis à l'attention
>> exclusive de ses destinataires. Toute utilisation ou diffusion, même partielle,
>> non autorisée est interdite. Tout message électronique est susceptible
>> d'altération; CEGID décline donc toute responsabilité au titre de ce message.
>> Si vous n'êtes pas le destinataire de ce message, merci de le détruire et
>> d'avertir l'expéditeur.
>>>
>>> This message and any attachments are confidential and intended solely for
>> the addressees. Any unauthorized use or disclosure, either whole or partial is
>> prohibited. E-mails are susceptible to alteration; CEGID shall therefore not be
>> liable for the content of this message. If you are not the intended recipient
>> of this message, please delete it and notify the sender.
>>>
> Ce message et les pièces jointes sont confidentiels et établis à l'attention exclusive de ses destinataires. Toute utilisation ou diffusion, même partielle, non autorisée est interdite. Tout message électronique est susceptible d'altération; CEGID décline donc toute responsabilité au titre de ce message. Si vous n'êtes pas le destinataire de ce message, merci de le détruire et d'avertir l'expéditeur.
> 
> This message and any attachments are confidential and intended solely for the addressees. Any unauthorized use or disclosure, either whole or partial is prohibited. E-mails are susceptible to alteration; CEGID shall therefore not be liable for the content of this message. If you are not the intended recipient of this message, please delete it and notify the sender.
>