Left Exception Join as a precursor to a larger issue

[SamC]

This is sort of a two-part issue.  I'll start with the simplest part of the problem and expand from there into the second part.

1. How do we accomplish left exception joins (for lack of a better term)?
• In SQL we could simply write something like this:
  select a.id from a left join b on b.a_id = a.id where b.id is null
  Normally, that's how you would find all "a" records that don't have an associated "b" record.  That's not an unusual query to make in my experience.
  
• This query, in Ebean, is interpreted roughly as (I might not have this quite right):
  select t0.id c0 from a t0 join b t1 on t1.a_id = t0.id where t1.a_id is null
  The problem with switching from a LEFT join to a regular (inner) join in this case is obvious: When you inner join two tables and filter one of the table's IDs on null, then you will always return 0 records.
2. The more complicated request that more closely reflects my particular scenario is a modified left exception join:
• select a.id from a left join b on b.a_id = a.id where b.id is null or b.active = false
  That query should return all IDs for object "a" where there is either no associated "b" record or the "b" record is not active.  Again, I view this as a common type of query that makes a number of business reporting requirements possible.
  
• In my recent experience Ebean also switches the join in this example to an inner join from a left join.

It appears as though Ebean defaults to a LEFT join when there are no filter criteria applied.  As soon as something pops up in the WHERE statement referencing the joined table, it switches to an inner join.

To fix this, is there a way to specify a fetch as a LEFT join (maybe in the FetchConfig)?  If not, would this be a reasonable feature request?

Also, I'd prefer to avoid RawSql if possible.  This particular query has about 40 columns, and I'm not looking forward to the prospect of adding 40 columnMapping entries.

Thanks in advance for any help and advice with this.

-Sam

[edge]

Which version of Ebean are you using? There was a bug where the restrictions on the join where placed in the where clause and not directly on the join i

e.g.

select t0.id c0 from a t0 join b t1 on t1.a_id = t0.id where t1.a_id is null

should be

select t0.id c0 from a t0 join b t1 on t1.a_id = t0.id and t1.a_id is null

The bug I mentioned arose when dealing with joins to derived classes with the discriminator being placed in the where instead of the join clause.

This looks a bit like similar and I know there was some more work done on it but I don't know if your issue was fixed.

[SamC]

I'm using the version included with Play 2.1.3.  When I look at my project's referenced libraries folder, I see various different version numbers for different Ebean components.  I'm not sure if I'm looking in the right place to find the overall version.

In any event, the generated SQL statements do not appear to place the restrictions in the join statement (they all go after the where), so I guess I'm on a recent enough version for that.

I'm still experimenting to see what I can do to force a LEFT join with restrictions (like IS NULL).  I've started digging through the Ebean source code to see how difficult it would be to build a version that allows me to override the join type when fetching the various tables.  I'm worried that may break some things in the process, but it's just one avenue I'm investigating at the moment.

I'm really hoping that I'm missing something obvious in all of this.  Left exception joins are pretty common (at least in my experience).

Thanks for your help, and let me know if you have any other thoughts on this.

[SamC]

Now I'm really starting to get frustrated.  There has to be a solution here somewhere.  My latest attempt has been creating the massive RawSql statement which basically looks like this:

select 

a.id, a.col1, a.col2 ... a.col30

, b.id, b.col1, b.col2 ... b.col8 

from

a

left join b on b.a_id = a.id

where

b.id is null

or b.active = false

That statement throws up a NullPointerException which I've narrowed down to the additional columns I'm pulling in from table "b."

If I eliminate all of the "b" columns in the select statement except for the id, everything works just fine.  As soon as I pull in any of the other supporting columns from "b," I get the NullPointerException.

If I'm reading the error trace correctly, I think this is coming from CQuery.java:255.  That appears to be part of the Ebean code that is trying to map columns (I think).

So, unless anybody has a clever solution for a left exception join, I'm beginning to think they are impossible in Ebean even if you resort to RawSql.  Could this really be the case?

[Daryl Stultz]

