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: | |
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
[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)