CODEWITHSUNDEEP
mysqlsqlprimary-keyuuid
Justin Bull
Justin Bull

Reputation: 8213

MySQL matches uuid against the primary key

I am having a really bizarre issue with MySQL selecting undesired records. Here is my setup that should reproduce the issue.

The Table

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The Records

INSERT INTO `users` (`id`, `uuid`)
VALUES
    (4, '23ee7c80-ce43-11e2-84b8-da3b984baf8c'),
    (12, '4bde3afe-ce30-11e2-9e98-b27639b9f5a0');

The Query

SELECT * FROM `users` WHERE `users`.`id` = '4bde3afe-ce30-11e2-9e98-b27639b9f5a' LIMIT 1;

The Result

+----+--------------------------------------+
| id | uuid                                 |
+----+--------------------------------------+
|  4 | 23ee7c80-ce43-11e2-84b8-da3b984baf8c |
+----+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

As you can see, it is selecting the wrong record. Any insight would be very appreciated.

EDIT

Thanks for your input. As it turns out it was type conversion playing tricks on me! Here is the warning MySQL was giving:

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '4bde3afe-ce30-11e2-9e98-b27639b9f5a' |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.01 sec)

Wish I thought to check that earlier, but my client program (Sequel Pro) doesn't register warnings, only errors.. Oh well

Upvotes: 1

Views: 1200

Answers (2)

Mikhail
Mikhail

Reputation: 1560

I guess, this is an issue with type converting, when you are comparing char with number, it takes only 4 from the string. This query, for example, returns correct result:

SELECT * FROM users WHERE cast(users.id as char) = '4bde3afe-ce30-11e2-9e98-b27639b9f5a0'

Upvotes: 2

ChrisCamp
ChrisCamp

Reputation: 682

looks like you should be filtering by UUID instead. try...

SELECT * 
FROM `users` 
WHERE `users`.`uuid` = '4bde3afe-ce30-11e2-9e98-b27639b9f5a0'

Upvotes: 1

Related Questions