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:
None 
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
Description:
Creating a decimal column with 18 or greater digits before the decimal place and a UNIQUE key causes certain queries to return no results. Queries using a non-quoted value return results while queries using a quoted value return no results.

In the following two cases, querying by either non-quoted value or quoted value will return results:
    1) decimal column with 17 or fewer digits before the decimal place
    2) decimal column with 18 or greater digits before the decimal place and no UNIQUE key

How to repeat:
-- 18,0 with UNIQUE key conditionally returns results
CREATE TABLE `test` (`x` decimal(18,0) NOT NULL, UNIQUE KEY `x` (`x`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT test VALUE ('123456789012345678');
SELECT * FROM test WHERE x = 123456789012345678; -- 1 row
SELECT * FROM test WHERE x = '123456789012345678'; -- 0 rows

DROP TABLE test;

-- 17,0 with UNIQUE key always returns results
CREATE TABLE `test` (`x` decimal(17,0) NOT NULL, UNIQUE KEY `x` (`x`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT test VALUE ('12345678901234567');
SELECT * FROM test WHERE x = 12345678901234567; -- 1 row
SELECT * FROM test WHERE x = '12345678901234567'; -- 1 row

DROP TABLE test;

-- 18,0 with no UNIQUE key always returns results
CREATE TABLE `test` (`x` decimal(18,0) NOT NULL, KEY `x` (`x`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT test VALUE ('123456789012345678');
SELECT * FROM test WHERE x = 123456789012345678; -- 1 row
SELECT * FROM test WHERE x = '123456789012345678'; -- 1 row
[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