MDEV-15739 sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string

2 files changed, 102 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/func_pad.result b/mysql-test/suite/compat/oracle/r/func_pad.result
new file mode 100644
index 00000000000..ca7d52cd542
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_pad.result
@@ -0,0 +1,71 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-15739 -  sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string
+#
+SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ;
+RPAD('a',0)	RPAD('abc',1)	RPAD('abc',2)
+NULL	a	ab
+SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ;
+RPAD('a',0,'.')	RPAD('abc',1,'.')	RPAD('abc',2,'.')
+NULL	a	ab
+SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ;
+LPAD('a',0)	LPAD('abc',1)	LPAD('abc',2)
+NULL	a	ab
+SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ;
+LPAD('a',0,'.')	LPAD('abc',1,'.')	LPAD('abc',2,'.')
+NULL	a	ab
+CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER);
+INSERT INTO t1 VALUES ('a',1,null,1);
+INSERT INTO t1 VALUES ('a',null,'.',2);
+INSERT INTO t1 VALUES (null,1,'.',3);
+INSERT INTO t1 VALUES ('a',-1,'.',4);
+INSERT INTO t1 VALUES ('a',0,'.',5);
+INSERT INTO t1 VALUES ('a',1,'.',6);
+INSERT INTO t1 VALUES ('a',2,'.',7);
+SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord;
+LPAD(c1,c2,c3)	LPAD(c1,c2)
+NULL	a
+NULL	NULL
+NULL	NULL
+NULL	NULL
+NULL	NULL
+a	a
+.a	 a
+SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
+RPAD(c1,c2,c3)	RPAD(c1,c2)
+NULL	a
+NULL	NULL
+NULL	NULL
+NULL	NULL
+NULL	NULL
+a	a
+a.	a 
+EXPLAIN EXTENDED SELECT RPAD('a',0,'.'), LPAD('a',0,'.'), LPAD(c1,c2,c3), LPAD(c1,c2), RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using filesort
+Warnings:
+Note	1003	select rpad_oracle('a',0,'.') AS "RPAD('a',0,'.')",lpad_oracle('a',0,'.') AS "LPAD('a',0,'.')",lpad_oracle("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "LPAD(c1,c2,c3)",lpad_oracle("test"."t1"."c1","test"."t1"."c2") AS "LPAD(c1,c2)",rpad_oracle("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "RPAD(c1,c2,c3)",rpad_oracle("test"."t1"."c1","test"."t1"."c2") AS "RPAD(c1,c2)" from "test"."t1" order by "test"."t1"."ord"
+CREATE VIEW v1 AS SELECT RPAD('a',0,'.') AS "C1", LPAD('a',0,'.') AS "C2", LPAD(c1,c2,c3) AS "C3", LPAD(c1,c2) AS "C4", RPAD(c1,c2,c3) AS "C5", RPAD(c1,c2) AS "C6" FROM t1 ORDER BY ord;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS select rpad_oracle('a',0,'.') AS "C1",lpad_oracle('a',0,'.') AS "C2",lpad_oracle("t1"."c1","t1"."c2","t1"."c3") AS "C3",lpad_oracle("t1"."c1","t1"."c2") AS "C4",rpad_oracle("t1"."c1","t1"."c2","t1"."c3") AS "C5",rpad_oracle("t1"."c1","t1"."c2") AS "C6" from "t1" order by "t1"."ord"	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+C1	C2	C3	C4	C5	C6
+NULL	NULL	NULL	a	NULL	a
+NULL	NULL	NULL	NULL	NULL	NULL
+NULL	NULL	NULL	NULL	NULL	NULL
+NULL	NULL	NULL	NULL	NULL	NULL
+NULL	NULL	NULL	NULL	NULL	NULL
+NULL	NULL	a	a	a	a
+NULL	NULL	.a	 a	a.	a 
+SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1;
+c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6
+---a--a
+-----
+-----
+-----
+-----
+--a-a-a-a
+--.a- a-a.-a 
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/func_pad.test b/mysql-test/suite/compat/oracle/t/func_pad.test
new file mode 100644
index 00000000000..bc33a9fa4f8
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_pad.test
@@ -0,0 +1,31 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # MDEV-15739 -  sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string
+--echo #
+
+SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ;
+SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ;
+SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ;
+SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ;
+
+CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER);
+INSERT INTO t1 VALUES ('a',1,null,1);
+INSERT INTO t1 VALUES ('a',null,'.',2);
+INSERT INTO t1 VALUES (null,1,'.',3);
+INSERT INTO t1 VALUES ('a',-1,'.',4);
+INSERT INTO t1 VALUES ('a',0,'.',5);
+INSERT INTO t1 VALUES ('a',1,'.',6);
+INSERT INTO t1 VALUES ('a',2,'.',7);
+
+SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord;
+SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
+
+EXPLAIN EXTENDED SELECT RPAD('a',0,'.'), LPAD('a',0,'.'), LPAD(c1,c2,c3), LPAD(c1,c2), RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
+
+CREATE VIEW v1 AS SELECT RPAD('a',0,'.') AS "C1", LPAD('a',0,'.') AS "C2", LPAD(c1,c2,c3) AS "C3", LPAD(c1,c2) AS "C4", RPAD(c1,c2,c3) AS "C5", RPAD(c1,c2) AS "C6" FROM t1 ORDER BY ord;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1;
+DROP VIEW v1;
+DROP TABLE t1;