Invalid SQL (IN with subquery which returns multiple columns) is accepted

[monetdb-team]

Date: 2014-03-28 17:31:18 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.9 (Jan2014)
CC: @njnes

Last updated: 2014-05-22 09:52:29 +0200

Comment 19715

Date: 2014-03-28 17:31:18 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:27.0) Gecko/20100101 Firefox/27.0
Build Identifier:

The SQL query:
SELECT * FROM SYS.ARGS
WHERE FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS)

is accepted by MonetDB and even executed!
However the where-clause is invalid as
SELECT * FROM SYS.FUNCTIONS
returns multiple columns which cannot be mapped to 1 col: FUNC_ID

This invalid SQL query should be detected by the SQL parser/executor and reported as an SQL error.

Instead MonetDB executes it returns 3401 rows, same data as when executing:
SELECT * FROM SYS.ARGS;

Reproducible: Always

Steps to Reproduce:

1. start mserver5
2. start SQuirreL connecting to demo database
3. execute SQL query:
   SELECT * FROM SYS.ARGS
   WHERE FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS)

Actual Results:

MonetDB executes it returns 3401 rows.
Same data as when executing: SELECT * FROM SYS.ARGS;

Expected Results:

An SQL syntax error (invalid subquery as it returns more than 1 col)

The correct SQL query:
SELECT * FROM SYS.ARGS
WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS)
works fine and returns 0 rows as expected.

Comment 19718

Date: 2014-03-29 14:20:50 +0100
From: @njnes

properly check correct types (ie also number of columns should match)

Comment 19721

Date: 2014-03-29 14:24:04 +0100
From: MonetDB Mercurial Repository <>

Changeset c5338ccd7803 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c5338ccd7803

Changeset description:

fixed bugs 3458, 3461 and 3462

3461 added missing select
3462 check number of columns