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