Re: [sqlite] Details on New Features

[Gabor Grothendieck]

On Sun, May 6, 2012 at 8:00 PM, Donald Griggs  wrote:
> Regarding:   What precisely are the
> "improvements" in handling of CSV inputs?
>
>
> Gabor, I don't know about "precisely" -- I'll let others on the list tell
> me where I'm off, but here's my take:
>
>
> A lot of strange things call themselves csv, but the change attempts to
> make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180.
>     http://tools.ietf.org/html/rfc4180
>
> http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization
>
> In particular, during CSV mode import:
>  -- Allow any field to be surrounded by double quote characters without
> those characters being considered part of the field data.
>  -- Allow fields to contain embedded commas (or other separators) when the
> field is surrounded by double quote characters.
>  -- Allow fields to span multiple lines if they are surrounded by double
> quote characters.
>  -- Allow the double quote character to be escaped by having two adjacent
> double quote characters. (But note that a field consisting solely of two
> double quote characters still represents an empty string field.)
>
>  -- On output in CSV mode, surround text fields with double quotes when
> needed.
>
>
> See check-in [93aa17d866]   http://www.sqlite.org/src/info/93aa17d866
>

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

Re: [sqlite] Details on New Features

[Donald Griggs]

Regarding:   What precisely are the
"improvements" in handling of CSV inputs?


Gabor, I don't know about "precisely" -- I'll let others on the list tell
me where I'm off, but here's my take:


A lot of strange things call themselves csv, but the change attempts to
make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180.
 http://tools.ietf.org/html/rfc4180

http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization

In particular, during CSV mode import:
  -- Allow any field to be surrounded by double quote characters without
those characters being considered part of the field data.
  -- Allow fields to contain embedded commas (or other separators) when the
field is surrounded by double quote characters.
  -- Allow fields to span multiple lines if they are surrounded by double
quote characters.
  -- Allow the double quote character to be escaped by having two adjacent
double quote characters. (But note that a field consisting solely of two
double quote characters still represents an empty string field.)

  -- On output in CSV mode, surround text fields with double quotes when
needed.


See check-in [93aa17d866]   http://www.sqlite.org/src/info/93aa17d866

(By the way, I believe the sqlite3 command line utility (CLI) was intended
to be more of a debug tool than a production component -- but it surely is
useful!)

For an example of CSV import, if I have file MyStuff.csv whose data is
shown below between the barred lines below (words in square brackets [] are
just my comments and were not present in the import file):
==
1,cat
2,"rat"[quotes are optional unless separator(s)
embedded]
3 ,"grey fox"  [extra whitespace will be handled differently
when affinity is numeric]
4, spacedog[There's a space before and after spacedog --
trust me]
5,o'possum
6,"big, bad, wolf"
7,"two-lined   [Fields can span lines]
zebra"
8, [Second field empty. (Maybe I forgot to type
"Missing lynx")]
9,imperial ("laughing") loon
==
 Now I create a test database.

C:\util>sqlite3 test.db

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> /* Define a simple table t, comprised of an integer column and a
text column */
sqlite> Create table t ( id integer, animal);

sqlite> /*  import the data above using csv mode */
sqlite> .mode csv
sqlite> .import MyStuff.csv   t


sqlite> /* Show the table in CSV mode
sqlite> select * from t;
1,cat
2,rat
3,"grey fox"
4," spacedog "
5,"o'possum"
6,"big, bad, wolf"
7,"two-lined
zebra"
8,""
9,"imperial (""laughing"") loon"
sqlite>
sqlite>
sqlite>
sqlite> /* Try changing the separator and show it again in LIST mode */
sqlite> .separator |
sqlite> .mode list
sqlite> select * from t;
1|cat
2|rat
3|grey fox
4| spacedog
5|o'possum
6|big, bad, wolf
7|two-lined
zebra
8|
9|imperial ("laughing") loon
sqlite>

Does this answer your questions?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Nico Williams]

On Fri, May 4, 2012 at 4:04 PM, Richard Hipp  wrote:
> Correction:  The one that it encounters first, since subsequent rows of the
> same value will not trigger a new copy of values into the output registers,
> since only a new min/max does that.

But surely that's non-deterministic, or you'd like users to think so.

I think that leaves SQLite3's handling of non-aggregate expressions in
GROUP BY queries as.. not fully satisfying.  I think there really has
to be a way to flag such queries as erroneous.

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

Re: [sqlite] Details on New Features

[Richard Hipp]

On Fri, May 4, 2012 at 5:02 PM, Richard Hipp  wrote:

