Safe-navigation operators (myentity?.myToOne) for implicit left joins

[yrodiere]

Problem statement

The JPA spec defines the navigation operator (.) in JPQL queries as yielding inner joins when it triggers implicit joins:

Path expression navigability is composed using “inner join” semantics. That is, if the value of a non-terminal field in the path expression is null, the path is considered to have no value, and does not participate in the determination of the result.

But there is a class of queries where a left join would be more convenient, e.g. here if documentType is optional, we have to write this:

SELECT document.id, documentType.name
FROM Document document LEFT JOIN document.documentType documentType

... in order to get this SQL:

SELECT d.id, dt.name
FROM document d LEFT JOIN document_type dt ON d.document_type_id = dt.id

Whereas we'd love to be able to write just this:

SELECT document.id, documentType.name -- Incorrect, this leads to an inner join
FROM Document document

... but obviously this is incorrect and will yield this SQL:

SELECT d.id, dt.name
FROM document d INNER JOIN document_type dt ON d.document_type_id = dt.id

Proposal

This behavior is what it is, but changing it would be challenging or would involve workarounds (hints), so it'll very likely stay that way.

We could, however, offer an alternative to the navigation operator (.) which would yield left joins instead of inner joins. This behavior is, in a way, similar to the safe-navigation operator (?.) used in various programming languages, so that operator would be a decent choice.

So we would be able to write this:

SELECT document.id, document?.documentType?.name
FROM Document document

And get the SQL we want, with an implicit left join:

SELECT d.id, dt.name
FROM document d LEFT JOIN document_type dt ON d.document_type_id = dt.id

In some extensions to JPQL, e.g in Hibernate, this could even be shortened to just SELECT document.id, document?.documentType?.name, without a FROM clause due to that clause being inferred from context -- but this is obviously out of scope for this issue.

This could remove a lot of verbosity in queries that -- all things considered -- are rather simple in their objectives, but get weighted down by the need for explicit joins.

For example this JPQL:

SELECT document.id, document.title, documentType.name, author.name, manager.name, folder.name
FROM Document document
LEFT JOIN document.documentType documentType
LEFT JOIN document.author author
LEFT JOIN author.manager manager
LEFT JOIN document.folder folder

... would become simply:

SELECT document.id, document.title, document?.documentType?.name,
     document?.author?.name, document?.author?.manager?.name, document?.folder?.name
FROM Document document

... or even (HQL with context-inferred FROM clause):

SELECT document.id, document.title, document?.documentType?.name,
     document?.author?.name, document?.author?.manager?.name, document?.folder?.name

Further considerations

1. While the safe navigation operator can be assigned a clear, intuitive meaning for associations, we would need to make an arbitrary choice for other attributes.
   For example, name being mapped to a varchar column, should we mandate that developers write document?.documentType?.name, or document?.documentType.name? Should we allow both?
2. If JPQL defines an [] operator, it could make sense to define a "safe" version there too, i.e. ?[]. That is, depending on whether this operator implies inner joins (??) and whether we decide to allow ?. on non-association attribute access.

[beikov]

Your examples also mention document?.collection?.name which is probably something that we should discuss separately, because collection de-reference isn't supported with the . operator. Not even in Hibernate ORM.

[yrodiere]

Your examples also mention document?.collection?.name which is probably something that we should discuss separately, because collection de-reference isn't supported with the . operator. Not even in Hibernate ORM.

Ooops, in my head this was a toOne association to a Collection entity. A named collection of documents. You're right, it's confusing, I'll edit the example to use Folder instead.

[gavinking]

@yrodiere Why are you writing:

SELECT document.id, document.title, document?.documentType?.name,
     document?.author?.name, document?.author?.manager?.name, document?.folder?.name
FROM Document document

I would have expected this to be:

SELECT document.id, document.title, document.documentType?.name,
     document.author?.name, document.author?.manager?.name, document.folder?.name
FROM Document document

Because document is not null.

[gavinking]

Another possibility, which has the (arguable) advantage of (arguably) fitting in more naturally with the aesthetics of SQL would be something like this:

SELECT document.id, document.title, 
       OUTER document.documentType.name,
       OUTER document.author.name, 
       OUTER document.author.manager.name, 
       OUTER document.folder.name
FROM Document document

where the keyword OUTER indicates that an left outer join should be used for the implicit join.

[yrodiere]

@yrodiere Why are you writing:

SELECT document.id, document.title, document?.documentType?.name,
document?.author?.name, document?.author?.manager?.name, document?.folder?.name
FROM Document document

I would have expected this to be:

SELECT document.id, document.title, document.documentType?.name,
document.author?.name, document.author?.manager?.name, document.folder?.name
FROM Document document

Because document is not null.

Because I'm hikacking the meaning of ?. here. . is already null-safe in JPQL, so it's almost like a ?. already... It's halfway there, but it happens to behave like an inner join on associations, which is very much not like ?. in most programming languages.

The difficulty here is that . in JPQL behaves differently depending on the value of the association: you'll see the effect of the INNER part of the INNER JOIN only if the association is not set. Which is not something . does in a programming language, usually: its behavior only depends on the left operand.

If JPQL had advanced typing similar to languages such as Ceylon or Kotlin, I guess we could say that ., on an unset association, may end up returning Nothing -- while ?., in my proposal, may end up returning Null. That is the distinction I'm after.

Now we could move the operator one step to the right, like you suggest, but I believe this puts us in a difficult position for a query like this:

SELECT document.id, author
FROM Document document
LEFT JOIN document.author author

You can't express this with ?. if the operator is supposed to be to the right of document.author... Unless you add a postfix ? operator?

SELECT document.id, document.author?
FROM Document document

Another possibility, which has the (arguable) advantage of (arguably) fitting in more naturally with the aesthetics of SQL would be something like this:

SELECT document.id, document.title,
OUTER document.documentType.name,
OUTER document.author.name,
OUTER document.author.manager.name,
OUTER document.folder.name
FROM Document document

where the keyword OUTER indicates that an left outer join should be used for the implicit join.

I suppose that's a reasonable alternative, though it does lose some expression power (can't have inner join followed by outer join, e.g. document.author?.favoriteFruit with the syntax of my original proposal).

Perhaps more importantly, I suspect it doesn't offer the same potential for integrating nicely with libraries building on JPA to offer "projection binding" -- e.g. Panache allows annotating DTOs with @ProjectedFieldName("<path>"), e.g. @ProjectedFieldName("document.id").
Not sure people would find @ProjectedFieldName("OUTER document.documentype.name") intuitive, though I suppose @ProjectedFieldName("document.documentype.name", outer = true) is an option.
But introducing ?. in JPQL would allow @ProjectedFieldName("document?.documentype.name"), which I think would make the library feel more consistent/integrated with JPQL.

Still, at this point I agree it's a lot about tastes and -- like you said -- aestethics. OUTER would work too.