Bug #6051 Query fails when IN () argument is an empty set
Submitted: 12 Oct 2004 21:42 Modified: 31 Jan 2020 13:37
Reporter: [ name withheld ] Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:4.0.21 OS:Linux (RedHat Linux)
Assigned to: CPU Architecture:Any

[12 Oct 2004 21:42] [ name withheld ]
Description:
If you try to use IN or NOT IN and no values are passed in the parentesis, MySQL  generates an error.

How to repeat:
CREATE TABLE Test (Num INT NOT NULL);
SELECT * FROM Test WHERE Num IN ();

Suggested fix:
Obviously, in this example, the client should determine that the query is useless before it is run, but on occasions, such as when NOT IN is used, it would be programatically easier not to have to remove the entire argument on the client side for the query to work.

BTW, I wasn't sure if this would be considered a bug, or a feature request (since I would imagine the developers are aware of the problem, but nevertheless, it isn't documented anywhere)
[13 Oct 2004 18:36] Matthew Lord
Hi, thank you for your bug report!

I've marked this as a feature request since the manual does show the need for 1 or more
values.

Best Regards
[13 Oct 2004 19:55] Sergei Golubchik
According to the standard:

<in predicate> ::= <row value predicand> <in predicate part 2>
<in predicate part 2> ::= [ NOT ] IN <in predicate value>
<in predicate value> ::=
    <table subquery>
  | <left paren> <in value list> <right paren>
<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]

as you can see, there MUST be at least one value in the parens - at least in the standard-compliant behaviour
[31 Jan 2020 13:37] Erlend Dahl
We will not implement this proposal, as it is not compliant with the SQL standard and provides little value in terms of functionality.