[MDEV-30151] parse error 1=2 not between/in Created: 2022-12-04  Updated: 2023-02-06  Resolved: 2023-01-26

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.11.2, 10.3.39, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 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



 Comments   
Comment by Sergei Golubchik [ 2022-12-04 ]

this fails too:

select 1 = 2 not between 3 and 4;


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

Comment by Alexander Barkov [ 2022-12-13 ]

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

Comment by Alexander Barkov [ 2023-01-25 ]

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.

Comment by Sergei Golubchik [ 2023-01-25 ]

dacb40a8b9^ + dacb40a8b9 are ok to push

Comment by Alexander Barkov [ 2023-01-26 ]

squashing, rebasing.

Generated at Thu Feb 08 10:14:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.