Bug #71031 IS operator does not accepts expression in paranthesis
Submitted: 28 Nov 2013 12:05 Modified: 6 Dec 2013 21:38
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any, 5.6.14 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any

[28 Nov 2013 12:05] Peter Laursen
Description:
This is a 'spin-off' from dicussion in 
http://bugs.mysql.com/bug.php?id=67732

SELECT NULL IS NULL; -- NULL
SELECT NULL IS NOT NULL; -- "0"
SELECT NULL IS (NULL); -- syntax error
SELECT NULL IS (NOT NULL); -- syntax error

How to repeat:
SELECT NULL IS NULL; -- NULL
SELECT NULL IS NOT NULL; -- "0"
SELECT NULL IS (NULL); -- syntax error
SELECT NULL IS (NOT NULL); -- syntax error

Suggested fix:
I do not find this limitation with "IS" documented here:
http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_is

But to me it would make perfect sense to use an expression in such statements (that may return NULL or not)
[28 Nov 2013 13:34] Umesh Shastry
Hello Peter,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[28 Nov 2013 13:45] Peter Laursen
Also this returns syntax error: "SELECT NULL IS !NULL;" (whereas "SELECT NULL IS NOT NULL;" works).

So it seems the parser expects and will only accept one of the *literal strings* "NULL" and "NOT NULL" with the IS-operator - and no other expression at all. 

I don't know what other RDBMS do here (if it is a MySQL-specific limitation or common) and alos not what SQL standards say (if they are clear - they are not always).  But at least I think this should be documented more explicitly.
[28 Nov 2013 17:30] Roy Lyseng
The MySQL documentation specifies that the operators are IS NULL and IS NOT
NULL.

In the SQL standard this is called a <null predicate>, and is documented as:

<null predicate> ::=
  <row value predicand> <null predicate part 2>

<null predicate part 2> ::=
  IS [ NOT ] NULL

Thus, the standard allows no parentheses and the MySQL documentation
specifies the exact syntax of the operators.
[28 Nov 2013 18:26] Peter Laursen
But MySQL docs page 12.3.1. Operator Precedence says that "IS" (alone) is an operator. 

Also "IS TRUE" and "IS FALSE" are valid according to http://dev.mysql.com/doc/refman/5.6/en/expressions.html and it works as expected like in "SELECT NULL IS FALSE". But "IS 1" and "IS 0" returns syntax errors. In (most) other contexts TRUE and 1 as well as FALSE and 0 is the same.

I never claimed it is a big issue. And this http://dev.mysql.com/doc/refman/5.6/en/expressions.html is too abstract for me to understand ...

Anyway I think that docs should state that "IS" followed by anything else that specific literal strings returns a syntax error. Because I can only understand that that is how the parser works internally. Anything but specific literal strings return an error (also "0", "1" and "!NULL").
[28 Nov 2013 23:06] Hartmut Holzgraefe
When it comes to syntax the syntax graphs win over more free form descriptions any time ...

And the syntax graphs, or better the EBNLish syntax specs, in 
http://dev.mysql.com/doc/refman/5.6/en/expressions.html
are pretty clear on the contexts that IS is allowed in,
there are only:

  boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}

and

  boolean_primary IS [NOT] NULL

So "not a bug", end of story ...
[1 Dec 2013 11:22] Peter Laursen
I think that docs should elaborate better when

1) "NOT NULL" is evaluated as a *single token* (as it is after IS operator)
2) .. and when it is evaluated as an expression (ie. like  "NOT (NULL)"
[2 Dec 2013 21:50] Roy Lyseng
The syntactical clauses IS NULL and IS NOT NULL are in my opinion thoroughly documented, in separate sections in ch. 12.3.2. Comparison Functions and Operators.

Use of the keyword NULL in numeric value expressions, string value expressions, datetime value expressions and boolean value expressions is a MySQL extension. E.g, specifying NULL in a numeric value expression, such as 100 + NULL, is identical to specifying a single NULL value.

Boolean value expressions are exceptions. When NULL is used with the operator OR and the second operand to OR is TRUE, then the value of the expression is TRUE. For almost any other expression containing NULL, the value of the expression is NULL.
[2 Dec 2013 21:58] Roy Lyseng
Reclassifying as a doc issue.

The use of the NULL keyword in expressions is documented in ch. 12.2. Type Conversion in Expression Evaluation as follows:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> [1149] equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

The use of NULL is also documented for AND, OR and NOT in ch. 12.3.3. Logical Operators.

The documentation is fairly extensive, but I am unsure whether it can be clarified more.
[6 Dec 2013 21:38] Paul Dubois
"The documentation is fairly extensive"

Agreed. No changes made.