How to fix strange behavior of group by?

Hello.

There are two tables - properties and their values.
Structure respectively the following:

products_options
bf86025dfb3b45bcaa09a9d18a7c8100.pngproduct_option_values
bca3cba5239a41b5acc6230b02777513.png

The problem is this - when a sample of properties on the appropriate commodities group unpredictably. Of the values just disappears.

Example query:
select * from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id`
where `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value`


As we can see - in response to the issued value, value not exceeding 150.
381c78379e1a438aa70d77f068ac0ab5.png

But if we remove the group by...
70dd9d74000c4b94823ec8f674e470ac.png

We see value with the value 200, and even 300.
What to do? I was already at the toaster with this question, but alas, I was not able to help. I'm tired of manually rebuild the table (copying data into a temporary, persondata, and returning the data back).

Tables - InnoDB
July 9th 19 at 13:44
2 answers
July 9th 19 at 13:46
Solution
Removed the response, thinking that the product_id is still unique but no.
You have in the screenshot in the query product_id 161 and 159, they have the same value = 80, in this situation, MySQL gives an undefined sample. Here's more info:
stackoverflow.com/questions/1591909/group-by-behav...

Do not use * in the sample, define explicitly which fields you want to see. Use the aggregation in the GROUP BY or do a GROUP BY on a unique field if you want to see DISTINCT.
Well, I need to group the data so that the value of the output is not repeated.
With its task group by cope, but somewhere to cut off part of the values. - edmond.Strom commented on July 9th 19 at 13:49
What gives this query on real data?
select `product_option_values`.`value`, count(`product_option_values`.`product_id`),
group_concat(`product_option_values`.`product_id`, ',') from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value` having count(`product_option_values`.`product_id`) > 1; - Elfrieda57 commented on July 9th 19 at 13:52
: The values in place.
https://habrastorage.org/files/a65/be3/6bd/a65be36...

Thus, if you perform corny
select `product_option_values`.`option_id`, `product_option_values`.`value` from `product_option_values` where `product_option_values`.`product_id` in (...) group by `product_option_values`.`value`

You will get the same bug - values are not. I feel the problem is just in the group. - edmond.Strom commented on July 9th 19 at 13:55
It is impossible to fix comments instead of group_concat(`product_option_values`.`product_id`,','), use group_concat(`product_option_values`.`product_id`) to an extra comma to remove.

And about the bug
select `product_option_values`.`value`, count(`product_option_values`.`option_id`),
group_concat(`product_option_values`.`option_id`) from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value` having count(`product_option_values`.`product_id`) > 1;
Follow this question and if it will return some data, so, too, is not unique option_id value for the group. - Elfrieda57 commented on July 9th 19 at 13:58
: Thank you. The problem was in option_id, request all grouped by the value which is repeated with a different option_id, here mysql and "merged" these two fields together.
Added option_id grouping (grouping on two fields), everything worked as it should. - edmond.Strom commented on July 9th 19 at 14:01
July 9th 19 at 13:48
Solution
select * from `product_options`
inner join `product_option_values` on `product_options`.`id` = `product_option_values`.`option_id` AND `product_option_values`.`product_id` in (1,2,3)
group by `product_option_values`.`value`
Sorry, not helping.
https://habrastorage.org/files/8b9/e23/897/8b9e238... - edmond.Strom commented on July 9th 19 at 13:51
Well, without this IN the sample GROUP BY id, these meet with the other value? There is a suspicion that it is in the group. It may be wise to split into subqueries and group ready-made information. - Elfrieda57 commented on July 9th 19 at 13:54
: No sample IN data is correct. Very strange - edmond.Strom commented on July 9th 19 at 13:57

Find more questions by tags MySQL