Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6944

SQL_MODEs related to parsing are not applied during execution of the actual statement under SET STATEMENT .. FOR

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: N/A
    • Fix Version/s: 10.1.4
    • Component/s: OTHER
    • Labels:
      None

      Description

      Note: there are quite a few tests for ANSI mode in the patch, but they seem to work the other way round – checking that SQL_MODEs which are set under 'SET STATEMENT sql_mode=<mode>}} are not applied when the whole initial SET STATEMENT sql_mode=... FOR ... is being parsed; so I'm not sure if the behavior described below is intentional, which is why I set it to 'Minor' for now. It does not look logical, though.

      Example 1 - ANSI_QUOTES

      DROP TABLE IF EXISTS t1;
      # Query OK, 0 rows affected (0.18 sec)
       
      # Set sql_mode = 'ANSI_QUOTES' to allow the initial parsing of the whole SET STATEMENT .. FOR
       
      SET sql_mode='ANSI_QUOTES';
      # Query OK, 0 rows affected (0.00 sec)
       
      # But for the *actual* CREATE statement we unset the sql_mode, so 
      # the execution should fail with the syntax error
       
      SET STATEMENT sql_mode='' FOR CREATE TABLE "t1" (i INT);
      # Query OK, 0 rows affected (0.50 sec)
       
      # ... but it doesn't.
       
      # Compare with the usual behavior:
       
      SET sql_mode = '';
      # Query OK, 0 rows affected (0.00 sec)
       
      CREATE TABLE "t1" (i INT);
      # ERROR 1064 (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 '"t1" (i INT)' at line 1

      Example 2 - IGNORE_SPACE

      DROP TABLE IF EXISTS count;
      # Query OK, 0 rows affected (0.21 sec)
       
      # IGNORE_SPACE mode should prevent creation of the `count` table:
       
      SET STATEMENT sql_mode='IGNORE_SPACE' FOR CREATE TABLE count (i INT);
      # Query OK, 0 rows affected (0.81 sec)
       
      # ... but it doesn't.
       
      # Compare with the usual behavior:
       
      SET sql_mode='IGNORE_SPACE';
      # Query OK, 0 rows affected (0.00 sec)
       
      CREATE TABLE count (i INT);
      # ERROR 1064 (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 'count (i INT)' at line 1

      It's all the same in Percona server.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.