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:b...@mariadb.com] >> Envoyé : jeudi 4 octobre 2018 17:29 >> À : jerome brauge >> Cc : MariaDB Developers (maria-developers@lists.launchpad.net) >> 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:b...@mariadb.com] Envoyé : jeudi 4 >>>> octobre 2018 16:05 À : jerome brauge Cc : MariaDB Developers >>>> (maria-developers@lists.launchpad.net) >>>> 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. > _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
