CODEWITHSUNDEEP
mysql
Musterknabe
Musterknabe

Reputation: 6081

MySQL returns a result for a WHERE clause that shouldn't match

I have two tables, where one has a reference to the other table. For testing purposes, I created these two tables

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test2_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test1_test2_id_fk` (`test2_id`),
  CONSTRAINT `test1_test2_id_fk` FOREIGN KEY (`test2_id`) REFERENCES `test2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,1);
UNLOCK TABLES;

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

LOCK TABLES `test2` WRITE;
INSERT INTO `test2` VALUES (1,'group','Baustahl');
UNLOCK TABLES;

So far so good, but when I now execute this query:

SELECT * FROM test1 t1 JOIN test2 t2 ON t2.id = t1.test2_id WHERE t2.name = 'group' AND t2.value = 0;

IMO it should yield an empty result, because the value in my db is Baustahl and I'm querying for 0. However, the Baustahl result is returned as you can see here

screenshot of db result

I can't seem to find the reason for this behavior as it is def. not my expectation. Any ideas why this happens?

Upvotes: 1

Views: 34

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

If your t2.value is varchar you should use '0'

SELECT * 
FROM test1 t1 
JOIN test2 t2 ON t2.id = t1.test2_id WHERE t2.name = 'group' AND t2.value = '0';

the nuemric 0 value is not converted as you think

Upvotes: 1

Related Questions