Debian Codegraph @

mysql-test/suite/compat/oracle/r/func_substr.result

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