Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Nicolas Williams]

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp wrote:
> On Mon, Oct 18, 2010 at 8:19 AM,  wrote:
> 
> > I made an error in my SQL when I did not include one of my non-aggregate
> > columns in my group.  I was surprised that Sqlite did not catch this, and
> > even more surprised when the docs spelled out this behavior.
> >
> > Is everyone ok with this?
> > Do any other SQL engines allow this?
> > (DB2 does not)
> >
> 
> I was going to change this at one point, so that it raised an error, but
> that suggestion raised such an outcry that I decided to leave it.
> Apparently, there are many applications out there that depend on this
> behavior.
> 
> I think the big use case is as a substitute for DISTINCT.

Perhaps there should be aggregate functions whose purpose is pick the
first/last value of some expression/column as ordered by some
expression (including by random).

Something like:

SELECT average(a), agg_random(b) FROM some_table GROUP BY c;
SELECT average(a), agg_first(b ORDER BY b ASC) FROM some_table GROUP BY c;
SELECT average(a), agg_last(b ORDER BY rowid ASC) FROM some_table GROUP BY c;

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Alexey Pechnikov]

2010/11/11 Drake Wilson 

>
> It looks like you're right, and the resulting row selected is only
> arbitrary (though often the one with the largest rowid).  This
> suggests that unless I'm misunderstanding the comparison, comparing
> SQLite's behavior of permitting this type of SELECT with Postgres's
> DISTINCT ON (as an earlier post in this thread did) is misleading,
> since DISTINCT ON is guaranteed to be semantically after ORDER BY
> processing and therefore allows controlling which row from a group is
> selected, if one is careful.
>

Is needed the sub-query with "order by" for this.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Drake Wilson]

Quoth "Jay A. Kreibich" , on 2010-11-10 18:43:06 -0600:
> > The observed useful behavior is to have such a reference return the
> > value from the first row in each group,
> 
>   I haven't verified this since 3.6.23.1, but in that version the
>   *last* row is the one that is returned.

Hmm.  Apparently I misremembered, then.

>   ORDER BY is applied after the GROUP BY and should not 
>   have any meaning to the rows within a group.  Any change
>   is likely a side-effect.

It looks like you're right, and the resulting row selected is only
arbitrary (though often the one with the largest rowid).  This
suggests that unless I'm misunderstanding the comparison, comparing
SQLite's behavior of permitting this type of SELECT with Postgres's
DISTINCT ON (as an earlier post in this thread did) is misleading,
since DISTINCT ON is guaranteed to be semantically after ORDER BY
processing and therefore allows controlling which row from a group is
selected, if one is careful.

Thanks for the corrections.

>-j

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Jay A. Kreibich]

On Wed, Nov 10, 2010 at 03:15:35PM -0700, Drake Wilson scratched on the wall:

> The observed useful behavior is to have such a reference return the
> value from the first row in each group,

  I haven't verified this since 3.6.23.1, but in that version the
  *last* row is the one that is returned.

  This is true of both ungrouped columns as well as grouped
  columns (which may not have identical values in each row).

  Of course "last" is up to the database, not your query,
  since there is no enforced ordering.

> and then to allow control of within-group ordering using ORDER BY. 

  ORDER BY is applied after the GROUP BY and should not 
  have any meaning to the rows within a group.  Any change
  is likely a side-effect.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Drake Wilson]

Quoth Alexey Pechnikov , on 2010-10-20 11:32:04 +0400:
> This is just one replacement for "distinct on" clause, as example. And you
> can use any sort order for non-aggregate values in your group so some
> queries are more simple than equal "distinct on" form in other DBMS
> (PostgreSQL,
> etc.). The feature is extremely useful for many applications.

So here's the followup million-dollar question, then: is this behavior
actually guaranteed?  I can't find any reference to how exactly a
reference to a non-aggregate result from outside a GROUP BY is
interpreted semantically in the SQLite documentation, and nor do I
think it's specified in SQL proper, especially since other engines
raise errors in this case.

The observed useful behavior is to have such a reference return the
value from the first row in each group, and then to allow control of
within-group ordering using ORDER BY.  But is this part of the public
interface, or is it an oddity that may change in future revisions?
Hipp's response upthread seems to indicate the former, but I'd rather
be sure.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Alexey Pechnikov]

