Forum: Building VoltDB Applications

Post: Join table twice

Join table twice
Sebastian
Feb 16, 2012
Suppose there are two tables, A and B. Table A has an Id column. B has a FirstID column and a SecondID column.

Table B basically has a reference to a pair as well as additional information related to a pair.

Is it possible to join B.FirstId on A and B.SecondId on A as in the following query?

SELECT ...
FROM A as First, A as Second, B
WHERE B.FirstId = First.Id AND B.SecondId = Second.Id


Catalog compilations fails since voltcompiler is "unable to plan for statement".

Is there a workaround for such a query?
Joining a table twice
rbetts
Feb 23, 2012
Hello Sebastian,
First - I apologize for the delay in answering this question.

I'm assuming that your ID table is partitioned and that the PAIRS table is replicated?
Possibly the planner detects this as the join of two partitioned tables - which is not currently supported? There should be a much better error message, however. I can reproduce the error you describe. I filed this as a defect to investigate (https://issues.voltdb.com/browse/ENG-2522).
I'll raise this in the office this morning and see if others have a better idea. The only workaround I can think of is to do two joins and manually rejoin the results. Which is unsatisfying.
Using the voter example, I added this replicated table:
create table votepairs (pn1 bigint not null, pn2 bigint not null, attr1 integer not null);
And inserted this data:
PHONE_NUMBER STATE CONTESTANT_NUMBER
------------- ------ ------------------
2 nh 200
1 ma 100
3 az 300
8> select * from votepairs;
PN1 PN2 ATTR1
---- ---- ------
1 2 1000
2 3 2000
2 2 4000
I get these results from some examples similar to your:
13> select * from votes as A, votepairs where A.phone_number = votepairs.pn1 and A.phone_number = votepairs.pn2;
PHONE_NUMBER STATE CONTESTANT_NUMBER PN1 PN2 ATTR1
------------- ------ ------------------ ---- ---- ------
2 nh 200 2 2 4000
14> select * from votes as A, votepairs where A.phone_number = votepairs.pn1 and votepairs.pn2 = 2;
PHONE_NUMBER STATE CONTESTANT_NUMBER PN1 PN2 ATTR1
------------- ------ ------------------ ---- ---- ------
1 ma 100 1 2 1000
2 nh 200 2 2 4000
15> select * from votes as A, votes as B, votepairs where A.phone_number = votepairs.pn1 and B.phone_number = 2;
Unexpected Ad Hoc Planning Error: ERROR: Unable to plan for statement. Error unknown.
I added a second partitioned table, votes2, that is an exact copy of votes (schema and data) and tried this:
15> select * from votes, votes2, votepairs where votes.phone_number=votepairs.pn1 and votes2.phone_number=votepairs.pn2;
Unexpected Ad Hoc Planning Error: ERROR: Unable to plan for statement. Likely statement is joining two partitioned tables in a multi-partition statement. This is not supported at this time.