Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

[James K. Lowden]

On Thu, 8 Jan 2015 08:42:25 +
Hick Gunter  wrote:

> It is legal and well defined in SQLite. See the explain output below.
> This is because of the well-documented feature of SQLite that columns
> that are neither GROUPED BY nor aggregated will have a defined value.

OK, understood, "in SQLite".  Thanks.  UIUC, I wouldn't call it "well"
defined because of the effect of the ungrouped column: 

> the b value returned for each a will be (an arbitrary one) 

so two SELECTs taking advantage of this aspect of SQLite may return
different results for the same data.  

--jkl
___
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

[Hick Gunter]

It is legal and well defined in SQLite. See the explain output below. This is 
because of the well-documented feature of SQLite that columns that are neither 
GROUPED BY nor aggregated will have a defined value.



First off, it is legal and perfectly normal to test for expressions containing 
columns that do not appear in the select list. Every JOIN with a foreign key 
that references a rowid does exactly that (nobody is interested in the 
internal, arbitrary rowid of the referenced table; just the facts please). The 
SQL Engine is expected to retrieve columns required to evaluate expressions.



The SQL standard defines WHERE as pertaining to the input rows and HAVING as 
pertaining to the output rows.



What you are really asking is:



SELECT count AS „count()“, a FROM (SELECT count() AS count, a, b FROM T GROUP 
BY a HAVING b > 0);



As already explained, the b value returned for each a will be (an arbitrary 
one) taken from the b values associated with this a. The fact that you are only 
interested in the first two columns of the result set does not preclude it 
containing addtional columns that are used because they are required to satisfy 
your query



asql> create temp table t (a int, b int, c int);

asql> .explain

asql> explain select count(),a from T group by a having b > 0;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

0 Trace  0 0 000  NULL

1 SorterOpen 1 3 0 Keyinfo(1,BINARY)  00  NULL

2 Integer0 5 000  clear abort flag

3 Integer0 4 000  indicate accumulator 
empty

4 Null   0 8 800  NULL

5 Gosub  7 44000  NULL

6 Goto   0 49000  NULL

7 OpenRead   0 2 1 2  00  t

8 Rewind 0 15000  NULL

9 Column 0 0 10   00  t.a

10Sequence   1 11000  NULL

11Column 0 1 12   00  t.b

12MakeRecord 103 13   00  NULL

13SorterInsert   1 13000  NULL

14Next   0 9 001  NULL

15Close  0 0 000  NULL

16OpenPseudo 2 13300  NULL

17SorterSort 1 48000  GROUP BY sort

18SorterData 1 13000  NULL

19Column 2 0 920  NULL

20Compare8 9 1 Keyinfo(1,BINARY)  00  NULL

21Jump   222622   00  NULL

22Move   9 8 100  NULL

23Gosub  6 35000  output one row

24IfPos  5 48000  check abort flag

25Gosub  7 44000  reset accumulator

26AggStep0 0 1 count(0)   00  NULL

27Column 2 0 200  NULL

28Column 2 2 300  NULL

29Integer1 4 000  indicate data in 
accumulator

30SorterNext 1 18000  NULL

31Gosub  6 35000  output final row

32Goto   0 48000  NULL

33Integer1 5 000  set abort flag

34Return 6 0 000  NULL

35IfPos  4 37000  Groupby result 
generator entry point

36Return 6 0 000  NULL

37AggFinal   1 0 0 count(0)   00  NULL

38Integer0 14000  NULL

39Le 14363 collseq(BINARY)  6c  NULL

40Copy   1 15000  NULL

41Copy   2 16000  NULL

42ResultRow  152 000  NULL

43Return 6 0 000  end groupby result 
generator

44Null   0 2 000  NULL

45Null   0 3 000  NULL

46Null   0 1 000  NULL

47Return 7 0 000  NULL

48Halt   0 0 000  NULL

49Transaction1 0 000  NULL

50VerifyCookie   1 1 000  NULL

51TableLock  1 2 0 t  00  NULL

52Goto   0 7  

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

[James K. Lowden]

On Mon, 5 Jan 2015 06:39:42 +
Hick Gunter  wrote:

> This is completely legal and well defined.
> 
> HAVING is applied to the RESULT set of a SELECT.

I beg to differ.  It's both invalid SQL and (therefore) undefined.
Furthermore, it's illogical.  Consider:

create table T (a int, b int, c int);
insert into T values (1, 2, 1);

select count(*), a from T group by a having b > 0;

Surely "select count(*), a from T" produces 1 row, 

count(*)a
-
   11

but what does "having b > 0" mean if "HAVING is applied to the RESULT
set of a SELECT"?  There is no B!  

If ad argumentum we say B refers to the B in the table, the question
remains: how to interpret the having clause?  Is it TRUE If 

1.  there exists a row for a given value of A for which B > 0? 
2.  all rows for a given A have B > 0? 

It so happens (per http://sqlfiddle.com/#!5/34e8d/1) that sqlite
accepts the query and uses interpretation #2.  But that is arbitrary:
HAVING is not a FORALL or EXISTS quantifier.  It has a single, clunky
job: to test aggregates.  

Most SQL DBMSs reject the query outright.  As well they should, because
it is nonsensical.  

--jkl
___
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

[Dan Kennedy]

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

[Dan Kennedy]

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

[Hick Gunter]

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

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

[James K. Lowden]

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] New column in select will not mask column of the same name in having clause and sqlite won't warn

[Tomas Telensky]

Hi,

here is a bug report as posted here: http://stackoverflow.com/q/27678468/684229

I got this query in sqlite

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?

PS: I am using sqlite using sqldf package in R (version 0.4-7.1). Package
RSQLite 0.11.4. According to its description the SQLite engine should be
version 3.7.17.

Regards,

Tomas

-- 
Pomozte nám chránit ptáky – staňte se členy ČSO! 
www.birdlife.cz/prihlaska.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users