Jump to content

Archived

This topic is now archived and is closed to further replies.

ojup

Mysql 4.1.20 give different result for select inside IN function

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

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


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

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
It really sholdn't even "know" about the names of the column inside... but you can never go wrong with a JOIN.

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
I know you can't use LIMIT clauses, but no other function should be an issue.

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
No kidding... so it was fine in 4.1.13, went bad at some point, and then ok again in 4.1.22?  Weird.

Share this post


Link to post
Share on other sites

×

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.