Description:
When the two diferent selects of a SELECT UNION has as result the same value, that value doesn't duplicate and one of them is lost.
Sample1:
SELECT sum(lresult) AS golsF FROM `jornades` WHERE local = 'Xerez' AND jugado = 1 AND jornada <= 37 UNION SELECT sum(vresult) AS golsF FROM `jornades` WHERE visitante = 'Xerez' AND jugado = 1 AND jornada <= 37
I obtain two rows with values 22 and 30
Sample2, the bug:
SELECT sum(lresult) AS golsF FROM `jornades` WHERE local = 'Eibar' AND jugado = 1 AND jornada <= 37 UNION SELECT sum(vresult) AS golsF FROM `jornades` WHERE visitante = 'Eibar' AND jugado = 1 AND jornada <= 37
I obtain only one row with value 12!
SELECT sum(lresult) AS golsF FROM `jornades` WHERE local = 'Eibar' AND jugado = 1 AND jornada <= 37
Obtains one row with value 12
SELECT sum(vresult) AS golsF FROM `jornades` WHERE visitante = 'Eibar' AND jugado = 1 AND jornada <= 37
Obtains one row with value 12
I think the SELECT UNION should obtain two rows with values 12 and 12!
How to repeat:
Execute the three SQL instructions of the sample 2 with this database:
CREATE TABLE `jornades` (
`id` int(11) NOT NULL auto_increment,
`jugado` tinyint(1) default '-1',
`data` varchar(10) NOT NULL default '',
`temporada` varchar(10) NOT NULL default '',
`divisio` varchar(10) NOT NULL default '',
`jornada` tinyint(1) NOT NULL default '0',
`local` varchar(250) NOT NULL default '',
`lresult` varchar(250) NOT NULL default '',
`visitante` varchar(250) NOT NULL default '',
`vresult` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `jornadaConcreta` (`temporada`,`divisio`,`jornada`)
) TYPE=MyISAM AUTO_INCREMENT=463 ;
INSERT INTO `jornades` VALUES (1, 1, '2005-08-27', '2005-06', '2a', 1, 'Tenerife', '3', 'Gimnàstic', '1');
INSERT INTO `jornades` VALUES (2, 1, '2005-08-27', '2005-06', '2a', 1, 'Levante', '2', 'Ejido', '0');
INSERT INTO `jornades` VALUES (3, 1, '2005-08-27', '2005-06', '2a', 1, 'Xerez', '2', 'Real Madrid B', '1');
INSERT INTO `jornades` VALUES (4, 1, '2005-08-27', '2005-06', '2a', 1, 'Lorca', '1', 'R. Ferrol', '1');
INSERT INTO `jornades` VALUES (5, 1, '2005-08-27', '2005-06', '2a', 1, 'Gijón', '2', 'Albacete', '0');
INSERT INTO `jornades` VALUES (6, 1, '2005-08-27', '2005-06', '2a', 1, 'Ciudad Murcia', '3', 'Rec Huelva', '1');
INSERT INTO `jornades` VALUES (7, 1, '2005-08-28', '2005-06', '2a', 1, 'Valladolid', '1', 'Murcia', '0');
INSERT INTO `jornades` VALUES (8, 1, '2005-08-28', '2005-06', '2a', 1, 'Eibar', '1', 'Elche', '2');
INSERT INTO `jornades` VALUES (9, 1, '2005-08-28', '2005-06', '2a', 1, 'Hércules', '1', 'Málaga B', '1');
INSERT INTO `jornades` VALUES (10, 1, '2005-08-28', '2005-06', '2a', 1, 'Numancia', '3', 'Almería', '0');
INSERT INTO `jornades` VALUES (11, 1, '2005-08-28', '2005-06', '2a', 1, 'Lleida', '1', 'Castellón', '0');
...
"The instructions on how to repeat your bug are too long" :(
