[Mysql 4.1.20 give different result for select inside IN function]

Thank you. ;D

That works beautifully.
But still the problem of this version persist.
I will try to upgrade to 4.1.22 version, maybe the bug is gone.
Still this makes me afraid to upgrade.
Anybody has version 5.1 and try this?

In my case it looks like mysql does not like a function like MAX or DISTINCT or other in the subquery inside IN() if we use more than 1 column to compare inside IN() function. Just my opinion.

Thanks again

[Mysql 4.1.20 give different result for select inside IN function]

Thats what exactly the question that makes me confuse. Both server return the exact same result for the inner query.


max( FY) company_id
2006   4
2005   5
2006   6
2006   7
2006   8
2006   9
2005   10
2006   11
2005   12
2000   13
2006   14

Does anyone has version 4.1.20 that have the same problem?

[Mysql 4.1.20 give different result for select inside IN function]

I use 2 different Mysql to check my query and I found this strange result.
This happend after I upgrade mysql to version 4.1.20
I wonder if my setup are wrong? Can anyone point me direction how should I solve this.

thanks a bunch... ;D

below is the detail

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 (this is redhat machine)

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 the same query in MySQL 4.1.11-standard-log (windows machine , this is the desired
result)

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