Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6417

Wrong results when using join and group

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.10, 10.0.12
    • 10.0.16
    • Optimizer
    • None
    • Ubuntu 14.04
      10.0.12-MariaDB-1~trusty-log - mariadb.org binary distribution

    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.

      Attachments

        1. bug_interesting.txt
          1 kB
        2. bugsql.txt
          5 kB
        3. explain.txt
          5 kB
        4. my.cnf
          5 kB

        Issue Links

          Activity

            tomasz_svk Tomasz added a comment - - edited

            I find that this query works OK:

            SELECT * 
            FROM (
            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
            ) t
            GROUP BY t.id

            tomasz_svk Tomasz added a comment - - edited I find that this query works OK: SELECT * FROM ( 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 ) t GROUP BY t.id

            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.

            elenst Elena Stepanova added a comment - 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.
            tomasz_svk Tomasz added a comment - - edited

            Sorry, I made an mistake when copying queries from database. Updated bugsql.txt, added my.cnf

            tomasz_svk Tomasz added a comment - - edited Sorry, I made an mistake when copying queries from database. Updated bugsql.txt, added my.cnf
            tomasz_svk Tomasz added a comment - - edited

            explain.txt - requested query, but changed after mistake. Warning is from time, when I tested the query on more complex table.

            tomasz_svk Tomasz added a comment - - edited 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 psergey 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` |

            elenst Elena Stepanova added a comment - 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 psergey 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` |
            tomasz_svk Tomasz added a comment -

            This is very interesting, when I delete line ID=30 everything works fine. (see attachment bug_interesting.txt)

            tomasz_svk Tomasz added a comment - 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.

            psergei Sergei Petrunia added a comment - Elena's guess was correct - this issue is fixed by the fix for MDEV-5719 .

            Marking as duplicate of MDEV-5719

            psergei Sergei Petrunia added a comment - Marking as duplicate of MDEV-5719

            People

              psergei Sergei Petrunia
              tomasz_svk Tomasz
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.