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;