MySQL Bugs: #12474: "ANY ()" or "IN ()" query should evaluate to false not throw an error

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-standard-log	OS:	Linux (FC3)
Assigned to:		CPU Architecture:	Any

Description:
"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

Ken,

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:
SELECT ... WHERE FALSE; 

In my humble opinion this is every bad practice too :)

Alexander,

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.

Thoughts,
ken

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'

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)

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>

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

In the last rule:

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