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:
$ ./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