>
>
> On Fri, May 4, 2012 at 5:01 PM, Ralf Junker  wrote:
>
>> On 04.05.2012 16:39, Richard Hipp wrote:
>>
>> > If a single min() or max() aggregate function appears in a query, then
>> any
>> > other columns that are not contained within aggregate functions and that
>> > are not elements of the GROUP BY will take values from one of the same
>> rows
>> > that satisfied the one min() or max() aggregate function.
>>
>> Given that more than one row satisfies the one min() or max() aggregate
>> function (think of multiple, identical smallest or largest values).
>> Which row will SQLite pick?
>>
>
> The row that it encounters last.
>

Correction:  The one that it encounters first, since subsequent rows of the
same value will not trigger a new copy of values into the output registers,
since only a new min/max does that.


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



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

Re: [sqlite] Details on New Features

[Igor Tandetnik]

On 5/4/2012 5:01 PM, Ralf Junker wrote:

On 04.05.2012 16:39, Richard Hipp wrote:


If a single min() or max() aggregate function appears in a query, then any
other columns that are not contained within aggregate functions and that
are not elements of the GROUP BY will take values from one of the same rows
that satisfied the one min() or max() aggregate function.


Given that more than one row satisfies the one min() or max() aggregate
function (think of multiple, identical smallest or largest values).
Which row will SQLite pick?


One of them.
--
Igor Tandetnik

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

Re: [sqlite] Details on New Features

[Richard Hipp]

On Fri, May 4, 2012 at 5:01 PM, Ralf Junker  wrote:

> On 04.05.2012 16:39, Richard Hipp wrote:
>
> > If a single min() or max() aggregate function appears in a query, then
> any
> > other columns that are not contained within aggregate functions and that
> > are not elements of the GROUP BY will take values from one of the same
> rows
> > that satisfied the one min() or max() aggregate function.
>
> Given that more than one row satisfies the one min() or max() aggregate
> function (think of multiple, identical smallest or largest values).
> Which row will SQLite pick?
>

The row that it encounters last.


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

Re: [sqlite] Details on New Features

[Marc L. Allen]

The last one it saw.  It's not deterministic.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Ralf Junker
> Sent: Friday, May 04, 2012 5:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Details on New Features
> 
> On 04.05.2012 16:39, Richard Hipp wrote:
> 
> > If a single min() or max() aggregate function appears in a query,
> then
> > any other columns that are not contained within aggregate functions
> > and that are not elements of the GROUP BY will take values from one
> of
> > the same rows that satisfied the one min() or max() aggregate
> function.
> 
> Given that more than one row satisfies the one min() or max() aggregate
> function (think of multiple, identical smallest or largest values).
> Which row will SQLite pick?
> 
> Ralf
> ___
> 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] Details on New Features

[Ralf Junker]

On 04.05.2012 16:39, Richard Hipp wrote:

> If a single min() or max() aggregate function appears in a query, then any
> other columns that are not contained within aggregate functions and that
> are not elements of the GROUP BY will take values from one of the same rows
> that satisfied the one min() or max() aggregate function.

Given that more than one row satisfies the one min() or max() aggregate
function (think of multiple, identical smallest or largest values).
Which row will SQLite pick?

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

Re: [sqlite] Details on New Features

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/05/12 09:42, Nico Williams wrote:
> A pragma by which to cause SQLite3 to return an error instead might be
> useful, but then, it's SQL_Lite_.

What I have always wanted for SQLite is some sort of "lint" mode.  It
would tell you when your queries rely on undefined behaviour, make no
sense or are suboptimal.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+kFhYACgkQmOOfHg372QSV4QCgzWo6QrO5Umx6G49uVhawCvqR
KmYAnAiOX67q1yM5i1JOGQB41QVuc/oC
=yzM2
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Richard Hipp]

On Fri, May 4, 2012 at 12:44 PM, Eric Sink  wrote:

>
> Is this new syntax likely to perform any better than the traditional way
> of writing the query?
>

Dunno.  Depends on which "traditional way" you are talking about, I suppose.

Here's how it works:  SQLite internally maintains a set of "registers"
(objects in which it can store any valid value) for the result set.  As it
is evaluating each row of the inputs to an aggregate query, if there is a
min() or max() agg function that hits a new minimum or maximum value, then
the other values of the current input row are copied into the output
registers.  If the min() or max() did not reach a new low or high, then the
values are not copied.  So, after all input rows have been examined, the
output registers will contain values that correspond to the last min() or
max() row.

