| Bug #21698 | ASCII() function results are not respected in a WHERE clause | ||
|---|---|---|---|
| Submitted: | 17 Aug 2006 15:07 | Modified: | 25 Sep 2006 18:02 |
| Reporter: | Shawn Green | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.24 | OS: | |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | ASCII, Optimizer, parser | ||
[4 Sep 2006 12:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11352 ChangeSet@1.2254, 2006-09-04 05:42:47-07:00, igor@rurik.mysql.com +10 -0 Fixed bug #21698: erroneously a field could be replaced by an equal constant under any circumstances. In fact this substitution can be allowed if the field is not of a type string or if the field reference serves as an argument of a comparison predicate.
[7 Sep 2006 16:52]
Sergey Petrunya
http://lists.mysql.com/commits/11565
[7 Sep 2006 16:52]
Sergey Petrunya
See the patch at http://lists.mysql.com/commits/11565
[7 Sep 2006 18:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11566 ChangeSet@1.2254, 2006-09-07 11:06:37-07:00, igor@rurik.mysql.com +10 -0 Fixed bug #21698: erroneously a field could be replaced by an equal constant under any circumstances. In fact this substitution can be allowed if the field is not of a type string or if the field reference serves as an argument of a comparison predicate.
[19 Sep 2006 8:33]
Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[25 Sep 2006 18:02]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.
Description: Under some conditions, the ASCII() function is not evaluated properly in a WHERE clause. How to repeat: -----------Error Test Case-------------- DROP TABLE IF EXISTS ascii_clause_gone ; CREATE TABLE ascii_clause_gone ( code varchar(255) ) ; INSERT INTO ascii_clause_gone VALUES ('a12'), ('A12'), ('a13') ; SELECT ASCII(code),ascii_clause_gone.* FROM ascii_clause_gone WHERE code = 'A12' ; SELECT ASCII(code),ascii_clause_gone.* FROM ascii_clause_gone WHERE code = 'A12' AND ASCII(code) = 65 ; ------------ERROR OUTPUT------------------ localhost.issue11104>SELECT ASCII(code),ascii_clause_gone.* -> FROM ascii_clause_gone -> WHERE code = 'A12' -> ; +-------------+------+ | ASCII(code) | code | +-------------+------+ | 97 | a12 | | 65 | A12 | +-------------+------+ 2 rows in set (0.00 sec) localhost.issue11104>SELECT ASCII(code),ascii_clause_gone.* -> FROM ascii_clause_gone -> WHERE code = 'A12' -> AND ASCII(code) = 65 -> ; +-------------+------+ | ASCII(code) | code | +-------------+------+ | 97 | a12 | | 65 | A12 | +-------------+------+ 2 rows in set (0.00 sec) localhost.issue11104>status; -------------- mysql Ver 14.12 Distrib 5.0.21, for Win32 (ia32) Connection id: 1 Current database: issue11104 Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.0.24-community-nt Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 TCP port: 3306 Uptime: 3 min 37 sec Threads: 1 Questions: 16 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 7 Queries per second avg: 0.074 -------------- Verified on 5.0.21 and 5.0.24 Suggested fix: Repair either the parsing code or the optimizer so that the results of the ASCII() function are applied appropriately to the WHERE clause of the query. One (IMO, horrible) workaround is to check a computed ASCII() column value with a HAVING clause. I tested this workaround and it did work but it required the return value of the ASCII() function to exist as a column in the output.