Forum: Building VoltDB Applications

Post: Error in executing SQL query when it involves more than two tables in outer join

Error in executing SQL query when it involves more than two tables in outer join
kamalkishore
Jan 22, 2014
Hi,

I have a use case where I am executing a SQL query which involves more than two tables in outer join and it is throwing an error saying that "Unexpected Ad Hoc Planning Error: java.lang.RuntimeException: Error compiling query: org.voltdb.planner.PlanningErrorException: VoltDB does not support outer joins with more than two tables involved".

The sample query is given below
"select avg(entity_lead_time.ageing)as avg_ageing_For_Month ,count(entity_lead_time.month_year) as avg_ageing_Volume from action_item_mgmt action left join entity_lead_time entity_lead_time on entity_lead_time.entity_id=action.id join relation rel on action.relation_id = rel.id left join contract cntrct on action.contract_id = cntrct.id where action.deleted = 0 and entity_lead_time.ageing is not null and entity_lead_time.entity_type_id=18 and action.status_id not in(4,7) and action.ageing is not null and action.id"

What is the workaround to solve this problem without changing table schema.

Rgds,
Kamal
pmartel
Jan 22, 2014
Kamal,
This problem is solved by using a more recent version of VoltDB.
Paul
kamalkishore
Jan 23, 2014
Hi paul,
Thanx for the reply.
I am currently using 3.7 version ( which is the latest available on website ) and with that I am hitting this issue.
Which version should I use to fix this issue.

Rgds,
Kamal
pmartel
Jan 23, 2014
Kamal,
I apologize for the confusion. The ability to support more than two tables in an outer join has only been released so far in our open source code offering.
This capability will be available in a pre-built package with our upcoming 4.0 version which we expect to release next week.
You will then be able to download it from our web site.
Regards,
Paul