Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 SELECT DECODE(10);
3 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE'
4 SELECT DECODE(10,10);
5 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE'
6 SELECT DECODE(10,10,'x10');
7 DECODE(10,10,'x10')
8 x10
9 SELECT DECODE(11,10,'x10');
10 DECODE(11,10,'x10')
11 NULL
12 SELECT DECODE(10,10,'x10','def');
13 DECODE(10,10,'x10','def')
14 x10
15 SELECT DECODE(11,10,'x10','def');
16 DECODE(11,10,'x10','def')
17 def
18 SELECT DECODE(10,10,'x10',11,'x11','def');
19 DECODE(10,10,'x10',11,'x11','def')
20 x10
21 SELECT DECODE(11,10,'x10',11,'x11','def');
22 DECODE(11,10,'x10',11,'x11','def')
23 x11
24 SELECT DECODE(12,10,'x10',11,'x11','def');
25 DECODE(12,10,'x10',11,'x11','def')
26 def
27 EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def');
28 id select_type table type possible_keys key key_len ref rows filtered Extra
29 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
30 Warnings:
31 Note 1003 select decode(12,10,'x10',11,'x11','def') AS "DECODE(12,10,'x10',11,'x11','def')"
32 CREATE TABLE decode (decode int);
33 DROP TABLE decode;
34 #
35 # MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent
36 #
37 SELECT DECODE(10);
38 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE'
39 SELECT DECODE(10,10);
40 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE'
41 SELECT DECODE_ORACLE(10);
42 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE_ORACLE'
43 SELECT DECODE_ORACLE(10,10);
44 ERROR 42000: Incorrect parameter count in the call to native function 'DECODE_ORACLE'
45 EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11');
46 id select_type table type possible_keys key key_len ref rows filtered Extra
47 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
48 Warnings:
49 Note 1003 select decode(12,10,'x10',11,'x11') AS "DECODE(12,10,'x10',11,'x11')"
50 EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def');
51 id select_type table type possible_keys key key_len ref rows filtered Extra
52 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
53 Warnings:
54 Note 1003 select decode(12,10,'x10',11,'x11','def') AS "DECODE(12,10,'x10',11,'x11','def')"
55 EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11');
56 id select_type table type possible_keys key key_len ref rows filtered Extra
57 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
58 Warnings:
59 Note 1003 select decode(12,10,'x10',11,'x11') AS "DECODE_ORACLE(12,10,'x10',11,'x11')"
60 EXPLAIN EXTENDED SELECT DECODE_ORACLE(12,10,'x10',11,'x11','def');
61 id select_type table type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
63 Warnings:
64 Note 1003 select decode(12,10,'x10',11,'x11','def') AS "DECODE_ORACLE(12,10,'x10',11,'x11','def')"
65 CREATE TABLE t1 (a INT);
66 CREATE VIEW v1 AS
67 SELECT
68 DECODE(a,1,'x1',NULL,'xNULL') AS d1,
69 DECODE(a,1,'x1',NULL,'xNULL','xELSE') AS d2,
70 DECODE_ORACLE(a,1,'x1',NULL,'xNULL') AS d3,
71 DECODE_ORACLE(a,1,'x1',NULL,'xNULL','xELSE') AS d4
72 FROM t1;
73 SHOW CREATE VIEW v1;
74 View Create View character_set_client collation_connection
75 v1 CREATE VIEW "v1" AS select decode("t1"."a",1,'x1',NULL,'xNULL') AS "d1",decode("t1"."a",1,'x1',NULL,'xNULL','xELSE') AS "d2",decode("t1"."a",1,'x1',NULL,'xNULL') AS "d3",decode("t1"."a",1,'x1',NULL,'xNULL','xELSE') AS "d4" from "t1" latin1 latin1_swedish_ci
76 DROP VIEW v1;
77 DROP TABLE t1;
78 SELECT DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def');
79 DECODE(TIME'10:20:31','10:20:31','then1','10:20:32','then2','def')
80 then1
81 SELECT DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def');
82 DECODE(TIME'10:20:32','10:20:31','then1','10:20:32','then2','def')
83 then2
84 SELECT DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def');
85 DECODE(TIME'10:20:33','10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def')
86 then3
87 SELECT DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def');
88 DECODE(NULL,TIME'10:20:31','then1',NULL,'then2NULL','10:20:33','then3','def')
89 then2NULL
90 SELECT DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def');
91 DECODE(TIMESTAMP'2001-01-01 10:20:31','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def')
92 then1
93 SELECT DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def');
94 DECODE(TIMESTAMP'2001-01-01 10:20:32','2001-01-01 10:20:31','then1','2001-01-01 10:20:32','then2','def')
95 then2
96 SELECT DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def');
97 DECODE(TIMESTAMP'2001-01-01 10:20:33','2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def')
98 then3
99 SELECT DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def');
100 DECODE(NULL,TIMESTAMP'2001-01-01 10:20:31','then1',NULL,'then2NULL','2001-01-01 10:20:33','then3','def')
101 then2NULL
102 SELECT DECODE('w1','w1','then1','w2','then2','def');
103 DECODE('w1','w1','then1','w2','then2','def')
104 then1
105 SELECT DECODE('w2','w1','then1','w2','then2','def');
106 DECODE('w2','w1','then1','w2','then2','def')
107 then2
108 SELECT DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def');
109 DECODE('w3','w1','then1',NULL,'then2NULL','w3','then3','def')
110 then3
111 SELECT DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def');
112 DECODE(NULL,'w1','then1',NULL,'then2NULL','w3','then3','def')
113 then2NULL
114 SELECT DECODE(1,1,'then1',2,'then2','def');
115 DECODE(1,1,'then1',2,'then2','def')
116 then1
117 SELECT DECODE(2,1,'then1',2,'then2','def');
118 DECODE(2,1,'then1',2,'then2','def')
119 then2
120 SELECT DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def');
121 DECODE(3,1,'then1',NULL,'then2NULL',3,'then3','def')
122 then3
123 SELECT DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def');
124 DECODE(NULL,1,'then1',NULL,'then2NULL',3,'then3','def')
125 then2NULL
126 SELECT DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def');
127 DECODE(CAST(NULL AS SIGNED),1,'then1',NULL,'then2NULL',3,'then3','def')
128 then2NULL
129 SELECT DECODE(1.0,1.0,'then1',2.0,'then2','def');
130 DECODE(1.0,1.0,'then1',2.0,'then2','def')
131 then1
132 SELECT DECODE(2.0,1.0,'then1',2.0,'then2','def');
133 DECODE(2.0,1.0,'then1',2.0,'then2','def')
134 then2
135 SELECT DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def');
136 DECODE(3.0,1.0,'then1',NULL,'then2NULL',3.0,'then3','def')
137 then3
138 SELECT DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def');
139 DECODE(NULL,1.0,'then1',NULL,'then2NULL',3.0,'then3','def')
140 then2NULL
141 SELECT DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def');
142 DECODE(CAST(NULL AS DECIMAL),1.0,'then1',NULL,'then2NULL',3.0,'then3','def')
143 then2NULL
144 SELECT DECODE(1e0,1e0,'then1',2e0,'then2','def');
145 DECODE(1e0,1e0,'then1',2e0,'then2','def')
146 then1
147 SELECT DECODE(2e0,1e0,'then1',2e0,'then2','def');
148 DECODE(2e0,1e0,'then1',2e0,'then2','def')
149 then2
150 SELECT DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def');
151 DECODE(3e0,1e0,'then1',NULL,'then2NULL',3e0,'then3','def')
152 then3
153 SELECT DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def');
154 DECODE(NULL,1e0,'then1',NULL,'then2NULL',3e0,'then3','def')
155 then2NULL
156 SELECT DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def');
157 DECODE(CAST(NULL AS DOUBLE),1e0,'then1',NULL,'then2NULL',3e0,'then3','def')
158 then2NULL
159 SELECT DECODE(NULL,NULL,1,2) FROM DUAL;
160 DECODE(NULL,NULL,1,2)
161 1
162 SELECT DECODE(NULL,10,10,NULL,1,2) FROM DUAL;
163 DECODE(NULL,10,10,NULL,1,2)
164 1
165 SELECT DECODE_ORACLE(NULL,NULL,1,2) FROM DUAL;
166 DECODE_ORACLE(NULL,NULL,1,2)
167 1
168 SELECT DECODE_ORACLE(NULL,10,10,NULL,1,2) FROM DUAL;
169 DECODE_ORACLE(NULL,10,10,NULL,1,2)
170 1
171 CREATE OR REPLACE TABLE t1 (a VARCHAR(10) DEFAULT NULL);
172 INSERT INTO t1 VALUES (NULL),(1);
173 SELECT a, DECODE(a,NULL,1,2) FROM t1;
174 a DECODE(a,NULL,1,2)
175 NULL 1
176 1 2
177 DROP TABLE t1;