[rhn-users] Mysql query return different result after upgrade to Mysql 4.1.20

[Agustiyanto, Pujo]

I just upgrade my mysql to version 4.1.20
but I found that one of my query does not return the same result as my
older version of mysql.
I do not know if this is a known bug or not, or I just got my mysql
configuration wrong.
I tried the query against this records

CREATE TABLE `portfolio` (
  `record_id` int(11) NOT NULL auto_increment,
  `company_id` int(11) NOT NULL default '0',
  `FY` int(4) NOT NULL default '0',
  `started_mon` int(11) NOT NULL default '0',
  `field_name` enum('equity','owned_share') NOT NULL default 'equity',
  `field_value` float NOT NULL default '0',
  PRIMARY KEY  (`record_id`),
  UNIQUE KEY `field_name` (`field_name`,`company_id`,`FY`,`started_mon`)
);


INSERT INTO `portfolio` VALUES (1, 4, 2006, 1, 'equity', 558000);
INSERT INTO `portfolio` VALUES (2, 4, 2006, 1, 'owned_share', 0.67);
INSERT INTO `portfolio` VALUES (3, 12, 2005, 1, 'owned_share', 0.88);
INSERT INTO `portfolio` VALUES (4, 10, 2005, 1, 'owned_share', 0.5);
INSERT INTO `portfolio` VALUES (5, 6, 2006, 1, 'equity', 6.191e+06);
INSERT INTO `portfolio` VALUES (6, 14, 2005, 1, 'owned_share', 0.67);
INSERT INTO `portfolio` VALUES (7, 6, 2006, 1, 'owned_share', 0.53);
INSERT INTO `portfolio` VALUES (8, 5, 2005, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (9, 8, 2006, 1, 'owned_share', 0.5);
INSERT INTO `portfolio` VALUES (10, 4, 2005, 1, 'equity', 550000);
INSERT INTO `portfolio` VALUES (11, 4, 2005, 1, 'owned_share', 0.45);
INSERT INTO `portfolio` VALUES (12, 12, 2006, 1, 'equity', 2.067e+06);
INSERT INTO `portfolio` VALUES (13, 12, 2005, 1, 'equity', 2e+06);
INSERT INTO `portfolio` VALUES (14, 7, 2005, 1, 'equity', 192000);
INSERT INTO `portfolio` VALUES (15, 7, 2006, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (16, 7, 2005, 1, 'owned_share', 0.55);
INSERT INTO `portfolio` VALUES (17, 6, 2005, 1, 'equity', 6e+06);
INSERT INTO `portfolio` VALUES (18, 6, 2005, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (19, 1, 2000, 1, 'equity', 0);
INSERT INTO `portfolio` VALUES (20, 1, 2000, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (21, 9, 2006, 1, 'equity', 1.887e+06);
INSERT INTO `portfolio` VALUES (22, 9, 2005, 1, 'equity', 1.8e+06);
INSERT INTO `portfolio` VALUES (23, 9, 2006, 1, 'owned_share', 0.5);
INSERT INTO `portfolio` VALUES (24, 9, 2005, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (25, 11, 2006, 1, 'equity', 1.3161e+07);
INSERT INTO `portfolio` VALUES (26, 11, 2005, 1, 'equity', 1.3e+07);
INSERT INTO `portfolio` VALUES (27, 11, 2006, 1, 'owned_share', 0.495);
INSERT INTO `portfolio` VALUES (28, 11, 2005, 1, 'owned_share', 0.49);
INSERT INTO `portfolio` VALUES (29, 14, 2004, 1, 'equity', 125000);
INSERT INTO `portfolio` VALUES (30, 8, 2005, 1, 'equity', 3.283e+06);
INSERT INTO `portfolio` VALUES (31, 8, 2005, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (32, 13, 2006, 1, 'equity', 6.41806e+07);
INSERT INTO `portfolio` VALUES (33, 13, 2005, 1, 'equity', 5.34342e+07);
INSERT INTO `portfolio` VALUES (34, 13, 2000, 1, 'owned_share', 1);
INSERT INTO `portfolio` VALUES (35, 10, 2006, 1, 'equity', 6.746e+06);
INSERT INTO `portfolio` VALUES (36, 10, 2005, 1, 'equity', 6e+06);
INSERT INTO `portfolio` VALUES (37, 14, 2006, 1, 'owned_share', 0.8);
INSERT INTO `portfolio` VALUES (38, 7, 2007, 1, 'owned_share', 1);


When I try this query in MySQL 4.1.20-log

SELECT FY, company_id, field_value
FROM portfolio
WHERE field_name = 'owned_share'
AND (
FY, company_id
)
IN (
SELECT max( FY ) , company_id
FROM portfolio
WHERE field_name = 'owned_share'
AND FY <=2006
AND company_id
IN ( 4, 5, 6, 8, 12, 13, 14, 7, 9, 10, 11 ) 
GROUP BY company_id
)

I got this result
 	
FY    company_id   field_value
2005 	6 	    1
2006 	7 	    1
2006 	8 	    0.5
2006 	9 	    0.5
2005 	11 	    0.49
2006 	14 	    0.8

But when I try this is in MySQL 4.1.11-standard-log
 	
FY  company_id  field_value
2006 	4 	0.67
2005 	5 	1
2006 	6 	0.53
2006 	7 	1
2006 	8 	0.5
2006 	9 	0.5
2005 	10 	0.5
2006 	11 	0.495
2005 	12 	0.88
2000 	13 	1
2006 	14 	0.8


Does anyone has any idea why this is happened?

thanks