Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?
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?
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?
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?
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?
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?
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?
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?
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
[sqlite] is it really ok to allow non-aggregates in an aggregate line?
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)
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
