[METAMODEL-1135] Support for sub queries in filter item operands

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Hans Drexler
• Created: 04/Jan/17 13:30
• Updated: 08/Jan/17 14:00

Description

MetaModel can not create queries like below, with a sub query in the where clause.

select f1, f2, f3 
                from S, O 
                where S.O_ID = O.ID 
                and S.ID in (
                   select ID from T 
                   where T.O_ID = ? 
                   order by T.ID ASC
                   LIMIT 1000 
                )

Although it is possible to write an equivalent query using join statements, the execution plans of those were orders of magnitudes slower than the query above.

With the sub-select, the database engine figured out that it should first get the 1000 records from the sub query and only then join the S and O tables. When using joins, both SQLServer and Postgres decided to do the join and then take limit from the result.

The run times were ~5000 seconds for the query using joins versus <1s for the version using the sub query.

It would be good if we could express the query with sub queries in MetaModel.