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 <[email protected]> Envoyé : mardi 23 mai 2017 11:41 À : jerome brauge Cc : MariaDB Developers ([email protected]); 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:[email protected]] >> Envoyé : jeudi 18 mai 2017 14:32 >> À : jerome brauge >> Cc : MariaDB Developers ([email protected]); 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. >>> _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
