Ebean's avoidance of SQL cartesian product - Still true? Potential downsides?

[artemik]

It's stated that "Ebean ORM will never generate a SQL cartesian product." I have the following questions regarding that.

1. Is it still true? Doesn't it depend on some auto tuning feature of Ebean nowadays? And, do I understand correctly that it means Ebean will never generate joins with more than a single collection, or is there more to this statement?
2. Avoiding cartesian product means executing multiple queries. Some people are worried that it might cause a read skew, which otherwise would've not been caused by a single query in databases having read committed isolation with snapshots (mvcc), like postgresql. It seems a valid worry, yet it also makes sense to be avoiding cartesian products anyway. So my question is what do Ebean maintainers think about it, is there some specific reasoning on why the "avoid" approach was chosen despite potential read skew issues? Are there some future plans related to it, for example allowing users to force a single query when needed (or is it something already supported but wasn't obvious for me on how to utilize)?

[rbygrave]

Is it still true?

Yes.

Doesn't it depend on some auto tuning feature of Ebean nowadays?

It never did depend on auto tuning no. [wrt Auto tuning, I'd say its more the opposite, that you'd never be able to support auto tuning without such a feature imo]

Ebean will never generate joins with more than a single collection

1. "Collection" would be better said as "*ToMany Collection" [a OneToMany or ManyToMany relationship]. That is, there are other collection options like DbArray and DbJson for which this does not apply.

2. "joins" here means ... "joins used for projection". There are joins for ToMany relationships that are not used for projection but only used for predicates / filtering.

So it's more accurate to say - Ebean will not generate projection joins for more than one *ToMany relationship.

some specific reasoning on why the "avoid" approach was chosen

Due to cartesian products having the potential to produce very large resultSets. e.g. Collection A has 100 rows, Collection B has 100 rows ... the cartesian produces a resultSet with 10,000 rows.

So performance risk is the reasoning. We can't predict the cardinality and we know it gets bad very quickly.

read skew issues?

As a workaround, you'd create a transaction at Repeatable Read or Serializable to span those queries - but there isn't a free lunch in that there are typically downsides to running in higher isolation levels which is why the default isolation level for Postgres is Read Committed. [Noting that there are some newer MVCC databases that I'd suggest argue for higher isolation levels as the default and work differently - e.g. Yugabyte]

Are there some future plans related to it, for example allowing users to force a single query when needed

No, there are no plans for that.

[rPraml]

In an other project (no ebean, based on PHP and manually written MariaDB-SQLs) I use JSON functions to fetch collections:

select t1.id, t1.name,
(select JSON_ARRAYAGG(JSON_OBJECT('id', t2.id,...)) from othertable t2 where t2.table1_id = t1.id) as jsonstring from table1 t1

With subselects, you can retrieve multiple "collections" without building cartesian products and retrieve exact the data, that is required

The advabtage is of course, you need only one query
The downside is

• json serialization/deserialization overhead
• database must support json functions
• there may be some size limits in the retured jsons

I hope you get the idea.
I think also, it should not be too difficult to extend ebean to support this (BeanPropertyMany needs a ScalaTypeJson, that handles deserialization or custom DTOs)

[rbygrave]

Yes, noting that this JSON approach is used by JOOQ.

Also note that SQL MULTISET could ultimately become useful here [and could be used if its supported by the database you are using], currently I don't think it has wide enough adoption so not practically useful yet imo.

[artemik]

Thanks for clarifications, closing. I think it's important to realize such potential issues (but also how it's a compromise), so wanted them to be confirmed in a written form, maybe also they could be added into documentation somewhere, to help advocate why in Ebean it was a good design decision.