SQLite Forum

SQL compliance on Wikipedia
Login

SQL compliance on Wikipedia

(1) By anonymous on 2020-04-18 12:34:52 [link] [source]

Hi

Could you guys please take a look at the SQL compliance article on Wikipedia, and mark features supported by SQLite as supported.

Thanks

(2) By Simon Slavin (slavin) on 2020-04-18 15:44:30 in reply to 1 [link] [source]

I have a question: what is a "Positioned UPDATE statement" ? An example of one isn't sufficient. To know whether SQL has full support for that feature I need to know all the required syntaxes and variations. Without that I can only ever mark 'No' or "Partial', but never 'Yes'.

The standards referred to in that page were defined in SQL:2008. (As far as I can tell. The web page does not explain what it's talking about, which not good.)

The documentation for the current version of that standard is not freely available but may be purchased from the ISO as ISO/IEC 9075(1-4,9-11,13,14):2016. I can't be bothered to go through all the volumes right now but the volumes I checked are all around the US$200 mark, so ten parts would cost around US$2,000. And they're already scheduled for being replaced by the 2019 version of the language. I don't want to spend that much money on almost obsolete documentation just to add to a Wikipedia page.

This may be why so many of the cells for other databases read 'Unknown'. Measure against a standard which we can obtain for free and twenty eager contributors to this forum will happily supply the information. Measure against one which nobody can read without paying two thousand Swiss Francs, and you're not going to get many takers.

(3) By anonymous on 2020-04-18 16:48:22 in reply to 1 [link] [source]

It says "INTEGER and SMALLINT data types (including all spellings)". What are "all spellings"? If all of them include "INT" as a substring, then the answer is yes. In SQLite, the two data types listed (and other spellings supported) are all signed 64-bit integers (although you can store other data in such a column too if you do not use an explicit cast). The other data types listed in the E011 section are supported (with the names in the same row of that table being the same data type in SQLite, but again you can store other data in such columns too if you do not use an explicit cast).

In the E021 section, for SQLite the CHARACTER and CHARACTER VARYING types are the same type, but again I don't know what "including all spellings" includes. There is no function called CHARACTER_LENGTH or OCTET_LENGTH, although guessing by the name of the latter function, LENGTH(CAST(X AS BLOB)) probably does what you want; also, user defined SQL functions can be implemented in C (although not in SQL), so a user can add these functions if wanted. E021-10 says "Implicit casting among the fixed-length and variable-length character string types", but in SQLite they are the same type, so then what?

And then, the T321 section mentions stored procedures. SQLite doesn't have stored procedures, although you can use INSTEAD OF triggers for some of the same purposes (which is something I have done).

So, in other words, in many cases in the table it is confusing what is meant or otherwise what to put in that table.

(4) By Tim Streater (Clothears) on 2020-04-18 17:30:37 in reply to 3 [link] [source]

You've read:

https://www.sqlite.org/datatype3.html

have you?

(5) By J. King (jking) on 2020-04-18 19:59:50 in reply to 1 [link] [source]

This is a pretty strange page. It isn't an encyclopedic article describing SQL compliance as a concept, isn't named "comparison of SQL implementations" as you'd expect for such a table, and even then has no front-matter or descriptive text of any kind. It's also dangerously low on references, making any current claims in the table all but meaningless.

I'm not sure it's worthwhile filling this in even if you do have the SQL specification and SQLite documentation sitting in front of you.

(6.1) Originally by anonymous with edits by Richard Hipp (drh) on 2020-04-24 13:15:09 from 6.0 in reply to 2 [link] [source]

"Positioned UPDATE statement" refers to the feature defined as "E121-06" in the standard document.

If you do not have access to the SQL:2008 standard but you have access to an earlier version of the standard you can write `{{Yes|SQL:1999}}` or `{{Yes}}<ref group=note>Only supports SQL:1999.</ref>`

(7) By anonymous on 2020-04-24 09:15:33 in reply to 5 [link] [source]

Well it have to start somewhere. Nothing is built in one day. It is an collaborative effort so feel free to contribute.

You are right that the article is deficient, but that is room for improvement, so please feel free to improve the article.

The SQLite website does not seem to have any page documenting compliance, so it is difficult to reference something that does not exist.

(8) By anonymous on 2020-04-24 09:24:58 in reply to 3 [link] [source]

I don't know, I guess you would have to refer to the SQL standard document (if you have access to it). I would guess it means BIGINT and TINYINT, but I am not sure.

As for CHARACTER and CHARACTER VARYING and all it spellings, it may refer to CHAR and VARCHAR.

If there is no CHARACTER_LENGTH or OCTET_LENGTH function then it is not supported, even though the equivalent functionality exists using the non-standard LENGTH(CAST(X AS BLOB)). If a standard query fails to run, then it is not supported even if SQLite does have provide that functionality under another name.

If they are the same treated as the same type, then I would argue it is supported even if the underlying implementation treats them the same, that is an implementation detail but the functionality as defined in the specification is supported.

If it SQLite does not support stored procedures then it does not comply with T321 and should hence be marked as {{No}}. If you can use something else for the same purpose that is irrelevant as it is not part of the standard. This article documents standard compliance, it is not take into account non-standard functionality.

(9) By tom (younique) on 2020-04-24 09:54:41 in reply to 2 [source]

what is a "Positioned UPDATE statement" ?

see e.g. https://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqlj26498.html

(10) By Richard Hipp (drh) on 2020-04-24 12:43:07 in reply to 7 [link] [source]

The SQLite website does not seem to have any page documenting compliance

Yes, because SQLite is more interested in solving real-world problems than complying with proprietary standards that nobody reads.

(11) By anonymous on 2020-04-24 16:29:53 in reply to 10 [link] [source]

Yes, because SQLite is more interested in solving real-world problems than complying with proprietary standards that nobody reads.

But it is easier to use SQLite to solve read-world problems if the knowledge a developer have attained using another SQL implementation is transferable and works with SQLite too.

Interoperability is a boon to productivity!

(12) By Richard Hipp (drh) on 2020-04-24 16:55:00 in reply to 11 [link] [source]

Yes, but interoperability ≠ compliance with a proprietary standard that cost $2000 USD to purchase and read.

I work at making SQLite interoperable with PostgreSQL (mostly) but also MySQL and SQL Server. Witness the fact that SQLite mimics some of the quirks of those other systems, such as MySQL back-tics (ex: `name`) and SQL Server's use of square brackets (ex: [name]). For RCEs, differing systems have different requirements for the use or non-use of the RECURSIVE keyword. SQLite works the same regardless, so that it can interoperate with everybody. Different systems place different restrictions on the position and content of computed columns. SQLite has no restrictions at all, so that it interoperates with everyone. Other engines are very particular about the datatype names they will accept. SQLite accepts any datatype name you want, so that it interoperates with everybody. And so forth.

If you have specific suggestions on how SQLite could interoperate better with other common systems, that would be great.

The previous paragraphs demonstrate that interoperability does not imply standard compliance. Note further that standards compliance does not imply interoperability. The brief glimpses I have had of the SQL standards leave lots of details either ambiguous, or implementation defined. It is not difficult to find two standards-compliant SQL database engines that will not interoperate.

(13) By Tony Papadimitriou (tonyp) on 2020-04-24 17:55:35 in reply to 12 [link] [source]

SQLite3 currently being at the top of the SQL engines (at least in terms of people using it either directly or indirectly), it IS by definition the standard.

There are many niceties that SQLite3 offers that make it more usable and practical for everyday use.

From zero setup to single file database and engine, ... to virtually no max column width, and many more...

So, maybe the burden should be on others to try to be more compatible with SQLite3.

(14) By Tim Streater (Clothears) on 2020-04-24 18:25:21 in reply to 13 [link] [source]

SQLite is serving a different market to the others. That it is almost certainly mostly compatible with the other, server-based, SQL offerings is good enough. And I can't imagine doing a feature comparison by looking at some certain-to-be-outofdate page on Wikipedia.

(15) By Simon Slavin (slavin) on 2020-04-25 01:03:18 in reply to 9 [link] [source]

This page neither defines E121-06, nor answers the question.

(16) By Simon Slavin (slavin) on 2020-04-25 01:17:53 in reply to 7 [link] [source]

Okay. You start. Turn each of the references in that page into a clickable link to its definition. For instance, where the table says "E121-06", have what E121-06 means defined on Wikipedia. (Of course, you will have to rephrase the text from the ISO documents, since the ISO considers its text as copyright and protects it jealously. Come to think of it it might even sue you for posting the paraphrase.)

Once we have the definitions we can tell you whether SQLite compatibility with that is 'No', 'Partial' or 'Yes'. We're quite happy to share our knowledge of SQLite once you've shared your knowledge of what the terms on that page means.

(17) By Simon Slavin (slavin) on 2020-04-25 01:44:02 in reply to 2 [link] [source]

I was too busy to figure it out for my original post but for those interested, prices for ISO/IEC 9075:2016 are as follows:

Volume Price in CHF
1 178
2 198
3 198
4 198
9 198
10 198
11 198
13 198
14 198
Total 1.762

Total for volumes 1-4,9-11,13,14 is CHF1.762 which right now is US$1,810, €1,674, £1,462. Add postage, packing and tax, whatever that is.

(18) By tom (younique) on 2020-04-25 06:41:58 in reply to 15 [link] [source]

from the page cited:

> The second syntactical form, called a positioned update, updates one or more columns on the current row of an open, updatable cursor. If columns were specified in the FOR UPDATE clause of the SELECT statement used to generate the cursor, only those columns can be updated. If no columns were specified or the select statement did not include a FOR UPDATE clause, all columns may be updated.

There ain't no mystery in that. E121-06 is just what is called a "feature ID":

> Positioned UPDATE statement — Subclause 14.9, ‘‘<update statement: positioned>’’: Without support of either Feature E153, ‘‘Updateable tables with subqueries’’ or Feature F221, ‘‘Explicit defaults’’

(from https://web.cecs.pdx.edu/~len/sql1999.pdf; I know it's 1999, but should be enough for answering the queastion)

(19) By TripeHound on 2020-04-25 09:31:52 in reply to 18 [link] [source]

But the "cited page" is an Oracle document, describing their implementation of the UPDATE statement, which might not be identical to the E121-06 standard (they may have missed bits out; they may have added extra features). Thus answering "Is SQLite compliant?" based on that page may or may not be accurate.

(20) By anonymous on 2020-06-04 18:13:27 in reply to 16 [link] [source]

For the willing:

These are the links to the excerpts/descriptions of SQL Standards supported in MS T-SQL as "Mandatory Features" (36 altogether). Published by MS as OpenSpecs. There are optional features listed too, but posting just these for brevity.

I'm not sure how to validate SQLite against these specs. There may be some ways to assess compliance.

std link
E021 E021, Character string types
E021-01 E021-01, CHARACTER data type (including all its spellings)
E021-02 E021-02, CHARACTER VARYING data type (including all its spellings)
E021-04 E021-04, CHARACTER_LENGTH function
E021-05 E021-05, OCTET_LENGTH function
E021-06 E021-06, SUBSTRING function
E021-07 E021-07, Character concatenation
E021-09 E021-09, TRIM function
E021-11 E021-11, POSITION function
E051 E051, Basic query specification
E051-09 E051-09, Rename columns in the FROM clause
E081 E081, Basic Privileges
E081-09 E081-09, USAGE privilege
E121 E121, Basic cursor support
E121-17 E121-17, WITH HOLD cursors
E141 E141, Basic integrity constraints
E141-07 E141-07, Column defaults
E152 E152, Basic SET TRANSACTION statement
E152-02 E152-02, SET TRANSACTION statement: READ ONLY and READ WRITE clauses
E171 E171, SQLSTATE support
E182 E182, Module language
F031 F031, Basic schema manipulation
F031-13 F031-13, DROP TABLE statement: RESTRICT clause
F031-16 F031-16, DROP VIEW statement: RESTRICT clause
F031-19 F031-19, REVOKE statement: RESTRICT clause
F051 F051, Basic date and time
F051-03 F051-03, TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6
F051-06 F051-06, CURRENT_DATE
F051-07 F051-07, LOCALTIME
F051-08 F051-08, LOCALTIMESTAMP
F812 F812, Basic flagging
S011 S011, Distinct data types
T321 T321, Basic SQL-invoked routines
T321-01 T321-01, User-defined functions with no overloading
T321-02 T321-02, User-defined stored procedures with no overloading
T321-04 T321-04, CALL statement

(21) By Simon Slavin (slavin) on 2020-06-05 15:17:28 in reply to 20 [link] [source]

That's a lot of work you put in there. You went quite a way to what we asked so I'm going to put some work into replying. I'm going to take the first link.

Subclause 6.1,"<data type>"
 CHARACTER [ <left paren> <character length> <right paren> ]
 | CHAR [ <left paren> <character length> <right paren> ]
 | CHARACTER VARYING <left paren> <character length> <right paren>
 | CHAR VARYING <left paren> <character length> <right paren>
 | VARCHAR <left paren> <character length> <right paren>
 | <character large object type>

This gives the syntaxes required for compliance to E021, Character string types. We could say whether SQLite complies with these. (Not full compliance. Possible partial compliance.) However it does not give enough details to judge even that.

To judge even partial compliance we'd have to know interpretation of each of these syntaxes. For instance does VARCHAR have to do anything different to CHAR ? I happen to know it does, but the extracts you've found don't say either way. They state the syntaxes supported, but not what the implementation should do for them. The meanings of each of these syntaxes are in the standards document we mentioned previously, but they are not mentioned in the extracts you've found in the MS SQL Server documentation.

So we're down to "no". We couldn't say even "partial" to any of these, because even if SQLite has the right syntax, we don't know whether it does the right thing on seeing that syntax, and doing the right thing is part of the standard you're asking us about.

To check this I looked at another random entry and picked F812, Basic flagging. I would have to say "no". There may be a way to use existing SQLite facilities to do this (compilation options ?) but it's not documented that way.

Another one: F051-07, LOCALTIME. This mentions many 'words'. I'm guessing SQLite complies with at least one of them. But that's just one. SQLite doesn't have CURRENT_CATALOG because it doesn't have catalogues. Is that allowed by the standard or not ? I can't tell from the information on that page because it lists only syntax. Even so, I feel like it would be cheating to say 'partial compliance' for this, since SQLite does not even partially comply with nine of the ten 'words'. But I could feel justified because the section is called LOCALTIME and that's the one I think it partially complies with.

So we could now say "no' to some of these. I think it would be very one-sided to supply a column which said only "no" or "unknown". But others here might feel differently.