On Friday, August 23, 2013 4:22:59 PM UTC-4, SamC wrote:

Also, I'd prefer to avoid RawSql if possible.  This particular query has about 40 columns, and I'm not looking forward to the prospect of adding 40 columnMapping entries.

Sam, you can mix raw sql with the API. If the matter is simply about what entities to retrieve, you can do this:

Ebean.find(Thing.class)

.fetch(...)

.where()

.raw("id in (select a.id from a left join b on b.a_id = a.id where b.id is null or b.active = false)")
.findList();

If Ebean can't match the expressions to the ORM model, it assumes it's raw SQL and passes it through. Sometimes the raw SQL can look like ORM expressions but usually fully qualified tables/columns works well. (The initial "id" is recognized as the id property of Thing.)

/Daryl

 

[SamC]

Thanks for that tip Daryl.  Now there is still a problem with that approach, unfortunately.  The "b" table I reference is one of several table being joined in this query.  So, while your solution do allow me to maintain a left join on that table in the master query, Ebean takes it upon itself to actually include table "b" in a secondary query that I apparently have no control over (the raw where statement isn't brought over to the second query).  I've noticed this behavior before, and I don't like the idea of Ebean deciding when to break up my query into multiple queries.  It's really hurting now that I appear to be closer to a solution.  I tried setting autofetch to false with the hope that it would help, but that didn't seem to fix it.  I'm not sure what to try next.  Any thoughts?

[Daryl Stultz]

On Sunday, September 1, 2013 9:43:48 PM UTC-4, SamC wrote:

 I've noticed this behavior before, and I don't like the idea of Ebean deciding when to break up my query into multiple queries.

Can you post the actual Ebean query you are building up and the resultant SQL queries being generated? It's hard to follow you since you've only posted the SQL queries.

/Daryl

[SamC]

Hi Daryl,

For the sake of clarity, here is the genericized Ebean query:

find
.fetch("objB")
.fetch("objC")
.fetch("objD")
.where()
.ge("propA", new DateTime())
.eq("propB", false)
.eq("propC", true)
.lt("propD", 5)
.lt("propE", 5)
.isNull("propF")
.raw("(objD.id in (select tblD_alias.id from tblA tblA_alias inner join tblD tblD_alias on tblD_alias.tblA_id = tblA_alias.id where tblA_alias.colA >= now() and tblA_alias.colB = false and tblA_alias.colC = true and tblA_alias.colD < 5 and tblA_alias.colE < 5 and tblA_alias.colF is null and tblD_alias.colA = 1) or objD.id is null)")
.findList();

The problem with this solution comes when Ebean generates the SQL statements.  The first SQL statement looks pretty good (with one big problem issue that I'll get to in a second).  I've reformatted the SQL a little to make it more readable:

select distinct
t0.id c0, ...blah blah blah... t0.colZ c25
, t2.id c26, ...blah blah blah... t2.colZ c28
, t3.id c29, ...blah blah blah... t3.colZ c40
from
tblA t0
left outer join tblB t2 on t2.tblA_id = t0.id
left outer join tblC t3 on t3.tblA_id = t0.id
left outer join tblD t1 on t1.tblA_id = t0.id  
where
colA >= 2013-09-02 09:45:49.981
and t0.colB = false
and colC = true
and colD < 5
and colE < 5
and colF is null
and (t1.id in ( select tblD_alias.id from tblA tblA_alias inner join tblD tblD_alias on tblD_alias.tblA_id = tblA_alias.id where tblA_alias.colA >= now() and tblA_alias.colB = false and tblA_alias.colC = true and tblA_alias.colD < 5 and tblA_alias.colE < 5 and tblA_alias.colF is null and tblD_alias.colA = 1) or t1.id is null)
order by t0.id

(I'll talk about the highlighted restriction further down)

Notice what Ebean omitted above: It completely left out t1 in the select statement.  I've had Ebean do this before, and it's quite annoying.  It seems like once you join in three or more additional tables, Ebean breaks up the query (how the heck is that helpful?).  In this case, Ebean generates a second query to get everything for t1 - BUT - it doesn't carry over my raw SQL where statement as you see below:

select t0.id c0
        , t1.id c1, ...blah blah blah... t1.colZ c8
from tblA t0
left outer join tblD t1 on t1.tblA_id = t0.id  
where t0.id in (230,234,238,239,240,242,243,244,245,246,247,248,249,250,251,252,230,230,230,230)  
order by t0.id

Those IDs are not necessarily relevant.  Ebean doesn't know that I've placed an additional restriction on t1 in the previous query (highlighted in the first query).  It, therefore, includes more results than I need.  As this table grows, I will inevitably be swamped with extra results from this additional SQL statement.  So, this approach doesn't seem to be working either.  What do you think?

[Daryl Stultz]

On Mon, Sep 2, 2013 at 10:31 AM, SamC <s...@rompn.com> wrote:

Those IDs are not necessarily relevant.  Ebean doesn't know that I've placed an additional restriction on t1 in the previous query (highlighted in the first query).  It, therefore, includes more results than I need.

Ah, I see. What the criteria of a query does is decide which of the root objects to retrieve. So the list of IDs for the secondary query are in fact valid. The population of the child relations is a separate issue from the parent entities retrieved. Really what you are saying is "give me all A entities that have (among other things) a child entity D with D.a = 1". You appear to then be expecting A to be loaded with the D collection populated only with the D entities that matched the original query. You specified fetch("objD") which means load all D child entities of parent A. (The "breaking up" of the queries can be controlled with FetchConfig).

I don't know if you can get everything you want with this, but filterMany allows you to load in parent A and populate child collection D where the members of collection D match the originally filtered A's. I would try it with the API only first to get a feel for it, not with raw expressions.

/Daryl

[SamC]

Hi Daryl,

Thanks for the quick response.  I had already tried the filterMany approach (didn't mention it here), and it does get closer to the mark.  There are still some problems.  I can seem to force a single query as the output.  I've tried it with no FetchConfig, query(), query(9999), lazy, lazy(9999), and queryFirst(9999).lazy(9999).  Ebean never lets me go down to one query (at least when I have more than two additional tables being joined).  In an ideal world, the generated SQL should look something like this:

select distinct
t0.id c0, ...blah blah blah... t0.colZ c25
, t2.id c26, ...blah blah blah... t2.colZ c28
, t3.id c29, ...blah blah blah... t3.colZ c40
from
tblA t0
left outer join tblB t2 on t2.tblA_id = t0.id
left outer join tblC t3 on t3.tblA_id = t0.id
left outer join tblD t1 on t1.tblA_id = t0.id  
where
colA >= 2013-09-02 09:45:49.981
and t0.colB = false
and colC = true
and colD < 5
and colE < 5
and colF is null

and (t1.colA = 1 or t1.colA is null)
order by t0.id

The filterMany should produce that, but it still kicks off a separate SQL statement.  How, specifically, can I force my API query to only result in one, single SQL statement?

Many, many thanks,

Sam

[SamC]

Actually, to be clear, the default fetch behavior with this latest approach seems to add and additional SQL statement for each 99 or 100 records found in the parent table.  We have a large parent table, so this would absolutely slam our database (this statement will be called frequently).  I really need this consolidated into a single SQL statement if that's at all possible.

[Daryl Stultz]

On Mon, Sep 2, 2013 at 3:07 PM, SamC <s...@rompn.com> wrote:

 I've tried it with no FetchConfig, query(), query(9999), lazy, lazy(9999), and queryFirst(9999).lazy(9999).

 

The filterMany should produce that, but it still kicks off a separate SQL statement.  How, specifically, can I force my API query to only result in one, single SQL statement?

That I don't know. With no FetchConfig and auto tune off, it should attempt one query. I don't know the inner workings enough to say. Perhaps there's a quirk in your model annotations.

Rob or Eddie might know better.

/Daryl 

[SamC]

Hi Daryl,

My annotations are pretty basic - mostly the usual stuff like @OneToMany.

Do you know how I might be able to reach Rob or Eddie more directly?  I think I've seen Rob's name on the project - is Eddie also a maintainer?

I really appreciate all of your advice with this.  You've been incredibly helpful.

All the best,

Sam

[Rob Bygrave]

I'm here but just buried in work this week.  I'll try and have a read of this thread tonight.

>>  for each 99 or 100 records found in the parent table.

So that is batch lazy loading going on.  By default EbeanORM will not generate any Cartesian product queries so normally this occurs when fetching an object graph with multiple OneToMany or ManyToMany relationships.  ... but I think you are using rawSql right so that is lazy loading AFTER the 'root query' has run.

Anyway, I'll see if I can read the thread and follow it tonight.

Cheers, Rob.

--
 
---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[SamC]

Hi Rob (sorry, had a typo here earlier!),

Thank you so much for agreeing to review this.  I look forward to hearing your input on this issue.

All the best,

Sam

[Rob Bygrave]

>> 1. select a.id from a left join b on b.a_id = a.id where b.id is null

Correct, Ebean won't generate that for you.

>> 2. The more complicated request that more closely reflects my particular scenario is a modified left exception join:

If you only are selecting ID's then you don't need Objects per say so you simply could use a SqlQuery -  ebeanServer.createSqlQuery(sql);

>> creating the massive RawSql statement

Hard to look at that without a test case.  I don't really follow how you get from selecting ID's to needing RawSql.

>>  It seems like once you join in three or more additional tables, Ebean breaks up the query (how the heck is that helpful?). 

It is doing this when you 'fetch join' multiple *ToMany relationships (it doesn't do this for any @ToOne relationships). If Ebean didn't do this you end up with cartesian product queries which are not great.

>>  How, specifically, can I force my API query to only result in one, single SQL statement?

I'd suggest you are 'fetch joining' multiple OneToMany/ManyToMany relationships so again Ebean isn't going to do that.

If you do have a single SQL statement and it isn't a cartesian product we can look at that.

>> We have a large parent table, so this would absolutely slam our database (this statement will be called frequently).  I really need this consolidated into a single SQL statement if that's at all possible.

Well, it depends a bit on what you are doing (report processing? reading all rows rather than just first rows?) and the cardinality of those joins to tableB tableC and tableD (ie. a single query resulting in a cartesian product could slam your database as well).

So the first thing is to clarify that you are indeed fetch joining multiple @OneToMany relationships.

The second thing is to consider if your requirement is actually better suited to a more 'relational approach'.

Cheers, Rob.

On 3 September 2013 11:09, SamC <s...@rompn.com> wrote:

Hi Roy,

Thank you so much for agreeing to review this.  I look forward to hearing your input on this issue.

All the best,

Sam

On Monday, September 2, 2013 5:04:49 PM UTC-4, Rob Bygrave wrote:

[SamC]

Hi Rob,  I've replied inline below

On Tuesday, September 3, 2013 7:16:54 AM UTC-4, Rob Bygrave wrote:

>> 1. select a.id from a left join b on b.a_id = a.id where b.id is null

Correct, Ebean won't generate that for you.

Judging by the recent requests around specifying the join type (I noticed a few threads and a bug mentioned in some of the posts today), this might be functionality that people need.

 

>> 2. The more complicated request that more closely reflects my particular scenario is a modified left exception join:

If you only are selecting ID's then you don't need Objects per say so you simply could use a SqlQuery -  ebeanServer.createSqlQuery(sql);

I apologize for not being more clear about this.  I was trying to keep the examples simple for that sake of readability, so I left out all of the other columns I need to bring in (over 40 at this point).

 

>> creating the massive RawSql statement

Hard to look at that without a test case.  I don't really follow how you get from selecting ID's to needing RawSql.

Again, sorry about over simplifying the sample case.

 

>>  It seems like once you join in three or more additional tables, Ebean breaks up the query (how the heck is that helpful?). 

It is doing this when you 'fetch join' multiple *ToMany relationships (it doesn't do this for any @ToOne relationships). If Ebean didn't do this you end up with cartesian product queries which are not great.

I this is a big problem for me.  I have designed the db with several one-to-many relationships that, yes, could produce large sets resulting from the cartesian products - except for the fact that I have architected my application to rigidly enforce one-to-"just a few."  So, the cartesian product won't be an issue in my case.  I would very much like to be able to override this as the many, many separate queries are actually making the data acquisition significantly less efficient. 

 

>>  How, specifically, can I force my API query to only result in one, single SQL statement?

I'd suggest you are 'fetch joining' multiple OneToMany/ManyToMany relationships so again Ebean isn't going to do that.

If you do have a single SQL statement and it isn't a cartesian product we can look at that.

I have a single SQL statement (shared earlier in this thread) that would only produce a mild cartesian product (one-to-"just a few").  I know what I'm doing, and I have handled this stuff many times over the years with straight SQL.  This is my first time trying to do it in Ebean, and I would like to have the ability to control this myself.

 

>> We have a large parent table, so this would absolutely slam our database (this statement will be called frequently).  I really need this consolidated into a single SQL statement if that's at all possible.

Well, it depends a bit on what you are doing (report processing? reading all rows rather than just first rows?) and the cardinality of those joins to tableB tableC and tableD (ie. a single query resulting in a cartesian product could slam your database as well).

 

So the first thing is to clarify that you are indeed fetch joining multiple @OneToMany relationships.

The second thing is to consider if your requirement is actually better suited to a more 'relational approach'.

Yes, in this scenario, all tables are one-to-many.  I'm not sure what you mean by a more "relational approach."

What I would like to propose (I've seen other requests for this in recent posts as I mentioned above) is more control over the nature of the join statements and the way queries are or aren't broken up.  It's awesome that Ebean tries its best to sort this stuff out for us, but Ebean just isn't able to arrive at the best solution for every problem.  So, that being the case, would it be possible for Ebean to allow a little more control with respect to how it handles complex cases like this?

All the best,

Sam

[SamC]

Somebody accidentally deleted this post, so I'm reposting it for other people who have this same problem:

For anybody who stumbles on this thread and wants to know how we finally solved this issue, this is how we did it:

Background:

At the time this was written our company was using the Play Framework version 2.1.3 and whichever version of Ebean was bundled by default with that version.  We were happy enough with Ebean until this particular snag.  To be fair, this was my first time working with an ORM, so I had no frame of reference with which to judge Ebean.  I've always been a code geek by nature, so I've always gravitated to using raw JDBC and ad-hoc object mapping in the past.  When we first started using Play, I was intrigued by the prospect of having a well-integrated ORM that handled some of the more mundane bits for us.  We have always operated under tight deadlines, so we welcome anything like this that can save us time.  We had hit a few other bumps along the way with Ebean, but we were usually able to creatively work our way around those other issues.  When we hit this issue, however, it cost us enough time that we started wondering if Ebean's magic was worth it.  I accept that we might not have tried every last approach to making this type of join work, but I feel like we did our due diligence and tried everything within reason (including many iterations you don't see in this thread).

Discovery of the solution:

While searching for alternatives we read a post about the upcoming release of Play Framework 2.3.  According to the Play 2.x Roadmap, it seems that Play will be switching to JPA from Ebean.  I haven't seen the rationale behind Typesafe's decision, but I trust that they have their reasons.  With this forthcoming change in mind, I looked around to see if JPA/Hibernate could handle LEFT exception joins (or even more fundamentally allow us to control whether a given join is an inner or a left outer join).  I was pleased to see from various sources that JPA 2 should handles this.  It was also clear from Play's current documentation that there should be no issues getting Hibernate up and running.

Results:

Over the course of this past week I've put in some crazy hours switching our entire project over to Hibernate 4.2.5/JPA2.  I couldn't be happier with this decision.  With JPA, I finally feel like I have the power to make decisions about how my queries operate.  Most importantly, the left exception join scenario works flawlessly.  I'm still running through all of our test cases, but everything is working great at the moment.

Tips:

A smooth conversion with minimal code rewriting required a few tricks that you might find helpful.

Jadira is useful for Joda DateTime persistence / serialization (and there are some other options if you don't like that one)

Hibernate's ImprovedNamingStrategy to handle the camelCase conversion

The expected @play.db.jpa.Transactional annotations in our controller classes

If you use Eclipse, you might need to swap out the Play Ebean jar for the Play JPA jar in your library

There were, of course, a few other tweaks to the code to complete the switch, but most of that is well-documented.

Look at the Play JPA sample code as a guide:

https://github.com/playframework/playframework/tree/master/samples/java/computer-database-jpa

I hope this helps!

All the best,

Sam

[Rob Bygrave]

Fair cop.

>> Somebody accidentally deleted this post,

Actually - weird - it looks like that post got picked up by google as spam.  That is odd as it is the only post to do so that I recall.  We (EbeanORM moderators) moderate peoples FIRST post only (to block the spammers), after the first post there is no moderation.

Cheers, Rob.

[Daryl Stultz]

On Tue, Sep 10, 2013 at 9:57 PM, SamC <s...@rompn.com> wrote:

Over the course of this past week I've put in some crazy hours switching our entire project over to Hibernate 4.2.5/JPA2.  I couldn't be happier with this decision.  With JPA, I finally feel like I have the power to make decisions about how my queries operate.  

We've been working little by little over the last 18 months to convert our system from JPA to Ebean. It's good to hear you are happy about how it's going, but I suspect you are in for a world of hurt when you run into the core design flaw that is the "session". Here's something you never get in Ebean:

"Attempt to access an unloaded field of detached instance 'foo.Bar'"

/Daryl

[SamC]

Hi Daryl,

It's always fascinating to me to set aside my personal preferences about technologies and frameworks and observe how a project like the one you are working on is a better fit for Ebean, and a project like the one I'm on appears to be a better fit for JPA.  To me this illustrates some of the reasons why I love the Java ecosystem so much: Variety and community.

To your point about the session management, I believe that the nature of our application (stateless, Play Framework) helps alleviate this concern.  The inbuilt @play.db.jpa.Transactional (source) annotation also helps to keep us on the right path.

Thanks again to everybody on the Ebean forum for your time and expertise.  Best of luck on your projects.

-Sam

[Daryl Stultz]

On Wednesday, September 11, 2013 9:28:44 PM UTC-4, SamC wrote:

To your point about the session management, I believe that the nature of our application (stateless, Play Framework) helps alleviate this concern.

Yes, that's probably true. Ours is mostly server-side stateful application so the attached/detached issue is of great concern.

/Daryl

[Daryl Stultz]

On Thursday, September 12, 2013 8:03:49 AM UTC-4, Daryl Stultz wrote:

To your point about the session management, I believe that the nature of our application (stateless, Play Framework) helps alleviate this concern.

Yes, that's probably true. Ours is mostly server-side stateful application so the attached/detached issue is of great concern.

On the other hand, here's something you might run in to. We have a routine that reads a CSV text file, loads a bunch of entities from the DB, cross references the entities with the CSV file, possibly inserting new entities, deleting and updating. Suppose all you have are a few entities to be updated but they are not all ready to be saved at once. You loop through the collection doing some final preparation and save them one at a time. In JPA, the entities are "managed" and opening/committing a transaction to save the first one actually saves all of them. There are certainly ways around this problem, the point is, even in a stateless workflow you can get burned by "features" of JPA that in my mind are unexpected/unnatural.

/Daryl

[SamC]

Hi Daryl,

I came to the conclusion while we were still using Ebean that there are just certain tasks ill-suited to an ORM tool.  Upon identifying those points, I shifted them to stored procedures using raw JDBC connections obtained directly from our connection pool.  Again, I can get away with the potential persistence issues as our application is stateless (changes made via a stored procedure will be reflected at the next query).  I recognize that in your scenario this probably wouldn't work as you would be making an end-run around the ORM and breaking the statefulness.  For me, however, this has been a great and highly-efficient hybrid solution.

All the best,

Sam