Debian Codegraph @

mysql-test/suite/compat/oracle/r/func_pad.result

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-15739 - sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string
4 #
5 SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ;
6 RPAD('a',0) RPAD('abc',1) RPAD('abc',2)
7 NULL a ab
8 SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ;
9 RPAD('a',0,'.') RPAD('abc',1,'.') RPAD('abc',2,'.')
10 NULL a ab
11 SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ;
12 LPAD('a',0) LPAD('abc',1) LPAD('abc',2)
13 NULL a ab
14 SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ;
15 LPAD('a',0,'.') LPAD('abc',1,'.') LPAD('abc',2,'.')
16 NULL a ab
17 CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER);
18 INSERT INTO t1 VALUES ('a',1,null,1);
19 INSERT INTO t1 VALUES ('a',null,'.',2);
20 INSERT INTO t1 VALUES (null,1,'.',3);
21 INSERT INTO t1 VALUES ('a',-1,'.',4);
22 INSERT INTO t1 VALUES ('a',0,'.',5);
23 INSERT INTO t1 VALUES ('a',1,'.',6);
24 INSERT INTO t1 VALUES ('a',2,'.',7);
25 SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord;
26 LPAD(c1,c2,c3) LPAD(c1,c2)
27 NULL a
28 NULL NULL
29 NULL NULL
30 NULL NULL
31 NULL NULL
32 a a
33 .a a
34 SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
35 RPAD(c1,c2,c3) RPAD(c1,c2)
36 NULL a
37 NULL NULL
38 NULL NULL
39 NULL NULL
40 NULL NULL
41 a a
42 a. a
43 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;
44 id select_type table type possible_keys key key_len ref rows filtered Extra
45 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using filesort
46 Warnings:
47 Note 1003 select rpad('a',0,'.') AS "RPAD('a',0,'.')",lpad('a',0,'.') AS "LPAD('a',0,'.')",lpad("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "LPAD(c1,c2,c3)",lpad("test"."t1"."c1","test"."t1"."c2") AS "LPAD(c1,c2)",rpad("test"."t1"."c1","test"."t1"."c2","test"."t1"."c3") AS "RPAD(c1,c2,c3)",rpad("test"."t1"."c1","test"."t1"."c2") AS "RPAD(c1,c2)" from "test"."t1" order by "test"."t1"."ord"
48 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;
49 SHOW CREATE VIEW v1;
50 View Create View character_set_client collation_connection
51 v1 CREATE VIEW "v1" AS select rpad('a',0,'.') AS "C1",lpad('a',0,'.') AS "C2",lpad("t1"."c1","t1"."c2","t1"."c3") AS "C3",lpad("t1"."c1","t1"."c2") AS "C4",rpad("t1"."c1","t1"."c2","t1"."c3") AS "C5",rpad("t1"."c1","t1"."c2") AS "C6" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci
52 SELECT * FROM v1;
53 C1 C2 C3 C4 C5 C6
54 NULL NULL NULL a NULL a
55 NULL NULL NULL NULL NULL NULL
56 NULL NULL NULL NULL NULL NULL
57 NULL NULL NULL NULL NULL NULL
58 NULL NULL NULL NULL NULL NULL
59 NULL NULL a a a a
60 NULL NULL .a a a. a
61 SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1;
62 c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6
63 ---a--a
64 -----
65 -----
66 -----
67 -----
68 --a-a-a-a
69 --.a- a-a.-a
70 DROP VIEW v1;
71 DROP TABLE t1;