General SIZE transformation enhancements

[Mobe91]

At the moment we generate subqueries as soon as we have more than one SIZE or any other collection in the query. We could replace these subqueries by using COUNT(DISTINCT elem).

[beikov]

This is related to #92
Beware that when having composite ids we need some special handling.

[beikov]

Also note that using count distinct to subquery transformation is only necessary for very few DBs like for example MySQL.

[beikov]

Please also help me with defining what the transformation should do. So far I came up with the following analogous to what was defined in #188.

Instead of creating a subquery for the SIZE function when appearing in the select clause, we can transform it into

• A newly generated JOIN node
• GROUP BY parent JOIN node
• and replace the SIZE with a COUNT(newAlias)

as long as there are no group bys present.

Multiple SIZE usages require to use COUNT(DISTINCT newAlias) which is not possible for some DBs when the relation uses a composite id.

This tranformation should be deactivatable via a property.

[Mobe91]

I would like to close #188 and have this issue for the planned SIZE transformation enhancements in general.

I came up with the following

General

• Generate subquery if at least one explicit group by exists and if there exists no group by that is equal to the group by that would be generated by a transformation (which is a group by the IDs along the path of the collection)
• If the query contains multiple join roots, generate a subquery
• If for a collection an explicit join already exists, a separate join must be generated and COUNT DISTINCT should be used
• Multiple uses of SIZE with different collections requires COUNT DISTINCT

JOIN

• Use only subqueries in ON clauses

WHERE

• SIZE uses in the WHERE clause can be transformed to COUNT and moved to the HAVING clause
• A separate collection join has to be generated in order to compensate for records filtered out by the remaining WHERE clause predicates
• Only allowed for atomic top-level disjuncts
• Other attributes used in the predicate expression have to be added to the group by