Bug #12474 "ANY ()" or "IN ()" query should evaluate to false not throw an error
Submitted: 9 Aug 2005 22:02 Modified: 13 Aug 2005 18:28
Reporter: Ken Johanson Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7-beta-standard-log OS:Linux (FC3)
Assigned to: CPU Architecture:Any

[9 Aug 2005 22:02] Ken Johanson
"IN ()" or "ANY ()" is a valid SQL syntax to indicate an empty array (empty arrays are valid objects in any language). It implies that nothing is contained in the array, and in the case of SQL's IN/ANY this should return false.

However, in MySQL the query "SELECT 'exists' FROM tbl WHERE rowid IN ()"; throws an exception.

The problem is that some query builders will opt to build an IN value by leaving the IN syntax in, say, a prepared stmt, but prefill the array arg from a List/Array type object:

List list = new ArrayList();
//fill the array
executePrepared("SELECT true FROM tbl WHERE rowid IN (?)", list);

if the list is not empty, is interpreted as :
"SELECT true FROM tbl WHERE rowid IN ('a','b','c')"

However if no elements are in the list, no 'default value could be safely provided that would allow Mysql to execute the query, except a random string:

"SELECT true FROM tbl WHERE rowid IN ('random-fgskdhgtebshjd')"

How to repeat:
Run query:

SELECT 'exists' FROM tbl WHERE fld IN ()

Suggested fix:
Evaluate as false
[10 Aug 2005 8:18] Alexander Keremidarski

I understand your point, but can you please tell us where the ANSI SQL standard says that:

> "IN ()" or "ANY ()" is a valid SQL syntax to indicate an empty array

I doubt you can find such reference because SQL. Some authors define SQL as "non-procedural language" to distingush if from other programming languages. Lot of common definitions simply can't be applied to SQL and in my humble opinion "array" is one of them.

In your example:

SELECT 'exists' FROM tbl WHERE fld IN ()

Sending this statement to the server is waste of resources. In Client - Server world it is sin to send reques to the Server if the Client knows the result in advance.

In this case the Client *knows* that when list is empty the Server will return empty set so implementing what you request will only encourage bad practices.

You may argue that the same can be said about another valid syntax:

In my humble opinion this is every bad practice too :)
[10 Aug 2005 14:30] Ken Johanson

I would contend that the empty-array syntax is fully valid:

-by virtue of it *not* being prohibited by the spec discussion of arrays, *and* an empty array is implied to be valid just as is *any* query that sends completely redundant or implied data (the spec doesnt prohibit those either), and there are far too many exmaples of those to list... so should the server throw an exception on each of those? I would argue, No.. it should behave uniformly *anytime* implied results are contained in a query, not differently for one type or another)

-other servers treat this a valid syntax

-dynamically built queries need this for easier composition, however lazy or neglible the need.

Subselects (which in effect return a list) *can* contain zero elements... but do not throw an exception in that case.

[12 Aug 2005 17:21] Aleksey Kishkin
Ken, which other servers do you mean? when we try it in oracle 8 and firebird we got syntax error. 

Actually according to sql:2003 standard, 'in (..)' must have one or more values in the list. I agree it could be convenient to have empty list (especially if some program creates sql expression) , but it's not a bug, i'd estimate it as 'feature request' for sql extension.

If you agree, I'll change severity to 'feature request'
[12 Aug 2005 17:28] Ken Johanson
Oh, man!! I'm not one to even try to argue with the spec.. they must have had a good reason for saying *must* (though I cant imagine what it is at the moment). So I can't even reccomend it as a feature request if it goes against what the spec says *must* be the behavior.

If the spec really says that, then please close this bug as a won't-fix.

What part/sub-part in the spec that in if you dont mind? (I dont have copy of sql'03)
[9 Sep 2005 11:53] Sergei Golubchik
in SQL2003, part 2 foundations, paragraph 8.4 <in predicate>, the syntax is specified as

<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> }... ]

that means, there should be at least one <row value expression>
[9 Sep 2005 14:40] Ken Johanson
Thank you very much, Sergei.

I am not certain that the quoted section explicitly indicates that the value (the <in value list>) *must* be non-empty; it is only describing the syntax for it. In fact I would assert that use of the word 'list' implicitly *allows* for a zero length list (however redundant that is), just as the quoted section describes an alternate subquery, which also can contain zero elements in normal practice.

If someone could describe to me, where the spec syntactically mandates that the <in value list> be non-empty, I would greatly appreciate it.

Thank you, k
[22 Sep 2005 10:27] Sergei Golubchik
In the last rule:

<in value list> ::= <row value expression> [ { <comma> <row value expression> }... ]