# In type systems, is there a name for SQL's way of cutting and combining record types into new types?

I'd like to have this feature in my application programming language (which these days, is Scala), but when I went to learn more about it on the internets, I realized I don't know the name of it. I'm talking about the ability to do this (in Scala-ish pseudocode):

// Define some types that correspond to table rows in relational DB
class User
val id: Int
val name: String
val email: String
val created: DateTime

class Comment
val id: Int
val text: String
val userId: Int
val created: DateTime

// Call a select/join query function
val list = select c.id, c.text, u.name


And then list gets a type of something like List[R] where R is an unnamed record type with properties id, text, name, or maybe c.id,c.text,u.name.

Some languages, like Scala, support you writing that select function so it returns a tuple type (Int, String, String), but not a type with named fields.

Is there a name for that?

• I'm having difficulty identifying what precisely you are asking about. Can you step back from the code, and describe the functionality that you're looking for the name of (in words)? Are you talking about a type, or about an operation? Are you familiar with record types? A record type is a type with named fields (which it sounds like might be what you are asking for). Is that what you are looking for? Or are you asking about a particular operation on record types, and if so, what? – D.W. Dec 20 '13 at 3:42
• I want the type of the element in the list to not be User or Comment. It is something created by the form of the query. (Just like in pure SQL.) I ask about this because in much of my web app, I'm struggling to define these named types that exist only to hold the results of various selects and joins. It would be nice if they could be created my the compiler or by macros, by looking at the form of the query. These types often don't have an obvious name, other than "the result of such and such query". Does that make sense? I can try again after some sleep. :) – Rob N Dec 20 '13 at 4:42
• @D.W. It seems like he wants anonymous records, presumably with the obvious subtyping relation. – Alexey Romanov Dec 20 '13 at 7:31
• @RobN C# supports this under the name "anonymous types", though you can't write such a type explicitly. – Alexey Romanov Dec 20 '13 at 7:34

I think what you are looking for is type inference for record types. Let me give you a bit of an overview on each of those; but those keywords should help you find a lot more on those topics.

Type inference means that you don't need to declare the type of everything. Instead, the compiler figures out the types where it can, from context. For instance if you write a function

function f(x,y):
return (x+y)/2


the compiler can figure out that f is a function that accepts two ints and returns an int. (How? It knows that 2 is an int, and that both operands to the / operator must have the same type. Therefore, the type of (x+y) must be int. That in turn means that both x and y must have type int. And so on.)

You want type inference, so you don't need to explicitly declare the type of the result of the select and join operations.

A record type is basically just a fancy name for a struct.

A record is an object that has a list of fields; each field has a name and a type. For instance, to invent some example syntax: {height: 69, weight: 152, name: "Bob"} might be a record with three fields; if r is a variable holding this record, then you might be able to use syntax like r.height to access the height field.

You might notice that a record is a lot like a tuple, except that the elements of a tuple do not have names (you just access the first element, second element, third element, etc. of a tuple), whereas the elements of a record do have names. The ability to provide names is convenient.

Others have suggested you want anonymous record types, meaning you don't have to explicitly declare the record type. That might be relevant, too.

With those concepts, here's what I think you want from a language. First, I think you want your language to support records. Second, assuming you want a statically typed language, it sounds like you want your language to support type inference, so that the compiler can infer the types of records where possible. (In a dynamically typed language, you might not need the type inference part.) Third, you want your language to provide select and join operations that operate on records and return records, and (assuming you want a statically typed language) you want the type inference operation to be able to infer the types on their result based on the types of what they're operating on.

There are some questions you'll need to decide. Will select and join be built into the language? Or do you want to enable them to be implemented as libraries or user-defined functions?

You'll also need to make some decisions about your type system. Do you want to alow subtyping on records? Do you want to allow functions in your language to be polymorphic over records? For instance, consider the following function:

function g(r):
return (r.weight + 10) / r.height


Do you want your type inference routine to infer that g is polymorphic in the sense that it can accept any record containing width and height fields that are both ints? Do you want to allow first-class field names? For instance, consider the following function:

function extract(r,f):
return r.f

r = {height: 69, weight: 152, name: "Bob"}
extract(r, #weight)


In other words, do you want to be able to pass the name of a field as a first-class value?

There's lots of work on type systems for this sort of stuff, in the programming languages community -- and especially in functional languages. I believe that F# and ML support sophisticated record types. Here are two samples:

For experience about how to integrate SQL-like database queries directly into the programming language, take a look at LINQ, which provides support for SQL queries directly in .NET / C#. There are both academic papers on LINQ and lots of documentation for developers. It looks very close to what you are looking for.

• Thanks. I think the key point I'm looking for is the anonymous record types. I'm using Scala for now, so I'll do some further research to see if one can implement these using macros, or if they're simply not possible at present. – Rob N Dec 22 '13 at 15:54
• @RobN You can look up OCaml references to the ones provided by D.W.; OCaml has record polymorphism (via objects, and polymorphic variants for the dual feature) and the authors (Didier Rémy, François Pottier, Jacques Garrigue, and others) have published many papers on the topic. – Gilles 'SO- stop being evil' Feb 12 '14 at 21:48