maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hello Jerome,


On 05/18/2017 05:51 PM, jerome brauge wrote:
> Hello Alexander,
>
> We have a long history of porting our application on different database providers and each time, string functions behavior is critical to success (essentially managing nulls and empty strings).
>
> For us, mandatory string functions that require a patch are:
>   - CONCAT
>   - SUBSTR
>   - LENGTH
>   - xTRIM
>   - empty strings as nulls
>   -TO_CHAR / TO_DATE
>
> Some others can be easily translated/substituted (CHR, xPAD, REPLACE, INSTR with 3 args) and they are here only to facilitate porting task.
>
> I can't be exhaustive because we only use a subset of oracle functions but it's the core.
> With all patches that I provided, I can connect to our application and it's already a challenge !


I'm afraid we cannot add xxx_oracle counterparts for all/some functions.
Concatenations was a special case, as it's obviously very critical,
and there is no a way to override behavior of operators.

Instead of adding built-in duplicate functions,
we need some more general solution to handle NULLs vs empty strings,
but this will require significant efforts.

For now, I propose to think about a simple workaround with stored functions.

MariaDB allows to create stored functions that have the same name with 
built-in functions:


SET sql_mode=ORACLE;
DELIMITER $$
CREATE OR REPLACE FUNCTION LENGTH(a TEXT) RETURN INT UNSIGNED AS
BEGIN
  RETURN CHAR_LENGTH(COALESCE(a,''));
END
$$
DELIMITER ;
SELECT LENGTH('a');


But the problem is that when you call, it still calls the built-in
function, unless a qualified name is used:


SELECT LENGTH(NULL), test.LENGTH(NULL);
+--------------+-------------------+
| LENGTH(NULL) | test.LENGTH(NULL) |
+--------------+-------------------+
|         NULL |                 0 |
+--------------+-------------------+


Notice, LENGTH() still returned NULL, while test.LENGTH() returned 0.


So it seems we need a new flag in sql_mode
to change the order of built-in-VS-stored function name resolution.

What do you think about this proposal?

From a glance, it should perfectly work when one needs only a limited 
set of functions, and it should be easy to do.

Thanks.


> Regards,
> Jérôme.
>
> > -----Message d'origine-----
> > De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
> > Envoyé : jeudi 18 mai 2017 14:32
> > À : jerome brauge
> > Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx); Sergei
> > Golubchik
> > Objet : Re: MDEV-10142 - Replace function
> >
> > Hello Jerome,
> >
> > We're slightly worried that we can end up with creating XXX_ORACLE
> > duplicates all MariaDB functions.
> >
> >
> > Why did you choose a limited number of functions: REPLACE, TRIM, SUBSTR?
> > What about other functions? Will you need changes in some other functions
> > as well?
> >
> > Thanks.
> >
> >
> > On 05/12/2017 12:31 PM, jerome brauge wrote:
> > > Hello Alexander,
> > > Here is patch for replace function in oracle mode.
> > > Do I have to add a test for replication ?
> > >
> > > Regards,
> > > Jérôme.