MySQL different result view vs query 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` &gt;= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` &lt; 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:


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` &gt;= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` &lt; 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??!


Can't find what causes this, any ideas please.
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;
&gt; apple - 6


but if you try

select kind, avg(price), color from fruit group by kind;
&gt; 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)