Re: is this a bug?

[Felix Geerinckx]

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.