Bug #59482 | UNIQUE key on decimal (18,0) causes query to fail | ||
---|---|---|---|
Submitted: | 13 Jan 2011 19:17 | Modified: | 13 Jan 2011 19:56 |
Reporter: | Jason Yates | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.1.46, 5.1.55-bzr | OS: | MacOS (10.6.6) |
Assigned to: | CPU Architecture: | Any |
[13 Jan 2011 19:17]
Jason Yates
[13 Jan 2011 19:27]
Jason Yates
This also affects Ubuntu 10.04.1 with MySQL 5.1.41
[13 Jan 2011 19:56]
Valeriy Kravchuk
This is what we have with current mysql-5.1 on bzr: macbook-pro:5.1 openxs$ 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 3 Server version: 5.1.55-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `test` (`x` decimal(18,0) NOT NULL, UNIQUE KEY `x` (`x`)) ENGINE=InnoDB -> DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.40 sec) mysql> INSERT test VALUE ('123456789012345678'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test WHERE x = 123456789012345678; -- 1 row +--------------------+ | x | +--------------------+ | 123456789012345678 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test WHERE x = '123456789012345678'; -- 0 rows Empty set (0.00 sec) mysql> explain extended SELECT * FROM test WHERE x = '123456789012345678'; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select '123456789012345678' AS `x` from `test`.`test` where ('123456789012345678' = '123456789012345678') 1 row in set (0.00 sec) mysql> explain extended SELECT * FROM test WHERE x = 123456789012345678; +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | const | x | x | 8 | const | 1 | 100.00 | Using index | +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select '123456789012345678' AS `x` from `test`.`test` where ('123456789012345678' = 123456789012345678) 1 row in set (0.00 sec) mysql> DROP TABLE test; Query OK, 0 rows affected (0.00 sec) mysql> mysql> -- 17,0 with UNIQUE key always returns results mysql> CREATE TABLE `test` (`x` decimal(17,0) NOT NULL, UNIQUE KEY `x` (`x`)) ENGINE=InnoDB -> DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.09 sec) mysql> INSERT test VALUE ('12345678901234567'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test WHERE x = 12345678901234567; -- 1 row +-------------------+ | x | +-------------------+ | 12345678901234567 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test WHERE x = '12345678901234567'; -- 1 row +-------------------+ | x | +-------------------+ | 12345678901234567 | +-------------------+ 1 row in set (0.00 sec) mysql> mysql> explain extended SELECT * FROM test WHERE x = '123456789012345678';+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain extended SELECT * FROM test WHERE x = '12345678901234567'; +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | const | x | x | 8 | const | 1 | 100.00 | Using index | +----+-------------+-------+-------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G*************************** 1. row *************************** Level: Note Code: 1003 Message: select '12345678901234567' AS `x` from `test`.`test` where ('12345678901234567' = '12345678901234567') 1 row in set (0.00 sec) mysql> DROP TABLE test; Query OK, 0 rows affected (0.01 sec) mysql> mysql> -- 18,0 with no UNIQUE key always returns results mysql> CREATE TABLE `test` (`x` decimal(18,0) NOT NULL, KEY `x` (`x`)) ENGINE=InnoDB DEFAULT -> CHARSET=latin1; Query OK, 0 rows affected (0.42 sec) mysql> INSERT test VALUE ('123456789012345678'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test WHERE x = 123456789012345678; -- 1 row +--------------------+ | x | +--------------------+ | 123456789012345678 | +--------------------+ 1 row in set (0.04 sec) mysql> SELECT * FROM test WHERE x = '123456789012345678'; -- 1 row +--------------------+ | x | +--------------------+ | 123456789012345678 | +--------------------+ 1 row in set (0.00 sec) mysql> explain extended SELECT * FROM test WHERE x = '123456789012345678';+----+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | ref | x | x | 8 | const | 1 | 100.00 | Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G*************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`test`.`x` AS `x` from `test`.`test` where (`test`.`test`.`x` = '123456789012345678') 1 row in set (0.00 sec) Even though one should compare numbers to numbers, and not to strings, whenever possible, and that http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html explains that in problematic case data are compared as imprecise (float) numbers, I'd still call this a bug. For a simple reason that adding or removing any index should NOT change the result of the query.
[13 Jan 2011 20:36]
MySQL Verification Team
related: bug #48769