Description:
The GROUP BY fails to raise an error when excluded with an aggregation function and a non-aggregated column. It simply returns the result of the aggregation against all rows in the table. This appears to be an incorrect behavior.
How to repeat:
DROP TABLE IF EXISTS nulls;
CREATE TABLE nulls ( stringfield VARHCAR(10));
INSERT INTO nulls VALUES ('');
INSERT INTO nulls VALUES (null);
SELECT stringfield, count(*)
FROM nulls
WHERE stringfield IS NULL;
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| NULL | 1 |
+-------------+----------+
1 row in set (0.00 sec)
SELECT stringfield, count(*)
FROM nulls
WHERE stringfield = '';
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| | 1 |
+-------------+----------+
SELECT stringfield, count(*)
FROM nulls
GROUP BY stringfield;
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| NULL | 1 |
| | 1 |
+-------------+----------+
2 rows in set (0.00 sec)
SELECT stringfield
, CASE
WHEN ISNULL(stringfield) THEN 'A real null'
ELSE 'A what again?'
END
FROM nulls;
+-------------+-----------------------------------------------+
| stringfield | CASE |
| | WHEN ISNULL(stringfield) THEN 'A real null' |
| | ELSE 'A what again?' |
| | END |
+-------------+-----------------------------------------------+
| | A what again? |
| NULL | A real null |
+-------------+-----------------------------------------------+
2 rows in set (0.00 sec)
SELECT stringfield, count(*)
FROM nulls;
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| | 2 |
+-------------+----------+
1 row in set (0.00 sec)
INSERT INTO nulls VALUES ('x');
SELECT stringfield, count(*)
FROM nulls;
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| | 3 |
+-------------+----------+
1 row in set (0.00 sec)
SELECT stringfield, count(*)
FROM nulls
GROUP BY stringfield;
+-------------+----------+
| stringfield | count(*) |
+-------------+----------+
| NULL | 1 |
| | 1 |
| x | 1 |
+-------------+----------+
2 rows in set (0.00 sec)
Suggested fix:
The lack of a GROUP BY in a statement where one column is aggregated and another isn't aggregated should raise a syntax error, and not perform the operation as if the non-aggregated column weren't in the select predicate.
Description: The GROUP BY fails to raise an error when excluded with an aggregation function and a non-aggregated column. It simply returns the result of the aggregation against all rows in the table. This appears to be an incorrect behavior. How to repeat: DROP TABLE IF EXISTS nulls; CREATE TABLE nulls ( stringfield VARHCAR(10)); INSERT INTO nulls VALUES (''); INSERT INTO nulls VALUES (null); SELECT stringfield, count(*) FROM nulls WHERE stringfield IS NULL; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | NULL | 1 | +-------------+----------+ 1 row in set (0.00 sec) SELECT stringfield, count(*) FROM nulls WHERE stringfield = ''; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | | 1 | +-------------+----------+ SELECT stringfield, count(*) FROM nulls GROUP BY stringfield; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | NULL | 1 | | | 1 | +-------------+----------+ 2 rows in set (0.00 sec) SELECT stringfield , CASE WHEN ISNULL(stringfield) THEN 'A real null' ELSE 'A what again?' END FROM nulls; +-------------+-----------------------------------------------+ | stringfield | CASE | | | WHEN ISNULL(stringfield) THEN 'A real null' | | | ELSE 'A what again?' | | | END | +-------------+-----------------------------------------------+ | | A what again? | | NULL | A real null | +-------------+-----------------------------------------------+ 2 rows in set (0.00 sec) SELECT stringfield, count(*) FROM nulls; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | | 2 | +-------------+----------+ 1 row in set (0.00 sec) INSERT INTO nulls VALUES ('x'); SELECT stringfield, count(*) FROM nulls; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | | 3 | +-------------+----------+ 1 row in set (0.00 sec) SELECT stringfield, count(*) FROM nulls GROUP BY stringfield; +-------------+----------+ | stringfield | count(*) | +-------------+----------+ | NULL | 1 | | | 1 | | x | 1 | +-------------+----------+ 2 rows in set (0.00 sec) Suggested fix: The lack of a GROUP BY in a statement where one column is aggregated and another isn't aggregated should raise a syntax error, and not perform the operation as if the non-aggregated column weren't in the select predicate.