Debian Codegraph @

mysql-test/suite/compat/oracle/t/func_pad.test

raw
1 SET sql_mode=ORACLE;
2
3 --echo #
4 --echo # MDEV-15739 - sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string
5 --echo #
6
7 SELECT RPAD('a',0), RPAD('abc',1), RPAD('abc',2) ;
8 SELECT RPAD('a',0,'.'), RPAD('abc',1,'.'), RPAD('abc',2,'.') ;
9 SELECT LPAD('a',0), LPAD('abc',1), LPAD('abc',2) ;
10 SELECT LPAD('a',0,'.'), LPAD('abc',1,'.'), LPAD('abc',2,'.') ;
11
12 CREATE TABLE t1 (c1 VARCHAR(10),c2 INTEGER, c3 VARCHAR(10), ord INTEGER);
13 INSERT INTO t1 VALUES ('a',1,null,1);
14 INSERT INTO t1 VALUES ('a',null,'.',2);
15 INSERT INTO t1 VALUES (null,1,'.',3);
16 INSERT INTO t1 VALUES ('a',-1,'.',4);
17 INSERT INTO t1 VALUES ('a',0,'.',5);
18 INSERT INTO t1 VALUES ('a',1,'.',6);
19 INSERT INTO t1 VALUES ('a',2,'.',7);
20
21 SELECT LPAD(c1,c2,c3), LPAD(c1,c2) FROM t1 ORDER BY ord;
22 SELECT RPAD(c1,c2,c3), RPAD(c1,c2) FROM t1 ORDER BY ord;
23
24 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;
25
26 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;
27 SHOW CREATE VIEW v1;
28 SELECT * FROM v1;
29 SELECT c1||'-'||c2||'-'||c3||'-'||c4||'-'||c5||'-'||c6 FROM v1;
30 DROP VIEW v1;
31 DROP TABLE t1;