⬅ Forum: Building VoltDB Applications

Post: SQL - Insufficient Join Criteria

SQL - Insufficient Join Criteria
brandon
Dec 4, 2013
I have been unable to get an INNER JOIN statement to work within VoltDB. I have the tables currently mirrored in Oracle for dev purposes and both statements work in oracle but do not in VoltDB. The following is what I've tried to run:

VOLTDB
SELECT CONVERSION_MAP_NAME.NAME FROM CONVERSION_MAP_NAME INNER JOIN FIRMOGRAPHICS ON CONVERSION_MAP_NAME.NAME LIKE 'HAMM%' AND CONVERSION_MAP_NAME.CODE = FIRMOGRAPHICS.NAME;

SELECT CONVERSION_MAP_NAME.NAME FROM CONVERSION_MAP_NAME, FIRMOGRAPHICS WHERE CONVERSION_MAP_NAME.NAME LIKE 'HAMM%' AND CONVERSION_MAP_NAME.CODE = FIRMOGRAPHICS.NAME;




ORACLE (The same as above but I have a schema attached to each table)
SELECT FIRMOGRAPHICS.CONVERSION_MAP_NAME.NAME FROM FIRMOGRAPHICS.CONVERSION_MAP_NAME, FIRMOGRAPHICS.FIRMOGRAPHICS_SEARCH_FIELDS WHERE FIRMOGRAPHICS.CONVERSION_MAP_NAME.NAME LIKE 'HAMM%' AND FIRMOGRAPHICS.CONVERSION_MAP_NAME.CODE = FIRMOGRAPHICS.FIRMOGRAPHICS_SEARCH_FIELDS.NAME

SELECT FIRMOGRAPHICS.CONVERSION_MAP_NAME.NAME FROM FIRMOGRAPHICS.CONVERSION_MAP_NAME INNER JOIN FIRMOGRAPHICS.FIRMOGRAPHICS_SEARCH_FIELDS ON FIRMOGRAPHICS.CONVERSION_MAP_NAME.NAME LIKE 'HAMM%' AND FIRMOGRAPHICS.CONVERSION_MAP_NAME.CODE = FIRMOGRAPHICS.FIRMOGRAPHICS_SEARCH_FIELDS.NAME


VoltDB Tells me:

Error: Unexpected Ad Hoc Planning Error: java.lang.RuntimeException: Error compiling query: org.voltdb.planner.PlanningErrorException: Join or union of multiple partitioned tables has insufficient join criteria.





If it helps to know what I am trying to accomplish here: My primary table has INTEGER "CODE"s for every item which maps to a STRING in that column's CONVERSION_MAP_X. So I'm searching on something LIKE HAMM in my main table and I want back the actual string values from the conversion table. The above statements work fine in Oracle but are not working out in Voltdb.


Any help much appreciated!
xin
Dec 6, 2013
Hi brandon,

If you ran into this kind of error "Join or union of multiple partitioned tables has insufficient join criteria.", VOLTDB assumes that the query is joined on multiple partitioned tables without sufficient join criteria. Currently, VOLTDB only support multiple partitioned tables join on their partitioned keys. This is because VOLTDB wants the multiple partitioned tables join only on each partition.

So I assume that your table "FIRMOGRAPHICS" and "CONVERSION_MAP_NAME" are both partitioned. According to your query join criteria "CONVERSION_MAP_NAME.CODE = FIRMOGRAPHICS.NAME", I assume you will have no problem if table "CONVERSION_MAP_NAME" is partitioned on column "CODE" and table "FIRMOGRAPHICS" is partitioned on column "NAME". If the above partitioned method is not working for you and one of your table can be a replicated table, you will not run into this kind of problem.

Please feel free to reply if you have more questions.

Thanks,
Xin