This is just one replacement for "distinct on" clause, as example. And you
can use any sort order for non-aggregate values in your group so some
queries are more simple than equal "distinct on" form in other DBMS
(PostgreSQL,
etc.). The feature is extremely useful for many applications.

2010/10/18 

> I made an error in my SQL when I did not include one of my non-aggregate
> columns in my group.  I was surprised that Sqlite did not catch this, and
> even more surprised when the docs spelled out this behavior.
>
> Is everyone ok with this?
> Do any other SQL engines allow this?
> (DB2 does not)
>
>
-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Jay A. Kreibich]

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp scratched on the wall:

> I was going to change this at one point, so that it raised an error, but
> that suggestion raised such an outcry that I decided to leave it.
> Apparently, there are many applications out there that depend on this
> behavior.

  I get extremely annoyed at databases that consider this an "error".
  I've had it get in the way many times, and never once has it saved me
  from an unintended error.
 
  I realize that it sets up the potential for the database to return
  somewhat nonsensical values, but getting SQL to do something dumb
  isn't exactly hard.  At the end of the day, I know my database much
  better than the RDBMS does, and the "we need to protect you from
  yourself" error is not really appreciated.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

[Richard Hipp]

On Mon, Oct 18, 2010 at 8:19 AM,  wrote:

> I made an error in my SQL when I did not include one of my non-aggregate
> columns in my group.  I was surprised that Sqlite did not catch this, and
> even more surprised when the docs spelled out this behavior.
>
> Is everyone ok with this?
> Do any other SQL engines allow this?
> (DB2 does not)
>

I was going to change this at one point, so that it raised an error, but
that suggestion raised such an outcry that I decided to leave it.
Apparently, there are many applications out there that depend on this
behavior.

I think the big use case is as a substitute for DISTINCT.



>
> Sent from my Verizon Wireless BlackBerry
>
> -Original Message-
> From: Stephen Chrzanowski 
> Sender: sqlite-users-boun...@sqlite.org
> Date: Mon, 18 Oct 2010 06:30:28
> To: General Discussion of SQLite Database
> Reply-To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Time calculation bug?
>
> Interesting.  I get the same results as you when I use sqlite3.exe, but, in
> a database manager, the result comes back as I reported.  I'll contact the
> developer of the utility and see if he can come up with something.
>
> On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies
> wrote:
>
> > On 18 October 2010 09:28, Stephen Chrzanowski 
> wrote:
> > > I seem to be having an odd behavioral problem with calculating time
> > stamps.
> > >
> > .
> > .
> > .
> > > For instance:
> > >
> > > select strftime('%s','now') RealUTC,strftime('%s','now','localtime')
> > > LocalTime,
> > >   strftime('%s','now') -   strftime('%s','now','localtime')
> > >
> > > Yeilds results of:
> > > RealUTCLocalTime  strftime('%s','now') -
> > > strftime('%s','now','localtime')
> > > -- --
> > > -
> > > 1287389442 1290053442 -2664000
> >
> > On my windoze7 m/c I set the time zone to Atlantic Time (Canada) (UTC
> > -04:00), and executed your query in sqlite3 shell:
> >
> > SQLite version 3.6.11
> > Enter ".help" for instructions
> > sqlite> select strftime('%s','now')
> > RealUTC,strftime('%s','now','localtime')
> >...> LocalTime,
> >   ...>   strftime('%s','now') -
> strftime('%s','now','localtime')
> >...> ;
> > 1287394030|1287379630|14400
> > sqlite>
> >
> > I do not see the problem that you report
> >
> > >
> > > I'm currently sitting in -0400 (EDT) and there should only be a maximum
> > of
> > > 14,400 seconds.  2664000 seems to add up to just under 31 days.
> > >
> > > Now, I'm writing the code that does the database management, and I've
> > > modified it so that when inserting/updating the time, its done with the
> > > date('2010-10-18 04:08:04','utc') to do the conversion, and the math
> > works
> > > without using UTC or LOCALTIME in the strftime functions but I'd still
> > like
> > > to know why the above SQL statement bombs?
> >
> > Regards,
> > Simon
> > ___
> > 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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users