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

parse error 1=2 not between/in

Details

    Description

      CREATE TABLE t ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );


      CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );

      Both of the above fail with a syntax error:

      10.3 7baf24a0f8e

      		 
      MariaDB [test]> CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
      		 
      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 'not between 3 and 4' at line 1
      		 
      MariaDB [test]> show warnings;
      		 
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                                                                                 |
      		 
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Error | 1064 | 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 'not between 3 and 4' at line 1 |
      		 
      | Error | 1033 | Incorrect information in file: './test/t.frm'                                                                                                                           |
      		 
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      2 rows in set (0.000 sec)

      A view gets created, but fails afterwards:

      MariaDB [test]> CREATE VIEW v AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
      		 
      Query OK, 0 rows affected (0.015 sec)
      		 
       
      		 
      MariaDB [test]> show create view v;
      		 
      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 'not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )`' at line 1

      The .frm contains

      query=select 1 = 2 not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )`
      		 
      source=SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 )
      		 
      view_body_utf8=select 1 = 2 not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )`

      SELECT itself works:

      MariaDB [test]> SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 );
      		 
      +--------------------------------+
      		 
      | 1 IN ( 2 NOT BETWEEN 3 AND 4 ) |
      		 
      +--------------------------------+
      		 
      |                              1 |
      		 
      +--------------------------------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MariaDB [test]> SELECT 1 IN ( 2 NOT BETWEEN 2 AND 4 );
      		 
      +--------------------------------+
      		 
      | 1 IN ( 2 NOT BETWEEN 2 AND 4 ) |
      		 
      +--------------------------------+
      		 
      |                              0 |
      		 
      +--------------------------------+
      		 
      1 row in set (0.000 sec)

      The errors started happening after this commit in 10.2.35:

      commit 05a878c139963d4859ef8f2c974fee5dae56ee51
      		 
      Author: Sergei Golubchik
      		 
      Date:   Mon Oct 5 12:50:51 2020 +0200
      		 
       
      		 
          precedence bugfixing

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment - - edited

          this fails too:

          select 1 = 2 not between 3 and 4;


          select 1 = 2 not in (3,4);

          serg Sergei Golubchik added a comment - - edited this fails too: select 1 = 2 not between 3 and 4; select 1 = 2 not in (3,4);
          bar Alexander Barkov added a comment -

          serg, please review an alternative patch fixing the problem without adding new shift/reduce conflicts (and even reducing the number of conflicts):

          https://github.com/MariaDB/server/commit/4ddf606debf84bf7539eee8fef06ac47ab39677a

          bar Alexander Barkov added a comment - serg , please review an alternative patch fixing the problem without adding new shift/reduce conflicts (and even reducing the number of conflicts): https://github.com/MariaDB/server/commit/4ddf606debf84bf7539eee8fef06ac47ab39677a
          bar Alexander Barkov added a comment -

          serg, please see an incremental patch testing your review suggestions here:
          https://github.com/MariaDB/server/commit/dacb40a8b99e1a43c7ca66dacb758a8c6e5e2ad3
          and an email on maria-developers.

          Thanks.

          bar Alexander Barkov added a comment - serg , please see an incremental patch testing your review suggestions here: https://github.com/MariaDB/server/commit/dacb40a8b99e1a43c7ca66dacb758a8c6e5e2ad3 and an email on maria-developers. Thanks.
          serg Sergei Golubchik added a comment -

          dacb40a8b9^ + dacb40a8b9 are ok to push

          serg Sergei Golubchik added a comment - dacb40a8b9^ + dacb40a8b9 are ok to push
          bar Alexander Barkov added a comment -

          squashing, rebasing.

          bar Alexander Barkov added a comment - squashing, rebasing.

          People

            bar Alexander Barkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.