SQLite "COUNT(*)"是否有害？

Question

考虑以下简单示例:

prompt% sqlite3 test.db
sqlite> create table employee (
           employee_id   integer       primary key,
           first_name    varchar2(32)  not null,
           last_name     varchar2(32)  not null
        );

sqlite> insert into employee (first_name, last_name) values ('Bill', 'Smith');
sqlite> insert into employee (first_name, last_name) values ('Sally', 'Jones');
sqlite> insert into employee (first_name, last_name) values ('Bill', 'Jones');

sqlite> select first_name, count(*) from employee;

结果会怎样？

天真的人可能会认为它会是:

Bill|2
Sally|1

但有经验的人会注意到 SELECT 查询缺少“GROUP BY”子句。 Oracle，当出现这个查询时，实际上会抛出一个错误:

SQL ERROR: ORA-00937: not a single-group group function

然而，SQLite 并没有提示，而是产生了:

Bill|3

这对我来说似乎是假的......我想显示总行数可能有意义，但简单地选择最后一个“first_name”似乎相当武断并且有潜在危险。

这是错误还是我无法理解的功能？ SQLite 不提供类似的安全网有什么原因吗？

Answer 1

这就是全部 documented :

If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression...

The single row of result-set data created by evaluating the aggregate and non-aggregate expressions in the result-set forms the result of an aggregate query without a GROUP BY clause. An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.

简单地说，没有安全网。如果需要，请不要忘记 GROUP BY。