0

I have a following query. THIS returns me correct result. Note the criteria.

SELECT 
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    WHERE business_id = 10 AND review_channel_id = 3 // CRITERIA
    GROUP BY `review_score_aggregate`.`business_id` 
        , MONTH(`review_score_aggregate`.`review_date`) 
        , YEAR(`review_score_aggregate`.`review_date`)

Correct result: correct

Now preferably I want to use view for this same query which is defined exactly like this:

CREATE VIEW `review_score_monthly_view` AS
    SELECT 
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    GROUP BY `review_score_aggregate`.`business_id` 
        , MONTH(`review_score_aggregate`.`review_date`) 
        , YEAR(`review_score_aggregate`.`review_date`)

.. and through the application I would simply query from the view. Now this gives me way different result.

SELECT * 
FROM review_score_monthly_view 
WHERE business_id = 10 
    AND review_channel_id = 3;

Wrong result??! wrong

Can't find what causes this, any ideas please.

0

1 Answer 1

2

In the first case the filtering by WHERE is done before the grouping so only those with review_channel_id = 3 are aggregated, but in the second case all are aggregated and only those who get review_channel_id = 3 from group by are shown.

What's probably a worse problem - you use GROUP BY incorrectly by selecting columns which are neither grouped by, nor aggregated or functionally dependent on the grouping columns (id, review_channel_id etc.).

To explain that imagine a table of fruit:

kind  | color | price
------+-------+------
apple | red   |     5
apple | green |     7

You can do

select kind, avg(price) from fruit group by kind;
> apple - 6

but if you try

select kind, avg(price), color from fruit group by kind;
> apple - 6 - ??(red/green)

The column can contain only one value and you gave it no instructions to decide which one so it picks one "randomly" (= it depends on specific implementation and the plan used to execute the query - implementation details can change between versions and the plan can change between two executions) so you may get different results from the same query and no one of them is "right".

You are allowed to do that in MySQL because it cannot detect functional dependencies before version 5.7.5 so it lets you write the code but trusts you to check the dependencies yourself. You can make it not to trust you by enabling ONLY_FULL_GROUP_BY - with that your query would not be allowed at all.

In 5.7 the check was implemented which can decide if the column is functionally dependent on the grouping columns -> if for each group there is at most one possible value for that column. Imagine another table fruit_shape, where "apple" is "round" and "pear" is "pear-shaped", if you join such a table to the fruits, each "apple" will always only be "round" by definition so there is no need to pick from different values as with red/green => the shape is functionally dependent on the kind.

If you want to learn more, try this article and its follow-up for 5.7

Now for your case:

  • do not use views if you can avoid it - there are some performance hits because the planner cannot work freely with the views as it can in the case you use the full query (and GROUP BY in the view definition limits the options even more)
  • if you have to use the view for any reason, add the review_channel_id to the GROUP BY list - it will compute the results for all (business, channel) pairs (and for each month of those) and you can filter by WHERE in the query later - but if you used the full query in the first time, it would compute only those pairs you ask for
  • do not select the id (=review_id) if there are more than one of them for any (business, channel, year, month) tuple - it will not give any meaningful value
  • the same with the review date probably, select year() and month() instead (or make the date showing the first day of a given month, but I would guess even 5.7 will have a hard time to detect that dependency even when it exists)
3
  • Thanks again, I understand now. As I see then actually selecting the values (channel_id, date) is actually bad practice anyway, more so after 5.7 then. The reason for including them was more implementation problem in Java-s Hibernate framework which needs these fields to be present, even though they don't serve any value. My query is only based on 3 conditions: business_id (always one), review_channel_id (1 or more), and review_date (between any range). Adding channel_id to group by works, but for end result I need to get aggregates for all channels combined and sums/avgs of all of them.
    – Vaelyr
    Commented Oct 7, 2015 at 7:05
  • You want to use the view to map is as "entity" in Hibernate? For "reporting" queries it is better to use Native SQL imho.
    – jkavalik
    Commented Oct 7, 2015 at 7:12
  • That was the intent yes, as for Hibernate it doesn't matter if it's table or view. But native queries might be a better approach here for sure. I think I'll take this path and only use view for one base aggregate.
    – Vaelyr
    Commented Oct 7, 2015 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.