[Maria-developers] MDEV-17359
Hello Alexander, Can you review this patch for MDEV-17359 ? Best regards. 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.
Hello Jerome, On 10/03/2018 05:33 PM, jerome brauge wrote:
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.
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:
Hi Alexander, Thank you very much. Do you think this these patches will be ready for 10.3.10 GA ? Regards.
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.
Hi Jerome, On 10/04/2018 06:11 PM, jerome brauge wrote:
||
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!
Hi Alexander, Ok, I will do a new patch. Thanks.
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.
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 .
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.
Hi Jerome, On 10/05/2018 01:21 PM, jerome brauge wrote:
Good catch. I guess this is because Oracle truncates the zero fractional part when converting a number to char:
while MariaDB preserves zeros:
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!
Hi Alexander, I've done a pull request . Regards.
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.
participants (2)
-
Alexander Barkov -
jerome brauge