| Bug #4108 | Subqueries with per-row values from the surrounding query don't work correctly | ||
|---|---|---|---|
| Submitted: | 11 Jun 2004 16:13 | Modified: | 12 Jun 2004 1:00 |
| Reporter: | Oliver Müller | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1 alpha | OS: | Windows (WinXP SP1) |
| Assigned to: | Dean Ellis | CPU Architecture: | Any |
[12 Jun 2004 1:00]
Dean Ellis
Are you using the 4.1.2 release? If not, please try that. If so, please submit a complete test case with the SQL to create, populate and query the table, demonstrating this behavior. I am, otherwise, unable to repeat this so far.
[12 Jun 2004 3:49]
Oliver Müller
Argh, i visited the site a thousand times for updates... :-/ Has the new version been out for long? (read: am i blind or dumb or both? :) Anyways, the new version seems to fix the problem... I'll most probably be using the mentioned style of subselects a lot in my current project, so i'll be back if i find more strange behaviours... =) But for now, thanks a lot!!!
Description: I'm not 110% sure if this is a bug, but as MySQL definitely doesn't complain about the syntax and the same construction always worked fine in Oracle, so it is likely to be one... The tables (both are INNODB): sb_forum_categories id s_title t_description n_position fk_forum_id 1 Testkategorie 1 für allgemeine Tests 1 1 2 Test Kram für Feature-Tests 2 1 3 Blabla Eine leere Kategorie!!! 3 1 sb_forum_topics id s_title fk_category_id fk_misc_id 1 Thread Nr. 1 1 0 2 Was funzt denn nicht??? :) 2 0 Now this query: SELECT sfc.id, sfc.s_title, sfc.t_description, (SELECT COUNT(*) FROM sb_forum_topics WHERE fk_category_id = sfc.id ) AS num_topics FROM sb_forum_categories sfc WHERE sfc.fk_forum_id = 1 ORDER BY sfc.n_position ASC The query should list the categories of forum 1 and the number of topics related to them. Instead it gives back the following: id s_title t_description num_topics 1 Testkategorie 1 für allgemeine Tests 1 2 Test Kram für Feature-Tests 2 <--- incorrect 3 Blabla Eine leere Kategorie!!! 0 either the use of a per-row (related to the surrounding primary query) 'sfc.id' in the subquery is not supported by MySQL, or it's a bug... How to repeat: Construct a query that uses a column's value from the surrounding query in a subquery. The Result will be unpredictable. In the given example, it's also dependant on the categorie's order (resp. the value in n_position in table sb_forum_categories)!