The above should make it clear what happens if you have multiple min()
and/or max() functions in the same aggregate query.  The one that reached
its extreme last is the one whose row values will appear in the output
set.  Of course, the order of the input rows is usually undefined, so there
are no guarantees about which row that will actually be.  You know that
whatever values appear in the final output will correspond to the extreme
of at least one of the various min()/max() functions - you just don't know
which one.

BTW, this ability for min()/max() to trigger the copying of input values
into the output register - it is not a capability that is exposed to
application-defined functions.  So you cannot add new functions that work
exactly like the built-in min() and max().  You can override the built-in
min() and max() aggregates, but if you do, you lose the magical processing
that makes output values all come from the row that contained the min or
max value.


>
> --
> E
>
>
> On May 4, 2012, at 11:42 AM, Nico Williams  wrote:
>
> > On Fri, May 4, 2012 at 9:20 AM, Richard Hipp  wrote:
> >>> Queries of the form: "SELECT max(x), y FROM table" returns the
> >>> value of y on the same row that contains the maximum x value.
> >>>
> >>> Is that standard SQL behavior?  I'd have expected that to return one
> row
> >>> for every row in the table.  To get the behavior described above, I'd
> use
> >>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
> >>
> >> It is definitely NOT standard behavior.  The standard behavior is
> >> undefined.  Or (with many SQL engines) it will throw an error if you
> have a
> >> term in the result set that is not part of an aggregate function or an
> >> element of the GROUP BY clause.  But lots of newbies expect SQL to work
> as
> >> described in the 3.7.11 release comments, and we used to get support
> >> questions because it did not.  And so rather than continue to answer the
> >> questions over and over, I figured it would be easier to tweak SQLite to
> >> reliably do what newbies expect.  I never anticipated that this change
> >> would be so controversial or confusing.
> >
> > This is very clever.  I'm not sure that an informative error message
> > wouldn't have been better, but I think you made the right choice given
> > SQLite3's previous behavior.  A pragma by which to cause SQLite3 to
> > return an error instead might be useful, but then, it's SQL_Lite_.
> >
> > Is there any way to define aggregate functions that pick a row for
> > providing column values in non-aggregate expressions?  E.g., you could
> > have a median() or mode(), no?  I don't think this is important, am
> > just curious.
> >
> > Nico
> > --
> > ___
> > 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

Re: [sqlite] Details on New Features

[Eric Sink]

Is this new syntax likely to perform any better than the traditional way of 
writing the query?

--
E


On May 4, 2012, at 11:42 AM, Nico Williams  wrote:

> On Fri, May 4, 2012 at 9:20 AM, Richard Hipp  wrote:
>>> Queries of the form: "SELECT max(x), y FROM table" returns the
>>> value of y on the same row that contains the maximum x value.
>>> 
>>> Is that standard SQL behavior?  I'd have expected that to return one row
>>> for every row in the table.  To get the behavior described above, I'd use
>>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>> 
>> It is definitely NOT standard behavior.  The standard behavior is
>> undefined.  Or (with many SQL engines) it will throw an error if you have a
>> term in the result set that is not part of an aggregate function or an
>> element of the GROUP BY clause.  But lots of newbies expect SQL to work as
>> described in the 3.7.11 release comments, and we used to get support
>> questions because it did not.  And so rather than continue to answer the
>> questions over and over, I figured it would be easier to tweak SQLite to
>> reliably do what newbies expect.  I never anticipated that this change
>> would be so controversial or confusing.
> 
> This is very clever.  I'm not sure that an informative error message
> wouldn't have been better, but I think you made the right choice given
> SQLite3's previous behavior.  A pragma by which to cause SQLite3 to
> return an error instead might be useful, but then, it's SQL_Lite_.
> 
> Is there any way to define aggregate functions that pick a row for
> providing column values in non-aggregate expressions?  E.g., you could
> have a median() or mode(), no?  I don't think this is important, am
> just curious.
> 
> Nico
> --
> ___
> 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] Details on New Features

[Nico Williams]

On Fri, May 4, 2012 at 9:20 AM, Richard Hipp  wrote:
>>         Queries of the form: "SELECT max(x), y FROM table" returns the
>> value of y on the same row that contains the maximum x value.
>>
>> Is that standard SQL behavior?  I'd have expected that to return one row
>> for every row in the table.  To get the behavior described above, I'd use
>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>
> It is definitely NOT standard behavior.  The standard behavior is
> undefined.  Or (with many SQL engines) it will throw an error if you have a
> term in the result set that is not part of an aggregate function or an
> element of the GROUP BY clause.  But lots of newbies expect SQL to work as
> described in the 3.7.11 release comments, and we used to get support
> questions because it did not.  And so rather than continue to answer the
> questions over and over, I figured it would be easier to tweak SQLite to
> reliably do what newbies expect.  I never anticipated that this change
> would be so controversial or confusing.

