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

Wrong results when using join and group

    XMLWordPrintable

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. my.cnf
          5 kB
        2. explain.txt
          5 kB
        3. bugsql.txt
          5 kB
        4. bug_interesting.txt
          1 kB

        Issue Links

          Activity

            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.