MySQL Forums
Forum List  »  Quality Assurance

Re: is this a bug?
Posted by: Felix Geerinckx
Date: April 10, 2007 04:08AM

Yuri Niyazov Wrote:

> The first time I execute that query, some of the values on the
> resultSet come out as NULL. Without modifying anything in the table, I re-execute the query, and
> now all the values in the resultSet come out as I
> expect them to.

Your use of user defined variables is extremely risky.
I doubt the results are correct on the second run.
Try it e.g. with the following simple example:

USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (c CHAR(1), i INT);
INSERT INTO foo VALUES
('a', 1), ('a',2), ('b', 10), ('b', 20);

-- warning: this code produces wrong results
SELECT
    c,
    @sum := SUM(i) AS sum,
    @sum / 10      AS sum_divided_by_ten
FROM foo
GROUP BY c;

SELECT
    c,
    @sum := SUM(i) AS sum,
    @sum / 10      AS sum_divided_by_ten
FROM foo
GROUP BY c;

Also see 9.4. User-Defined Variables in the Reference Manual. Especially this:
Quote

Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list.

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Views
Written By
Posted
4020
April 10, 2007 01:18AM
Re: is this a bug?
2638
April 10, 2007 04:08AM
2752
April 10, 2007 12:05PM
2810
April 11, 2007 02:56AM
4056
April 16, 2007 09:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.