Bug #18539 uncompress(d) is null: impossible?

Description:
Hi,

The optimizer thinks uncompress(?) is null is never true. I think otherwise.

How to repeat:
CREATE TABLE `xcl_input` (
  `iid` int(11) NOT NULL auto_increment,
  `d` blob NOT NULL,
  `ipa` int(11) NOT NULL,
  `mtime` int(11) NOT NULL,
  `gid` int(11) NOT NULL default '0',
  `ws_gid` int(11) NOT NULL,
  PRIMARY KEY  (`iid`),
  KEY `ws_gid` (`ws_gid`),
  KEY `gid` (`gid`)
);

mysql> select iid from xcl_input where uncompress(d) is null;
Empty set (0.00 sec)

mysql> explain select iid from xcl_input where uncompress(d) is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> select iid, uncompress(d) from xcl_input where iid = 559000;
+--------+---------------+
| iid    | uncompress(d) |
+--------+---------------+
| 559000 | NULL          |
+--------+---------------+
1 row in set, 1 warning (0.00 sec)

Thank you for a problem report. 

Please, explain, how you inserted that row with iid = 559000? That is, provide a complete test case, with INSERT.

$ mysqlhotcopy -u aaa -p=aaa test ./test_copy
DBD::mysql::db do failed: Access denied for user 'aaa'@'%' to database 'test' at /usr/bin/mysqlhotcopy line 470.
$

470      $dbh->do("LOCK TABLES $hc_locks");

Oops, wrong bug.

The field contained 'random' binary data that was not compressed.
So null should be expected.

Yes, it is not a bug then. This is clearly described in the manual (http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html):

"UNCOMPRESS(string_to_uncompress)

Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL."

> If the argument is not a compressed value, the result is NULL."

So why did the explain say "Impossible WHERE" for "where uncompress(d) is null"?

Yes, it looks like a bug. But I still need INSERT statement that will give me uncompress(d) that is NULL. If I put some data into d, cast ('123' as binary) e.g., ifnull(uncompress(d)) gives me 0. Dump of 1 row from your table will be sufficient.

create table `t` 
(
  `pass` varchar(32) not null default ''
) engine=myisam default charset=latin1;
insert into `t` values ('a');
explain select * from t where uncompress(pass) is null;
select * from t where uncompress(pass) is null;
explain select *, uncompress(pass) from t;
select *, uncompress(pass) from t;

mysql> create table `t`
    -> (
    ->   `pass` varchar(32) not null default ''
    -> ) engine=myisam default charset=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into `t` values ('a');
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from t where uncompress(pass) is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> select * from t where uncompress(pass) is null;
Empty set (0.01 sec)

mysql> explain select *, uncompress(pass) from t;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | system | NULL          | NULL | NULL    | NULL | 1    |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> select *, uncompress(pass) from t;
+------+------------------+
| pass | uncompress(pass) |
+------+------------------+
| a    | NULL             |
+------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

mysql> select *, uncompress(pass), uncompress(pass) is null from t;
+------+------------------+--------------------------+
| pass | uncompress(pass) | uncompress(pass) is null |
+------+------------------+--------------------------+
| a    | NULL             |                        0 |
+------+------------------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

Also, if the field is not defined as not null, the bug doesn't seem to happen.

Verified just as described with 5.0.23-BK on Linux:

mysql> create table `t`
    -> (
    ->   `pass` varchar(32) not null default ''
    -> ) engine=myisam default charset=latin1;
iQuery OK, 0 rows affected (0.01 sec)

mysql> insert into `t` values ('a');
Query OK, 1 row affected (0.01 sec)

mysql> explain select * from t where uncompress(pass) is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

smysql> select * from t where uncompress(pass) is null;
Empty set (0.00 sec)

mysql> explain select *, uncompress(pass) from t;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | system | NULL          | NULL | NULL    | NULL | 1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

smysql> select *, uncompress(pass) from t;
+------+------------------+
| pass | uncompress(pass) |
+------+------------------+
| a    |                  |
+------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select *, uncompress(pass), uncompress(pass) is null from t;
+------+------------------+--------------------------+
| pass | uncompress(pass) | uncompress(pass) is null |
+------+------------------+--------------------------+
| a    |                  |                        0 |
+------+------------------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1256
Message: Uncompressed data size too large; the maximum size is 1048576 (probably, length of uncompressed data was corrupted)
1 row in set (0.00 sec)

Moreover:

mysql> select *, uncompress('a'), uncompress('a') is null from t;
+------+-----------------+-------------------------+
| pass | uncompress('a') | uncompress('a') is null |
+------+-----------------+-------------------------+
| a    |                 |                       0 |
+------+-----------------+-------------------------+
1 row in set, 1 warning (0.01 sec)

This is clear contradiction to what manual (http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html) says... 

So, it is a bug (or a request for proper documentation).

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

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

Pushed to 5.0.25

it was a bug in UNCOMPRESS() -- it did not indicate that it could return NULL, causing the optimizer to do the wrong thing.

Noted in 5.0.25 changelog.

Merged into 5.1.12

Noted in 5.1.12 changelog.