Details
Description
I am using two tables joined together. If I use DISTINCT everything is OK, but I need unique rows. Using GROUP BY returns NULL. In old mysql 5.5 everything works fine.
Query 1 bad results:
SELECT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id
Query 2 - good results, but not unique by items_6.id
SELECT DISTINCT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id
Export and results provided in attachment.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-5719 Wrong result with GROUP BY and LEFT OUTER JOIN
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi,
Thanks for the report.
The wrong result from query 1 seems to be very similar to bug MDEV-5719, reproducible on the current 10.0 tree.
Query 1 - bad results: SELECT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id +----+-------+-------+----------+--------+--------+ | id | brand | model | imported | brand1 | model1 | +----+-------+-------+----------+--------+--------+ | 28 | 54 | 618 | 0 | NULL | NULL | | 29 | 65 | 1133 | 0 | NULL | NULL | | 30 | 123 | 0 | 0 | NULL | NULL | +----+-------+-------+----------+--------+--------+
However, I cannot reproduce the non-unique result from query 2. Could you please confirm that you are really getting this (copy-paste from the attachment):
Query 2 - good results, but not unique by items_6.id: SELECT DISTINCT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id +----+-------+-------+----------+------------+--------+ | id | brand | model | imported | brand1 | model1 | +----+-------+-------+----------+------------+--------+ | 28 | 54 | 618 | 0 | NULL | NULL | | 29 | 65 | 1133 | 0 | Volkswagen | Touran | | 29 | 65 | 1133 | 0 | VW | Touran | | 30 | 123 | 0 | 0 | NULL | NULL | +----+-------+-------+----------+------------+--------+
The result looks like something you would get without GROUP BY clause.
If you are really indeed getting it, could you please also paste the output of
EXPLAIN EXTENDED SELECT DISTINCT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id; SHOW WARNINGS;
and attach your cnf file .
Thanks.
Sorry, I made an mistake when copying queries from database. Updated bugsql.txt, added my.cnf
explain.txt - requested query, but changed after mistake. Warning is from time, when I tested the query on more complex table.
Okay, thanks for clarification.
Without GROUP BY, the result of the second query looks okay, no issues there.
The problem with query 1 looks the same as or similar to MDEV-5719, I will assign it to Sergei Petrunia to confirm.
Here is EXPLAIN from the 1st query:
EXPLAIN EXTENDED SELECT items_6 . * , cm1.brand AS brand1, cm2.model AS model1 FROM items_6 LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model GROUP BY items_6.id; +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | items_6 | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | | | 1 | SIMPLE | cm1 | ref | brandid | brandid | 2 | test.items_6.brand | 11 | 100.00 | Using where | | 1 | SIMPLE | cm2 | eq_ref | PRIMARY | PRIMARY | 2 | test.items_6.model | 1 | 100.00 | Using where | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+-------------+ | Note | 1003 | select `test`.`items_6`.`id` AS `id`,`test`.`items_6`.`brand` AS `brand`,`test`.`items_6`.`model` AS `model`,`test`.`items_6`.`imported` AS `imported`,`test`.`cm1`.`brand` AS `brand1`,`test`.`cm2`.`model` AS `model1` from `test`.`items_6` left join `test`.`cars_models` `cm1` on(((`test`.`cm1`.`brandid` = `test`.`items_6`.`brand`) and (`test`.`items_6`.`brand` is not null))) left join `test`.`cars_models` `cm2` on(((`test`.`cm2`.`id` = `test`.`items_6`.`model`) and (`test`.`items_6`.`model` is not null))) where 1 group by `test`.`items_6`.`id` |
This is very interesting, when I delete line ID=30 everything works fine. (see attachment bug_interesting.txt)
Elena's guess was correct - this issue is fixed by the fix for MDEV-5719.
Marking as duplicate of MDEV-5719
I find that this query works OK: