Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn
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 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
Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn
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 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
Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn
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);
-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 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] Missing db name with the SQLITE_ATTACH action callback
Hello, I am trying to implement a cache of one connection metadata (databases, tables, columns, indexes). It seems possible to automatically update the cache by using an authorizer. But there is one problem with the SQLITE_ATTACH action: #define SQLITE_ATTACH 24 /* FilenameNULL*/ #define SQLITE_DETACH 25 /* Database Name NULL*/ Only the filename is available and is optional/not unique (for ":memory:" and "" temp database). Would you mind adding the database name as the fourth argument of the authorizer callback ? Thanks and regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
