[MDEV-18423] Unable to unset a flag from sql_mode set to oracle Created: 2019-01-30  Updated: 2019-04-17  Resolved: 2019-04-17

Status: Closed
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.3.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: 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)
 
 



 Comments   
Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-30 ]

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

Comment by Elena Stepanova [ 2019-01-30 ]

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.

Comment by Elena Stepanova [ 2019-01-30 ]

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.

Comment by Alexander Barkov [ 2019-03-29 ]

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?

Generated at Thu Feb 08 08:43:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.