EVOLUTION-MANAGER

Edit File: func_pad.result

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;