| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 3.23 | OS: | Windows (Win2000) |
| Assigned to: | CPU Architecture: | Any | |
[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 ...
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.