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

parse error 1=2 not between/in

    XMLWordPrintable

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

          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.