MDEV-10342 Providing compatibility for basic SQL built-in functions

Adding the Oracle style DECODE function:

  DECODE(operand, search, result [, search, result ...] [, default_result])

4 files changed, 59 insertions, 0 deletions

diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index 7031adcca8f..d9b8d911ef4 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -346,3 +346,5 @@ CREATE TABLE raw (raw int);
 DROP TABLE raw;
 CREATE TABLE varchar2 (varchar2 int);
 DROP TABLE varchar2;
+CREATE TABLE decode (decode int);
+DROP TABLE decode;
diff --git a/mysql-test/suite/compat/oracle/r/func_decode.result b/mysql-test/suite/compat/oracle/r/func_decode.result
new file mode 100644
index 00000000000..a7c0ffdc264
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_decode.result
@@ -0,0 +1,33 @@
+SET sql_mode=ORACLE;
+SELECT DECODE(10);
+ERROR 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
+SELECT DECODE(10,10);
+ERROR 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
+SELECT DECODE(10,10,'x10');
+DECODE(10,10,'x10')
+x10
+SELECT DECODE(11,10,'x10');
+DECODE(11,10,'x10')
+NULL
+SELECT DECODE(10,10,'x10','def');
+DECODE(10,10,'x10','def')
+x10
+SELECT DECODE(11,10,'x10','def');
+DECODE(11,10,'x10','def')
+def
+SELECT DECODE(10,10,'x10',11,'x11','def');
+DECODE(10,10,'x10',11,'x11','def')
+x10
+SELECT DECODE(11,10,'x10',11,'x11','def');
+DECODE(11,10,'x10',11,'x11','def')
+x11
+SELECT DECODE(12,10,'x10',11,'x11','def');
+DECODE(12,10,'x10',11,'x11','def')
+def
+EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select (case 12 when 10 then 'x10' when 11 then 'x11' else 'def' end) AS "DECODE(12,10,'x10',11,'x11','def')"
+CREATE TABLE decode (decode int);
+DROP TABLE decode;
diff --git a/mysql-test/suite/compat/oracle/t/func_decode.test b/mysql-test/suite/compat/oracle/t/func_decode.test
new file mode 100644
index 00000000000..ae05cb2c3d1
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_decode.test
@@ -0,0 +1,21 @@
+SET sql_mode=ORACLE;
+
+--error ER_PARSE_ERROR
+SELECT DECODE(10);
+--error ER_PARSE_ERROR
+SELECT DECODE(10,10);
+
+SELECT DECODE(10,10,'x10');
+SELECT DECODE(11,10,'x10');
+
+SELECT DECODE(10,10,'x10','def');
+SELECT DECODE(11,10,'x10','def');
+
+SELECT DECODE(10,10,'x10',11,'x11','def');
+SELECT DECODE(11,10,'x10',11,'x11','def');
+SELECT DECODE(12,10,'x10',11,'x11','def');
+
+EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def');
+
+CREATE TABLE decode (decode int);
+DROP TABLE decode;
diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test
index b0b85539ee7..b9e52ab8308 100644
--- a/mysql-test/t/keywords.test
+++ b/mysql-test/t/keywords.test
@@ -241,3 +241,6 @@ DROP TABLE raw;
 
 CREATE TABLE varchar2 (varchar2 int);
 DROP TABLE varchar2;
+
+CREATE TABLE decode (decode int);
+DROP TABLE decode;