GODJonez
Not a bug: MySQL VIEW fails
2009-10-25 7:41 AM PDT
So making my bug tracker I encountered a problem with MySQL. I have a view that holds information of bugs such as "last time edited".
For Atom feeds I needed to get the name of the person reporting the bug. Well, I thought of just adding this information to this view as it would make sense there. Trying my theory with an SQL query showed promising:
It worked nicely, for each bug it listed the number of edits done to it, when the last edit was made, who was the author (first history event for the bug) and who was the person doing the latest edit.
Okay, so those subqueries there worked great to achieve what I wanted. Now to apply them to the view where I could query the data from:
When selecting * from that view, everything looks the same as in the query itself... except for the "editor" column. Both editor and author columns had the same values, the name of the author. It seemed weird why this view would give different result with identical query.
After I read a few MySQL documents and tried changing ALGORITHM and playing around different variations, I came to conclusion that it could be a bug in MySQL. So I searched a bit through the MySQL bug database. After a while I found the answer: This is not a bug, it's working as intended.
While it doesn't really make sense for the VIEW to give different result than SELECT query itself, it's just how it works.
When you do a SELECT with GROUP BY, it implies an ORDER BY with the same field name. And if there are subqueries with ORDER BY, the outer ORDER BY overrides these. So in practice, the ORDER BY time was overridden by ORDER BY bugid.
The question then is, why did it work differently inside VIEW than as a stand-alone SELECT query? The answer was that the optimizer can parse it differently when a VIEW is used and when it's not used, so the result is not really defined.
This still doesn't make sense to me, I still consider it a bug. But if they say it's not a bug, then I need to do multiple queries to the database myself, one for each bug separately, instead of having the result nicely available in the view that otherwise has all the info I need.
views: 60
For Atom feeds I needed to get the name of the person reporting the bug. Well, I thought of just adding this information to this view as it would make sense there. Trying my theory with an SQL query showed promising:
It worked nicely, for each bug it listed the number of edits done to it, when the last edit was made, who was the author (first history event for the bug) and who was the person doing the latest edit.
Okay, so those subqueries there worked great to achieve what I wanted. Now to apply them to the view where I could query the data from:
When selecting * from that view, everything looks the same as in the query itself... except for the "editor" column. Both editor and author columns had the same values, the name of the author. It seemed weird why this view would give different result with identical query.
After I read a few MySQL documents and tried changing ALGORITHM and playing around different variations, I came to conclusion that it could be a bug in MySQL. So I searched a bit through the MySQL bug database. After a while I found the answer: This is not a bug, it's working as intended.
While it doesn't really make sense for the VIEW to give different result than SELECT query itself, it's just how it works.
When you do a SELECT with GROUP BY, it implies an ORDER BY with the same field name. And if there are subqueries with ORDER BY, the outer ORDER BY overrides these. So in practice, the ORDER BY time was overridden by ORDER BY bugid.
The question then is, why did it work differently inside VIEW than as a stand-alone SELECT query? The answer was that the optimizer can parse it differently when a VIEW is used and when it's not used, so the result is not really defined.
This still doesn't make sense to me, I still consider it a bug. But if they say it's not a bug, then I need to do multiple queries to the database myself, one for each bug separately, instead of having the result nicely available in the view that otherwise has all the info I need.
Code:
DELIMITER |
CREATE FUNCTION GetAuthor (in_bugid INT) RETURNS VARCHAR(32) DETERMINISTIC
BEGIN
DECLARE author VARCHAR(32);
SELECT who INTO author
FROM history
WHERE bugid=in_bugid
ORDER BY time
LIMIT 1;
RETURN author;
END|
CREATE FUNCTION GetEditor (in_bugid INT) RETURNS VARCHAR(32) DETERMINISTIC
BEGIN
DECLARE editor VARCHAR(32);
SELECT who INTO editor
FROM history
WHERE bugid=in_bugid
ORDER BY time DESC
LIMIT 1;
RETURN editor;
END|
DELIMITER ;
CREATE VIEW bug_history (
bugid,
historycount,
author,
editor
) AS
SELECT bugid, COUNT(*), MAX(time), GetAuthor(bugid), GetEditor(bugid)
FROM history
GROUP BY bugid;
(bah isn't the purpose of the 'code' bbcode that it renders with a style='white-space:pre; font-family:monospace'?)
*edit* I just committed some code to fix the above.