Bug #55840 Precedence / Order of Operations = IN strange.
Submitted: 9 Aug 2010 14:45 Modified: 18 Aug 2011 21:40
Reporter: Richard Lynch Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.45, 5.5-bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: precedence IN = order operations, regression
Triage: Triaged: D3 (Medium)

[9 Aug 2010 14:45] Richard Lynch
Description:
SELECT balance FROM customer WHERE rep_num = 65 IN (0);

I would have expected that rep_num = 65 be done first, since = and IN have same precedence, so "left to right" is the rule.

It does 65 IN (0) first.

Try it and see.

How to repeat:
-- typed by hand, untested
create table customer (balance decimal(5, 2), rep_num int(11));
insert into customer values (42, 65);
insert into customer values (37, 99);
select balance from customer where rep_num = 65 IN (0);
[9 Aug 2010 15:51] Valeriy Kravchuk
Verified just as described:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table customer (balance decimal(5, 2), rep_num int(11));
Query OK, 0 rows affected (0.18 sec)

mysql> insert into customer values (42, 65);
Query OK, 1 row affected (0.07 sec)

mysql> insert into customer values (37, 99);
Query OK, 1 row affected (0.00 sec)

mysql> select balance from customer where rep_num = 65 IN (0);
Empty set (0.00 sec)

mysql> select balance from customer where (rep_num = 65) IN (0);
+---------+
| balance |
+---------+
|   37.00 |
+---------+
1 row in set (0.02 sec)

mysql> select balance from customer where rep_num = 65 = 0;
+---------+
| balance |
+---------+
|   37.00 |
+---------+
1 row in set (0.00 sec)

Maybe this is intended behavior, but then manual (http://dev.mysql.com/doc/refman/5.1/en/operator-precedence.html) should explain it in details.
[13 Aug 2010 23:12] Peter Gulutzan
I don't see where the manual says "left to right" is the rule,
and "left to right" isn't a standard requirement for
operators of equal precedence. Unless I've missed something,
this is a request for a change in undocumented behaviour.
[16 Aug 2010 12:37] Scott Noyes
If it isn't left-to-right, then we need documentation on the associativity of each operator.
[19 Aug 2010 15:31] Paul Dubois
According to sql_yacc.yy, the precedence of = and IN are the same, and they associate left to right:

%left   SET_VAR
%left   OR_OR_SYM OR_SYM OR2_SYM
%left   XOR
%left   AND_SYM AND_AND_SYM
%left   BETWEEN_SYM CASE_SYM WHEN_SYM THEN_SYM ELSE
%left   EQ EQUAL_SYM GE GT_SYM LE LT NE IS LIKE REGEXP IN_SYM    <<<< EQ is '=', IN_SYM is 'IN'
%left   '|'
%left   '&'
%left   SHIFT_LEFT SHIFT_RIGHT
%left   '-' '+'
%left   '*' '/' '%' DIV_SYM MOD_SYM
%left   '^'
%left   NEG '~'
%right  NOT_SYM NOT2_SYM
%right  BINARY COLLATE_SYM
%left  INTERVAL_SYM

Furthermore, the behavior *used* to be as expected.
Consider the expression 2 = 0 IN (0). With left-to-right evaluation, this should be 0 IN (0), i.e., 1.

Here is the result in MySQL 4.0 and 4.1:

+--------------+
| 2 = 0 in (0) |
+--------------+
|            1 |
+--------------+

The behavior underwent a regression in 5.0:

5.0.0 and 5.0.1:

+--------------+
| 2 = 0 in (0) |
+--------------+
|            1 |
+--------------+

5.0.2:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in (0)'

5.0.3:

+--------------+
| 2 = 0 in (0) |
+--------------+
|            0 |
+--------------+

This looks like a bug, because the grammar still specifies that = and IN have equal precedence and associate left to right.

I would like a developer to look at this and either:

* Fix it as a server bug, or
* Tell me whether the precedence of IN has changed, and if so, why, and where it now fits in the precedence hierarchy at http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html.  The precedence in current releases does not seem to match what the grammar specifies, as far as I can tell.
[19 Aug 2010 15:49] Peter Gulutzan
"My comment on 2010-08-14 was true but i was unaware of the information that Mr DuBois has kindly provided. i withdraw my comment, with apologies.
[19 Aug 2010 15:55] Konstantin Osipov
Davi, please find out for Paul who did the change and in scope of which task.
Thank you,
-- 
kostja
[13 Oct 2010 14:38] Davi Arnaut
Looks like a regression introduced by the patch for Bug#6726.
[13 Oct 2010 14:42] Davi Arnaut
The priority and, as a consequence, the grouping were changed without any explanation. Given that this precedence is at least counter-intuitive, this is a parser bug.
[14 Oct 2010 1:35] Richard Lynch
I "assumed" the order was "left to right" as that's the "rule" in Mathematics for operators of equal precedence.

It was true in 5th grade (Please Excuse My Dear Aunt Sally) and it was true in Honors Math courses in college, and everywhere in between.

But, yes, once the bug is fixed, the Manual should be quite explicit about this rather than making one assume it "matches" Math.

Perhaps even a nifty tool to translate the YACC into English :-)
[14 Oct 2010 2:26] Davi Arnaut
Somewhat agree, the important thing is what makes sense in SQL -- there are quite a few scenarios to think about. Nonetheless, we need to sort out and document the precedence and _associativity_ of each operator. In this particular case, we should keep in mind that the four basic arithmetic operations are usually left-associative.
[18 Aug 2011 21:40] Richard Lynch
Going forward, if the associativity would be counter-intuitive to basic or even advanced Mathematics theory, it REALLY needs documenting.

As I recalll, left-associative is generally the norm.

Negation and assignment operators are right-associative.

And some operators it might be meaningless.

It's also been 25 years since I was in a Math class.

But I'd suggest a chart similar to this:
http://www.php.net/manual/en/language.operators.precedence.php
(I'm sure there are plenty similar examples. Just picked one I have handy.)

I would suggest SQL operators like IN should follow standard set theory practices in Mathematics, unless a compelling reason for not doing that exists.