maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hi Alexander,
Our application logic is fully written in PL/SQL.
We have near of 13 millions lines of PL/SQL code and if we want challenge Oracle , performance will be a critical point.
>From my point of view, using UDF for low level function like these will cause too big overhead.

Regards,
Jérôme.


________________________________________
De : Alexander Barkov <bar@xxxxxxxxxxx>
Envoyé : mardi 23 mai 2017 11:41
À : jerome brauge
Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx); Sergei Golubchik
Objet : Re: MDEV-10142 - Replace function

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.
>>>