Mysql where field=0 strange behaviour

Hi,


CREATE TABLE IF NOT EXISTS media(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    contestant_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description VARCHAR(65535) DEFAULT NULL,
    uid VARCHAR(255) NOT NULL,
    published DATETIME NOT NULL,
    visible TINYINT(1) DEFAULT 0 COMMENT '1 yes 0 no',
    iswinner tinyint(1) DEFAULT 0 COMMENT '1 yes 0 no',
    PRIMARY KEY (id),
    UNIQUE KEY unique_media_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

can anyone explain me why on earth with this query


SELECT * 
FROM  `media` 
WHERE uid =0

I get data ?

in uid field I’ve got data like
kHnn67 the youtube watch id.

Thanks in advance.

could you dump a few rows of data so we can test it ourselves?

here they are:


INSERT INTO `media` (`id`, `contestant_id`, `title`, `description`, `uid`, `published`, `visible`, `iswinner`) VALUES
(10, 1, 'Video of .', NULL, 'PCHGsCvwgvg', '2013-05-16 14:41:46', 0, 0),
(11, 2, 'Video of ..', NULL, 'LdeDXCKphpE', '2013-05-16 14:44:34', 1, 0),
(12, 3, 'Video of ...', NULL, 'Mqkh6EgbBZc', '2013-05-16 14:48:59', 1, 0),
(13, 1, 'Video of ....', NULL, '8K-LgBpDSYQ', '2013-05-16 14:57:05', 1, 0),
(18, 1, 'Video of .....', NULL, 'fZLIYR_0-CQ', '2013-05-16 16:14:50', 1, 0),
(19, 1, 'Video of ......', NULL, '8CtEksBPia0', '2013-05-16 16:17:29', 1, 0);

isn’t that the weirdest? i also get 4 of the 6 rows returned

but there’s good news, everybody

change uid=0 to uid=‘0’ and it works exactly as expected

my guess is, when you say uid=0, it converts uid to an integer!

and converting a string to an integer, it proceeds left to right, and abandons the conversion at the first non-numeric character

thus …

SELECT id
     , uid
     , CAST(uid AS UNSIGNED) as n
  FROM media

[B]id  uid          n[/B]
19  8CtEksBPia0  8
13  8K-LgBpDSYQ  8
18  fZLIYR_0-CQ  0
11  LdeDXCKphpE  0
12  Mqkh6EgbBZc  0
10  PCHGsCvwgvg  0

notice the leftmost character of uid, and the integer conversion

:slight_smile: I think of a thing like that but it VERY VERY STRANGE ^^
Thanks for the explanation.