Bug #37305 select count(*); returns value
Submitted: 10 Jun 2008 9:53 Modified: 14 Aug 2008 16:40
Reporter: Philip Stoev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0/5.1/6.0, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[10 Jun 2008 9:53] Philip Stoev
Description:
count(*) returns value even if no tables are involved. I think this is wrong because:

mysql> select * from dual;
ERROR 1096 (HY000): No tables used
mysql> select *;
ERROR 1096 (HY000): No tables used
mysql> select count(*) from dual;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from Q;
ERROR 1146 (42S02): Table 'test.Q' doesn't exist

mysql> select count(*) fromQ;
+-------+
| fromQ |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

A single missing space would cause a bad query to actually work and return a bogus answer.

How to repeat:
mysql> select count(*) from dual;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Suggested fix:
Return "no tables used" if no tables are specified but the * argument to COUNT() is used.
[10 Jun 2008 10:38] Miguel Solorzano
Thank you for the bug report.
[10 Jun 2008 12:07] Paul Dubois
re:

"
mysql> select count(*) fromQ;
+-------+
| fromQ |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

A single missing space would cause a bad query to actually work and
return a bogus answer.
"

The "bad query " is not necessarily a bad query. It's a query of the form "SELECT value alias_name", equivalent to "SELECT value AS alias_name".
[10 Jun 2008 12:20] Philip Stoev
Yes I understand the parsing problem with the AS being optional. I just wanted to point out that there are cases where allowing a standalone count(*) to return 1 can really bite the user.
[14 Aug 2008 16:40] Philip Stoev
This bug is verified, it was mistakenly left in "Analysing".
[10 Jun 2014 10:58] Hartmut Holzgraefe
It might be debatable whether COUNT(*) without a table should return 0 or 1,
but as PostgreSQL and SQLite behave the same way I'd say: not a bug
[13 Jul 2017 8:01] Umesh Shastry
Bug #87051 marked as duplicate of this one