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:
None 
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 2:14] tsubasa tanaka
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.
[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