Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.11
-
None
Description
Unable to unset a flag from sql_mode set to oracle
MariaDB [(none)]> SET session sql_mode = 'ORACLE';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [(none)]> SELECT @@SQL_MODE;
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| @@SQL_MODE |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
|
MariaDB [(none)]> SET session sql_mode=(SELECT REPLACE(@@sql_mode,'ANSI_QUOTES',''));
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [(none)]> SELECT @@SQL_MODE;
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| @@SQL_MODE |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
|
+----------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
|
Attachments
Activity
As explicitly documented in MariaDB KB (and in MySQL manual too, if you prefer it), ORACLE is in fact an alias for a collection of modes, one of which is ANSI_QUOTES. The fact that starting from 10.3 setting the mode to ORACLE also makes the server understand a subset of PL/SQL syntax doesn't change the fact that it's still a collection of modes. You can't unset one element, but keep it the collection. If you unset ANSI_QUOTES, it's not "ORACLE" mode anymore. If you want to have all other elements of the collection except for ANSI_QUOTES, you need to unset/remove the ORACLE switch as well.
On the second thought, while all the above is true for the current versions, I also don't like it much that ORACLE mode has a double role – an alias for a set of modes and a switch for PL/SQL syntax. I guess it's an unfortunate legacy which isn't easy to overcome, but let's see if bar considers any possibility to change it in future.
winstone, is my understanding correct that in your session you want:
- enable Oracle syntax for stored procedures
- but disable ANSI_QUOTES at the same time
?
Why this combination might be needed?
unset ANSI_QUOTES actually , with MariaDB [(none)]> SET session sql_mode=(SELECT REPLACE(@@sql_mode,'ANSI_QUOTES','')); is expected to unset the ANSI_QUOTES flag