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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.24 OS:
Assigned to: Igor Babaev CPU Architecture:Any
Tags: ASCII, Optimizer, parser

[17 Aug 2006 15:07] Shawn Green
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.
[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.