MySQL Bugs: #55840: Precedence / Order of Operations = IN strange.

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

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);

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.

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.

If it isn't left-to-right, then we need documentation on the associativity of each operator.

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.

"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.

Davi, please find out for Paul who did the change and in scope of which task.
Thank you,
-- 
kostja

Looks like a regression introduced by the patch for Bug#6726.

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.

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 :-)

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.

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.