1--echo # 2--echo # MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1 3--echo # MDEV-10574 - sql_mode=Oracle: return null instead of empty string 4--echo # 5 6SET sql_mode=ORACLE; 7SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual; 8SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual; 9SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual; 10SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual; 11SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual; 12SELECT SUBSTR('abc',null) FROM dual; 13SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual; 14SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual; 15SELECT SUBSTR('abc',2,-1),SUBSTR('abc',1,-1), SUBSTR('abc',0,-1) FROM dual; 16SELECT SUBSTR(SPACE(0),1) FROM DUAL; 17 18CREATE TABLE t1 (c1 VARCHAR(10),start INTEGER, length INTEGER); 19INSERT INTO t1 VALUES ('abc', 1, 1); 20INSERT INTO t1 VALUES ('abc', 0, 1); 21INSERT INTO t1 VALUES (null, 1, 1); 22INSERT INTO t1 VALUES (null, 0, 1); 23INSERT INTO t1 VALUES ('abc', 1, 0); 24INSERT INTO t1 VALUES ('abc', 0, 0); 25INSERT INTO t1 VALUES (null, 1, 0); 26INSERT INTO t1 VALUES (null, 0, 0); 27INSERT INTO t1 VALUES ('abc', 1, -1); 28INSERT INTO t1 VALUES ('abc', 0, -1); 29INSERT INTO t1 VALUES (null, 1, -1); 30INSERT INTO t1 VALUES (null, 0, -1); 31INSERT INTO t1 VALUES (SPACE(0), 0, 1); 32 33SELECT SUBSTR(c1,start,length) FROM t1; 34DROP TABLE t1; 35 36CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL); 37CREATE TABLE t2 AS SELECT SUBSTR(C1,1,1) AS C1 from t1; 38SHOW CREATE TABLE t2; 39DROP TABLE t2; 40DROP TABLE t1; 41 42EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ; 43 44CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ; 45SHOW CREATE VIEW v1; 46SELECT * FROM v1; 47DROP VIEW v1; 48