Bug #4509 Inert Statement insert rows when it shouldn't be
Submitted: 12 Jul 2004 0:06 Modified: 15 Aug 2004 20:31
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0 alpha OS:MacOS (Mac OS X 10.3.4)
Assigned to: Assigned Account CPU Architecture:Any

[12 Jul 2004 0:06] [ name withheld ]
Description:
In the 2 queries below, the first query should insert 1 row and the second should insert no rows, 
but the second query is inserting a row(excpet that the IFNULL(r.SignedThru, YEAR(CURDATE()) 
+ 2) is being inserted as zero). If you were to comment out the insert of the second query, the 
first query inserts 1 row, and the select query return no rows, which is correct.

How to repeat:
INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate)
SELECT 1, 5, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, 
IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN 
tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 5 LEFT JOIN tblRoster x ON 
p.pkey = x.player AND x.League = 1 AND x.Team <> 5 WHERE p.pkey = 3905 AND x.Pkey IS 
NULL GROUP BY p.pKey;

INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate)
SELECT 1, 2, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, 
IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN 
tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 2 LEFT JOIN tblRoster x ON 
p.pkey = x.player AND x.League = 1 AND x.Team <> 2 WHERE p.pkey = 3905 AND x.Pkey IS 
NULL GROUP BY p.pKey;
[12 Jul 2004 0:12] [ name withheld ]
Can re-write (and it works) as:

INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate)
SELECT 1, 5, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, 
IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN 
tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 5 WHERE p.pkey = 3905 AND 
NOT EXISTS (SELECT * FROM tblRoster WHERE League = 1 AND Team <> 5 AND Player = 3905) 
GROUP BY p.pKey;

INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate)
SELECT 1, 2, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, 
IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN 
tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 2 WHERE p.pkey = 3905 AND 
NOT EXISTS (SELECT * FROM tblRoster WHERE League = 1 AND Team <> 2 AND Player = 3905) 
GROUP BY p.pKey;
[13 Jul 2004 3:07] Matthew Lord
Hi,

Could you provide the create table statement for this table so that I can easily attempt to repeat 
the problem?

Best Regards
[13 Jul 2004 5:09] [ name withheld ]
CREATE TABLE `tblroster` (
  `pKey` int(11) NOT NULL auto_increment,
  `League` int(11) NOT NULL default '0',
  `Team` int(11) NOT NULL default '0',
  `Player` int(11) NOT NULL default '0',
  `Salary` int(11) NOT NULL default '0',
  `Position` char(3) NOT NULL default '',
  `Stint` int(11) NOT NULL default '0',
  `SignedThru` int(11) NOT NULL default '0',
  `SignDate` date NOT NULL default '0000-00-00',
  `ReleaseDate` date default NULL,
  `Reserved` tinyint(1) NOT NULL default '0',
  `ReservedDate` date default NULL,
  PRIMARY KEY  (`pKey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `tblplayers` (
  `pKey` int(11) NOT NULL auto_increment,
  `LastName` varchar(50) NOT NULL default '',
  `FirstName` varchar(50) NOT NULL default '',
  `Position` varchar(12) NOT NULL default '',
  `LahmanID` varchar(50) default NULL,
  `CBSID` int(11) default NULL,
  `CurrentMLBTeam` char(3) default NULL,
  `League` char(2) default NULL,
  `LastPlayedOn` date default NULL,
  PRIMARY KEY  (`pKey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
[15 Jul 2004 20:31] Matthew Lord
Hi,

Could you provide a dump of the tblroster table for me?

Best Regards
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".