This is very clever.  I'm not sure that an informative error message
wouldn't have been better, but I think you made the right choice given
SQLite3's previous behavior.  A pragma by which to cause SQLite3 to
return an error instead might be useful, but then, it's SQL_Lite_.

Is there any way to define aggregate functions that pick a row for
providing column values in non-aggregate expressions?  E.g., you could
have a median() or mode(), no?  I don't think this is important, am
just curious.

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

Re: [sqlite] Details on New Features

[Gabor Grothendieck]

On Fri, May 4, 2012 at 10:46 AM, Gabor Grothendieck
 wrote:
> On Fri, May 4, 2012 at 10:39 AM, Richard Hipp  wrote:
>> On Fri, May 4, 2012 at 10:33 AM, Gabor Grothendieck >> wrote:
>>
>>> On Fri, May 4, 2012 at 10:20 AM, Richard Hipp  wrote:
>>> > On Fri, May 4, 2012 at 10:06 AM, Rob Richardson <
>>> rdrichard...@rad-con.com>wrote:
>>> >
>>> >> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
>>> >>         Queries of the form: "SELECT max(x), y FROM table" returns the
>>> >> value of y on the same row that contains the maximum x value.
>>> >>
>>> >> Is that standard SQL behavior?  I'd have expected that to return one row
>>> >> for every row in the table.  To get the behavior described above, I'd
>>> use
>>> >> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>>> >>
>>> >
>>> > It is definitely NOT standard behavior.  The standard behavior is
>>> > undefined.  Or (with many SQL engines) it will throw an error if you
>>> have a
>>> > term in the result set that is not part of an aggregate function or an
>>> > element of the GROUP BY clause.  But lots of newbies expect SQL to work
>>> as
>>> > described in the 3.7.11 release comments, and we used to get support
>>> > questions because it did not.  And so rather than continue to answer the
>>> > questions over and over, I figured it would be easier to tweak SQLite to
>>> > reliably do what newbies expect.  I never anticipated that this change
>>> > would be so controversial or confusing.
>>> >
>>> > All the existing, portable, documented ways to find the maximum element
>>> of
>>> > one column while simultaneously finding the other elements in the same
>>> row,
>>> > continue to work as they always have.  You are not required to use this
>>> new
>>> > approach.  In fact, if you want your SQL to be portable, you should
>>> > probably avoid it.  By adding this feature, we had hoped to help
>>> > application developers avoid a common SQL programming error.  That's all.
>>> > There is nothing profound going on here.
>>>
>>> Can't anyone answer the question directly?  I would still like to know
>>> precisely what works and what does not.  Its not possible to rely on
>>> general SQL documentation for this so I think its important to
>>> document it exactly.  Otherwise, we are left to examine the source
>>> code or use trial and error (and these methods only tell you how it
>>> works but not how its intended to work and they could be different if
>>> there are bugs).  If the documentation does exist please point me to
>>> it but I did not find it on my own.
>>>
>>
>> If a single min() or max() aggregate function appears in a query, then any
>> other columns that are not contained within aggregate functions and that
>> are not elements of the GROUP BY will take values from one of the same rows
>> that satisfied the one min() or max() aggregate function.
>
> Thanks!  I expect that this will be useful for me for at least quick
> and dirty computations.
>
> I suggest that this statement be added to the docs if its not already there.

Also the other part of my question.  What were the improvements to the
csv import?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Richard Hipp]

On Fri, May 4, 2012 at 10:33 AM, Gabor Grothendieck  wrote:

> On Fri, May 4, 2012 at 10:20 AM, Richard Hipp  wrote:
> > On Fri, May 4, 2012 at 10:06 AM, Rob Richardson <
> rdrichard...@rad-con.com>wrote:
> >
> >> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
> >> Queries of the form: "SELECT max(x), y FROM table" returns the
> >> value of y on the same row that contains the maximum x value.
> >>
> >> Is that standard SQL behavior?  I'd have expected that to return one row
> >> for every row in the table.  To get the behavior described above, I'd
> use
> >> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
> >>
> >
> > It is definitely NOT standard behavior.  The standard behavior is
> > undefined.  Or (with many SQL engines) it will throw an error if you
> have a
> > term in the result set that is not part of an aggregate function or an
> > element of the GROUP BY clause.  But lots of newbies expect SQL to work
> as
> > described in the 3.7.11 release comments, and we used to get support
> > questions because it did not.  And so rather than continue to answer the
> > questions over and over, I figured it would be easier to tweak SQLite to
> > reliably do what newbies expect.  I never anticipated that this change
> > would be so controversial or confusing.
> >
> > All the existing, portable, documented ways to find the maximum element
> of
> > one column while simultaneously finding the other elements in the same
> row,
> > continue to work as they always have.  You are not required to use this
> new
> > approach.  In fact, if you want your SQL to be portable, you should
> > probably avoid it.  By adding this feature, we had hoped to help
> > application developers avoid a common SQL programming error.  That's all.
> > There is nothing profound going on here.
>
> Can't anyone answer the question directly?  I would still like to know
> precisely what works and what does not.  Its not possible to rely on
> general SQL documentation for this so I think its important to
> document it exactly.  Otherwise, we are left to examine the source
> code or use trial and error (and these methods only tell you how it
> works but not how its intended to work and they could be different if
> there are bugs).  If the documentation does exist please point me to
> it but I did not find it on my own.
>

