MySQL Bugs: #7751: Problem with where clause in 4.1.8

Bug #7751	Problem with where clause in 4.1.8
Submitted:	9 Jan 2005 11:48	Modified:	5 Feb 2005 2:02
Reporter:	Svetozar Urumov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	musql 4.1.7 , 4.1.8	OS:	Windows (Windows, FreeBSD)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
During migration from 4.0.22 we discover some strange behavior of server during some queries.  In some cases Explain returns:
 “Impossible WHERE noticed after reading const tables”

How to repeat:

 CREATE TABLE `ff` (
  `ff_id` int(11) NOT NULL auto_increment,
  `pp_m1` bigint(20) unsigned default NULL,
  `pp_m2` bigint(20) unsigned default NULL,
  `in_fi` tinyint(4) NOT NULL default '0',
  `print` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`ff_id`),
  UNIQUE KEY `pp_mm` (`pp_m1`,`pp_m2`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

insert into ff values ('0','16307858876001849059','17753655576141847281','0','1');

explain
 SELECT in_fi, print
 FROM ff
 WHERE pp_m1 = CONV('e251273eb74a8ee3', 16, 10)
  AND  pp_m2 = CONV('f661a7af00915af1', 16, 10);

---- we check with exact values 
select CONV('e251273eb74a8ee3', 16, 10), CONV('f661a7af00915af1', 16, 10);

  explain
 SELECT in_fi, print
 FROM ff
 WHERE pp_m1 = 16307858876001849059
  AND  pp_m2 = 17753655576141847281;

Suggested fix:
Fix it :)

The bug can be reproduced with a simpler test:

mysql> CREATE TABLE t1 (
    ->   id int(11) NOT NULL auto_increment,
    ->   a bigint(20) unsigned default NULL,
    ->   PRIMARY KEY  (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (4.52 sec)

mysql>
mysql> INSERT INTO t1 VALUES
    -> ('0','16307858876001849059');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT CONV('e251273eb74a8ee3', 16, 10);
+----------------------------------+
| CONV('e251273eb74a8ee3', 16, 10) |
+----------------------------------+
| 16307858876001849059             |
+----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> EXPLAIN
    -> SELECT id
    ->   FROM t1
    ->   WHERE a = 16307858876001849059;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>
mysql> EXPLAIN
    ->   SELECT id
    ->   FROM t1
    ->   WHERE a = CONV('e251273eb74a8ee3', 16, 10);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

On 5.0 the results are the same.

Hasn't this bug been pushed already? Please close it, if it's resolved. Thanks!

ChangeSet
  1.2164 05/02/01 23:34:31 igor@rurik.mysql.com +3 -0
  func_str.result, func_str.test:
    Added a test case for bug #7751.
  item_strfunc.cc:
    Fixed bug #7751.
    The function Item_func_conv::val_str did not update
    the unsigned_flag value.

The fix is applied to 5.0 as well.

Mentioned in 4.1.10 and 5.0.3 change notes.