maria-developers team mailing list archive
Mailing list archive
Re: MDEV-10142 - Replace function
On 05/18/2017 05:51 PM, jerome brauge wrote:
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:
- 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
CREATE OR REPLACE FUNCTION LENGTH(a TEXT) RETURN INT UNSIGNED AS
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.
De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
Envoyé : jeudi 18 mai 2017 14:32
À : jerome brauge
Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx); Sergei
Objet : Re: MDEV-10142 - Replace function
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
On 05/12/2017 12:31 PM, jerome brauge wrote:
Here is patch for replace function in oracle mode.
Do I have to add a test for replication ?