Description:
(following the guide to bug reporting)
1) What I did:
I executed this query:
update cities as c,resources as m, resources as tg,users as u set
m.resourceValue = m.resourceValue + oldMine * timeInterval,
tg.resourceValue = tg.resourceValue + oldWork * timeInterval /2,
c.citizens = oldCity + newPopulation - population,
u.gold = u.gold + (4*timeInterval*citizensSatisfaction + 20*(citizensSatisfaction-oldCity)*exp(-timeInterval/5)-20*(citizensSatisfaction-oldCity)) - cityOutcomes * timeInterval,
u.researchPoints = u.researchPoints + oldScience * timeInterval
where c.id = cityId
and m.cityId = c.id and m.resourceType = 0
and tg.cityId = c.id and tg.resourceType = (select tradegood_type from squares where id = c.squareId)
and u.id = c.ownerId;
consider timeInterval, cityOutcomes, the identifiers beginning with old and the ones ending with Satisfaction as constant local variables (since this query is run from a stored routine) defined as double precision
1) What I expected to happen?
Cities.id, (resources.resourceType, resources.cityId), users.id and squares.id are all primary keys, so I expected to be only one matching row (and the corresponding fields to be updated only once)
2) What happened instead?
Running the query or the associated procedure I found 3 matching rows. Then selecting the updated field I found values higher than what I expected, although exact calculation is difficult because data depend on time elapsed since last update
How to repeat:
I have no idea about how to repeat this using different table, but I can suggest to run my query using dummy values in the local variables (old values can be 0, timeInterval 1, satisfaction can be what ever you want).
The structure of tables involved in query is this:
CREATE TABLE `cities` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cityName` varchar(255) NOT NULL,
`ownerId` int(10) unsigned NOT NULL,
`squareId` int(10) unsigned NOT NULL,
`citizens` double unsigned NOT NULL DEFAULT '40',
`workers` double unsigned NOT NULL DEFAULT '0',
`miners` double unsigned NOT NULL DEFAULT '0',
`scientists` double unsigned NOT NULL DEFAULT '0',
`builders` double unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `ownerId` (`ownerId`),
KEY `cityName` (`cityName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `resources` (
`cityId` int(10) unsigned NOT NULL,
`resourceType` tinyint(3) unsigned NOT NULL,
`resourceValue` float unsigned NOT NULL,
PRIMARY KEY (`cityId`,`resourceType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `squares` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`coordX` smallint(5) unsigned NOT NULL,
`coordY` smallint(5) unsigned NOT NULL,
`metal_mine` tinyint(3) unsigned NOT NULL DEFAULT '1',
`tradegood_mine` tinyint(3) unsigned NOT NULL DEFAULT '1',
`tradegood_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `coords` (`coordX`,`coordY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`capitalCityId` int(10) unsigned NOT NULL,
`allyId` int(10) unsigned DEFAULT NULL,
`researchMilitary` tinyint(3) unsigned NOT NULL DEFAULT '0',
`researchKnowledge` tinyint(3) unsigned NOT NULL DEFAULT '0',
`researchEconomy` tinyint(3) unsigned NOT NULL DEFAULT '0',
`researchCivilization` tinyint(3) unsigned NOT NULL DEFAULT '0',
`researchPoints` double unsigned NOT NULL DEFAULT '0',
`researchField` tinyint(4) NOT NULL DEFAULT '1',
`gold` double unsigned NOT NULL DEFAULT '1500',
`registrationTime` datetime NOT NULL,
`userLastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
