| Bug #34080 | EXPLAIN EXTENDED converts NULL to empty string | ||
|---|---|---|---|
| Submitted: | 26 Jan 2008 17:25 | Modified: | 26 Jan 2008 19:46 |
| Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.54, 5.0.45 | OS: | Linux (Ubuntu) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | qc | ||
[26 Jan 2008 19:46]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described:
C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -P3308
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table t;
Query OK, 0 rows affected (0.20 sec)
mysql> CREATE TABLE `t` (
-> `a` char(1) default NULL,
-> `b` char(1) character set utf8 default NULL,
-> `c` char(1) character set cp1251 default NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.22 sec)
mysql>
mysql> insert into t values('a', 'a', null);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> explain extended select * from t where a=b or b=c;
+----+-------------+-------+--------+---------------+------+---------+------+---
---+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | ro
ws | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+---
---+-------+
| 1 | SIMPLE | t | system | NULL | NULL | NULL | NULL |
1 | |
+----+-------------+-------+--------+---------------+------+---------+------+---
---+-------+
1 row in set, 1 warning (0.03 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select 'a' AS `a`,'a' AS `b`,'' AS `c` from `test`.`t` where ((convert(
'a' using utf8) = 'a') or ('a' = convert('' using utf8)))
1 row in set (0.02 sec)
Description: EXPLAIN EXTENDED converts a comparison against NULL to a comparison with '', which will not preserve the semantics in all cases. It seems to happen only when there is one row in the table, which I know the optimizer treats as a special case. How to repeat: mysql> CREATE TABLE `t` ( -> `a` char(1) default NULL, -> `b` char(1) character set utf8 default NULL, -> `c` char(1) character set cp1251 default NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.03 sec) mysql> insert into t values('a', 'a', null); Query OK, 1 row affected (0.00 sec) mysql> explain extended select * from t where a=b or b=c; +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ | 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, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select 'a' AS `a`,'a' AS `b`,'' AS `c` from `test`.`t` where ((convert('a' using utf8) = 'a') or ('a' = convert('' using utf8))) 1 row in set (0.00 sec)