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@mariadb.org> Envoyé : mardi 23 mai 2017 11:41 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net); 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@mariadb.org] Envoyé : jeudi 18 mai 2017 14:32 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net); 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.