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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • N/A
    • 10.1.4
    • OTHER
    • 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

            It can't work because all statement parsed as one. (doc should be updated)

            sanja Oleksandr Byelkin added a comment - It can't work because all statement parsed as one. (doc should be updated)

            could be fixed by early assignment after parsing SET STATEMENT part, but 7006 have to be approved first...

            sanja Oleksandr Byelkin added a comment - could be fixed by early assignment after parsing SET STATEMENT part, but 7006 have to be approved first...

            Most part of this bug fixed in MDEV-7012

            sanja Oleksandr Byelkin added a comment - Most part of this bug fixed in MDEV-7012

            revision-id: 7054be616427d9a3c92f321741b3cd120321281f
            parent(s): 622611bc6a9371803f349d8cf4de6538ebbcdb37
            committer: Oleksandr Byelkin
            branch nick: server
            timestamp: 2015-03-18 00:10:42 +0100
            message:

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

            Actually fix for MDEV-7012 is fixing it, here is only parsing mode recalculating added and test suite.

            sanja Oleksandr Byelkin added a comment - revision-id: 7054be616427d9a3c92f321741b3cd120321281f parent(s): 622611bc6a9371803f349d8cf4de6538ebbcdb37 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-03-18 00:10:42 +0100 message: MDEV-6944 : SQL_MODEs related to parsing are not applied during execution of the actual statement under SET STATEMENT .. FOR Actually fix for MDEV-7012 is fixing it, here is only parsing mode recalculating added and test suite. —

            the behavior will be described in documantation

            sanja Oleksandr Byelkin added a comment - the behavior will be described in documantation

            People

              sanja Oleksandr Byelkin
              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.