Showing entries 1 to 10 of 15
5 Older Entries »
Displaying posts with tag: Standard SQL (reset)
The Tarantool SQL Alpha

Tarantool, a Lua application server plus NoSQL DBMS, is now an SQL DBMS too, in alpha version 1.8. I was interested in how the combination "Lua + NoSQL + SQL" works. Disclaimer: I do paid work related to Tarantool but it has nothing to do with this blog.

First let's verify that it's really SQL. The illustrations are all unretouched screenshots from ocelotgui for Windows, connected to a Tarantool 1.8 server on Linux, which I built from a source download on github.

Example of SELECT

Yes, the "history" below the query window shows successful CREATE and INSERT statements, the "result set" at the bottom shows a successful SELECT statement's output. A close look at the clauses shows that there's support for constraints, foreign keys, triggers, ... and so on. In all, it's a reasonable subset of the SQL standard, pretty well …

[Read more]
The SQL Standard is SQL:2016

Now the words "SQL Standard" mean ISO/IEC 9075 Information technology -- Database languages -- SQL ... 2016" or more briefly SQL:2016. I am no longer a member of the committee, I don't have the latest copy, but I believe these are the notable changes.

The LIST_AGG function

The example I've seen (credit to Markus Winand) is

SELECT grp, LIST_AGG(val, ',') WITHIN GROUP (ORDER BY val) FROM t ORDER BY grp;

and the result is supposed to be: take each "val" value and concatenate it with the previous one, separating by "," as specified.
Notice a similarity to GROUP_CONCAT() in MySQL/MariaDB

[Read more]
Sequences

What sequences are, who supports them, and why they're better than GUIDs or auto-increment columns except when they aren't.

What sequences are

When I say "sequence" I actually mean "sequence generator" because that's what SQL's CREATE SEQUENCE statement actually creates. So a sequence is a black box that produces an integer with a guarantee that this integer is greater than the previous integer that it produced, and less than the next integer that it will produce. Thus if I invoke it five times I might get 2, 4, 5, 6, 7. In poker that wouldn't be considered a sequence because there's a gap between 2 and 4. We can make sequences with few gaps or no gaps, but they're expensive.

Sequences are good for generating unique keys, although they're not the only tool for that.

How they work

This is the minimal scenario with a good DBMS.

An administrator says "create sequence x", with most things default: …

[Read more]
Pronouncing Database Terms

It is the business of educated people to speak so that no-one may be able to tell in what county their childhood was passed. -- A. Burrell, A Handbook for Teachers in Public Elementary School, 1891

The terms that reveal where a person (mis)spent a DBMS-related childhood are "char", "data", "GIF", "gigabyte", "GUI", "JSON", "query", "schema", "tuple", "_", "`", and "«".

CHAR

(1) Like "Care" because it's short for "Character" (so hard C and most folks say "Character" that way)?
(2) Like "Car" because it's short for "Character" (so hard C and a few folks in the British Isles say it that way and perhaps all other English words ending in consonant + "ar" are pronounced that way)?
(3) Like "Char" (the English word for a type of trout)?
C/C++ programmers say (3), for example Bjarne Stroustrup of C++ fame says that's …

[Read more]
SQLite and Standard SQL

I'm going to need to use SQLite syntax for a project that I'm involved with, and predictably I wonder: how standard is it? The SQLite folks themselves make modest claims to support most of the features with a special focus on SQL-92, but (a) I like to do my own counting (b) there's no official standard named SQL-92 because it was superseded 17 years ago.

By ignoring SQL-92 claims I eschew use of the NIST test suite. I'll be far less strict and more arbitrary: I'll go through SQL:2011's "Feature taxonomy and definition for mandatory features". For each feature in that list, I'll come up with a simple example SQL statement. If SQLite appears to handle the example, I'll mark it "Okay", else I'll mark it "Fail". I'm hoping that arbitrariness equals objectivity, because the unfair pluses should balance the unfair minuses.

Skip to the end of this blog post if …

[Read more]
Comments in SQL Statements

First I will say what the comment syntax is for various vendors' dialects. Then I will get specific about some matters that specifically affect MySQL or MariaDB.

Syntax Table

Standard YES YES NO YES NO
Oracle 12c YES YES NO NO YES
DB2 YES YES NO
[Read more]
SQL qualified names

Bewilderedly behold this SQL statement:
SELECT * FROM a.b.c;
Nobody can know the meaning of "a.b.c" without knowing the terminology, the context, and the varying intents of various DBMS implementors.

The terminology

It's pretty clear that a.b.c is a name, that is, a unique reference to an SQL object, which in this case is a table.

The name happens to be qualified -- it has three parts separated by periods. A generalist would say that each part is a container, with the first part referring to the outermost container a, which contains b, which contains c. Analogies would be: a directory with a subdirectory and a sub-subdirectory, or an element within a struct within a struct.

That is true but incomplete. Now we fight about what kind of container each
identifier refers to, and what is the maximum number of levels.

What kind of container

The standard suggests that the levels …

[Read more]
Standard SQL/JSON and MySQL 5.7 JSON

Support for storing and querying JSON within SQL is progressing for the ANSI/ISO SQL Standard, and for MySQL 5.7. I'll look at what's new, and do some comparisons.

The big picture

The standard document says

The SQL/JSON path language is a query language used by certain SQL operators (JSON_VALUE, JSON_QUERY, JSON_TABLE, and JSON_EXISTS, collectively known as the SQL/JSON query operators) to query JSON text.The SQL/JSON path language is not, strictly speaking, SQL, though it is embedded in these operators within SQL. Lexically and syntactically, the SQL/JSON path language adopts many features of ECMAScript, though it is neither a subset nor a superset of ECMAScript.The semantics of the SQL/JSON path language are primarily SQL semantics.

Here is a chart that shows the JSON-related data types and functions in the standard, and whether a particular DBMS has something with the same name and a similar functionality. …

[Read more]
What is a data type?

I'd suppose that these statements are generally accepted:

A data type is a set of values.

So any value that belongs to that set is said to "have" or "belong to" that data type.

And any column which is defined as "of" that data type is going to contain values which have that data type, only.

For example a column C may be defined as of type INTEGER and will only contain values which belong to the set of integers.

And now for the exceptions, details, caveats, and errors.

What is synonymous with data type?

There are no synonyms; no other term should ever be used.

Not datatype. Oracle's manual has "datatype" but IBM's doesn't and …

[Read more]
Temporary tables, standard SQL

The PostgreSQL manual says:

"The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases."

The first sentence is false. The second sentence could be rephrased as "MySQL copied us", although nobody else did, as far as I know.

The standard's definition is widely followed

I base this claim on the documentation of DB2, Oracle Rdb, Oracle 12c, and …

[Read more]
Showing entries 1 to 10 of 15
5 Older Entries »