Here are routines that can handle date arithmetic on BC dates, Julian day functions, and simulation of Oracle's support of old-style-calendar dates -- including simulation of an Oracle bug. So the routines are good for extending the range of useable dates, compact storage, and import/export between DBMSs that have different rules.

If you need to refresh your understanding of dates, read our old-but-lovely article first: The Oracle Calendar.

I wrote the main routines with standard SQL so they should run on
any DBMS that supports the standard, but tested only with
MySQL and MariaDB. (UPDATE on 2019-02-04: corrections were needed with HSQLDB, see the comments.)

ocelot_date_to_julianday

  Return number of days since 4713-01-01, given yyyy-mm-dd [BC] date

ocelot_date_validate

  Return okay or error, given yyyy-mm-dd BC|AD date …

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

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 …

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. …

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 …

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 …

MySQL's support of the BINARY and VARBINARY data type is good, and the BINARY and VARBINARY data types are good things. And now the details. What applies here for MySQL applies for MariaDB as well.

Who supports VARBINARY

There's an SQL:2008 standard optional feature T021 "BINARY and VARBINARY data types". Our book has a bigger description, but here is one that is more up to date:

Firebird is an open-source DBMS with a long history and good SQL support. Although I measured Firebird's importance as smaller than MySQL's or MariaDB's, it exists, and might grow a bit when Firebird becomes the default LibreOffice DBMS.

I decided to compare the current versions of MySQL 5.6 and Firebird SQL 2.5. I only looked at features that end users can see, without benchmarking. Preparing for the comparison was easy. I looked at the instructions for downloading Firebird with Ubuntu and within 15 …

"It is better to keep silence and be thought a fool, than to say 'tuple' and remove all doubt."

But recently people have been using the word "tuple" more frequently. Doubtless all those people know that in relational databases a tuple (or a tuple value) is a formal term for a row of a table. It's possible to know a bit more than that.

Pronounced Tyoople, Toople, or Tuhple?

The Oxford Dictionaries site says Tyoople. Other dictionaries are neutral about the terms from which Tuple was derived (sextuple, octuple, etc.), for example Merriam-Webster says they usually end in Toople but Tuhple is an accepted alternate, and the Oxford Canadian Dictionary says …

Here are examples comparing MySQL 5.6 against PostgreSQL 9.3 Core Distribution, where MySQL seems to comply with "standard SQL" more closely than PostgreSQL does. The examples are also true for MariaDB 10.0 so whenever I say "MySQL" I mean "MySQL and/or MariaDB". When I say "more closely" I do not mean that MySQL is completely compliant, or that PostgreSQL is completely non-compliant.

Identifiers

Example:

CREATE TABLE          ŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽ (s1 INT); /* 32-character name */
SELECT COUNT(*) FROM information_schema.tables
  WHERE table_name = 'ŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽŽ';
SELECT COUNT(*) FROM information_schema.tables
  WHERE table_name LIKE LOWER('Ž%');

Result:
PostgreSQL says count(*) is 0. MySQL says it's 1.
Reason:
(1) PostgreSQL maximum identifier length is 63 bytes; MySQL maximum identifier length is 64 …