[MDEV-13158] Error in subquery not causing outer query to fail Created: 2017-06-22  Updated: 2017-06-23  Resolved: 2017-06-23

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

Type: Bug Priority: Minor
Reporter: Jason Johnson Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

MariaDB on Amazon AWS (Ubuntu 16.04.1 LTS)


 Description   

The following query doesn't return an error from the SQL engine:

SELECT *
 
FROM table1
 
WHERE col1 NOT IN (SELECT col2 FROM table2);

In this case, "table2" doesn't contain a column named 'col2'. But the query still runs and returns empty set.

 Comments   
Comment by Sergei Golubchik [ 2017-06-23 ]

Does table1 have col2 column? If yes, that's what you select. Show the table1 definition please.

Comment by Jason Johnson [ 2017-06-23 ]

Sorry, let me revise the query as follows:

SELECT * FROM table1 WHERE col1 NOT IN (SELECT col1 FROM table2);

The definitions are:

MariaDB [testdb]> explain table1;
 
+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| col1  | int(11) | NO   | PRI | NULL    |       |
 
+-------+---------+------+-----+---------+-------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [testdb]> explain table2;
 
+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| col3  | int(11) | NO   | PRI | NULL    |       |
 
+-------+---------+------+-----+---------+-------+
 
1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2017-06-23 ]

Right, so it's not a bug. If in doubt, please check the manual or any SQL book about how name resolution in subqueries work.

Generated at Thu Feb 08 08:03:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.