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

COUNT DISTINCE with GROUP BY with ORDER BY broken

    XMLWordPrintable

Details

    Description

      I'm running two queries on a view. Both group by the same set of columns (a,b,c) and count the values of a fourth column d. When using COUNT(d), the query returns 13 rows. When using COUNT(Dinstinct d), the queries returns >30 rows, most of them have count(d)=1. Obviously, the latter output is wrong.

      Using distinct works when skipping the ORDER BY clause or using the same column ordering in the order by clause as with the group by clause.

      Works:

      SELECT a,b,c , count( d ) FROM someView
      GROUP BY a,b,c
      ORDER BY c,a,b
      LIMIT 0,30

      Works:

      SELECT a,b,c , count(distinct d ) FROM someView
      GROUP BY a,b,c
      ORDER BY a,b,c
      LIMIT 0,30

      Broken:

      SELECT a,b,c , count(distinct d ) FROM someView
      GROUP BY a,b,c
      ORDER BY c,a,b
      LIMIT 0,30

      I'm using MariaDB 10.0.22-MariaDB-0+deb8u1 - (Debian). This used to work with MySQL 5 and maybe MariaDB 5.

      Attachments

        Activity

          People

            Unassigned Unassigned
            m-dev M. Braun
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.