[MDEV-9375] Syntax Error when using "NOT IN (SELECT" Created: 2016-01-06  Updated: 2016-01-11  Resolved: 2016-01-11

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.1.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Cimeos Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux Debian 8 64 bits
LAMP server (web development server)
2cpu/4Go ram

mariaDB packages (rom mariaDB apt repository)
ii libmariadbclient18 10.1.9+maria-1~jessie amd64 MariaDB database client library
ii mariadb-client-10.1 10.1.9+maria-1~jessie amd64 MariaDB database client binaries
ii mariadb-client-core-10.1 10.1.9+maria-1~jessie amd64 MariaDB database core client binaries
ii mariadb-common 10.1.9+maria-1~jessie all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii mariadb-server-10.1 10.1.9+maria-1~jessie amd64 MariaDB database server binaries
ii mariadb-server-core-10.1 10.1.9+maria-1~jessie amd64 MariaDB database core server files



 Description   

Using a query with "NOT IN (SELECT" triggers a syntax error despite the fact that the query is syntaxically correct. Removing the "NOT" in the query does not trigger an error anymore.

We simplified the query to show the issue (mytable can be any table)

select * from mytable where 2 not in ( select 1 from mytable )

Exact error message is :

#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 'LIMIT 0, 25' at line 1

Executing the same query under mysql 5.5.46 works as expected.

 Comments   
Comment by Elena Stepanova [ 2016-01-11 ]

The provided query cannot trigger the quoted message. The message says that LIMIT 0, 25 was a part of the statement, and it's not there. Please paste the whole query that you are trying to run, the unabridged output from your client, like below, only with the error message that you are getting. 

MariaDB [test]> create table mytable (i int);
 
Query OK, 0 rows affected (0.33 sec)
 
 
 
MariaDB [test]> select * from mytable where 2 not in ( select 1 from mytable );
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.1.10-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Comment by Cimeos [ 2016-01-11 ]

Hi Elena,
Thanks for your feedback. I did the test mysql using the CLI directly and did not have the issue. It appears the initial tests have been done using phpmyadmin (I thought it was via CLI). The "LIMIT" is indeed coming from phpmyadmin. Using the CLI (or adminer) does not create this strange parsing error.
Sorry for the trouble, you can close the ticket as there's no issue directly related to mariadb

Comment by Cimeos [ 2016-01-11 ]

Just FYI, upgrading phpMyAdmin to the latest version (4.5.3.1) also solved the issue when executing the query via the interface

Comment by Elena Stepanova [ 2016-01-11 ]

Thanks for the info.
Do you happen to know what kind of erroneous query the older version was producing?

Comment by Cimeos [ 2016-01-11 ]

sure,
original query : select * from mytable where 2 not in ( select 1 from mytable )

Here's the produced query shown in mariadb logs

90 Query select * from mytable where 2 not in ( LIMIT 0, 25

So it's completely removing the subselect part. The exact phpMyAdmin version that is causing this bug is 4.5.0.2. It doesn't seem to happen with other versions (I tested even some 4.3 & 4.4). Last 4.5.3.1 is working ok too

Generated at Thu Feb 08 07:34:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.