Jump to content


Photo

Mysql 4.1.20 give different result for select inside IN function


  • Please log in to reply
10 replies to this topic

Poll: Anybody know why mysql acting weird? (0 member(s) have cast votes)

Anybody know why mysql acting weird?

  1. mysql configuration (0 votes [0.00%])

    Percentage of vote: 0.00%

  2. version bug (0 votes [0.00%])

    Percentage of vote: 0.00%

Vote Guests cannot vote

#1 ojup

ojup
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 September 2006 - 02:48 PM

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



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 September 2006 - 03:15 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 ojup

ojup
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 September 2006 - 03:57 PM

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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 September 2006 - 04:27 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 08 September 2006 - 04:46 PM

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 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 (row subqueries).

I looked for an entry in the changelogs that may have addressed this but the closest thing I could find was

Some subqueries of the form SELECT ... WHERE ROW(...) IN (subquery) were being handled incorrectly. (Bug#11867)

bug#11867.

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.
IN (
    SELECT max( FY ) AS FY, company_id
If I can find an appropriate 4.1.20 download I may 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.
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';


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 September 2006 - 05:01 PM

It really sholdn't even "know" about the names of the column inside... but you can never go wrong with a JOIN.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 08 September 2006 - 05:08 PM

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

Agreed, but it may be a bug of some sort.

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


#8 ojup

ojup
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 September 2006 - 06:57 PM

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

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 September 2006 - 07:39 PM

I know you can't use LIMIT clauses, but no other function should be an issue.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 08 September 2006 - 11:29 PM

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.

Bugs fixed:

A subquery in the WHERE clause of the outer query and using IN and GROUP BY returned an incorrect result. (Bug#16255)

4.1.22 changelog
bug #16255

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 September 2006 - 03:51 AM

No kidding... so it was fine in 4.1.13, went bad at some point, and then ok again in 4.1.22?  Weird.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users