Squinglish: SQL’s Inconsistent Calling Conventions

One of the main motivations for writing delightql was to provide a more consistent syntax for querying a database.

There have been many criticisms raised about SQL’s syntax. Classically, the order of relational operations is implicit and opaque and there is no useful abstraction for reusability. These are both true.

But my particular bugbear is the inconsistent calling convention for functions and predicates.

A calling convention is nothing more than the syntax for invoking a subroutine/function/method/whatever. In most programming languages the consistency is so built-into the language that you won’t realize what you have ’till it’s gone.

To get to the point, consider these valid SQL calls:

select overlay('w3333333rce' placing 'resou' from 3);
select substring('w3resource' from 4 for 5);
select trim(trailing 'st' from 'tetew3resourcestst');
select extract(CENTURY FROM TIMESTAMP '2016-12-31 13:30:15'); -- postgres
select extract(DAY FROM INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second' );

I can only think that the heritage of being a 4-GL caused the introduction of these functions to double down on inconsistent grammar in search for something that reads like a sentence. That has always been SQL’s goal – one of the original of many many waves where a language/technology claims that you can disintermediate the programmer and just have regular people coding. Look, it reads like English! they always claimed.

These functions actually look something like Smalltalk. In Smalltalk, sending a message (i.e. calling a “method”) interleaves the parameters with keyword-like syntax:

42 between: 41 and: 43

You might think that I would have the same problem with Smalltalk as I have with these SQL mini-DSLs. But I don’t, for the simple reason that Smalltalk is consistent – it has message sending rules for unary, binary, and keywords.

In Smalltalk, the choice of the message indicators between: and and: is a choice made by the programmer and eminently discoverable. This same style happens in those languages that allow keywords during invocation like Python, or Ruby. They are discoverable in the method signature.

When I look at these SQL mini-DSLs, I have no idea which keywords are available for which function, and why. I cannot posit a unifying grammar to these constructs and would be surprised if there was even one.

This is inconsistent.

SQL has two worlds: the regular function calling convention: upper(someString) and these oddities – some of which are even part of the SQL standard. But the standard does not require that such an English-function¹ also have an alternate sane calling convention:

substring(x from 4 for 5)
pg_catalog.substring(x, 4, 5)

While Postgres might have this alternate form, there is no guarantee that it exist.

This same case-by-case basis for grammar exists in the predicate world where LIKE and BETWEEN are so entrenched in our brains that we forget that we could do this instead²:

select * from fruit_time where like('bana%',fruit_flies) and like('arrow%',time_flies);

which could induce us to ask why we can’t have reusable predicates.

One thing I’m not lumping into this critique is the squinglish of analytic functions:

SELECT c, a, b, group_concat(b, '.') OVER (
  PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY a;

For as voluminous this syntax is, it is still consistent.

As SQL moves forward, consider this post a datum for one person asking for one ergonomic fix to the language: if we continue with squinglish constructs at least mandate an alternate consistent spelling.