MDEV-15664 : sql_mode=ORACLE: Make TRIM return NULL instead of empty string

2 files changed, 247 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/func_trim.result b/mysql-test/suite/compat/oracle/r/func_trim.result
new file mode 100644
index 00000000000..bed8dadf97f
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_trim.result
@@ -0,0 +1,170 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+#
+SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM('  '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
+TRIM('abc')	TRIM('abc ')||'.'	'.'||TRIM(' abc ')||'.'	TRIM('  ')	TRIM(NULL)	TRIM(SPACE(0))	TRIM(SPACE(10))
+abc	abc.	.abc.	NULL	NULL	NULL	NULL
+SELECT TRIM(TRAILING 'abc' FROM 'abc');
+TRIM(TRAILING 'abc' FROM 'abc')
+NULL
+SELECT TRIM(TRAILING 'abc' FROM 'abc ');
+TRIM(TRAILING 'abc' FROM 'abc ')
+abc 
+SELECT TRIM(TRAILING 'abc' FROM ' abc');
+TRIM(TRAILING 'abc' FROM ' abc')
+ 
+SELECT TRIM(LEADING 'abc' FROM 'abc');
+TRIM(LEADING 'abc' FROM 'abc')
+NULL
+SELECT TRIM(LEADING 'abc' FROM 'abc ');
+TRIM(LEADING 'abc' FROM 'abc ')
+ 
+SELECT TRIM(LEADING 'abc' FROM ' abc');
+TRIM(LEADING 'abc' FROM ' abc')
+ abc
+SELECT TRIM(BOTH 'abc' FROM 'abc');
+TRIM(BOTH 'abc' FROM 'abc')
+NULL
+SELECT TRIM(BOTH 'abc' FROM 'abc ');
+TRIM(BOTH 'abc' FROM 'abc ')
+ 
+SELECT TRIM(BOTH 'abc' FROM ' abc');
+TRIM(BOTH 'abc' FROM ' abc')
+ 
+SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM('  '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
+RTRIM('abc')	RTRIM('abc ')||'.'	RTRIM(' abc ')||'.'	RTRIM('  ')	RTRIM(NULL)	RTRIM(SPACE(0))	RTRIM(SPACE(10))
+abc	abc.	 abc.	NULL	NULL	NULL	NULL
+SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM('  '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
+LTRIM('abc')	LTRIM('abc ')	LTRIM(' abc ')	LTRIM('  ')	LTRIM(NULL)	LTRIM(SPACE(0))	LTRIM(SPACE(10))
+abc	abc 	abc 	NULL	NULL	NULL	NULL
+CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
+INSERT INTO t1 VALUES ('abc',1);
+INSERT INTO t1 VALUES (SPACE(0),2);
+INSERT INTO t1 VALUES ('',3);
+INSERT INTO t1 VALUES (' ',4);
+INSERT INTO t1 VALUES ('      ',5);
+INSERT INTO t1 VALUES ('   a     ',6);
+INSERT INTO t1 VALUES ('aa',7);
+INSERT INTO t1 VALUES ('aabb',8);
+INSERT INTO t1 VALUES ('bbaa',9);
+INSERT INTO t1 VALUES ('aabbaa',10);
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord	'['||c1||']'	'.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.'
+1	[abc]	.bc.
+2	[]	.NULL.
+3	[]	.NULL.
+4	[ ]	. .
+5	[      ]	.      .
+6	[   a     ]	.   a     .
+7	[aa]	.NULL.
+8	[aabb]	.bb.
+9	[bbaa]	.bbaa.
+10	[aabbaa]	.bbaa.
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord	'['||c1||']'	'.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.'
+1	[abc]	.abc.
+2	[]	.NULL.
+3	[]	.NULL.
+4	[ ]	. .
+5	[      ]	.      .
+6	[   a     ]	.   a     .
+7	[aa]	.NULL.
+8	[aabb]	.aabb.
+9	[bbaa]	.bb.
+10	[aabbaa]	.aabb.
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord	'['||c1||']'	'.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.'
+1	[abc]	.bc.
+2	[]	.NULL.
+3	[]	.NULL.
+4	[ ]	. .
+5	[      ]	.      .
+6	[   a     ]	.   a     .
+7	[aa]	.NULL.
+8	[aabb]	.bb.
+9	[bbaa]	.bb.
+10	[aabbaa]	.bb.
+SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
+ord	'['||c1||']'	COALESCE(LTRIM(c1),'NULL')
+1	[abc]	abc
+2	[]	NULL
+3	[]	NULL
+4	[ ]	NULL
+5	[      ]	NULL
+6	[   a     ]	a     
+7	[aa]	aa
+8	[aabb]	aabb
+9	[bbaa]	bbaa
+10	[aabbaa]	aabbaa
+SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
+ord	'['||c1||']'	COALESCE(RTRIM(c1),'NULL')||'.'
+1	[abc]	abc.
+2	[]	NULL.
+3	[]	NULL.
+4	[ ]	NULL.
+5	[      ]	NULL.
+6	[   a     ]	   a.
+7	[aa]	aa.
+8	[aabb]	aabb.
+9	[bbaa]	bbaa.
+10	[aabbaa]	aabbaa.
+EXPLAIN EXTENDED SELECT TRIM('abc'),
+TRIM(BOTH 'a' FROM 'abc'),
+TRIM(LEADING 'a' FROM 'abc'),
+TRIM(TRAILING 'a' FROM 'abc') ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select trim_oracle('abc') AS "TRIM('abc')",trim_oracle(both 'a' from 'abc') AS "TRIM(BOTH 'a' FROM 'abc')",trim_oracle(leading 'a' from 'abc') AS "TRIM(LEADING 'a' FROM 'abc')",trim_oracle(trailing 'a' from 'abc') AS "TRIM(TRAILING 'a' FROM 'abc')"
+EXPLAIN EXTENDED SELECT RTRIM('abc'),
+LTRIM('abc');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')"
+CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
+'['||c1||']',
+TRIM(LEADING 'a' FROM c1),
+TRIM(TRAILING 'a' FROM c1),
+TRIM(BOTH 'a' FROM c1),
+LTRIM(c1),
+RTRIM(c1)
+FROM t1 ORDER BY ord ;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS select "t1"."ord" AS "ord",trim_oracle('abc') AS "TRIM('abc')",rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')",concat_operator_oracle(concat_operator_oracle('[',"t1"."c1"),']') AS "'['||c1||']'",trim_oracle(leading 'a' from "t1"."c1") AS "TRIM(LEADING 'a' FROM c1)",trim_oracle(trailing 'a' from "t1"."c1") AS "TRIM(TRAILING 'a' FROM c1)",trim_oracle(both 'a' from "t1"."c1") AS "TRIM(BOTH 'a' FROM c1)",ltrim_oracle("t1"."c1") AS "LTRIM(c1)",rtrim_oracle("t1"."c1") AS "RTRIM(c1)" from "t1" order by "t1"."ord"	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+ord	TRIM('abc')	RTRIM('abc')	LTRIM('abc')	'['||c1||']'	TRIM(LEADING 'a' FROM c1)	TRIM(TRAILING 'a' FROM c1)	TRIM(BOTH 'a' FROM c1)	LTRIM(c1)	RTRIM(c1)
+1	abc	abc	abc	[abc]	bc	abc	bc	abc	abc
+2	abc	abc	abc	[]	NULL	NULL	NULL	NULL	NULL
+3	abc	abc	abc	[]	NULL	NULL	NULL	NULL	NULL
+4	abc	abc	abc	[ ]	 	 	 	NULL	NULL
+5	abc	abc	abc	[      ]	      	      	      	NULL	NULL
+6	abc	abc	abc	[   a     ]	   a     	   a     	   a     	a     	   a
+7	abc	abc	abc	[aa]	NULL	NULL	NULL	aa	aa
+8	abc	abc	abc	[aabb]	bb	aabb	bb	aabb	aabb
+9	abc	abc	abc	[bbaa]	bbaa	bb	bb	bbaa	bbaa
+10	abc	abc	abc	[aabbaa]	bbaa	aabb	bb	aabbaa	aabbaa
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
+CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
+TRIM(TRAILING 'a' FROM c1) AS C2,
+TRIM(BOTH 'a' FROM c1) AS C3,
+LTRIM(c1) AS C4,
+RTRIM(c1) AS C5
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "C1" varchar(10) DEFAULT NULL,
+  "C2" varchar(10) DEFAULT NULL,
+  "C3" varchar(10) DEFAULT NULL,
+  "C4" varchar(10) DEFAULT NULL,
+  "C5" varchar(10) DEFAULT NULL
+)
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;
diff --git a/mysql-test/suite/compat/oracle/t/func_trim.test b/mysql-test/suite/compat/oracle/t/func_trim.test
new file mode 100644
index 00000000000..153238fb092
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_trim.test
@@ -0,0 +1,77 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
+--echo #
+
+SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM('  '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
+
+SELECT TRIM(TRAILING 'abc' FROM 'abc');
+SELECT TRIM(TRAILING 'abc' FROM 'abc ');
+SELECT TRIM(TRAILING 'abc' FROM ' abc');
+
+SELECT TRIM(LEADING 'abc' FROM 'abc');
+SELECT TRIM(LEADING 'abc' FROM 'abc ');
+SELECT TRIM(LEADING 'abc' FROM ' abc');
+
+SELECT TRIM(BOTH 'abc' FROM 'abc');
+SELECT TRIM(BOTH 'abc' FROM 'abc ');
+SELECT TRIM(BOTH 'abc' FROM ' abc');
+
+SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM('  '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
+SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM('  '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
+
+CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
+INSERT INTO t1 VALUES ('abc',1);
+INSERT INTO t1 VALUES (SPACE(0),2);
+INSERT INTO t1 VALUES ('',3);
+INSERT INTO t1 VALUES (' ',4);
+INSERT INTO t1 VALUES ('      ',5);
+INSERT INTO t1 VALUES ('   a     ',6);
+INSERT INTO t1 VALUES ('aa',7);
+INSERT INTO t1 VALUES ('aabb',8);
+INSERT INTO t1 VALUES ('bbaa',9);
+INSERT INTO t1 VALUES ('aabbaa',10);
+
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
+SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
+
+EXPLAIN EXTENDED SELECT TRIM('abc'),
+                        TRIM(BOTH 'a' FROM 'abc'),
+                        TRIM(LEADING 'a' FROM 'abc'),
+                        TRIM(TRAILING 'a' FROM 'abc') ;
+
+EXPLAIN EXTENDED SELECT RTRIM('abc'),
+                        LTRIM('abc');
+
+
+CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
+                         '['||c1||']',
+                         TRIM(LEADING 'a' FROM c1),
+                         TRIM(TRAILING 'a' FROM c1),
+                         TRIM(BOTH 'a' FROM c1),
+                         LTRIM(c1),
+                         RTRIM(c1)
+                  FROM t1 ORDER BY ord ;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
+CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
+                          TRIM(TRAILING 'a' FROM c1) AS C2,
+                          TRIM(BOTH 'a' FROM c1) AS C3,
+                          LTRIM(c1) AS C4,
+                          RTRIM(c1) AS C5
+        FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE trim_oracle (trim_oracle int);
+DROP TABLE trim_oracle;