MySQL Bugs: #21159: Optimizer: wrong result after AND with different data types

Bug #21159	Optimizer: wrong result after AND with different data types
Submitted:	19 Jul 2006 20:25	Modified:	13 Sep 2006 2:22
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug-log/4.1BK/5.0BK	OS:	Linux (SUSE 10.0)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
If "(a=x and b=x)" is true, MySQL assumes
"(a=x and b=x) and a=b" is true. It isn't
always so, if a and b have different data
types.

This is similar to bug#9509 and bug#13311.
The difference is that "collation" is not involved.

How to repeat:
mysql> create table t316 (s1 tinyint(1),s2 binary(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t316 values (0x01,0x01);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t316 where s1=s2;
Empty set (0.00 sec)

mysql> select * from t316 where s1=s2 and s2=0x01;
+------+------+
| s1   | s2   |
+------+------+
|    1 |     |
+------+------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified as described on Suse 10 32-bit.

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/10052

ChangeSet@1.2235, 2006-08-04 16:23:41+03:00, gkodinov@macbook.gmz +5 -0
  Bug #21159: Optimizer: wrong result after AND with different data types
  Disable const propagation for conditions that compare 
  strings with numbers because they may cause wrong 
  comparison. example : f1_num=const_str AND f2_str=f1_num
  where e.g. f1_num=5, const_str="5a", f2_str="5b".

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/10271

ChangeSet@1.2235, 2006-08-10 17:13:26+03:00, gkodinov@macbook.gmz +5 -0
  Bug #21159: Optimizer: wrong result after AND with different data types
    Must not propagate hex string constants through result types:
    e.g. <int_col> = <bin_col> AND <bin_col> = <bin_lit> must not be
    transformed into <int_col> = <bin_lit> AND <bin_col> = <bin_lit>.
    This is because of asimetry of the hex string literal implemnetation:
    <hex_string>->val_int() != CAST (<hex_string>->val_str() as VARBINARY)->val_str()

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/10374

ChangeSet@1.2235, 2006-08-14 18:35:09+03:00, gkodinov@macbook.gmz +3 -0
  Bug #21159: Optimizer: wrong result after AND with different data types
  Disable const propagation for Item_hex_string.
  This must be done because Item_hex_string->val_int() is not
  the same as (Item_hex_string->val_str() in BINARY column)->val_int().
  We cannot simply disable the replacement in a particular context (
  e.g. <bin_col> = <int_col> AND <bin_col> = <hex_string>) since
  Items don't know the context they are in and there are functions like 
  IF (<hex_string>, 'yes', 'no').
  Note that this will disable some valid cases as well 
  (e.g. : <bin_col> = <hex_string> AND <bin_col2> = <bin_col>) but 
  there's no way to distinguish the valid cases without having the
  Item's parent say something like : Item->set_context(Item::STRING_RESULT)
  and have all the Items that contain other Items do that consistently.

See also bug#21475

Fixed in 5.0.25

Available in 5.0.25.

Available in 5.0.25.

Fixed in 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

The optimizer assumed that if (a=x AND b=x) is true, (a=x AND b=x)
AND a=b is also true. But that is not always so if a and b have
different data types.