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. Should we change the "like" operator behavior ? (in another patch ?) Regards .
-----Message d'origine----- De : Alexander Barkov [mailto:bar@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:bar@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,
USER_SYM not given precedence
I'll have a patch soon, then will return to MDEV-17359.
Greetings.
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
On 10/04/2018 09:26 AM, Alexander Barkov wrote: 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
ESCAPE_SYM, 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.