not understanding why 1 is not found?

[Jason Drake]

So I have a query that I am firing to see if I have any objects that match three pieces of criteria (well four, but one is set in my query). This is a NodeJS app.

Here is the Query that is being sent to MySQL. I have validated this using the Workbench.

SELECT SQL_CALC_FOUND_ROWS DISTINCT(s.ID),s.DENOMINATION,s.DESCRIPTION,s.COUNTRY_ID,s.WANTLIST FROM STAMPS AS s JOIN CATALOGUENUMBERS AS c ON s.ID=c.STAMP_ID WHERE s.COUNTRY_ID=3201 AND c.CATALOGUE_REF=22 AND c.NUMBER='1' AND c.ACTIVE=1 LIMIT 0,1000

I am sending in the CATALOGUE_REF, COUNTRY_ID and NUMBER values. NUMBER is a string. What is odd, is if NUMBER is anything other than 1, it will find results. For example, if I manually change NUMBER in the DB to "1a" and run this query for "1a" it finds it. It also works for any other NUMBER that exists... I tried, "4", "23a" etc.

I don't understand why the NUMBER='1' case does not work, yet others do. Can someone explain it to me, or is this some odd bug here?

Just for reference.... I can likely write this statement more optimally, but I use the same logic for many other queries that are more complex (it is built dynamically from form posted data and OData query strings).


Here are my table CREATE statements (I removed a few foreign constraints that are not relevant for this issue/question...)

CREATE TABLE `STAMPS` (
`ID` bigint(20) NOT NULL,
`DESCRIPTION` varchar(250) DEFAULT NULL,
`COUNTRY_ID` bigint(20) NOT NULL,
`DENOMINATION` varchar(25) DEFAULT NULL,
`MODIFYSTAMP` datetime DEFAULT NULL,
`CREATESTAMP` datetime DEFAULT NULL,
`CATALOGUE_COUNT` tinyint(4) DEFAULT '0',
`WANTLIST` tinyint(4) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `FK_STAMPS_COUNTRY_ID` (`COUNTRY_ID`),
KEY `DENOMINATION` (`DENOMINATION`) USING BTREE,
KEY `DESCRIPTION` (`DESCRIPTION`) USING BTREE,
KEY `CATALOGUE_COUNT` (`CATALOGUE_COUNT`) USING BTREE,
KEY `WANTLIST` (`WANTLIST`) USING BTREE,
KEY `CREATE_STAMP` (`CREATESTAMP`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CATALOGUENUMBERS` (
`ID` bigint(20) NOT NULL,
`CATALOGUEVALUE` float DEFAULT NULL,
`NUMBER` varchar(25) CHARACTER SET latin1 NOT NULL,
`STAMP_ID` bigint(20) NOT NULL,
`CAT_CONDITION` int(11) DEFAULT NULL,
`ACTIVE` tinyint(1) DEFAULT '0',
`CATALOGUE_REF` bigint(20) DEFAULT NULL,
`CREATESTAMP` datetime DEFAULT NULL,
`MODIFYSTAMP` datetime DEFAULT NULL,
`UNKNOWN_VALUE` tinyint(1) DEFAULT '0',
`NOTAVAILABLE` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `FK_CATALOGUENUMBERS_STAMP_ID` (`STAMP_ID`),
KEY `FK_CATALOGUENUMBERS_CATALOGUE_REF` (`CATALOGUE_REF`),
KEY `CAT_NUMBER` (`NUMBER`) USING BTREE,
CONSTRAINT `FK_CATALOGUENUMBERS_STAMP_ID` FOREIGN KEY (`STAMP_ID`) REFERENCES `STAMPS` (`ID`) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks for you help. I am sure it is something silly.... DB and queries are simply not my strength..... (still I do ok)