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