[hibernate-dev] Extended KEY expression support

[Christian Beikov]

I created an issue for this feature: 
https://hibernate.atlassian.net/browse/HHH-11433

If we agree to render "domain joins" that involve multiple tables with 
the ANSI SQL parenthesis join syntax, we can actually even "ignore" the 
join types for key and value tables. That would make things like 
referring to subtype properties in an ON clause also a lot easier.

Here a quick example:

@Entity class Entity{ Map<X, A> map; }
@Entity @Inheritance(JOINED) class A{ ... }
@Entity class B extends A { String propertyB; }
@Entity class C extends A { String propertyC; }

@Entity @Inheritance(JOINED) class X { String propertyX; }
@Entity class Y { String propertyY; }
@Entity class Z { String propertyZ; }

SELECT e.id, KEY(m), VALUE(m)
FROM Entity e
LEFT JOIN e.map m
   ON KEY(m).propertyX >  0 AND TREAT(KEY(m) AS Y).propertyY = TREAT(m 
AS B).propertyB
   OR KEY(m).propertyX <= 0 AND TREAT(KEY(m) AS Z).propertyZ = TREAT(m 
AS C).propertyC

That would render roughly like

SELECT ...
FROM table_entity e
LEFT JOIN (
   table_map m
   JOIN (
     table_x x
     LEFT JOIN table_y y ON x.id = y.id
     LEFT JOIN table_z z ON x.id = z.id
   ) ON m.key = x.id
   JOIN (
     table_a a
     LEFT JOIN table_b b ON a.id = b.id
     LEFT JOIN table_c c ON a.id = c.id
   ) ON m.value = a.id
) m ON m.e_id = e.id AND (
      x.property_x >  0 AND y.property_y = b.property_b
   OR x.property_x <= 0 AND z.property_z = c.property_c
)

I guess the concept of the parenthesis join fits the table group idea?

Am 27.01.2017 um 23:58 schrieb Steve Ebersole
>
>     A further change to that would be to not generate the implicit key
>     table join anymore but require the user to do the join explicitly.
>     Since
>     that would break backwards compatibility, I'd like to make that
>     behavior
>     configurable and of course, by default it will generate the
>     implicit key
>     join to maintain backwards compatibility. I also propose to switch the
>     default in 6.0 so that the join is not generate anymore.
>
>
> I personally would vote to *not* make this change in 5.x even in terms 
> of making it configurable.  The grammar there is fugly enough already 
> and this is the kind of thing (especially making branches of it 
> configurable) that takes that fugliness to a new level.

I gave that another thought and don't think that requiring the user to 
do a separate join is a good idea anymore. We can actually infer if a 
join is required and when using the ANSI parenthesis join syntax the 
join type does not really matter. If we stick to the join rendering 
style we have, we should just use the collection tables join type.

Do you think it makes sense to implement support for "JOIN 
KEY(m).association" in 5.2 or just leave it as it is? I would implement 
that if you give your ok.

What do you think about all that so far?