[MDEV-30151] parse error 1=2 not between/in - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 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
Component/s: Parser
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

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2022-12-04 20:46

Elena Stepanova made changes - 2022-12-04 20:47

Field	Original Value	New Value
Description	{code:sql} CREATE TABLE t ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); {code} {code:sql} CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); {code} Both of the above fail with a syntax error: {code:sql\|title=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) {code} A view gets created, but fails afterwards: {code:sql} 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 {code} The .frm contains {code:sql} 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 )` {code} The errors started happening after this commit in 10.2.35: {noformat} commit 05a878c139963d4859ef8f2c974fee5dae56ee51 Author: Sergei Golubchik Date: Mon Oct 5 12:50:51 2020 +0200 precedence bugfixing {noformat}	{code:sql} CREATE TABLE t ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); {code} {code:sql} CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); {code} Both of the above fail with a syntax error: {code:sql\|title=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) {code} A view gets created, but fails afterwards: {code:sql} 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 {code} The .frm contains {code:sql} 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 )` {code} SELECT itself works: {code:sql} 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) {code} The errors started happening after this commit in 10.2.35: {noformat} commit 05a878c139963d4859ef8f2c974fee5dae56ee51 Author: Sergei Golubchik Date: Mon Oct 5 12:50:51 2020 +0200 precedence bugfixing {noformat}

Elena Stepanova made changes - 2022-12-04 20:47

Description

{code:sql}
CREATE TABLE t ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
{code}
{code:sql}
CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
{code}

Both of the above fail with a syntax error:
{code:sql|title=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)
{code}

A view gets created, but fails afterwards:
{code:sql}
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
{code}
The .frm contains
{code:sql}
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 )`
{code}

SELECT itself works:
{code:sql}
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)
{code}

The errors started happening after this commit in 10.2.35:
{noformat}
commit 05a878c139963d4859ef8f2c974fee5dae56ee51
Author: Sergei Golubchik
Date: Mon Oct 5 12:50:51 2020 +0200

precedence bugfixing
{noformat}

{code:sql}
CREATE TABLE t ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
{code}
{code:sql}
CREATE TABLE t ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) );
{code}

Both of the above fail with a syntax error:
{code:sql|title=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)
{code}

A view gets created, but fails afterwards:
{code:sql}
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
{code}
The .frm contains
{code:sql}
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 )`
{code}

SELECT itself works:
{code:sql}
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)
{code}

The errors started happening after this commit in 10.2.35:
{noformat}
commit 05a878c139963d4859ef8f2c974fee5dae56ee51
Author: Sergei Golubchik
Date: Mon Oct 5 12:50:51 2020 +0200

precedence bugfixing
{noformat}

Elena Stepanova made changes - 2022-12-04 20:48

Summary

Syntax error upon virtual column / check creation or getting view definition

Syntax error upon creating virtual column / check or getting view definition

Sergei Golubchik made changes - 2022-12-04 22:41

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2022-12-04 22:41

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2022-12-04 23:11

Summary

Syntax error upon creating virtual column / check or getting view definition

parse error 1=2 not between/in

Sergei Golubchik made changes - 2022-12-04 23:23

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2022-12-13 06:38

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2022-12-28 15:20

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2023-01-25 08:46

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2023-01-25 08:46

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Alexander Barkov made changes - 2023-01-25 08:46

Status

In Testing [ 10301 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2023-01-25 08:46

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2023-01-25 08:47

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-01-25 14:29

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2023-01-26 07:29

issue.field.resolutiondate

2023-01-26 07:29:56.0

2023-01-26 07:29:56.802

Alexander Barkov made changes - 2023-01-26 07:29

Fix Version/s		10.3.38 [ 28507 ]
Fix Version/s		10.4.28 [ 28509 ]
Fix Version/s		10.5.19 [ 28511 ]
Fix Version/s		10.6.12 [ 28513 ]
Fix Version/s		10.7.8 [ 28515 ]
Fix Version/s		10.8.7 [ 28517 ]
Fix Version/s		10.9.5 [ 28519 ]
Fix Version/s		10.10.3 [ 28521 ]
Fix Version/s		10.11.2 [ 28523 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Fix Version/s	10.8 [ 26121 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Daniel Bartholomew made changes - 2023-02-06 17:12

Fix Version/s		10.3.39 [ 28508 ]
Fix Version/s		10.4.29 [ 28510 ]
Fix Version/s		10.5.20 [ 28512 ]
Fix Version/s		10.6.13 [ 28514 ]
Fix Version/s		10.8.8 [ 28518 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s		10.10.4 [ 28522 ]
Fix Version/s	10.3.38 [ 28507 ]
Fix Version/s	10.4.28 [ 28509 ]
Fix Version/s	10.5.19 [ 28511 ]
Fix Version/s	10.6.12 [ 28513 ]
Fix Version/s	10.7.8 [ 28515 ]
Fix Version/s	10.8.7 [ 28517 ]
Fix Version/s	10.9.5 [ 28519 ]
Fix Version/s	10.10.3 [ 28521 ]

People

Assignee:: Alexander Barkov

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-12-04 20:46

Updated:: 2023-02-06 17:12

Resolved:: 2023-01-26 07:29

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration