Details
Major Description
When working on mdev-8646 I discovered that in in mariadb-10.0/10.1.
the query
SELECT DISTINCT (COUNT(*) + 1) AS c FROM t1 GROUP BY a
|
always returns only 1 row.
This can be demonstrated with the following test case:
create table t1 (a int, b int);
|
insert into t1 values (1,3), (2,4), (1,5), (1,3), (2,1), (1,5), (1,7), (3,1), (3,2), (3,1), (2,4);
|
SELECT (COUNT(*) + 1) AS c FROM t1 GROUP BY a;
|
SELECT DISTINCT (COUNT(*) + 1) AS c FROM t1 GROUP BY a;
|
The first query from the test case returns the correct result set:
MariaDB [test]> SELECT (COUNT(*) + 1) AS c FROM t1 GROUP BY a;
|
+---+
|
| c |
|
+---+
|
| 6 |
|
| 4 |
|
| 4 |
|
+---+
|
The second query returns only 1 row:
MariaDB [test]> SELECT DISTINCT (COUNT(*) + 1) AS c FROM t1 GROUP BY a;
|
+---+
|
| c |
|
+---+
|
| 6 |
|
+---+
|
Interesting that the query
|
SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
|
returns the correct result set:
MariaDB [test]> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
|
+---+
|
| c |
|
+---+
|
| 4 |
|
| 3 |
|
+---+
|
I did not check the earlier versions of mariadb.