SQL - Insufficient Join Criteria
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!
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!