Error 1: Last week I was working on a project where I had to write a complex SQL query by joining multiple tables. So I wrote the query and it was working well until yesterday when I found out the glitch in the query. My query was something like this:
SELECT a.name, b.thumbURL, b.imageTip, b.id, SUM(c.count) AS total FROM `someTable1` b LEFT JOIN `someTable2` a ON (b.userId = a.FBuserId) LEFT JOIN `someTable` c ON (b.id = c.imageId) WHERE b.weekId='$weekId' GROUP BY c.imageId ORDER BY total DESC
On primary look the query will seems okay and theoretically it should work as expected. But my table structure was kind of wrong (see below) and my data in table c was empty for that specific weekId value what resulted major glitch in the query affecting the result output. I was grouping by NULL value from table c which was giving me data but not correct. how? I had two row coming from table a and table b but I didn’t have any row from table c. I was expecting two row of result both having total=0. Instead I was getting one row of result because I was grouping it by null value. (If it seems complicated to you, I’m sorry! Not very good in words.) Thanks goes to my boss Sarah who helped me figure out the bug.
Error 2: This error was in the same SQL query above. My table structure for table c was:
CREATE TABLE IF NOT EXISTS `someTable` (
`id` int(11) NOT NULL auto_increment,
`dateJoined` datetime NOT NULL,
`imageId` bigint(20) NOT NULL,
`visitorId` bigint(20) NOT NULL,
`count` enum('1','0') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I was using mysql SUM() on enum entity which was giving me wrong result apparently. Cause for enum value the first one is equal to 1 and the second one is 2. Although I defined 0 as second enum value but when I use SUM it counted as second value==2. So if I had one entry in the above table with count value=0 and if I run this query:
INSERT INTO `someTable` ( `dateJoined` , `imageId` , `visitorId` , `count` ) VALUES ( NOW( ) , '1', '0', '0' ); SELECT SUM( `count` ) FROM someTable WHERE imageId =0;
I will get 2 as result although I was expecting 0. I was wondering why there is no error message from mysql about using SUM on enum values. Any idea?
[Note: if you don't know, count is a reserve word in mysql so if you want to use it as entity name in a table make sure you put backtick (grave accent ) around `count` else you might get error]
IE bug: I have hit this bug so many times in my life that I think its worthy to write it down if it helps other people debugging stupid comma bug in IE. Yeah, this is about `rogue commas`. If you are a perl programmer you will know what I’m talking about. Sticking a comma after the last item is to make life easier when adding new value in the array, something like this:
var hash = {
'.jpg' : 1,
'.jpeg' : 1,
'.JPG' : 1,
'.JPEG' : 1,
};
All other browsers will parse it fine but IE will say F*** off. IE doesn’t have native debugging tool so good luck figuring out what happened. So I’m not sure which one is standard , putting a trailing comma or not but I’m sure that trailing comma can ruin your productive hours. So avoid trailing commas in javascript. Read this link by Dan Lee if you want to know more.
