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_sta tus.transaction _id as this_id,
person_id, payment_method, status, transaction_ite m.sale_item_id,
transaction_tim estamp, YEAR(transactio n_timestamp) as year
FROM transaction_sta tus
LEFT JOIN transaction USING (transaction_id )
LEFT JOIN transaction_ite m USING (transaction_id )
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transacti on_status_id) =
(SELECT (status = "Completed" ), transaction_sta tus_id
FROM transaction_sta tus
WHERE transaction_id = this_id
ORDER BY transaction_tim estamp 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_sta tus.transaction _id as this_id,
person_id, payment_method, status, transaction_ite m.sale_item_id,
transaction_tim estamp, YEAR(transactio n_timestamp) as year,
SUM(quantity * (transaction_it em.sale_item_id IN (8,9,12))) as
adultBanquet,
SUM(quantity * (transaction_it em.sale_item_id = 13)) as childBanquet,
SUM(quantity * (transaction_it em.sale_item_id IN (8,9,10,11))) as
convention,
SUM(quantity * (transaction_it em.sale_item_id IN (14,15))) as dayPass

FROM transaction_sta tus
LEFT JOIN transaction USING (transaction_id )
LEFT JOIN transaction_ite m USING (transaction_id )
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transacti on_status_id) =
(SELECT (status = "Completed" ), transaction_sta tus_id
FROM transaction_sta tus
WHERE transaction_id = this_id
ORDER BY transaction_tim estamp 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_sta tus.transaction _id,
person_id,
payment_method, status,
transaction_ite m.sale_item_id, quantity,transa ction_item.amou nt,
transaction_tim estamp, YEAR(transactio n_timestamp) as year,
SUM(quantity * (transaction_it em.sale_item_id IN (13,14,17))) as
num_banq,
SUM(quantity * (transaction_it em.sale_item_id = 18)) as
num_banq_child,
SUM(quantity * (transaction_it em.sale_item_id IN (13,14,15,16)))
as convention,
SUM(quantity * (transaction_it em.sale_item_id IN (19,20))) as
dayPass

FROM transaction_sta tus
INNER JOIN (SELECT
transaction_id, MAX(transaction _timestamp) as timetag
FROM transaction_sta tus
GROUP BY transaction_id) as T USING (transaction_id )
LEFT JOIN transaction USING (transaction_id )
LEFT JOIN transaction_ite m USING (transaction_id )
LEFT JOIN sale_item USING (sale_item_id)
WHERE
transaction_sta tus.transaction _timestamp = T.timetag
AND
status = "Completed"
AND
YEAR(transactio n_timestamp) = YEAR(NOW())
GROUP BY person_id

Cheers,

Jean