Jump to content

Mysql 4.1.20 give different result for select inside IN function


ojup

Anybody know why mysql acting weird?  

  1. 1. Anybody know why mysql acting weird?

    • mysql configuration
      0
    • version bug
      0


Recommended Posts

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.