Alexander, Here is the patch I was talking about. It not address all aspects of MDEV-10574, especially, empty strings in table column are not view as null. But as in oracle mode, no empty string in columns should be created, an fully oracle application will never fall in this case. Regards, Jérôme.

-----Message d'origine----- De : jerome brauge Envoyé : mardi 19 septembre 2017 14:13 À : 'Alexander Barkov' Objet : RE: Oracle strings functions compatibility : substr

Hello Alexander, Yes I'm aware. This point will be treated by my next patch which will also affect the others string functions (a part of MDEV-10574). I will refresh this new patch with the current development branch, and I submit to you. Regards. Jérôme.

...

-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : mardi 19 septembre 2017 13:16 À : jerome brauge Objet : Re: Oracle strings functions compatibility : substr

Hi Jerome,

I noticed one more difference in SUBSTR:

SELECT SUBSTR('aaa',1,-1) FROM DUAL;

MariaDB returns an empty string. Oracle returns NULL.

Here's Oracle's documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.ht m

I think we should do this in the same patch, to avoid behavior change in the future.

Would you like to try doing this additional change? Or would you like me to make an incremental patch on top of yours?

Thanks!

On 09/19/2017 10:33 AM, jerome brauge wrote:

...

Hello Alexander, It's done.

Do you have news from Sergei for MDEV-12874, MDEV-13417 and MDEV- 13418 ?

I asked Sergei, awaiting for his answer. I'll let you know.

...

Thank you very much.

...

-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 18 septembre 2017 18:12 À : jerome brauge Objet : Re: Oracle strings functions compatibility : substr

Hello Jerome,

On 09/18/2017 05:14 PM, jerome brauge wrote:

...

Hello Alexander, I don't understand how I've missed such an obvious solution ! Here is the new patch.

Thanks. Now it looks simpler.

Can you please do one small thing: move get_position() from "public:" to "protected:".

Ok to make a pull request after this change.

Thank you very much!

...

Regards. Jérôme.

...

-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 18 septembre 2017 11:30 À : jerome brauge Objet : Re: Oracle strings functions compatibility : substr

Hello Jerome,

On 08/17/2017 04:43 PM, jerome brauge wrote: > Hello Alexander, > Here is a patch for function SUBSTR in sql_mode=oracle (If > position is 0, then it is treated as 1).

I'm thinking of how to get a smaller patch.

Wouldn't it be possible to introduce:

virtual longlong get_position();

longlong Item_func_substr::get_position() { return args[1]->val_int(); }

longlong Item_func_substr_oracle::get_position() { longlong pos= args[1]->val_int(); return pos == 0 ? 1 : pos; }

and just replace all calls for args[1]->val_int() to get_position() ?

Thanks!

> > Regards, > Jérôme. >