when I group a query, result is unexpected

sfullman

I'm sorry but I'm really stumped by this one.

The join syntax is pretty complex on these queries but the grouping concept is something I'm probably missing - one of those "duh" moments :-).

For simplicity I provided a link:

http://www.mga-inc.com/public/query.htm

you'll notice that the first query has 12 results. I have highlighted the rows that are of main concern.

Now, the 2nd query is identical exc. that I have grouped by and added a column to count the number of rooms reserved ("Count"), and another column to count the number of instances of the "Private" flag, which as you can see from result 1, is 1 out of the three highlighted rows.

The problem? The last column - for the second grouped result, the number of private rooms reserved shows 2 in both cases.

This is baffling but I'm sure there's a reason for it which I'd like to learn. Can anyone explain why I get a 2 in both cases when I should get a 1 and a 0?

Thanks,
Samuel

Sxooter

The basic problem is that group by in MySQL is buggy. Or maybe we can call it a feature. Either way, you're being bitten by a bug in how group by works in MySQL.

Here's a simple example of the problem:

mysql> create table test (id int, info text);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values (1,'abc');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test values (1,'xyz');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test group by id;
+------+------+
| id   | info |
+------+------+
| 1    | abc  |
+------+------+
2 rows in set (0.03 sec)

Why did I get 1, abc instead of 1, xyz. Here's what PostgreSQL does with that:

select * from test group by id;
ERROR: column "test.info" must appear in the GROUP BY clause or be used in an aggregate function

And here's what Oracle says:

ERROR at line 1:
ORA-00979: not a GROUP BY expression

If you aren't grouping by a field, and you aren't running an aggregate function on it (min, max, avg, sum, dev etc...) then you have no guarantee of what you're getting back.

sfullman

sxooter, figured it'd be you replying and figured you'd give me a technical, complex answer :-) Thank you and I'll use that answer to see if I can drill down to that field in some way.

Samuel

Sxooter

Hehe. Sorry about the short, simple, difficult to use answer. 🙂

But the more I look at this, the more it looks like a real bug, not the "group how ya like, select how ya like" normal bugginess.

Got an DDL / insert statements to let me recreate this on my machine and play with it?

sfullman

it's client data so I need to sanitize it but I'll send you something by PM this eve if I can get to it .. thanks - I'll put it on the forum as well

Sxooter

OK. Yeah, I'm looking closer, and the only explanation I can come up with is that it's a bug. What version of MySQL are you running?

Sxooter

OK, if you run the first query but do it INTO another table (i.e. select .... into testtable from ...)

and then run your group by on that test table, do you still get the wrong answers?

You could use such a table to build a sample test set for me to play with that doesn't need all the joins to make it work just for testing.

Sxooter

OK, decided to make a summary table and test on that. I made a table like this (in pgsql):

create table test (
        roomblocks_id int,
        blockdate date,
        qty int,
        initialprivate int,
        nom_private int,
        price text,
        rooms_id int,
        Contractsroomblocks_ID int,
        private int);
COPY test (roomblocks_id, blockdate, qty, initialprivate, nom_private, price, rooms_id, contractsroomblocks_id, private) FROM stdin;
31      2007-09-04      5       5       0       80129139139     \N      \N      \N
31      2007-09-05      85      55      0       80129139139     174     73      0
31      2007-09-06      105     55      0       80129139139     174     74      0
31      2007-09-07      85      55      0       80129139139     173     75      1
31      2007-09-07      85      55      0       80129139139     174     75      0
31      2007-09-07      85      55      0       80129139139     175     75      0
31      2007-09-08      5       5       0       80129139139     173     76      1
32      2007-09-04      5       5       0       80129139139     \N      \N      \N
32      2007-09-05      256     15      0       80129139139     \N      \N      \N
32      2007-09-06      284     15      0       80129139139     \N      \N      \N
32      2007-09-07      256     15      0       80129139139     \N      \N      \N
32      2007-09-08      5       5       0       80129139139     \N      \N      \N
\.

When I ran a simplified version of your query:

select 
  Roomblocks_ID, 
  BlockDate, 
  Qty, 
  InitialPrivate, 
  nom_private, 
  price, 
  rooms_id, 
  Contractsroomblocks_ID, 
  private, 
  sum(private) 
