| Bug #89055 | bigint(20) unsigned with uuid_short() value didn`t updated or deleted when quote | ||
|---|---|---|---|
| Submitted: | 26 Dec 2017 10:39 | Modified: | 26 Dec 2017 22:17 |
| Reporter: | mohamed atef | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[26 Dec 2017 17:25]
MySQL Verification Team
Thank you for the bug report. Please read: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.22 Source distribution 2017-DEC-14 Copyright (c) 2000, 2018, 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> select '12707537433722356750'; Field 1: `12707537433722356750` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 60 Max_length: 20 Decimals: 31 Flags: NOT_NULL +----------------------+ | 12707537433722356750 | +----------------------+ | 12707537433722356750 | +----------------------+ 1 row in set (0.00 sec) mysql> mysql> select 12707537433722356750; Field 1: `12707537433722356750` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 20 Max_length: 20 Decimals: 0 Flags: NOT_NULL UNSIGNED BINARY NUM +----------------------+ | 12707537433722356750 | +----------------------+ | 12707537433722356750 | +----------------------+ 1 row in set (0.00 sec)
[26 Dec 2017 17:36]
mohamed atef
thanks for your reply but when i remove digit from 12707537433722356750 to become 2707537433722356750 ---> 19 digits UPDATE `db1`.`tb1` SET `ID`='2707537433722356750' WHERE `ID`=12707537433722356750; UPDATE `db1`.`tb1` SET `ID`='2707537433722356751' WHERE `ID`=12707537433722356751; UPDATE `db1`.`tb1` SET `ID`='2707537433722356752' WHERE `ID`=12707537433722356752; then try to delete DELETE FROM `db1`.`tb1` WHERE `ID`='2707537433722356750'; 1 row(s) affected DELETE FROM `db1`.`tb1` WHERE `ID`='2707537433722356751'; 1 row(s) affected DELETE FROM `db1`.`tb1` WHERE `ID`='2707537433722356752'; 1 row(s) affected also when i check data type it will show that select '2707537433722356750',2707537433722356750 first is varchar second is bigint my question why it happen when digits of bigint unsigned is 20 but less than 20 it work great ?????????????
[26 Dec 2017 22:17]
MySQL Verification Team
The issue here is the conversion of data type '12707537433722356750' in the query which fails is converted to float, so comparing BIGINT with float returns false. https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html "In all other cases, the arguments are compared as floating-point (real) numbers. " https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html "Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values...."
Description: bigint(20) unsigned with uuid_short() value didn`t updated or deleted when quoted when table created with primary key bigint unsigned and used uuid_short() value if uuid_short() value length is 20 digits update or delete with quoted value not affect records How to repeat: CREATE DATABASE DB1; USE DB1; CREATE TABLE TB1 (ID BIGINT(20) UNSIGNED NOT NULL, DT VARCHAR(50) NOT NULL, PRIMARY KEY (ID)) ENGINE=INNODB; DROP TRIGGER IF EXISTS `db1`.`tb1_BEFORE_INSERT`; DELIMITER $$ USE `db1`$$ CREATE DEFINER = CURRENT_USER TRIGGER `db1`.`tb1_BEFORE_INSERT` BEFORE INSERT ON `tb1` FOR EACH ROW BEGIN SET NEW.ID=UUID_SHORT(); END$$ DELIMITER ; INSERT INTO `db1`.`tb1` (`DT`) VALUES ('DATA 1'); INSERT INTO `db1`.`tb1` (`DT`) VALUES ('DATA 2'); INSERT INTO `db1`.`tb1` (`DT`) VALUES ('DATA 3'); SELECT * FROM `db1`.`tb1`; RESULT ID DT 12707537433722356750 DATA 1 12707537433722356751 DATA 2 12707537433722356752 DATA 3 DELETE FROM `DB1`.`TB1` WHERE `ID`='12707537433722356750'; 0 row(s) affected DELETE FROM `DB1`.`TB1` WHERE `ID`='12707537433722356751'; 0 row(s) affected DELETE FROM `DB1`.`TB1` WHERE `ID`='12707537433722356752'; 0 row(s) affected BUT WITHOUT QUOTES DELETE FROM `DB1`.`TB1` WHERE `ID`=12707537433722356750; 1 row(s) affected DELETE FROM `DB1`.`TB1` WHERE `ID`=12707537433722356751; 1 row(s) affected DELETE FROM `DB1`.`TB1` WHERE `ID`=12707537433722356752; 1 row(s) affected Suggested fix: i don`t know