Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
4 #
5 SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM(' '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
6 TRIM('abc') TRIM('abc ')||'.' '.'||TRIM(' abc ')||'.' TRIM(' ') TRIM(NULL) TRIM(SPACE(0)) TRIM(SPACE(10))
7 abc abc. .abc. NULL NULL NULL NULL
8 SELECT TRIM(TRAILING 'abc' FROM 'abc');
9 TRIM(TRAILING 'abc' FROM 'abc')
10 NULL
11 SELECT TRIM(TRAILING 'abc' FROM 'abc ');
12 TRIM(TRAILING 'abc' FROM 'abc ')
13 abc
14 SELECT TRIM(TRAILING 'abc' FROM ' abc');
15 TRIM(TRAILING 'abc' FROM ' abc')
16
17 SELECT TRIM(LEADING 'abc' FROM 'abc');
18 TRIM(LEADING 'abc' FROM 'abc')
19 NULL
20 SELECT TRIM(LEADING 'abc' FROM 'abc ');
21 TRIM(LEADING 'abc' FROM 'abc ')
22
23 SELECT TRIM(LEADING 'abc' FROM ' abc');
24 TRIM(LEADING 'abc' FROM ' abc')
25 abc
26 SELECT TRIM(BOTH 'abc' FROM 'abc');
27 TRIM(BOTH 'abc' FROM 'abc')
28 NULL
29 SELECT TRIM(BOTH 'abc' FROM 'abc ');
30 TRIM(BOTH 'abc' FROM 'abc ')
31
32 SELECT TRIM(BOTH 'abc' FROM ' abc');
33 TRIM(BOTH 'abc' FROM ' abc')
34
35 SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM(' '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
36 RTRIM('abc') RTRIM('abc ')||'.' RTRIM(' abc ')||'.' RTRIM(' ') RTRIM(NULL) RTRIM(SPACE(0)) RTRIM(SPACE(10))
37 abc abc. abc. NULL NULL NULL NULL
38 SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM(' '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
39 LTRIM('abc') LTRIM('abc ') LTRIM(' abc ') LTRIM(' ') LTRIM(NULL) LTRIM(SPACE(0)) LTRIM(SPACE(10))
40 abc abc abc NULL NULL NULL NULL
41 CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
42 INSERT INTO t1 VALUES ('abc',1);
43 INSERT INTO t1 VALUES (SPACE(0),2);
44 INSERT INTO t1 VALUES ('',3);
45 INSERT INTO t1 VALUES (' ',4);
46 INSERT INTO t1 VALUES (' ',5);
47 INSERT INTO t1 VALUES (' a ',6);
48 INSERT INTO t1 VALUES ('aa',7);
49 INSERT INTO t1 VALUES ('aabb',8);
50 INSERT INTO t1 VALUES ('bbaa',9);
51 INSERT INTO t1 VALUES ('aabbaa',10);
52 SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
53 ord '['||c1||']' '.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.'
54 1 [abc] .bc.
55 2 [] .NULL.
56 3 [] .NULL.
57 4 [ ] . .
58 5 [ ] . .
59 6 [ a ] . a .
60 7 [aa] .NULL.
61 8 [aabb] .bb.
62 9 [bbaa] .bbaa.
63 10 [aabbaa] .bbaa.
64 SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
65 ord '['||c1||']' '.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.'
66 1 [abc] .abc.
67 2 [] .NULL.
68 3 [] .NULL.
69 4 [ ] . .
70 5 [ ] . .
71 6 [ a ] . a .
72 7 [aa] .NULL.
73 8 [aabb] .aabb.
74 9 [bbaa] .bb.
75 10 [aabbaa] .aabb.
76 SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
77 ord '['||c1||']' '.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.'
78 1 [abc] .bc.
79 2 [] .NULL.
80 3 [] .NULL.
81 4 [ ] . .
82 5 [ ] . .
83 6 [ a ] . a .
84 7 [aa] .NULL.
85 8 [aabb] .bb.
86 9 [bbaa] .bb.
87 10 [aabbaa] .bb.
88 SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
89 ord '['||c1||']' COALESCE(LTRIM(c1),'NULL')
90 1 [abc] abc
91 2 [] NULL
92 3 [] NULL
93 4 [ ] NULL
94 5 [ ] NULL
95 6 [ a ] a
96 7 [aa] aa
97 8 [aabb] aabb
98 9 [bbaa] bbaa
99 10 [aabbaa] aabbaa
100 SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
101 ord '['||c1||']' COALESCE(RTRIM(c1),'NULL')||'.'
102 1 [abc] abc.
103 2 [] NULL.
104 3 [] NULL.
105 4 [ ] NULL.
106 5 [ ] NULL.
107 6 [ a ] a.
108 7 [aa] aa.
109 8 [aabb] aabb.
110 9 [bbaa] bbaa.
111 10 [aabbaa] aabbaa.
112 EXPLAIN EXTENDED SELECT TRIM('abc'),
113 TRIM(BOTH 'a' FROM 'abc'),
114 TRIM(LEADING 'a' FROM 'abc'),
115 TRIM(TRAILING 'a' FROM 'abc') ;
116 id select_type table type possible_keys key key_len ref rows filtered Extra
117 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
118 Warnings:
119 Note 1003 select trim('abc') AS "TRIM('abc')",trim(both 'a' from 'abc') AS "TRIM(BOTH 'a' FROM 'abc')",trim(leading 'a' from 'abc') AS "TRIM(LEADING 'a' FROM 'abc')",trim(trailing 'a' from 'abc') AS "TRIM(TRAILING 'a' FROM 'abc')"
120 EXPLAIN EXTENDED SELECT RTRIM('abc'),
121 LTRIM('abc');
122 id select_type table type possible_keys key key_len ref rows filtered Extra
123 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
124 Warnings:
125 Note 1003 select rtrim('abc') AS "RTRIM('abc')",ltrim('abc') AS "LTRIM('abc')"
126 CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
127 '['||c1||']',
128 TRIM(LEADING 'a' FROM c1),
129 TRIM(TRAILING 'a' FROM c1),
130 TRIM(BOTH 'a' FROM c1),
131 LTRIM(c1),
132 RTRIM(c1)
133 FROM t1 ORDER BY ord ;
134 SHOW CREATE VIEW v1;
135 View Create View character_set_client collation_connection
136 v1 CREATE VIEW "v1" AS select "t1"."ord" AS "ord",trim('abc') AS "TRIM('abc')",rtrim('abc') AS "RTRIM('abc')",ltrim('abc') AS "LTRIM('abc')",concat(concat('[',"t1"."c1"),']') AS "'['||c1||']'",trim(leading 'a' from "t1"."c1") AS "TRIM(LEADING 'a' FROM c1)",trim(trailing 'a' from "t1"."c1") AS "TRIM(TRAILING 'a' FROM c1)",trim(both 'a' from "t1"."c1") AS "TRIM(BOTH 'a' FROM c1)",ltrim("t1"."c1") AS "LTRIM(c1)",rtrim("t1"."c1") AS "RTRIM(c1)" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci
137 SELECT * FROM v1;
138 ord TRIM('abc') RTRIM('abc') LTRIM('abc') '['||c1||']' TRIM(LEADING 'a' FROM c1) TRIM(TRAILING 'a' FROM c1) TRIM(BOTH 'a' FROM c1) LTRIM(c1) RTRIM(c1)
139 1 abc abc abc [abc] bc abc bc abc abc
140 2 abc abc abc [] NULL NULL NULL NULL NULL
141 3 abc abc abc [] NULL NULL NULL NULL NULL
142 4 abc abc abc [ ] NULL NULL
143 5 abc abc abc [ ] NULL NULL
144 6 abc abc abc [ a ] a a a a a
145 7 abc abc abc [aa] NULL NULL NULL aa aa
146 8 abc abc abc [aabb] bb aabb bb aabb aabb
147 9 abc abc abc [bbaa] bbaa bb bb bbaa bbaa
148 10 abc abc abc [aabbaa] bbaa aabb bb aabbaa aabbaa
149 DROP VIEW v1;
150 DROP TABLE t1;
151 CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
152 CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
153 TRIM(TRAILING 'a' FROM c1) AS C2,
154 TRIM(BOTH 'a' FROM c1) AS C3,
155 LTRIM(c1) AS C4,
156 RTRIM(c1) AS C5
157 FROM t1;
158 SHOW CREATE TABLE t2;
159 Table Create Table
160 t2 CREATE TABLE "t2" (
161 "C1" varchar(10) DEFAULT NULL,
162 "C2" varchar(10) DEFAULT NULL,
163 "C3" varchar(10) DEFAULT NULL,
164 "C4" varchar(10) DEFAULT NULL,
165 "C5" varchar(10) DEFAULT NULL
166 )
167 DROP TABLE t2;
168 DROP TABLE t1;
169 CREATE TABLE trim_oracle (trim_oracle int);
170 DROP TABLE trim_oracle;