Re: [sqlite] Details on New Features
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
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
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
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
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
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
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
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
-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
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
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
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
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
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
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
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
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
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
[sqlite] Details on New Features
In this link: http://sqlite.org/releaselog/3_7_11.html it refers to these new features: 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. Improvements to the handling of CSV inputs in the command-line shell Is there documentation somewhere that defines exactly what these mean? Does the max(x) apply to min(x) too? does the max have to precede the y? Can there be multiple y's? What precisely are the "improvements" in handling of CSV inputs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users