SQL distinction between tables and collection-valued columns

[DavidPratten]

from README.md

What exactly is the SQL "distinction between tables and collection-valued columns"?

Relational expressions are an experiment bringing the features of query languages such as SQL into a functional language. We believe that a little syntactic sugar, backed by a relational query planner, makes ML into a powerful and convenient tool for querying large data sets. Conversely, we want to see how SQL would look if it supported lambdas, function-values, polymorphism, pattern-matching, and removed the syntactic distinction between tables and collection-valued columns.

David

[julianhyde]

In SQL, table and column expressions are in different namespaces. Expressions in the FROM clause see table expressions (e.g. catalog.schema.table) whereas expressions in the SELECT clause see column expressions (e.g. tableAlias.column.nestedColumn).

This means that there are essentially two languages in SQL (three if you note that the resolution rules are different in an aggregate query). The type system is not unified (e.g. a table does not have a datatype of 'multiset of record' or 'array of record', in fact it doesn't really have a datatype). There are operators and conventions to map between the two languages, for example:

• When I write 'FROM emps' in the table-language this implicitly makes an 'emps' variable available in the column-language.
• The sub-query operators (IN, EXISTS, and the scalar sub-query operator ( ... )) map from table expressions to column expressions.
• The TABLE operator allows me to call a user-defined table function - which, as a function, is a column expression - and convert it to a table expression.
• When I want to union two columns whose values are multisets, I can't use the UNION operator, because that's for tables. So I instead use the MULTISET UNION operator. Or I convert the columns to tables, use UNION, and convert them back to multisets using the MULTISET query constructor. (Similarly ARRAY, which is the other datatype for collection-valued columns.)
• In some dialects, CROSS APPLY allows me to call a table function without the TABLE operator. If there is an object in the table namespace with the same name as a function, heaven knows which gets chosen.

There are plenty more examples. SQL's two-language approach is rather shocking from the perspective of language design, and in my experience users struggle with it.