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
