CODEWITHSUNDEEP
sqlmysql
nkspartan
nkspartan

Reputation: 485

MySQL query gone wild?

Can anyone tell me why the following is happening, and how to fix it?

I have a MySQL query with an ORDER BY clause that looks like this..

ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC

So it should order the results by their "effective" rating, as in..

1st. 10 up - 1 down = 9 effective
2nd. 10 up - 5 down = 5 effective
3rd. 10 up - 7 down = 3 effective

However, as you can see on my page here, it's ordering them wrong, and giving me this..

1st. 1 up - 3 down = -2 effective
2nd. 16 up - 6 down = 10 effective
3rd. 15 up - 5 down = 10 effective

Obviously, that 1st place row shouldn't be there.

More information..

CREATE TABLE dictionary_definitions (
  did_id int(11) unsigned NOT NULL auto_increment,
  did_wordid int(11) unsigned NOT NULL default '0',
  did_userid int(11) unsigned NOT NULL default '0',
  did_status tinyint(1) unsigned NOT NULL default '0',
  did_date int(11) NOT NULL default '0',
  did_definition text,
  did_example text,
  did_votecheck int(11) NOT NULL default '0',
  did_voteup smallint(5) unsigned NOT NULL default '0',
  did_votedown smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY (did_id),
  KEY (did_wordid),
  KEY (did_userid)
) ENGINE=MyISAM;

SELECT a.did_id, a.did_userid, a.did_definition, a.did_example, 
    a.did_votecheck, a.did_voteup, a.did_votedown, b.user_name, b.user_extra8 
FROM dictionary_definitions AS a LEFT JOIN users AS b ON a.did_userid=b.user_id 
WHERE did_wordid=4 
ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC LIMIT 0, 5

Upvotes: 3

Views: 340

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332631

It's a known issue regarding subtraction from unsigned integers.

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value. If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative.

Reference: Numeric Types

Upvotes: 9

Related Questions