1# 2# MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1 3# MDEV-10574 - sql_mode=Oracle: return null instead of empty string 4# 5SET sql_mode=ORACLE; 6SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual; 7SUBSTR('abc',2,1) SUBSTR('abc',1,1) SUBSTR('abc',0,1) 8b a a 9SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual; 10SUBSTR('abc',2) SUBSTR('abc',1) SUBSTR('abc',0) 11bc abc abc 12SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual; 13SUBSTR(null,2,1) SUBSTR(null,1) SUBSTR(null,0) 14NULL NULL NULL 15SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual; 16SUBSTR('abc',-2) SUBSTR('abc',-1) SUBSTR('abc',-0) 17bc c abc 18SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual; 19SUBSTR('abc',-2,1) SUBSTR('abc',-1,1) SUBSTR('abc',-0,1) 20b c a 21SELECT SUBSTR('abc',null) FROM dual; 22SUBSTR('abc',null) 23NULL 24SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual; 25SUBSTR('abc',2,null) SUBSTR('abc',1,null) SUBSTR('abc',0,null) 26NULL NULL NULL 27SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual; 28SUBSTR('abc',2,0) SUBSTR('abc',1,0) SUBSTR('abc',0,0) 29NULL NULL NULL 30SELECT SUBSTR('abc',2,-1),SUBSTR('abc',1,-1), SUBSTR('abc',0,-1) FROM dual; 31SUBSTR('abc',2,-1) SUBSTR('abc',1,-1) SUBSTR('abc',0,-1) 32NULL NULL NULL 33SELECT SUBSTR(SPACE(0),1) FROM DUAL; 34SUBSTR(SPACE(0),1) 35NULL 36CREATE TABLE t1 (c1 VARCHAR(10),start INTEGER, length INTEGER); 37INSERT INTO t1 VALUES ('abc', 1, 1); 38INSERT INTO t1 VALUES ('abc', 0, 1); 39INSERT INTO t1 VALUES (null, 1, 1); 40INSERT INTO t1 VALUES (null, 0, 1); 41INSERT INTO t1 VALUES ('abc', 1, 0); 42INSERT INTO t1 VALUES ('abc', 0, 0); 43INSERT INTO t1 VALUES (null, 1, 0); 44INSERT INTO t1 VALUES (null, 0, 0); 45INSERT INTO t1 VALUES ('abc', 1, -1); 46INSERT INTO t1 VALUES ('abc', 0, -1); 47INSERT INTO t1 VALUES (null, 1, -1); 48INSERT INTO t1 VALUES (null, 0, -1); 49INSERT INTO t1 VALUES (SPACE(0), 0, 1); 50SELECT SUBSTR(c1,start,length) FROM t1; 51SUBSTR(c1,start,length) 52a 53a 54NULL 55NULL 56NULL 57NULL 58NULL 59NULL 60NULL 61NULL 62NULL 63NULL 64NULL 65DROP TABLE t1; 66CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL); 67CREATE TABLE t2 AS SELECT SUBSTR(C1,1,1) AS C1 from t1; 68SHOW CREATE TABLE t2; 69Table Create Table 70t2 CREATE TABLE "t2" ( 71 "C1" varchar(1) DEFAULT NULL 72) 73DROP TABLE t2; 74DROP TABLE t1; 75EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ; 76id select_type table type possible_keys key key_len ref rows filtered Extra 771 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 78Warnings: 79Note 1003 select substr_oracle('abc',2,1) AS "SUBSTR('abc',2,1)" 80CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ; 81SHOW CREATE VIEW v1; 82View Create View character_set_client collation_connection 83v1 CREATE VIEW "v1" AS select substr_oracle('abc',2,1) AS "SUBSTR('abc',2,1)" latin1 latin1_swedish_ci 84SELECT * FROM v1; 85SUBSTR('abc',2,1) 86b 87DROP VIEW v1; 88