Re: [sqlite] "Standard SQL" ?
On Sun, 2 Feb 2020 10:05:11 +0100 Markus Winand wrote: > When you say ?many standards? do you mean the different releases > those standards have? Yes. > IMHO, there is only one SQL standard, namely ISO/IEC 9075. The > current and technically only valid version is that of 2016 (even > though an extension was added in 2019). That's a defensible proposition. And that's one way to measure an implementation. I think of standards more as accreting. The longer a feature has been standardiized, the more succeeding versions of the standard include it, the "more standard" it is. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Standard SQL" ?
> On 1 Feb 2020, at 23:31, James K. Lowden wrote: > > Similarly, last I checked, no SQL standard supported LIMIT for SELECT. Just FYI: The the functionality of LIMIT was added to the SQL standard ISO/IEC 9075 with the 2008 update in form of FETCH FIRST … ROWS ONLY This clause also support some variants like FETCH FIRST 10 PERCENT ONLY and also a WITH TIES modifier. All of that is still there in the current version of the SQL standard (from 2016). I would say LIMIT is widely supported, but it is not standard SQL, maybe common SQL. > > On the positive side, some parts of SQL haven't changed since the Late > Bronze Age. "SELECT *" still means all columns; "FROM" still takes a > table argument, whether a tablename, view, or expression. "WHERE" > operates on values "before" aggregation; "HAVING" on values "after" > aggregation. Any implementation that operates any other way does not > implement standard SQL. > > SQL is hardly unique in this regard. We also refer to "the" C standard > library, to "Posix", and to "the" C or C++ standard. Like SQL, there > are many such and (also like SQL) some parts are unchanged since the > beginning while, just as usefully, some that weren't part of the > first standard haven't changed since they were introduced. When you say “many standards” do you mean the different releases those standards have? IMHO, there is only one SQL standard, namely ISO/IEC 9075. The current and technically only valid version is that of 2016 (even though an extension was added in 2019). -markus ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Standard SQL" ?
On Thu, 30 Jan 2020 19:19:53 + Simon Slavin wrote: > Have any of you been using this term for a meaning other than "Fully > conforming to SQL:2019 (or whatever version you think current) ? Do > you have documentation somewhere ? Or are my suspicions correct and > there's no such thing ? I often refer to "the SQL standard" as if there were only one. For many purposes, there might as well be just one. For example, some SQL DBMSs support "UPDATE FROM" to update one table from another. If we can agree that's nonstandard SQL, then surely it's also "not standard SQL" and "is not defined by the SQL standard". Similarly, last I checked, no SQL standard supported LIMIT for SELECT. On the positive side, some parts of SQL haven't changed since the Late Bronze Age. "SELECT *" still means all columns; "FROM" still takes a table argument, whether a tablename, view, or expression. "WHERE" operates on values "before" aggregation; "HAVING" on values "after" aggregation. Any implementation that operates any other way does not implement standard SQL. SQL is hardly unique in this regard. We also refer to "the" C standard library, to "Posix", and to "the" C or C++ standard. Like SQL, there are many such and (also like SQL) some parts are unchanged since the beginning while, just as usefully, some that weren't part of the first standard haven't changed since they were introduced. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Standard SQL" ?
> On 30 Jan 2020, at 21:12, Keith Medcalf wrote: > > On Thursday, 30 January, 2020 12:20, Simon Slavin > wrote: > >> I would appreciate your help. Reading a technical article today, I came >> across a casual reference to "Standard SQL" as if it was a well-known >> thing. This worried me since I've never heard the term and I'm meant to >> know about such things. I’m an author using that term. What I personally mean with “Standard SQL” is the language described by ISO/IEC 9075. (Sorry, I’m saying ISO/IEC 9075 a lot in my response to avoid the term “standard” and the related confusion). > > I would say that "Standard SQL" is that variant of SQL is that "minimal > common subset" dialect of SQL that is understood by every single > implementation claiming to be "SQL compliant" that has ever existed until the > present. In other words, it is the dialect of SQL that will be understood by > *any* software claiming to be "SQL compliant". I think the word for what you describe is “Core SQL”. I wrote about it here: https://modern-sql.com/standard/levels > >> It doesn't seem to refer to the official standard for SQL, which is huge >> and contains a plethora of features implemented once or never. The >> author seemed to think it was a sort of 'core SQL' – features identically >> implemented by all, or most, of the well-known SQL engines. Google, for example, is also using the term “Standard SQL” for products like BigQuery. Although Googles “Standard SQL” is more close to ISO/IEC 9075 that the other “SQL” dialects they offer, they are sill not conforming to ISO/IEC 9075 in pretty basic ways. > > There is a core SQL. It is not defined. It is defined in ISO/IEC 9075. There are tables called (quoting): “Feature taxonomy and definition for mandatory features”, contains a taxonomy of the features of SQL language in Core SQL that are specified in this part of ISO/IEC 9075. There is an explicit list of mandatory features for Core SQL. And there are many optional features. > Most SQL (particularly DDL) is "implementation specific". About the only > things that comprise "standard SQL" are the DML constructs: ISO/IEC 9075 also defines DDL. Generally ISO/IEC 9075 only describes semantics, but no implementation details. E.g. CREATE TABLE is defined, CREATE INDEX not. ISO/IEC 9075 allows language extensions as long as they use a syntax **not** described in ISO/IEC 9075. > > INSERT INTO () VALUES (); > SELECT FROM [WHERE ] [GROUP BY list> [HAVING ]] [ORDER BY ] > DELETE FROM [WHERE ] > UPDATE SET = [, = ]... [WHERE > ] > >> The one possibility I can think of is SQL:1999. This is the first >> version which has features marked as 'mandatory' or 'optional'. A full >> implementation of all mandatory features could, I suppose, be called >> "Standard SQL", but I've never heard of that term being used for that. > > This is very Johnny-lately. I think the first standard was SQL-85 but even > that was chock full of vendor implementation specifics and light on being > "standard". Subsequent versions of the SQL Standard committee output simply > became more a practice in documenting vendor implementation specifics rather > than defining a standard. Most standards suffer from this problem. > >> Have any of you been using this term for a meaning other than "Fully >> conforming to SQL:2019 (or whatever version you think current) ? Do you >> have documentation somewhere ? Or are my suspicions correct and there's >> no such thing ? > > Yes. "standard SQL" is that subset of SQL that is understood by every > implemetation ever without making changes. Well, that’s now how I’m using this term. Not does Google use it in this way. -markus ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Standard SQL" ?
On Thursday, 30 January, 2020 12:20, Simon Slavin wrote: >I would appreciate your help. Reading a technical article today, I came >across a casual reference to "Standard SQL" as if it was a well-known >thing. This worried me since I've never heard the term and I'm meant to >know about such things. I would say that "Standard SQL" is that variant of SQL is that "minimal common subset" dialect of SQL that is understood by every single implementation claiming to be "SQL compliant" that has ever existed until the present. In other words, it is the dialect of SQL that will be understood by *any* software claiming to be "SQL compliant". >It doesn't seem to refer to the official standard for SQL, which is huge >and contains a plethora of features implemented once or never. The >author seemed to think it was a sort of 'core SQL' – features identically >implemented by all, or most, of the well-known SQL engines. There is a core SQL. It is not defined. Most SQL (particularly DDL) is "implementation specific". About the only things that comprise "standard SQL" are the DML constructs: INSERT INTO () VALUES (); SELECT FROM [WHERE ] [GROUP BY [HAVING ]] [ORDER BY ] DELETE FROM [WHERE ] UPDATE SET = [, = ]... [WHERE ] >The one possibility I can think of is SQL:1999. This is the first >version which has features marked as 'mandatory' or 'optional'. A full >implementation of all mandatory features could, I suppose, be called >"Standard SQL", but I've never heard of that term being used for that. This is very Johnny-lately. I think the first standard was SQL-85 but even that was chock full of vendor implementation specifics and light on being "standard". Subsequent versions of the SQL Standard committee output simply became more a practice in documenting vendor implementation specifics rather than defining a standard. Most standards suffer from this problem. >Have any of you been using this term for a meaning other than "Fully >conforming to SQL:2019 (or whatever version you think current) ? Do you >have documentation somewhere ? Or are my suspicions correct and there's >no such thing ? Yes. "standard SQL" is that subset of SQL that is understood by every implemetation ever without making changes. It is a very small subset of the language. Any "standard SQL" statement will operate the same way on every "SQL compliant" bit of software that ever existed or will exist in the future. No. It comes from experience using more than one SQL language implementation. No. THere is most assuredly a "standard SQL" that is understood by every implemetation of SQL that ever existed or even will exist. That it is not documented is entirely beside the point. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Standard SQL" ?
A few resources I found from https://www.google.com/search?q=sql+standard : - https://en.wikipedia.org/wiki/SQL - https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref - https://dev.to/0xcrypto/who-owns-the-sql-standard-76m - https://modern-sql.com/standard - https://docs.oracle.com/cd/B28359_01/server.111/b28286/intro002.htm It seems to me like the last ANSI standard was published in 2016. It sound to me like implementors would generally implement a ``common SQL subset'' or ``common subset of the ANSI SQL standard''. My (US) $0.02 worth. On Thu, Jan 30, 2020 at 2:20 PM Simon Slavin wrote: > I would appreciate your help. Reading a technical article today, I came > across a casual reference to "Standard SQL" as if it was a well-known > thing. This worried me since I've never heard the term and I'm meant to > know about such things. > > It doesn't seem to refer to the official standard for SQL, which is huge > and contains a plethora of features implemented once or never. The author > seemed to think it was a sort of 'core SQL' – features identically > implemented by all, or most, of the well-known SQL engines. > > The one possibility I can think of is SQL:1999. This is the first version > which has features marked as 'mandatory' or 'optional'. A full > implementation of all mandatory features could, I suppose, be called > "Standard SQL", but I've never heard of that term being used for that. > > Have any of you been using this term for a meaning other than "Fully > conforming to SQL:2019 (or whatever version you think current) ? Do you > have documentation somewhere ? Or are my suspicions correct and there's no > such thing ? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Standard SQL" ?
I would appreciate your help. Reading a technical article today, I came across a casual reference to "Standard SQL" as if it was a well-known thing. This worried me since I've never heard the term and I'm meant to know about such things. It doesn't seem to refer to the official standard for SQL, which is huge and contains a plethora of features implemented once or never. The author seemed to think it was a sort of 'core SQL' – features identically implemented by all, or most, of the well-known SQL engines. The one possibility I can think of is SQL:1999. This is the first version which has features marked as 'mandatory' or 'optional'. A full implementation of all mandatory features could, I suppose, be called "Standard SQL", but I've never heard of that term being used for that. Have any of you been using this term for a meaning other than "Fully conforming to SQL:2019 (or whatever version you think current) ? Do you have documentation somewhere ? Or are my suspicions correct and there's no such thing ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
