| Bug #81116 | Evaluating between DECIMAL and string returns different result depends on index | ||
|---|---|---|---|
| Submitted: | 18 Apr 2016 2:14 | Modified: | 18 Apr 2016 7:11 |
| Reporter: | Tsubasa Tanaka (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.6.30, 5.5.49 | OS: | CentOS (6.7) |
| Assigned to: | CPU Architecture: | Any | |
[18 Apr 2016 7:11]
MySQL Verification Team
Hello Tanaka-San, Thank you for the report and test case. Observed this with 5.5.49/5.6.30 builds. Thanks, Umesh
[18 Apr 2016 7:11]
MySQL Verification Team
-- 5.5.49
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.5.49: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.49-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> CREATE TABLE xdecimal (id DECIMAL(20));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243459);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243460);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243461);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243459 |
| 18015376320243460 |
| 18015376320243461 |
+-------------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE xdecimal ADD KEY idx_id(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT id FROM xdecimal IGNORE INDEX(idx_id) WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243459 |
| 18015376320243460 |
| 18015376320243461 |
+-------------------+
3 rows in set (0.00 sec)
-- 5.6.30
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE xdecimal (id DECIMAL(20));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243459);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243460);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243461);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243459 |
| 18015376320243460 |
| 18015376320243461 |
+-------------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE xdecimal ADD KEY idx_id(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT id FROM xdecimal IGNORE INDEX(idx_id) WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243459 |
| 18015376320243460 |
| 18015376320243461 |
+-------------------+
3 rows in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.30 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.30-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
-- 5.7.12
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE `xdecimal` (
-> `id` decimal(20,0) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243459);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243460);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO xdecimal VALUES (18015376320243461);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE xdecimal ADD KEY idx_id(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT id FROM xdecimal WHERE id=18015376320243461;
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT id FROM xdecimal IGNORE INDEX(idx_id) WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
[2 May 2016 9:11]
Tor Didriksen
Posted by developer:
Duplicate of
Bug#18411494 WRONG COMPARSION ON BIG DECIMAL VALUES
Bug#72056 Wrong comparsion on big DECIMAL values
which is fixed in 5.7.8
Description: When evaluating between DECIMAL and string-literal, it returns wrong result because of 'implicit cast for double-literal'. ``` mysql56> SHOW CREATE TABLE xdecimal\G *************************** 1. row *************************** Table: xdecimal Create Table: CREATE TABLE `xdecimal` ( `id` decimal(20,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql56> SELECT id FROM xdecimal IGNORE INDEX(idx_id) WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243459 | | 18015376320243460 | | 18015376320243461 | +-------------------+ 3 rows in set (0.00 sec) ``` This behavior describes in docs I knew, https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html By the way, when the table has index, this query returns only one row. ``` mysql56> ALTER TABLE xdecimal ADD KEY idx_id(id); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql56> SELECT id FROM xdecimal WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243461 | +-------------------+ 1 row in set (0.00 sec) ``` 5.7.12 returns only one row and it doesn't depend the table has index or not. ``` mysql57> SHOW CREATE TABLE xdecimal\G *************************** 1. row *************************** Table: xdecimal Create Table: CREATE TABLE `xdecimal` ( `id` decimal(20,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec) mysql57> SELECT id FROM xdecimal WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243461 | +-------------------+ 1 row in set (0.00 sec) ``` How to repeat: mysql56> CREATE TABLE xdecimal (id DECIMAL(20)); Query OK, 0 rows affected (0.02 sec) mysql56> INSERT INTO xdecimal VALUES (18015376320243459); Query OK, 1 row affected (0.00 sec) mysql56> INSERT INTO xdecimal VALUES (18015376320243460); Query OK, 1 row affected (0.00 sec) mysql56> INSERT INTO xdecimal VALUES (18015376320243461); Query OK, 1 row affected (0.00 sec) mysql56> SELECT id FROM xdecimal WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243459 | | 18015376320243460 | | 18015376320243461 | +-------------------+ 3 rows in set (0.01 sec) mysql56> ALTER TABLE xdecimal ADD KEY idx_id(id); Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql56> SELECT id FROM xdecimal WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243461 | +-------------------+ 1 row in set (0.00 sec) mysql56> SELECT id FROM xdecimal IGNORE INDEX(idx_id) WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243459 | | 18015376320243460 | | 18015376320243461 | +-------------------+ 3 rows in set (0.00 sec) Suggested fix: Backport 5.7's implementation into 5.6 series too.