Description:
A query across multiple tables does not aggregate properly. The query joins row counts as show below:
3 rows->1 row->10 rows-> Each of the previous 10 joins to 1-3 rows
If I remove the first table with 4 rows everything aggregates fine. If you leave the entire query it will aggregate properly but then multiple the result by the amount of rows in the first table.
I've also tested on the most recent version of 5.1 as well.
How to repeat:
Create these tables:
CREATE TABLE IF NOT EXISTS `yso_Games` (
`gameID` int(11) NOT NULL AUTO_INCREMENT,
`gameDate` int(11) NOT NULL DEFAULT '0',
`gameSeason` int(11) NOT NULL DEFAULT '0',
`gameOpposition` varchar(45) NOT NULL DEFAULT '',
`gameOwner` int(11) NOT NULL DEFAULT '0',
`gameTeamInd` binary(1) NOT NULL DEFAULT '\0',
`gameWL` varchar(10) NOT NULL DEFAULT '',
`gameHomeAway` tinyint(4) NOT NULL,
`gameHomeScore` decimal(9,2) NOT NULL,
`gameAwayScore` decimal(9,2) NOT NULL,
PRIMARY KEY (`gameID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;
--
-- Dumping data for table `yso_games`
--
INSERT INTO `yso_Games` (`gameID`, `gameDate`, `gameSeason`, `gameOpposition`, `gameOwner`, `gameTeamInd`, `gameWL`, `gameHomeAway`, `gameHomeScore`, `gameAwayScore`) VALUES
(164, 1225782000, 70, 'Tigers', 0, '\0', '', 0, 0.00, 0.00),
(165, 1225868400, 70, 'Brewers', 0, '\0', '', 0, 0.00, 0.00),
(166, 1226041200, 70, 'Seattle', 0, '\0', '', 0, 0.00, 0.00),
(167, 1226041200, 70, 'New York', 0, '\0', '', 0, 0.00, 0.00),
(168, 1226127600, 70, 'Conn', 0, '\0', '', 0, 0.00, 0.00),
(169, 1226214000, 70, 'Florida', 0, '\0', '', 0, 0.00, 0.00),
(170, 1226300400, 70, 'Chicago', 0, '\0', '', 0, 0.00, 0.00),
(171, 1226473200, 70, 'Buffalo', 0, '\0', '', 0, 0.00, 0.00),
(172, 1226559600, 70, 'Binghamton', 0, '\0', '', 0, 0.00, 0.00),
(173, 1226646000, 70, 'Boise', 0, '\0', '', 0, 0.00, 0.00);
-- --------------------------------------------------------
--
-- Table structure for table `yso_hitrows`
--
CREATE TABLE IF NOT EXISTS `yso_HitRows` (
`hitID` int(11) NOT NULL AUTO_INCREMENT,
`hitPlayer` int(11) NOT NULL DEFAULT '0',
`hitGame` int(11) NOT NULL DEFAULT '0',
`hitAB` smallint(6) NOT NULL DEFAULT '0',
`hitH` smallint(6) NOT NULL DEFAULT '0',
`hitR` smallint(6) NOT NULL DEFAULT '0',
`hitTwoB` smallint(6) NOT NULL DEFAULT '0',
`hitThreeB` smallint(6) NOT NULL DEFAULT '0',
`hitHR` smallint(6) NOT NULL DEFAULT '0',
`hitBB` smallint(6) NOT NULL DEFAULT '0',
`hitHBP` smallint(6) NOT NULL DEFAULT '0',
`hitSF` smallint(6) NOT NULL DEFAULT '0',
`hitRBI` smallint(6) NOT NULL DEFAULT '0',
`hitK` smallint(6) NOT NULL DEFAULT '0',
`hitSB` smallint(6) NOT NULL DEFAULT '0',
`hitCS` smallint(6) NOT NULL DEFAULT '0',
`hitVerify` tinyint(1) NOT NULL DEFAULT '0',
`hitTimestamp` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`hitID`),
UNIQUE KEY `hitPlayer` (`hitPlayer`,`hitGame`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 AUTO_INCREMENT=91 ;
--
-- Dumping data for table `yso_hitrows`
--
INSERT INTO `yso_HitRows` (`hitID`, `hitPlayer`, `hitGame`, `hitAB`, `hitH`, `hitR`, `hitTwoB`, `hitThreeB`, `hitHR`, `hitBB`, `hitHBP`, `hitSF`, `hitRBI`, `hitK`, `hitSB`, `hitCS`, `hitVerify`, `hitTimestamp`) VALUES
(70, 157823858, 164, 5, 2, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1226019573),
(71, 157823858, 165, 3, 1, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940058),
(72, 157823858, 167, 6, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940075),
(73, 157823858, 166, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940069),
(74, 157823858, 168, 4, 3, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1225940083),
(75, 157823858, 169, 3, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940094),
(76, 157823858, 170, 4, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940103),
(77, 157823858, 171, 5, 2, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940110),
(78, 157823858, 172, 4, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940119),
(79, 157823858, 173, 4, 3, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940125),
(80, 157823985, 164, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1226019573),
(81, 157823985, 165, 5, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940058),
(82, 157823985, 166, 6, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940069),
(83, 157823985, 167, 4, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940075),
(84, 157823985, 168, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940083),
(85, 157823985, 169, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940094),
(86, 157823985, 170, 4, 2, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1225940103),
(87, 157823985, 171, 4, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940110),
(88, 157823985, 172, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940119),
(89, 157823985, 173, 5, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1225940125),
(90, 157824018, 164, 3, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1226019573);
-- --------------------------------------------------------
--
-- Table structure for table `yso_seasonteam`
--
CREATE TABLE IF NOT EXISTS `yso_SeasonTeam` (
`steamSeason` int(11) NOT NULL DEFAULT '0',
`steamTeam` int(11) NOT NULL DEFAULT '0',
`steamAddTimeStamp` int(11) NOT NULL,
`steamRemTimeStamp` int(11) NOT NULL,
`steamActive` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`steamSeason`,`steamTeam`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `yso_seasonteam`
--
INSERT INTO `yso_SeasonTeam` (`steamSeason`, `steamTeam`, `steamAddTimeStamp`, `steamRemTimeStamp`, `steamActive`) VALUES
(70, 23, 0, 0, 1);
-- --------------------------------------------------------
--
-- Table structure for table `yso_teamplayer`
--
CREATE TABLE IF NOT EXISTS `yso_TeamPlayer` (
`teampTeam` int(11) NOT NULL DEFAULT '0',
`teampPlayer` int(11) NOT NULL DEFAULT '0',
`teampLevel` tinyint(4) NOT NULL DEFAULT '0',
`teampAddTimeStamp` int(11) NOT NULL,
`teampRemTimeStamp` int(11) NOT NULL,
`teampActive` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`teampTeam`,`teampPlayer`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `yso_teamplayer`
--
INSERT INTO `yso_TeamPlayer` (`teampTeam`, `teampPlayer`, `teampLevel`, `teampAddTimeStamp`, `teampRemTimeStamp`, `teampActive`) VALUES
(23, 157823858, 2, 1225860910, 0, 1),
(23, 157823985, 0, 1225939337, 0, 1),
(23, 157824018, 0, 1226019558, 0, 1);
Run this query to get the incorrect answer of 264:
SELECT SUM( hitAB )
FROM yso_TeamPlayer tp2
INNER JOIN yso_SeasonTeam st2 ON tp2.teampTeam = st2.steamTeam
INNER JOIN yso_Games g2 ON st2.steamSeason = g2.gameSeason
INNER JOIN yso_HitRows gr2 ON g2.gameID = gr2.hitGame
WHERE tp2.teampLevel <3
AND tp2.teampTeam =23
AND st2.steamSeason =70
GROUP BY g2.gameSeason
ORDER BY g2.gameDate ASC
Run this query without the first table to get the correct answer of 82:
SELECT SUM( hitAB )
FROM yso_SeasonTeam st2
INNER JOIN yso_Games g2 ON st2.steamSeason = g2.gameSeason
INNER JOIN yso_HitRows gr2 ON g2.gameID = gr2.hitGame
WHERE st2.steamSeason =70
GROUP BY g2.gameSeason
ORDER BY g2.gameDate ASC