from test 
group by 
  roomblocks_id, 
  blockdate;

I got the expected error:
ERROR: column "test.qty" must appear in the GROUP BY clause or be used in an aggregate function

After adding requisite aggregate functions (I guessed which ones seems most likely to work right) I had this query:

select 
  Roomblocks_ID, 
  BlockDate, 
  sum(Qty), 
  sum(InitialPrivate), 
  sum(nom_private), 
  max(price), 
  count(rooms_id), 
  count(Contractsroomblocks_ID), 
  sum(private) 
from test 
group by 
  roomblocks_id, 
  blockdate;

 roomblocks_id | blockdate  | sum | sum | sum |     max     | count | count | sum
---------------+------------+-----+-----+-----+-------------+-------+-------+-----
            32 | 2007-09-04 |   5 |   5 |   0 | 80129139139 |     0 |     0 |
            32 | 2007-09-05 | 256 |  15 |   0 | 80129139139 |     0 |     0 |
            32 | 2007-09-06 | 284 |  15 |   0 | 80129139139 |     0 |     0 |
            32 | 2007-09-07 | 256 |  15 |   0 | 80129139139 |     0 |     0 |
            32 | 2007-09-08 |   5 |   5 |   0 | 80129139139 |     0 |     0 |
            31 | 2007-09-07 | 255 | 165 |   0 | 80129139139 |     3 |     3 |   1
            31 | 2007-09-08 |   5 |   5 |   0 | 80129139139 |     1 |     1 |   1
            31 | 2007-09-05 |  85 |  55 |   0 | 80129139139 |     1 |     1 |   0
            31 | 2007-09-06 | 105 |  55 |   0 | 80129139139 |     1 |     1 |   0
            31 | 2007-09-04 |   5 |   5 |   0 | 80129139139 |     0 |     0 |
(10 rows)

huh, got the right number in the test version. Not sure if the lack of joins is making it get the right answer or what.

Roger_Ramjet

Not a bug but a feature I believe, Sxooter.

MySQL is the only db that I know of that will let you select from columns not mentioned in the group by clause - all the others give the sort of errors you demonstrated.

What mysql is doing is to ignore the grouping when it does the SUM because they think that is the way to do things with columns not grouped on 😕 It seems to be the behaviour across all versions.

If you add the column to the group by clause then the SUM should work the way we expect it to.

Roger_Ramjet

Whoops, wrong tab :o

Sxooter

Roger Ramjet wrote:
Not a bug but a feature I believe, Sxooter.

MySQL is the only db that I know of that will let you select from columns not mentioned in the group by clause - all the others give the sort of errors you demonstrated.

What mysql is doing is to ignore the grouping when it does the SUM because they think that is the way to do things with columns not grouped on 😕 It seems to be the behaviour across all versions.

If you add the column to the group by clause then the SUM should work the way we expect it to.

Are you sure? Cause that makes no sense to me. If you have this data:

id amt
1 10
1 20

then select sum(amt) from table group by id should give you:

30 in any database I've ever used. and it is what I get from mysql when I test it on my machine.

Again, I don't trust mysql for math generally, but even they don't make that big a mistake usually.

But letting you select fields that aren't grouped OR acted on by an aggregate function can only be described as a misfeature at best. Anytime you get can get a random result without using a random function, your database is misbehaving. 🙂

Roger_Ramjet

I totally agree, which is one more reason why I personally would never use mysql for anything more than user login and fast display of dynamic content. Oh and maybe a shopping cart as well.

The particular results that Samuel is getting are due to a version bug I am sure, have to check the version logs for that. But in general, this select without grouping has always produced oddball results.

My solution to most of the mysql quirks has always been to code sql without any of those 'handy' shortcuts the slack syntax allows.

Sxooter

Yeah, I know what you mean, and I am sure it's a version specific bug too.

You can guess what my solution to various mysql bugs has been for quite some time.

tomhath

There's no way to say if the answer is right or wrong. If you're going to use a GROUP BY, you must list every column you select that's not in an aggregate function (as Sxooter said in his first post). Otherwise the result is undefined.

The right way to handle this is to aggregate the data in a subselect that returns only Roomblocks_ID, BlockDate, and the aggregated columns. Then join that result back to the other tables as needed.