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