Mysql 4.1.20 give different result for select inside IN function

[ojup]

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

[fenway]

Very strange indeed... it works as desired on my box (4.1.13a).  To be honest, I can't think of any change that would have caused this.  I know that FLOATs changed at some point, but your query isn't based on this.  Maybe it has to do with the way vector subqueries are handled.  See if the inner query returns the same results on both versions.

[ojup]

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?

[fenway]

I've never seen a IN clause that returns two operands without explicitly using ROW() on the lvalue, but I guess they're both valid row constructors.

[shoz]

[quote=ojup]
[code]
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
    )
[/code]
[/quote]
I also had not seen that in a query and it may be the problem. I had to go to the Mysql docs to check if that was standard and it seems to be valid ([url=http://dev.mysql.com/doc/refman/4.1/en/row-subqueries.html]row subqueries[/url]).

I looked for an entry in the changelogs that may have addressed this but the closest thing I could find was
[quote=mysql 4.14 cchangelog]
Some subqueries of the form SELECT ... WHERE ROW(...) IN (subquery) were being handled incorrectly. (Bug#11867)
[/quote]
[url=http://bugs.mysql.com/bug.php?id=11867]bug#11867[/url].

I don't have 4.1.20 installed, but assuming that this is the problem one thing you may try is giving the column an alias. Perhaps the same name as the column it's matching.
[code]
IN (
    SELECT max( FY ) AS FY, company_id
[/code]
If I can find an appropriate 4.1.20 download I [b]may[/b] install it to see if I can find something.

For the time being the following query should produce the same result you're looking for.
[code]
SELECT
p.FY, p.company_id, p.field_value
FROM
portfolio AS p
INNER JOIN
(
    SELECT
    max( FY ) AS 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
) AS pmax
ON
p.FY=pmax.FY
AND p.company_id=pmax.company_id
WHERE p.field_name = 'owned_share';
[/code]

[fenway]

It really sholdn't even "know" about the names of the column inside... but you can never go wrong with a JOIN.

[shoz]

[quote author=fenway link=topic=107355.msg430732#msg430732 date=1157734902]
It really sholdn't even "know" about the names of the column inside... but you can never go wrong with a JOIN.
[/quote]
Agreed, but it may be a bug of some sort.

Another thing you may try is changing the column type to YEAR.
[code]
ALTER TABLE portfolio MODIFY FY YEAR NOT NULL DEFAULT 0;
[/code]

[ojup]

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

[fenway]

I know you can't use LIMIT clauses, but no other function should be an issue.

[shoz]

I went in the wrong direction when looking in the changelog for info on this. I looked at 4.1.20 and below originally.

4.1.22 has the bug fixed apparently.
[quote=mysql 4.1.22 changelog]
Bugs fixed:

A subquery in the WHERE clause of the outer query and using IN and GROUP BY returned an incorrect result. (Bug#16255)
[/quote]
[url=http://dev.mysql.com/doc/refman/4.1/en/news-4-1-22.html]4.1.22 changelog[/url]
[url=http://bugs.mysql.com/bug.php?id=16255]bug #16255[/url]

[fenway]

No kidding... so it was fine in 4.1.13, went bad at some point, and then ok again in 4.1.22?  Weird.