Bug #14886 false result with a subquery

Description:
1. create test environment

CREATE TABLE  `subq` (
  `customerno` mediumint(8) unsigned NOT NULL default '0',
  `titleno` mediumint(8) unsigned NOT NULL,
  `seriesno` smallint(5) unsigned NOT NULL,
  `seriesnorank` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`customerno`,`titleno`,`seriesno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

INSERT INTO `subq` VALUES (503,7,3001,1),(503,7,3002,2),(503,7,3003, 3),(504,7,3001,1),(504,7,3003,3)

2. do 2 slightly different queries that should deliver the same result, compare the results and see the difference that shouldn't be there

2a) Correct result:

SELECT b1.customerno, b1.titleno, b1.seriesno, b1.seriesnorank,
(
SELECT COUNT(b2.seriesno)
FROM subq b2
WHERE b2.customerno = b1.customerno AND b2.titleno = b1.titleno AND b2.seriesnorank < b1.seriesnorank AND b2.seriesnorank + 3 >= b1.seriesnorank
) 'BezAnz'
FROM subq b1
;

2b) wrong result:

SELECT b1.customerno, b1.titleno, b1.seriesno, b1.seriesnorank,
(
SELECT COUNT(b2.seriesno)
FROM subq b2
WHERE b2.customerno = b1.customerno AND b2.titleno = b1.titleno AND b2.seriesnorank < b1.seriesnorank AND b2.seriesnorank  >= b1.seriesnorank - 3
) 'BezAnz'
FROM subq b1
;

2a)
+------------+---------+----------+--------------+--------+
| customerno | titleno | seriesno | seriesnorank | BezAnz |
+------------+---------+----------+--------------+--------+
|        503 |       7 |     3001 |            1 |      0 |
|        503 |       7 |     3002 |            2 |      1 |
|        503 |       7 |     3003 |            3 |      2 |
|        504 |       7 |     3001 |            1 |      0 |
|        504 |       7 |     3003 |            3 |      1 |
+------------+---------+----------+--------------+--------+
5 rows in set (0.01 sec)

2b)
+------------+---------+----------+--------------+--------+
| customerno | titleno | seriesno | seriesnorank | BezAnz |
+------------+---------+----------+--------------+--------+
|        503 |       7 |     3001 |            1 |      0 |
|        503 |       7 |     3002 |            2 |      0 |
|        503 |       7 |     3003 |            3 |      2 |
|        504 |       7 |     3001 |            1 |      0 |
|        504 |       7 |     3003 |            3 |      1 |
+------------+---------+----------+--------------+--------+
5 rows in set (0.00 sec)

the only reason for the difference in the resulting line 2 I see is that in 2b) we get a negative number in the where part - but nevertheless the result is an error.

How to repeat:
see above

Thank you for a problem report. Everything works just as you described, even on newer 5.0.17-BK, but it is not a bug - this behaviour is documented (you found a reason for the result yourself already). Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html):

"Warning: You should be aware that when you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned. See Section 12.8, “Cast Functions and Operators”."

It one of the rare cases where data type of the result you get from the expression is clearly documented.

If you change your column types to signed, you'll get the same results from your queries.