If a single min() or max() aggregate function appears in a query, then any
other columns that are not contained within aggregate functions and that
are not elements of the GROUP BY will take values from one of the same rows
that satisfied the one min() or max() aggregate function.



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

Re: [sqlite] Details on New Features

[Gabor Grothendieck]

On Fri, May 4, 2012 at 10:20 AM, Richard Hipp  wrote:
> On Fri, May 4, 2012 at 10:06 AM, Rob Richardson 
> wrote:
>
>> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
>>         Queries of the form: "SELECT max(x), y FROM table" returns the
>> value of y on the same row that contains the maximum x value.
>>
>> Is that standard SQL behavior?  I'd have expected that to return one row
>> for every row in the table.  To get the behavior described above, I'd use
>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>>
>
> It is definitely NOT standard behavior.  The standard behavior is
> undefined.  Or (with many SQL engines) it will throw an error if you have a
> term in the result set that is not part of an aggregate function or an
> element of the GROUP BY clause.  But lots of newbies expect SQL to work as
> described in the 3.7.11 release comments, and we used to get support
> questions because it did not.  And so rather than continue to answer the
> questions over and over, I figured it would be easier to tweak SQLite to
> reliably do what newbies expect.  I never anticipated that this change
> would be so controversial or confusing.
>
> All the existing, portable, documented ways to find the maximum element of
> one column while simultaneously finding the other elements in the same row,
> continue to work as they always have.  You are not required to use this new
> approach.  In fact, if you want your SQL to be portable, you should
> probably avoid it.  By adding this feature, we had hoped to help
> application developers avoid a common SQL programming error.  That's all.
> There is nothing profound going on here.

Can't anyone answer the question directly?  I would still like to know
precisely what works and what does not.  Its not possible to rely on
general SQL documentation for this so I think its important to
document it exactly.  Otherwise, we are left to examine the source
code or use trial and error (and these methods only tell you how it
works but not how its intended to work and they could be different if
there are bugs).  If the documentation does exist please point me to
it but I did not find it on my own.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Richard Hipp]

On Fri, May 4, 2012 at 10:06 AM, Rob Richardson wrote:

> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
> Queries of the form: "SELECT max(x), y FROM table" returns the
> value of y on the same row that contains the maximum x value.
>
> Is that standard SQL behavior?  I'd have expected that to return one row
> for every row in the table.  To get the behavior described above, I'd use
> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)".
>

It is definitely NOT standard behavior.  The standard behavior is
undefined.  Or (with many SQL engines) it will throw an error if you have a
term in the result set that is not part of an aggregate function or an
element of the GROUP BY clause.  But lots of newbies expect SQL to work as
described in the 3.7.11 release comments, and we used to get support
questions because it did not.  And so rather than continue to answer the
questions over and over, I figured it would be easier to tweak SQLite to
reliably do what newbies expect.  I never anticipated that this change
would be so controversial or confusing.

All the existing, portable, documented ways to find the maximum element of
one column while simultaneously finding the other elements in the same row,
continue to work as they always have.  You are not required to use this new
approach.  In fact, if you want your SQL to be portable, you should
probably avoid it.  By adding this feature, we had hoped to help
application developers avoid a common SQL programming error.  That's all.
There is nothing profound going on here.



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

Re: [sqlite] Details on New Features

[Rob Richardson]

Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
Queries of the form: "SELECT max(x), y FROM table" returns the value of 
y on the same row that contains the maximum x value.

Is that standard SQL behavior?  I'd have expected that to return one row for 
every row in the table.  To get the behavior described above, I'd use "SELECT 
x, y FROM table WHERE x = (SELECT max(x) FROM table)".

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