Thanks for reminding me about SQL “create view”. I’ve written DB2, Oracle, MySQL, SQLite, and each of them was painful, so I suppose my brain has just hidden away as much as possible of the pain.
SQL:Prolog :: COBOL : Python
These are helpful but not enough. I once tried to write some graph-traversal code using XSB tabling … it was great for queries like “is there a path” and “find shortest path” but not for “find path with the traversed nodes” (I forget the details, but David Warren also got stuck on it - the problem was that the tabling implementation didn’t have the ability to specify that some of the parameters didn’t need to be used). As soon as we’re out of Datalog territory and arbitrary terms can show in the query, it’s not obvious what is easy to do and what isn’t with tabling.
Part of “deeply wrong” is the way SQL mixes row results with set/bag results. In Prolog, I can use bagof (or setof) and member to go back and forth between these but SQL mixes them in weird ways (and also gets “DISTINCT” wrong).
There are also many of other things that are wrong in SQL, such as the poor semantics for NULL. For example sum(a+b) != sum(a)+sum(b):
sqlite> create table T(A,B);
sqlite> insert into T values (0,1);
sqlite> insert into T values (1,null);
sqlite> select sum(A)+sum(B) from T;
sqlite> select sum(A+B) from T;