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 p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 SorterOpen 1 3 0 Keyinfo(1,BINARY) 00 NULL 2 Integer 0 5 0 00 clear abort flag 3 Integer 0 4 0 00 indicate accumulator empty 4 Null 0 8 8 00 NULL 5 Gosub 7 44 0 00 NULL 6 Goto 0 49 0 00 NULL 7 OpenRead 0 2 1 2 00 t 8 Rewind 0 15 0 00 NULL 9 Column 0 0 10 00 t.a 10 Sequence 1 11 0 00 NULL 11 Column 0 1 12 00 t.b 12 MakeRecord 10 3 13 00 NULL 13 SorterInsert 1 13 0 00 NULL 14 Next 0 9 0 01 NULL 15 Close 0 0 0 00 NULL 16 OpenPseudo 2 13 3 00 NULL 17 SorterSort 1 48 0 00 GROUP BY sort 18 SorterData 1 13 0 00 NULL 19 Column 2 0 9 20 NULL 20 Compare 8 9 1 Keyinfo(1,BINARY) 00 NULL 21 Jump 22 26 22 00 NULL 22 Move 9 8 1 00 NULL 23 Gosub 6 35 0 00 output one row 24 IfPos 5 48 0 00 check abort flag 25 Gosub 7 44 0 00 reset accumulator 26 AggStep 0 0 1 count(0) 00 NULL 27 Column 2 0 2 00 NULL 28 Column 2 2 3 00 NULL 29 Integer 1 4 0 00 indicate data in accumulator 30 SorterNext 1 18 0 00 NULL 31 Gosub 6 35 0 00 output final row 32 Goto 0 48 0 00 NULL 33 Integer 1 5 0 00 set abort flag 34 Return 6 0 0 00 NULL 35 IfPos 4 37 0 00 Groupby result generator entry point 36 Return 6 0 0 00 NULL 37 AggFinal 1 0 0 count(0) 00 NULL 38 Integer 0 14 0 00 NULL 39 Le 14 36 3 collseq(BINARY) 6c NULL 40 Copy 1 15 0 00 NULL 41 Copy 2 16 0 00 NULL 42 ResultRow 15 2 0 00 NULL 43 Return 6 0 0 00 end groupby result generator 44 Null 0 2 0 00 NULL 45 Null 0 3 0 00 NULL 46 Null 0 1 0 00 NULL 47 Return 7 0 0 00 NULL 48 Halt 0 0 0 00 NULL 49 Transaction 1 0 0 00 NULL 50 VerifyCookie 1 1 0 00 NULL 51 TableLock 1 2 0 t 00 NULL 52 Goto 0 7 0 00 NULL asql> insert into T values (1,2,1); rows ---- 1 asql> select count(),a from T group by a having b > 0; coun a ---- ------------- 1 1 asql> insert into T values (2,0,3); rows ---- 1 asql> select count(),a from T group by a having b > 0; coun a ---- ------------- 1 1 asql> insert into T values (2,1,3); rows ---- 1 asql> select count(),a from T group by a having b > 0; coun a ---- ------------- 1 1 2 2 asql> insert into T values (2,0,4); rows ---- 1 asql> select count(),a from T group by a having b > 0; coun a ---- ------------- 1 1 >-----Ursprüngliche Nachricht----- >Von: James K. Lowden [mailto:jklow...@schemamania.org] >Gesendet: Donnerstag, 08. Jänner 2015 03:56 >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 Mon, 5 Jan 2015 06:39:42 +0000 >Hick Gunter <h...@scigames.at<mailto:h...@scigames.at>> 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 > -------- - > 1 1 It *outputs* 1 row with 2 fields > >but what does "having b > 0" mean if "HAVING is applied to the RESULT set of a >SELECT"? There is no B! > Internally there is, because *you explicitly asked for it* >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. #3: a randomly selected b from the subset of records having a given a If the single aggregate function is MIN or MAX, the returned value for b will be from one of the records where the minimum or maximum respectively occurs. > >Most SQL DBMSs reject the query outright. As well they should, because it is >nonsensical. > >--jkl ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna,Austria Tel: +43 1 80100 - 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
