Bug #21857 simple JOIN delivers wrong result

• Submitted: 28 Aug 2006 5:42
• Modified: 29 Aug 2006 7:16
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.24
• OS: Windows (WinXP)
• CPU Architecture: Any

[28 Aug 2006 5:42] Peter Laursen

Description:
-- this query:

select Name as 'Where Spanish Is Spoken', Percentage 
	from country, countrylang 
	where (code = countrycode) and (language = 'Spanish');
order by Name limit 5;

/* does not respect LIMIT and 'Aruba' is alphbetized wrong

Where Spanish Is Spoken  Percentage
-----------------------  ----------
Aruba                           7.4
Andorra                        44.6
Argentina                      96.8
Belize                         31.6
Bolivia                        87.7
Canada                          0.7
Chile                          89.7
Colombia                       99.0
Costa Rica                     97.5
Cuba                          100.0
Dominican Republic             98.0
...
...
United States                   7.5
Venezuela                      96.9
Virgin Islands, U.S.           13.3
*/

-- however this (more complicated) query

select Name as 'Where Spanish Is Spoken', Percentage 
	from Country 
	left join CountryLang 
	on Country.Code = CountryLang.CountryCode 
where code 
	in (select CountryCode from CountryLang where Language = 'Spanish')
	and language = 'Spanish'
order by Name Limit 5;

/* returns as expected 5 rows and sorting is correct

Where Spanish Is Spoken  Percentage
-----------------------  ----------
Andorra                        44.6
Argentina                      96.8
Aruba                           7.4
Belize                         31.6
Bolivia                        87.7
*/

-- did I miss something?

How to repeat:
See above.

Suggested fix:
No idea!

[28 Aug 2006 5:43] Peter Laursen

test case

Attachment: country.sql (application/octet-stream, text), 63.38 KiB.

[28 Aug 2006 6:00] Peter Laursen

I forgot to mention that this is the same without the FK and with MyISAM tables as well.

[28 Aug 2006 7:27] Sergei Golubchik

There's a typo in the first query - you have a semicolon before ORDER BY clause,
so neither ORDER BY nor LIMIT are part of the query.

The question is where did you make the typo - in a bugreport, or in the original query that returned wrong results ?

[28 Aug 2006 23:13] Peter Laursen

THANKS ...

yes .. my fault!

[29 Aug 2006 7:16] Sveta Smirnova

> yes .. my fault!

So I change status to "Not a Bug".

Thank you for your interest in MySQL.