Adding GROUP BY empties the result set?

[lagj]

Using MySQL 5.0.15

I am no expert so maybe I am deeply misundertanding how this should
work. I have a relatively complex quey with joins and a subquery (it is
probably far from optimized, but that is another issue; right now I am
more interested in correctness than speed). If I do not put a GROUP BY
clause then the query returns a number of rows, as expected. If I add a
GROUP BY clause, I get an empty set! My understanding of the semantics
is that GROUP BY should act on the same set that would have been
returned by the query without it, so it does not make sense to me that
the set becomes completely empty. Depending on the column(s) one groups
on, in the "worst" case there should be one row left. I did notice by
using EXPLAIN that MySQL does not execute the query the same way with
and without the GROUP BY, but that should only affect the speed of the
query, not the end result.

Here is my query (without the GROUP BY):

SELECT
transaction_status.transaction_id as this_id,
person_id, payment_method, status, transaction_item.sale_item_id,
transaction_timestamp, YEAR(transaction_timestamp) as year
FROM transaction_status
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transaction_status_id) =
(SELECT (status = "Completed"), transaction_status_id
FROM transaction_status
WHERE transaction_id = this_id
ORDER BY transaction_timestamp desc
LIMIT 1)

Cheers,

Jean

[lagj]

At least I solved my problem by placing the subquery in the FROM
clause, but I am still curious about my original question.

Cheers,

Jean

[Bill Karwin]

lagj wrote:

Using MySQL 5.0.15 ...
If I do not put a GROUP BY
clause then the query returns a number of rows, as expected. If I add a
GROUP BY clause, I get an empty set! ...
Here is my query (without the GROUP BY):

....

The question about the GROUP BY behavior is hard to answer without
knowing which field you were using in the GROUP BY clause!

Also, typically you'd use GROUP BY if you have an aggregate function in
your SELECT list, such as COUNT(), SUM(), MAX(), etc. Read the page
http://dev.mysql.com/doc/refman/5.0/...functions.html

There are some mentions that some aggregate functions ignore NULLs.
So if you are GROUPing BY a field that is always NULL (because of your
outer joins), it could yield no results.

Regards,
Bill K.

[lagj]

Bill Karwin wrote:

The question about the GROUP BY behavior is hard to answer without
knowing which field you were using in the GROUP BY clause!

Good point Bill. Yeah, I am at least aware of the point of using GROUP
BY... Here was my original query with the GROUP BY (the one that
returned an empty set):

SELECT
transaction_status.transaction_id as this_id,
person_id, payment_method, status, transaction_item.sale_item_id,
transaction_timestamp, YEAR(transaction_timestamp) as year,
SUM(quantity * (transaction_item.sale_item_id IN (8,9,12))) as
adultBanquet,
SUM(quantity * (transaction_item.sale_item_id = 13)) as childBanquet,
SUM(quantity * (transaction_item.sale_item_id IN (8,9,10,11))) as
convention,
SUM(quantity * (transaction_item.sale_item_id IN (14,15))) as dayPass

FROM transaction_status
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transaction_status_id) =
(SELECT (status = "Completed"), transaction_status_id
FROM transaction_status
WHERE transaction_id = this_id
ORDER BY transaction_timestamp desc
LIMIT 1)
GROUP BY year, person_id

Neither quantity nor sale_id_id can be NULL, so I don't think your
possible explanation is it. Thanks for trying. It's not that important
to me now anyway since I found another way.

The query that gave me the result I wanted is:

SELECT transaction_status.transaction_id,
person_id,
payment_method, status,
transaction_item.sale_item_id,quantity,transaction _item.amount,
transaction_timestamp, YEAR(transaction_timestamp) as year,
SUM(quantity * (transaction_item.sale_item_id IN (13,14,17))) as
num_banq,
SUM(quantity * (transaction_item.sale_item_id = 18)) as
num_banq_child,
SUM(quantity * (transaction_item.sale_item_id IN (13,14,15,16)))
as convention,
SUM(quantity * (transaction_item.sale_item_id IN (19,20))) as
dayPass

FROM transaction_status
INNER JOIN (SELECT
transaction_id,MAX(transaction_timestamp) as timetag
FROM transaction_status
GROUP BY transaction_id) as T USING (transaction_id)
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
transaction_status.transaction_timestamp = T.timetag
AND
status = "Completed"
AND
YEAR(transaction_timestamp) = YEAR(NOW())
GROUP BY person_id

Cheers,

Jean