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

Wrong results when using join and group

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.10, 10.0.12
    • Fix Version/s: 10.0.16
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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

              People

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

                Dates

                Created:
                Updated:
                Resolved: