Bug #340 Where clause issue
Submitted: 28 Apr 2003 8:41 Modified: 28 Apr 2003 8:48
Reporter: Jeff Ortel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23 OS:Windows (Win2000)
Assigned to: CPU Architecture:Any

[28 Apr 2003 8:41] Jeff Ortel
Description:
The where clause seems to be ignoring the last clause "a.id in (13,16)"
as you can see in the result.

jortel-lt:/home/jortel >wdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38438 to server version: 3.23.54-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  select a.id,
    ->         a.firstname,
    ->         a.alias,
    ->         a.lastname,
    ->         if(a.phone is null, a.extension, a.phone),
    ->         a.email
    ->  from contact a
    ->  left join employee b on b.contactId = a.id
    ->  where
    ->        (upper(a.firstname) like upper('%jo%')  or
    ->         upper(a.alias)     like upper('%jo%')  or
    ->         upper(a.lastname)  like upper('%jo%')  or
    ->         upper(a.email)     like upper('%jo%')) or
    ->        (upper(a.firstname) like upper('%j%') and
    ->         upper(a.lastname)  like upper('%j%')) or
    ->        (upper(a.alias)     like upper('%j%') and
    ->         upper(a.lastname)  like upper('%o%')) and
    ->        a.id in (13,16)
    -> order by 4,2;
+----+-----------+-------+----------+-------------------------------------------+-----------------+
| id | firstname | alias | lastname | if(a.phone is null, a.extension, a.phone) | email           |
+----+-----------+-------+----------+-------------------------------------------+-----------------+
| 60 | Jon       | NULL  | Beck     | NULL                                      | jbeck@xxxx.com |
|  2 | John      | NULL  | Byzek    | 887                                      | jbyzek          |
| 21 | John      | NULL  | Murphy   | 119                                       | jmurphy         |
| 25 | Jeff      | NULL  | Ortel    | 116                                       | jortel          |
| 88 | Jo        | NULL  | Somers   | 122                                       | jsomers         |
+----+-----------+-------+----------+-------------------------------------------+-----------------+
5 rows in set (0.01 sec)

How to repeat:
See SQL in description.
[28 Apr 2003 8:48] Guilhem Bichot
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

where
    ->        (upper(a.firstname) like upper('%jo%')  or
    ->         upper(a.alias)     like upper('%jo%')  or
    ->         upper(a.lastname)  like upper('%jo%')  or
    ->         upper(a.email)     like upper('%jo%')) 
or
    ->        (upper(a.firstname) like upper('%j%') and
    ->         upper(a.lastname)  like upper('%j%'))
or
    ->        (upper(a.alias)     like upper('%j%') and
    ->         upper(a.lastname)  like upper('%o%'))
and
    ->        a.id in (13,16)

AND has higher priority than OR, so what you're doing is
like
WHERE (...) OR (...) OR (... AND ...)
hence the problem. Rewrite it to
WHERE (... OR ... OR ...) AND ...