Bug #35922 | Equity propagation does not work properly w functions | ||
---|---|---|---|
Submitted: | 9 Apr 2008 5:23 | Modified: | 11 Apr 2008 13:08 |
Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0.54, 5.0.60 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[9 Apr 2008 5:23]
Peter Zaitsev
[9 Apr 2008 6:11]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with latest 5.0.60-BK: openxs@suse:~/dbs/5.0> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.60 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `p` ( -> `id` bigint(20) unsigned NOT NULL auto_increment, -> `url` varchar(1000) NOT NULL, -> `url_crc` int(10) unsigned NOT NULL, -> PRIMARY KEY (`id`), -> KEY `url_crc` (`url_crc`) -> ) ENGINE=MyISAM AUTO_INCREMENT=67464 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> insert into p values (1, 'http://www.pollenation.net/', 0); Query OK, 1 row affected (0.00 sec) mysql> insert into p values (2, 'http://www.nnseek.com/cat/ibm/', 0); Query OK, 1 row affected (0.00 sec) mysql> insert into p values (3, 'http://www.nnseek.com/cat/thur/', 0); Query OK, 1 row affected (0.00 sec) mysql> insert into p values (4, 'http://www.utoronto.ca', 0); Query OK, 1 row affected (0.01 sec) mysql> insert into p values (5, 'http://labs.digg.com/arc', 0); Query OK, 1 row affected (0.00 sec) mysql> update p set url_crc=crc32(url); Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from p limit 5; +----+---------------------------------+------------+ | id | url | url_crc | +----+---------------------------------+------------+ | 1 | http://www.pollenation.net/ | 1776188156 | | 2 | http://www.nnseek.com/cat/ibm/ | 2644613628 | | 3 | http://www.nnseek.com/cat/thur/ | 665366524 | | 4 | http://www.utoronto.ca | 3747297788 | | 5 | http://labs.digg.com/arc | 2866438908 | +----+---------------------------------+------------+ 5 rows in set (0.00 sec) mysql> explain select * from p where url="http://www.pollenation.net/" and -> url_crc=crc32(url); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.02 sec) mysql> explain select * from p where url="http://www.pollenation.net/" and url_crc=crc32('http://www.pollenation.net'); +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | p | ref | url_crc | url_crc | 4 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain extended select * from p where url="http://www.pollenation.net/" and url_crc=crc32(url); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`p`.`id` AS `id`,`test`.`p`.`url` AS `url`,`test`.`p`.`url_crc` AS `url_crc` from `test`.`p` where ((`test`.`p`.`url` = _latin1'http://www.pollenation.net/') and (`test`.`p`.`url_crc` = crc32(`test`.`p`.`url`))) 1 row in set (0.00 sec) Adding more rows and analysing table does not help optimizer to make any other plan: mysql> insert into p(url, url_crc) select url, url_crc from p where url not like '%poll%'; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into p(url, url_crc) select url, url_crc from p where url not like '%poll%'; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 ... mysql> insert into p(url, url_crc) select url, url_crc from p where url not like '%poll%'; Query OK, 2048 rows affected (0.04 sec) Records: 2048 Duplicates: 0 Warnings: 0 mysql> analyze table p; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.p | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> explain select * from p where url="http://www.pollenation.net/" and url_crc=crc32(url)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4097 Extra: Using where 1 row in set (0.00 sec)
[11 Apr 2008 13:08]
Sergei Golubchik
It is not as easy as it looks. In general from A=B does not follow that f(A)=f(B). And in this particular case it is certainly false: Using your testcase... CREATE TABLE `p` ( `id` bigint(20) unsigned NOT NULL auto_increment, `url` varchar(1000) NOT NULL, `url_crc` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `url_crc` (`url_crc`) ) ENGINE=MyISAM AUTO_INCREMENT=67464 DEFAULT CHARSET=latin1; insert into p values (1, 'http://www.pollenation.net/', 0); insert into p values (2, 'HTTP://WWW.POLLENATION.NET/', 0); update p set url_crc=crc32(url); mysql> select * from p where url="http://www.pollenation.net/" and url_crc=crc32(url); +----+-----------------------------+------------+ | id | url | url_crc | +----+-----------------------------+------------+ | 1 | http://www.pollenation.net/ | 1776188156 | | 2 | HTTP://WWW.POLLENATION.NET/ | 4216947324 | +----+-----------------------------+------------+ 2 rows in set (0.00 sec) mysql> select * from p where url="http://www.pollenation.net/" and url_crc=crc32('http://www.pollenation.net/'); +----+-----------------------------+------------+ | id | url | url_crc | +----+-----------------------------+------------+ | 1 | http://www.pollenation.net/ | 1776188156 | +----+-----------------------------+------------+ 1 row in set (0.00 sec)