MySQL Forums
Forum List  »  Quality Assurance

Assignment with aggregation different than assignement without aggregation
Posted by: Doug Carr
Date: January 02, 2008 07:43PM

Hi,

I have noticed an issue with MySQL 4.1.22 when doing assignments / evaluations of user variables in a select with and without aggregation columns. If I have a table named var_test:

mysql> show create table var_test;
+----------+--------------------------
| Table | Create Table |
+----------+--------------------------
| var_test | CREATE TABLE `var_test` (
`num` int(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------
1 row in set (0.00 sec)

which contains 25 rows, with 'num' set to 1 .. 25 I get different results with

set @itemCount = -1;
select floor((@itemCount := @itemCount + 1) / 5), count(*) from var_test group by 1;

and

set @itemCount = -1;
select floor((@itemCount := @itemCount + 1) / 5) from var_test group by 1;

The first one (with the 'count(*)') gives me

+-------------------------------------------+----------+
| floor((@itemCount := @itemCount + 1) / 5) | count(*) |
+-------------------------------------------+----------+
| 0 | 4 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
| 6 | 1 |
+-------------------------------------------+----------+
7 rows in set (0.00 sec)

while the second one (no 'count(*)') gives me

+-------------------------------------------+
| floor((@itemCount := @itemCount + 1) / 5) |
+-------------------------------------------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+-------------------------------------------+
5 rows in set (0.00 sec)

I had expected to get 5 groups of 5 items back in both cases, not 7 groups of 4 (or 1). I can use other function in the one that fails (sum, avg, max, min,...) and get the same (unexpected) results of 7 rows.

If I change the equation to 'floor((@itemCount := @itemCount + 1) / 5)' then the query with aggregation produces 5 sets of 5 items:

select floor((@itemCount := @itemCount + 1) / 6), count(*) from var_test group by 1;
+-------------------------------------------+----------+
| floor((@itemCount := @itemCount + 1) / 6) | count(*) |
+-------------------------------------------+----------+
| 0 | 5 |
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
+-------------------------------------------+----------+
5 rows in set (0.00 sec)

but (as expected) the one without the aggregating column then fails.

Is there an explanation for why adding the count (or other) function would change the evaluation of the equation?

Thanks,
Doug.

Options: ReplyQuote


Subject
Views
Written By
Posted
Assignment with aggregation different than assignement without aggregation
2923
January 02, 2008 07:43PM


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.