On 01/05/2015 02:52 PM, Dan Kennedy wrote:
On 01/05/2015 01:39 PM, Hick Gunter wrote:
This is completely legal and well defined.
HAVING is applied to the RESULT set of a SELECT.
The select asks to count the "distinct kontrola" in each group of
kvadrat and datum, the HAVING clause specifies returning only those
records with pocet > 1.
If there were no pocet column in table b, this would return only the
non-empty groups, which is what the OP intended.
As there is a pocet column in table b, the HAVING clause refers to
the original b.pocet which contains a (from the POV of the
programmer) "randomly selected from the group" rows' value. This is a
documented SQLite feature.
SELECT kvadrat, datum, counted_pocet as pocet from (SELECT kvadrat,
datum ,pocet, count(distinct kontrola) as counted_pocet from b group
by kvadrat, datum HAVING pocet > 1);
SQLite prefers regular column references to aliases. For example:
$ ./sqlite3
SQLite version 3.8.8 2015-01-03 18:59:17
sqlite> CREATE TABLE t1(a, b, c);
sqlite> INSERT INTO t1 VALUES(1, 1, 1);
sqlite> INSERT INTO t1 VALUES(2, 2, 2);
sqlite> INSERT INTO t1 VALUES(3, 3, 3);
then:
sqlite> SELECT a, b+1 AS c FROM t1 WHERE c=2;
2|3
sqlite> SELECT a, b+1 AS d FROM t1 WHERE d=2;
1|2
In the first SELECT, the "c" in the WHERE clause still refers to
column "c", despite the alias. In the second, "d" is an alias for
"b+1". Or:
sqlite> SELECT a, b*-1 AS c FROM t1 ORDER BY c ASC;
3|-3
2|-2
1|-1
In the above, the "c" in the ORDER BY refers to (b*-1), not the column
"c".
MySQL and Postgres do the same thing for the ORDER BY example. Other
databases do not allow column aliases to be referred to from within
WHERE clauses.
Is this actually documented anywhere? That original names trump aliases?
So I guess the same thing is happening in the HAVING clause. The
column "pocet" is taking precedence over the alias "pocet".
MySQL does not support ungrouped columns in a GROUP BY clause. So it
picks out the "pocet" alias and the query behaves as desired. Postgres
does not support aliases in the GROUP BY clause, so the situation
doesn't come up. But MySQL does agree with SQLite for the following:
In the above paragraph, read "HAVING" for "GROUP BY"....
$ ./sqlite3
sqlite> CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
sqlite> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 1, 3);
sqlite> SELECT count(*) AS b FROM t1 GROUP BY b HAVING b=2;
1
sqlite> SELECT count(*) AS d FROM t1 GROUP BY b HAVING d=2;
2
Showing that given a choice, MySQL picks an original column over an
alias within the HAVING clause as well.
So, I guess, not a bug...
Dan.
-----Ursprüngliche Nachricht-----
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 03. Jänner 2015 00:45
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] New column in select will not mask column of
the same name in having clause and sqlite won't warn
On Sun, 28 Dec 2014 17:46:08 +0100
Tomas Telensky <tomas.telen...@gmail.com> wrote:
select kvadrat, datum, count(distinct kontrola) as pocet from b group
by kvadrat, datum having pocet > 1
The problem was that pocet was actually a column in table b and I
didn't notice, and the having clause was using the table column
instead of the newly derived column specified in select clause.
So far so good, but sqlite should at least issue any warning, right?
I would say it should raise an error. The HAVING clause should
include at least one aggregate. Comparing a column to a constant is
the job of WHERE.
The accepted syntax is ambiguous. Was the HAVING applied before or
after the aggregation. IOW, did you get
1. the count for each {kvadrat, datum} pair for which pocet > 1, or
2. the count of {kvadrat, datum} pairs that have at least one pocet > 1
?
In the first case the counts would be smaller by the number of rows
for which pocet <= 1. In the second case results rows would be
eliminated for pairs that contain only rows for which pocet <= 1.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users