Re: "Standard SQL" ?

> On 30 Jan 2020, at 21:12, Keith Medcalf <[email protected]> wrote:
> 
> On Thursday, 30 January, 2020 12:20, Simon Slavin <[email protected]> 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 <table> (<column list>) VALUES (<value list>);
> SELECT <column list> FROM <table list> [WHERE <condition>] [GROUP BY <column list> [HAVING <having list>]] [ORDER BY <ordering list>]
> DELETE FROM <table> [WHERE <condition>]
> UPDATE <table> SET <column> = <value>[, <column> = <value>]... [WHERE <condition>]
> 
>> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users