Alexander Barkov added a comment - 2023-04-24 03:43 - edited Oracle 11g works as follows: CREATE TABLE t1 (subject CLOB, pattern CLOB, replacement CLOB); INSERT INTO t1 VALUES ( 'x-x' , '-' , '' ); INSERT INTO t1 VALUES ( 'x-x' , '-' , NULL );   SELECT subject, pattern, replacement, REPLACE (subject,pattern,replacement) AS rpl, REGEXP_REPLACE(subject,pattern,replacement) AS rrpl FROM t1; | SUBJECT | PATTERN | REPLACEMENT | RPL | RRPL | |---------|---------|-------------|-----|------| | x-x | - | (null) | xx | xx | | x-x | - | (null) | xx | xx | SELECT REPLACE ( 'x-x' , '-' , '' ) AS rpl, REGEXP_REPLACE( 'x-x' , '-' , '' ) AS rrpl FROM DUAL; | RPL | RRPL | |-----|------| | xx | xx | SELECT REPLACE ( 'x-x' , '-' , NULL ) AS rpl, REGEXP_REPLACE( 'x-x' , '-' , NULL ) AS rrpl FROM DUAL; | RPL | RRPL | |-----|------| | xx | xx |

Alexander Barkov added a comment - 2023-04-24 04:27 - edited MariaDB-10.4-28: DELIMITER $$ CREATE OR REPLACE PROCEDURE p1(mode TEXT) BEGIN SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 (subject TEXT, pattern TEXT, replacement TEXT); INSERT INTO t1 VALUES ( 'x-x' , '-' , '' ); INSERT INTO t1 VALUES ( 'x-x' , '-' , NULL );   SET sql_mode=mode; -- EXECUTE IMMEDIATE is needed to reparse the statements using the given sql_mode   EXECUTE IMMEDIATE 'SELECT subject, pattern, replacement, REPLACE(subject,pattern,replacement) AS rpl, REGEXP_REPLACE(subject,pattern,replacement) AS rrpl FROM t1' ; EXECUTE IMMEDIATE "SELECT REPLACE('x-x','-','') AS rpl, REGEXP_REPLACE('x-x','-','') AS rrpl" ; EXECUTE IMMEDIATE "SELECT REPLACE('x-x','-',NULL) AS rpl, REGEXP_REPLACE('x-x','-',NULL) AS rrpl" ; END ; $$ DELIMITER ; CALL p1( 'ORACLE' ); +---------+---------+-------------+------+------+ | subject | pattern | replacement | rpl | rrpl | +---------+---------+-------------+------+------+ | x-x | - | | xx | xx | | x-x | - | NULL | xx | NULL | <-- expect 'xx' +---------+---------+-------------+------+------+ 2 rows in set (0.055 sec)   +-----+------+ | rpl | rrpl | +-----+------+ | xx | xx | +-----+------+ 1 row in set (0.055 sec)   +------+------+ | rpl | rrpl | +------+------+ | xx | NULL | <-- expect 'xx' +------+------+ 1 row in set (0.055 sec) CALL p1( 'ORACLE,empty_string_is_null' ); +---------+---------+-------------+------+------+ | subject | pattern | replacement | rpl | rrpl | +---------+---------+-------------+------+------+ | x-x | - | | xx | xx | | x-x | - | NULL | xx | NULL | <-- expect 'xx' +---------+---------+-------------+------+------+ 2 rows in set (0.032 sec)   +------+------+ | rpl | rrpl | +------+------+ | xx | NULL | <-- expect 'xx' +------+------+ 1 row in set (0.032 sec)   +------+------+ | rpl | rrpl | +------+------+ | xx | NULL | <-- expect 'xx' +------+------+ 1 row in set (0.032 sec) Obvervation: REPLACE consistently returns a not NULL result for sql_mode='ORACLE' - this is correct REGEXP_REPLACE returns NULL for NULL in the third argument if sql_mode='ORACLE' - this is wrong REGEXP_REPLACE returns NULL for empty string literal in the third argument if sql_mode='ORACLE,empty_string_is_null' - this is wrong

Sergei Golubchik added a comment - 2023-06-27 17:01 Because it depends on MDEV-27744 . Instead of adding yet another function with the _ORACLE suffix, REGEX_REPLACE_ORACLE , MDEV-27744 introduces a uniform handling of all oracle-specific functions without adding more keywords with suffixes. We'd like to avoid the hack of adding more names with _ORACLE (or _MARIADB) suffix, as we'll never be able to take them out.

Alexander Barkov added a comment - 2023-11-24 10:24 Hello, serg , please review this patch: https://github.com/MariaDB/server/commit/9d6de9212d54b586b3c3434201abf833512190dc Thanks.

Alexander Barkov added a comment - 2024-01-16 09:45 Hello serg , Please review a new patch version: https://github.com/MariaDB/server/commit/94fff9671daff70137bc806723363e9b507a5034 Also, comments sent by email. Thanks.

Sergei Golubchik added a comment - 2024-01-23 20:02 94fff9671daff70137bc806723363e9b507a5034 is ok to push