On Tue, Mar 9, 2010 at 9:56 AM, Scott Hess <sh...@google.com> wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano <tim.rom...@yahoo.com> wrote: >> Of these three: >> >> select c from T where 1=2 // returns 0 rows >> select min(c) from T where 1=2 // returns 1 row >> select min(88,99) from T where 1=2 // returns 0 rows >> >> the only case that "threw" me is the second one, where a row is returned >> despite a WHERE condition that should yield an empty set (or so I thought). > > The first and last cases will run for each row in the WHERE clause. > The second case is aggregating over all c, and will always return one > row, even if the WHERE clause selects many rows, so it is consistent > for it to return one row if the WHERE clause selects for no rows. > It's as if you coded it like this: > > SELECT min(SELECT c FROM t WHERE ...) > > meaning the minimum of that set of inputs, and if that set is empty, > there is no minimum, so you get a result of NULL, but not no result, > if you see what I mean. >
That is a great explanation. Another way to think about it is to go from right to left. In first and third cases, the WHERE clause is true for 0 rows, and so SELECT is unable to return anything. In the second case also the WHERE clause returns 0 rows, but there is no Min defined for 0 rows, so a NULL is returned. Now, you may ask, how is Min(88,89) different from Min(c), and that is where the note from the docs I sent comes in. The Min(x,y..) form makes min() perform like a simple function such as Sin() or Left(), etc. But when given a single argument, Min() operates as an aggregate function if given only a single argument. Consider sqlite> SELECT Min(88,89) WHERE 1=2; sqlite> SELECT Min(88) WHERE 1=2; Min(88) ---------- sqlite> Note that Min(88) is returning a NULL row while Min(88,89) is not returning anything. > -scott > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
