Bug #2063 Strange GROUP BY behavior with user variables

• Submitted: 9 Dec 2003 12:45
• Modified: 10 Dec 2003 5:58
• Reporter: Dean Ellis
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.17
• CPU Architecture: Any

[9 Dec 2003 12:45] Dean Ellis

Description:
Two queries (using user variables and a GROUP BY) which in theory should produce the same results do not.  I assume the optimizer is causing this, but it appears there may be a bug in this somewhere or other.

In 3.23, both queries produce the same results (those produced by the second query).

How to repeat:
CREATE TABLE uv ( a int, b int );
INSERT INTO uv VALUES (1,100), (2,50), (2,49);

SET @a = 0;
SELECT @a, @a:=@a+SUM(b) FROM uv GROUP BY a;

-- |    0 |           100 |
-- |    0 |            99 |

SET @a = 0;
SELECT @a+0, @a:=@a+0+SUM(b) FROM uv GROUP BY a;

-- |    0 |             100 |
-- |  100 |             199 |

DROP TABLE uv;

Suggested fix:
The second query produces results that are consistent with 3.23's behavior, so in theory they should both produce those results in 4.0, as it is obviously possible for 4.0 to do so.

Or, the documentation needs to reflect that the results of queries such as this are undefined and unpredictable (if they are) and should not be used.

[9 Dec 2003 12:48] Dean Ellis

Acknowledging the manual's statement, "The general rule is to never assign and use the same variable in the same statement", but I still see the potential for a bug here.

[10 Dec 2003 5:58] Sergei Golubchik

unfortunately, it is something that cannot be easily fixed
the rule is the manual still applies.