Bug #73907 REGRESSION: Indexes miss on quoted large numeric bigints
Submitted: 12 Sep 2014 18:27 Modified: 14 Sep 2014 3:50
Reporter: Mike Willbanks Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.5/5.6/5.7 OS:Windows (Server 2008)
Assigned to: CPU Architecture:Any
Tags: BIGINT, numeric strings, regression

[12 Sep 2014 18:27] Mike Willbanks
Description:
This happens on windows only as my linux slave running 5.5.39 works as expected returning all of the data on each query whereas windows will fail.  I'm not entirely sure of the threshold of the number but I am able to reproduce this particular case everytime.

Windows Version information:
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.39                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.39-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.05 sec)

Ubuntu 14.04 Version Information:
mysql> show variables like '%version%';
+-------------------------+------------------------------------+
| Variable_name           | Value                              |
+-------------------------+------------------------------------+
| innodb_version          | 5.5.39                             |
| protocol_version        | 10                                 |
| slave_type_conversions  |                                    |
| version                 | 5.5.39-1+deb.sury.org~trusty+1-log |
| version_comment         | (Ubuntu)                           |
| version_compile_machine | x86_64                             |
| version_compile_os      | debian-linux-gnu                   |
+-------------------------+------------------------------------+
7 rows in set (0.05 sec)

How to repeat:
WINDOWS SERVER 2008:
====================

CREATE TABLE `test_12345` (
    `testId` BIGINT(20) UNSIGNED NOT NULL,
    `testId2` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`testId`, `testId2`)
);

INSERT INTO `test_12345` VALUES (15780613094306253766, 3213);

mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
Empty set (0.05 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.05 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = CAST('15780613094306253766' AS UNSIGNED) AND `testId2` = '3213';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.05 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = 15780613094306253766 AND `testId2` = 3213;
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.05 sec)

Ubuntu 14.04 (replicant exact same mysql version)
======================================
mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.05 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.04 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = CAST('15780613094306253766' AS UNSIGNED) AND `testId2` = '3213';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.06 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = 15780613094306253766 AND `testId2` = 3213;
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.04 sec)
[12 Sep 2014 19:35] Peter Laursen
Bug is reproducible for me with MySQL 5.6.20 (64 bit) on Win7

Futher: 

SELECT * FROM test_12345 WHERE `testId` = 15780613094306253766 AND `testId2` = '3213'; -- works as expected
SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = 3213; -- does not work

So the (non-standard(?) and allowed in MySQL) comparison between a numerical  and a 'quoted string' seems to fail on Windows with large numbers?

BTW: the most basic test case is probably something like this:

SELECT 3213 = '3213'; -- returns 1
SELECT '15780613094306253766' = 15780613094306253766; -- returns 0 

-- Peter
-- not a MySQL/Oracle person
[12 Sep 2014 20:20] Peter Laursen
I cannot reproduce with decimal numbers - only integers. For instance: 

SELECT 11111111111111111111111111111111111111111.1 = '11111111111111111111111111111111111111111.1'; -- is 1|TRUE

My guess is that the expression "xx ='xx'" (number = string) involves a DOUBLE as intermediate data type for the comparison and is processed/calculated differently on Linux and Windows in some cases (ie. with large integers as in this case)
[14 Sep 2014 3:23] MySQL Verification Team
miguel@lara ~/dbs/5.6 $ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-debug Source distribution

Copyright (c) 2000, 2014, 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> CREATE DATABASE x1;
Query OK, 1 row affected (0,03 sec)

mysql> USE x1
Database changed
mysql> CREATE TABLE `test_12345` (
    ->     `testId` BIGINT(20) UNSIGNED NOT NULL,
    ->     `testId2` BIGINT(20) UNSIGNED NOT NULL,
    ->     PRIMARY KEY (`testId`, `testId2`)
    -> );
Query OK, 0 rows affected (0,39 sec)

mysql> 
mysql> INSERT INTO `test_12345` VALUES (15780613094306253766, 3213);
Query OK, 1 row affected (0,04 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0,00 sec)

mysql> SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = 3213; -- does not work
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0,00 sec)

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.6.22              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.22-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0,00 sec)
[14 Sep 2014 3:44] MySQL Verification Team
On Windows:

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 Source distribution

Copyright (c) 2000, 2014, 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 5.6 > CREATE DATABASE x2;
Query OK, 1 row affected (0.06 sec)

mysql 5.6 > USE x2
Database changed
mysql 5.6 > CREATE TABLE `test_12345` (
    ->     `testId` BIGINT(20) UNSIGNED NOT NULL,
    ->     `testId2` BIGINT(20) UNSIGNED NOT NULL,
    ->     PRIMARY KEY (`testId`, `testId2`)
    -> );
Query OK, 0 rows affected (0.52 sec)

mysql 5.6 > INSERT INTO `test_12345` VALUES (15780613094306253766, 3213);
Query OK, 1 row affected (0.13 sec)

mysql 5.6 > SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
Empty set (0.06 sec)
[14 Sep 2014 3:46] MySQL Verification Team
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, 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 5.1 > CREATE DATABASE x2;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > USE x2
Database changed
mysql 5.1 > CREATE TABLE `test_12345` (
    ->     `testId` BIGINT(20) UNSIGNED NOT NULL,
    ->     `testId2` BIGINT(20) UNSIGNED NOT NULL,
    ->     PRIMARY KEY (`testId`, `testId2`)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql 5.1 > INSERT INTO `test_12345` VALUES (15780613094306253766, 3213);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
+----------------------+---------+
| testId               | testId2 |
+----------------------+---------+
| 15780613094306253766 |    3213 |
+----------------------+---------+
1 row in set (0.05 sec)

mysql 5.1 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.74-Win X64      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
[14 Sep 2014 3:50] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution

Copyright (c) 2000, 2014, 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 5.7 > CREATE DATABASE x2;
Query OK, 1 row affected (0.03 sec)

mysql 5.7 > USE x2
Database changed
mysql 5.7 > CREATE TABLE `test_12345` (
    ->     `testId` BIGINT(20) UNSIGNED NOT NULL,
    ->     `testId2` BIGINT(20) UNSIGNED NOT NULL,
    ->     PRIMARY KEY (`testId`, `testId2`)
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql 5.7 > INSERT INTO `test_12345` VALUES (15780613094306253766, 3213);
Query OK, 1 row affected (0.06 sec)

mysql 5.7 > SELECT * FROM test_12345 WHERE `testId` = '15780613094306253766' AND `testId2` = '3213';
Empty set (0.03 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.7.6               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.7.6-m16           |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.01 sec)