1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
#
# MDEV-14012 - sql_mode=Oracle: substr(): treat position 0 as position 1
#
SET sql_mode=ORACLE;
SELECT SUBSTR('abc',2,1),SUBSTR('abc',1,1), SUBSTR('abc',0,1) FROM dual;
SELECT SUBSTR('abc',2),SUBSTR('abc',1), SUBSTR('abc',0) FROM dual;
SELECT SUBSTR(null,2,1),SUBSTR(null,1), SUBSTR(null,0) FROM dual;
SELECT SUBSTR('abc',-2),SUBSTR('abc',-1), SUBSTR('abc',-0) FROM dual;
SELECT SUBSTR('abc',-2,1),SUBSTR('abc',-1,1), SUBSTR('abc',-0,1) FROM dual;
SELECT SUBSTR('abc',null) FROM dual;
SELECT SUBSTR('abc',2,null),SUBSTR('abc',1,null), SUBSTR('abc',0,null) FROM dual;
SELECT SUBSTR('abc',2,0),SUBSTR('abc',1,0), SUBSTR('abc',0,0) FROM dual;
create table t1 (c1 varchar(10),start integer, length integer);
INSERT INTO t1 VALUES ('abc', 1, 1);
INSERT INTO t1 VALUES ('abc', 0, 1);
INSERT INTO t1 VALUES (null, 1, 1);
INSERT INTO t1 VALUES (null, 0, 1);
select substr(c1,start,length) from t1;
drop table t1;
EXPLAIN EXTENDED SELECT SUBSTR('abc',2,1) ;
CREATE VIEW v1 AS SELECT SUBSTR('abc',2,1) ;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;
|
