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: mohamed atef

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

[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
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...."

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.0.36-u4-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of