Incorrect result when select count distinct with order by and math function in MySQL 5.7.17

Question

I have a simple table named temp with three columns and the following data:

# c1  c2  v
  1   1  'a'
  1   2  'b'
  1   1  'b'
  1   2  'a'

I run the query:

SELECT
    t01.c1,
    t01.c2,
    COUNT(DISTINCT v) AS cnt
FROM
(
    SELECT 
        FLOOR(c1) AS c1,
        FLOOR(c2) AS c2,
        v
    FROM 
        temp
)   AS t01
GROUP BY
    t01.c1,
    t01.c2
ORDER BY
    cnt DESC

This should return the following:

# c1, c2, cnt
   1,  1,  2
   1,  2,  2

But actually it returns this:

# c1, c2, cnt
   1,  1,  1
   1,  2,  1
   1,  1,  1
   1,  2,  1

This is strange just when it contains math function floor order by group by count distinct and the result is incorrect - it does not group the data.

Is this a MySQL bug in version 5.7.17?

Answer 1

Yes, this is a bug related to merging the derived table (subquery in FROM clause) into the outer query. Please, file a bug at bugs.mysql.com.

Workarounds:

• Set optimizer_switch='derived_merge=off';
• Add a LIMIT clause to the subquery. That will be prevent it from being merged into the outer query.
• Manually merge the subquery with the outer query:

---

SELECT 
  FLOOR(c1) AS g1, 
  FLOOR(c2) AS g2, 
  COUNT(DISTINCT v) AS cnt   
FROM temp
GROUP BY g1, g2
ORDER BY cnt DESC;  

Answer 2

It just tried it in MySql 5.6 on sqlfiddle and it just works. And on dbfiddle in MySql 5.7 it doesn't.

So it's probably something with MySql 5.7 as you already thought.

According to MySql reference manual under (Features Deprecated in MySQL 5.7)

GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators), but relying on implicit GROUP BY sorting in MySQL 5.7 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use To produce a given sort order, use explicit ASC or DESC designators for GROUP BY columns or provide an ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.

---

Update:

I tried something else for you which works:

SELECT
    t01.c1,
    t01.c2,
    count(v) AS cnt
FROM
(
    SELECT 
        DISTINCT v as v,
        FLOOR(c1) AS c1,
        FLOOR(c2) AS c2

    FROM 
        temp
)   AS t01
GROUP BY
    t01.c1,
    t01.c2
ORDER BY
    cnt DESC

This is my create statement:

CREATE TABLE temp
    (`c1` int, `c2` int, `v` varchar(5))
;

INSERT INTO temp
    (`c1`, `c2`, `v`)
VALUES
    (1, 1, '''a'''),
    (1, 1, '''a'''),
    (1, 2, '''b'''),
    (1, 1, '''b'''),
    (1, 1, '''c'''),
    (1, 2, '''a''')
;

the result: https://www.db-fiddle.com/f/7zBFKzd3pE7ymrD5LTcmkz/1