Will Leshner <[EMAIL PROTECTED]> writes:
> Say I have a table defined and populated as follows:
>
> CREATE TABLE test (a TEXT);
> INSERT INTO test (a) VALUES ('hello');
> INSERT INTO test (a) VALUES ('hello');
> INSERT INTO test (a) VALUES ('hello');
>
> And I perform the following query:
>
> SELECT rowid,count(a) FROM test
>
> In SQLite I get back:
>
> 3|hello
This must be typo. You probably mean that you get back 3|3.
> But in MySQL I get back an error:
>
> #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no
> GROUP columns is illegal if there is no GROUP BY clause
>
> I'm wondering if MySQL isn't right to treat this as an error?
Even sqlite can't quite decide what to do in this case. sqlite3 returns, in
this case, the highest row number (it seems). sqlite2 returns NULL for the
rowid given the same inserts and query.
In any case, there's no defined way to determine *which* row the non-group
data will be returned from, so at best, it's an unreliable query.
(It'd be nice if you got back three rows from the above query:
1|3
2|3
3|3
but that's not what happens with either version of sqlite.)
Cheers,
Derrell
