tl;dr: I think one of the biggest causes of NoSQL is that while SQL is a powerful language because of the ideas behind it, it's often implemented in clumsy and archaic ways. A language that learns from SQL could make relational data better to manipulate for programmers.
I'll try to think of things similar to those that I have dealt with in real-world situations and how a better query language could have helped. I'd love discussion on what could be better here!
I'm not picky myself, but basing syntax off of PL/I is a 1970's IBM choice that probably wouldn't fly today. Due to popular demand, such a language would pick up C aesthetics syntactically, though perhaps with some ML or Prolog influence.
With better syntax I hope can come better parsers. I especially loathe MySQL's parser, which never actually tells you where problems lie or what it is, if it isn't some syntax absurdity like DELIMITER. (Better SQL parsers do exist in conventional implementations though - Oracle's is surprisingly good at reporting errors by telling you what it expects.)
The examples I write are just for show; not wed to anything or any demand what syntax must be. My influences are most likely from F# (ML family), Erlang (Prolog-esque), and Elixir (Erlang and Ruby like).
SQL's 4GL qualities where you describe how you want your data instead of looping over it by hand is SQL's most powerful weapon. This is pretty close to a lot of functional programming paradigms like lazy evaluation. Unfortunately, the standard library of most SQL dialects is somewhat anemic on this front; being optimized for 1980's procedural programs. This ends up reflected in most user SQL code, where they imitate the style that the language and standard library make easy, which involves a lot of dealing with mutable state and procedures over functions. Defaults matter.
While being a 4GL is a strength with how powerful compilers and optimizers are optimizing most code, it can be easy to make a mistake that makes a query more expensive, but planners can be cryptic unless you're already an SQL optimization expert. (Again, special mention to how bad MySQL's "explain"ing tools are for this.) While not strictly PLT related, it is something weak in current SQL implementations that computer scientists have learned a lot about.
One schema that illustrates how modern functional programming techniques could be applied here is this function that returns stack frame information. While this is very useful for DBAs-turned-system administrators in the heat of debugging, it is unfortunately clumsy, because effectivelly there's "groups" of columns that are effectively mutually exclusive, lots of nullables because of that, and string fields that are effectively enums.
Some of these are just poor schema design (perhaps not helped by the fact it must be returned in a single table); the stringy enums can be fixed with a foreign key constraint on a table that acts as an enum. Some are down to language expressiveness in implementations, though.
Using this idea, I try to come up with a better example that would make queries less verbose and error-prone:
// heavily omitting things for simplicity; i.e displacement or additional enum cases
// Each frame type, while similar, is not identical, and has different
// semantics or qualifications.
type MachineInterfaceInfo =
{
ActivationGroup: long;
ASP: long;
Library: string;
}
// For those that lack context here, IBM i supports multiple program models:
// - Java programs, which runtime provides the system some special insight
// - OPM programs, the old managed runtime program ABI
// - ILE programs, the new managed runtime program ABI
// - AIX programs, through syscall emulation
// - LIC, the IBM i kernel
// It can generate stack traces for all these kinds of programs; some programs
// may have a call stack containing a frame entry of each type.
type FrameType =
// Inherit fields from another record type.
| ILE { MachineInterfaceInfo | ServiceProgram: string; Module: string; }
| OPM { MachineInterfaceInfo | Program: string; }
| AIX { Bitness: enum(32 | 64); LibArchive: Option(string); Module: string, Syscall: bool; }
| Java { MethodType: enum(DirectExecution | Glue | Interp | JIT | MMI); ClassName: string; Signature: Option(string); }
table Frame =
{
ThreadID: long;
FrameType: FrameType;
Function: Option(string(;
}
function StackInfo(JobID: string) : Frame;
// An SQL-like select with pattern matching to filter.
select Function from StackInfo("1234/JOB/5678") where AIX { Bitness: 64 } = FrameType;
select Function from StackInfo("1234/JOB/5678") where MachineInterfaceInfo { Library: "QSYS" } = FrameType;
select Function from StackInfo("1234/JOB/5678") where AIX { LibArchive: "libc.a" } = FrameType;
select Function from StackInfo("1234/JOB/5678") where AIX { LibArchive: None } = FrameType;
// A function that prints information with a pattern match inside of it.
function FrameFullySpecifiedProgramName(frame : Frame) : string =
match frame.FrameInfo with
| OPM { Program: program } -> program
| ILE { ServiceProgram: srvpgm, Module: module } -> "#{srvpgm}/#{module}"
| AIX { LibArchive: None, Module: module } -> module
| AIX { LibArchive: lib, Module: module } -> "#{lib}(#{module})"
| Java { ClassName: class } -> class
// we must match all possible types, or discard with _
| _ -> "?"
// A function that uses pattern matching based overloads and destructuring.
function FrameJavaFunctionDef(frame : Frame { Java { Signature: None } = .FrameInfo }) : string =
"#{frame.Function}()"
function FrameJavaFunctionDef(frame : Frame { Java { Signature: signature } = .FrameInfo }) : string =
"#{frame.Function}(#{signature})"
// A call to this with a non-Java frame is an error, because no patterns could match.
The gigantic table full of mutually exclusive columns is annoying - and one way it expresses that is through having to scroll left and right constantly through data to find the chunk you want. Unfortunately, I don't have as good answers for this, even with how to visualize a discriminated union in a table while having things work like you expect.
Say I have tables "Software", "Version", and "Download", that each could have images, with a "Picture" table. Usually, you would use a many-to-many table for each kind of relation, so "SoftwarePicture", "VersionPicture", etc. This seems like pointless duplication, if instead we could have a many to many table that effectively has a discriminated union on foreign keys:
table SoftwarePictures =
{
// a foreign key is assumed to have the same type as what it relates to
PictureID: key relates to (Picture.PictureID);
ObjectID: key relates to (Software.SoftwareID | Version.VersionID | Download.DownloadID);
}
insert into SoftwarePictures (PictureID, ObjectID) values (0x1234, DownloadID { 0x1234 });