MDEV-20021 sql_mode="oracle" does not support MINUS set operator

MINUS is mapped to EXCEPT

Author: woqutech

6 files changed, 86 insertions, 2 deletions

diff --git a/mysql-test/suite/compat/oracle/r/minus.result b/mysql-test/suite/compat/oracle/r/minus.result
new file mode 100644
index 00000000000..0386100a51e
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/minus.result
@@ -0,0 +1,44 @@
+CREATE TABLE tx1 (c1 int, c2 varchar(30));
+CREATE TABLE tx2 (c1 int, c2 varchar(30));
+CREATE TABLE tx3 (c1 int, c2 varchar(30));
+INSERT INTO tx1 VALUES (1, 'jim');
+INSERT INTO tx1 VALUES (2, 'menny');
+INSERT INTO tx1 VALUES (3, 'linda');
+INSERT INTO tx2 VALUES (1, 'jim');
+INSERT INTO tx2 VALUES (2, 'kris');
+INSERT INTO tx2 VALUES (3, 'shory');
+INSERT INTO tx3 VALUES (1, 'jim');
+INSERT INTO tx3 VALUES (2, 'kris');
+INSERT INTO tx3 VALUES (3, 'linda');
+#
+# test when sql_mode is not oracle
+#
+SELECT c2 FROM tx1 EXCEPT SELECT c2 from tx2;
+c2
+menny
+linda
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
+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 'MINUS SELECT c2 from tx2' at line 1
+#
+# test when sql_mode is oracle
+#
+SET sql_mode=ORACLE;
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
+c2
+menny
+linda
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 MINUS SELECT c2 from tx3;
+c2
+menny
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 EXCEPT SELECT c2 from tx3;
+c2
+menny
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 UNION SELECT c2 from tx3;
+c2
+jim
+menny
+linda
+kris
+DROP TABLE tx1;
+DROP TABLE tx2;
+DROP TABLE tx3;
diff --git a/mysql-test/suite/compat/oracle/t/minus.test b/mysql-test/suite/compat/oracle/t/minus.test
new file mode 100644
index 00000000000..1be75740d9f
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/minus.test
@@ -0,0 +1,36 @@
+CREATE TABLE tx1 (c1 int, c2 varchar(30));
+CREATE TABLE tx2 (c1 int, c2 varchar(30));
+CREATE TABLE tx3 (c1 int, c2 varchar(30));
+INSERT INTO tx1 VALUES (1, 'jim');
+INSERT INTO tx1 VALUES (2, 'menny');
+INSERT INTO tx1 VALUES (3, 'linda');
+
+INSERT INTO tx2 VALUES (1, 'jim');
+INSERT INTO tx2 VALUES (2, 'kris');
+INSERT INTO tx2 VALUES (3, 'shory');
+
+INSERT INTO tx3 VALUES (1, 'jim');
+INSERT INTO tx3 VALUES (2, 'kris');
+INSERT INTO tx3 VALUES (3, 'linda');
+
+--echo #
+--echo # test when sql_mode is not oracle
+--echo #
+
+SELECT c2 FROM tx1 EXCEPT SELECT c2 from tx2;
+--error 1064
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
+
+--echo #
+--echo # test when sql_mode is oracle
+--echo #
+
+SET sql_mode=ORACLE;
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 MINUS SELECT c2 from tx3;
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 EXCEPT SELECT c2 from tx3;
+SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 UNION SELECT c2 from tx3;
+
+DROP TABLE tx1;
+DROP TABLE tx2;
+DROP TABLE tx3;
\ No newline at end of file
diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
index f538c1ecb26..5c3f793b5d2 100644
--- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
+++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
@@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1
 ####################################
 SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long;
 event_name	digest	digest_text	sql_text
-statement/sql/select	d2246116093fecdd0c2fa9a15a9aa724	SELECT ? + ? + 	SELECT ...
-statement/sql/truncate	2f59e63bd68dc3378f3feb48860859e7	TRUNCATE TABLE 	truncat...
+statement/sql/select	4c1aba56f2a1ca7cecf87972e590e2a3	SELECT ? + ? + 	SELECT ...
+statement/sql/truncate	654f3b63c6b4e3465a22fb477b9df254	TRUNCATE TABLE 	truncat...
diff --git a/sql/lex.h b/sql/lex.h
index 09933a8020a..752d2aaa3c5 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -400,6 +400,7 @@ static SYMBOL symbols[] = {
   { "MICROSECOND",	SYM(MICROSECOND_SYM)},
   { "MIDDLEINT",	SYM(MEDIUMINT)},	/* For powerbuilder */
   { "MIGRATE",          SYM(MIGRATE_SYM)},
+  { "MINUS",            SYM(MINUS_ORACLE_SYM)},
   { "MINUTE",		SYM(MINUTE_SYM)},
   { "MINUTE_MICROSECOND", SYM(MINUTE_MICROSECOND_SYM)},
   { "MINUTE_SECOND",	SYM(MINUTE_SECOND_SYM)},
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 7b81fa754d2..1e7f3c3ef4d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1413,6 +1413,7 @@ int Lex_input_stream::find_keyword(Lex_ident_cli_st *kwd,
       case EXCEPTION_MARIADB_SYM:      return EXCEPTION_ORACLE_SYM;
       case EXIT_MARIADB_SYM:           return EXIT_ORACLE_SYM;
       case GOTO_MARIADB_SYM:           return GOTO_ORACLE_SYM;
+      case MINUS_ORACLE_SYM:           return EXCEPT_SYM;
       case NUMBER_MARIADB_SYM:         return NUMBER_ORACLE_SYM;
       case OTHERS_MARIADB_SYM:         return OTHERS_ORACLE_SYM;
       case PACKAGE_MARIADB_SYM:        return PACKAGE_ORACLE_SYM;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 43f5c304f33..28e8869db29 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -575,6 +575,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
 %token  <kwd> MEDIUMINT
 %token  <kwd> MEDIUMTEXT
 %token  <kwd> MIN_SYM                       /* SQL-2003-N */
+%token  <kwd> MINUS_ORACLE_SYM              /* Oralce-R   */
 %token  <kwd> MINUTE_MICROSECOND_SYM
 %token  <kwd> MINUTE_SECOND_SYM
 %token  <kwd> MODIFIES_SYM                  /* SQL-2003-R */
@@ -16001,6 +16002,7 @@ reserved_keyword_udt_not_param_type:
         | MINUTE_MICROSECOND_SYM
         | MINUTE_SECOND_SYM
         | MIN_SYM
+        | MINUS_ORACLE_SYM
         | MODIFIES_SYM
         | MOD_SYM
         | NATURAL