Forum: Building VoltDB Applications

Post: Support migrating a query having multiple self-joins

Support migrating a query having multiple self-joins
prashanth
Dec 4, 2013
Our current query from Timesten has multiple self and outer joins as shown below:

SELECT a.PARENTTYPE,a.PARENTVALUE,b.PARENTTYPE,b.PARENTVALUE,c.PARENTTYPE,c.PARENTVALUE,d.PARENTTYPE,d.PARENTVALUE from idstorage d, idstorage c, idstorage b,idstorage a where d.LASTOPTIMESTAMP(+) > 0 AND d.CHILDVALUE(+)=c.PARENTVALUE AND d.CHILDTYPE (+)=c.PARENTTYPE AND c.LASTOPTIMESTAMP(+) > 0 AND c.CHILDVALUE(+)=b.PARENTVALUE AND c.CHILDTYPE (+)=b.PARENTTYPE AND b.LASTOPTIMESTAMP(+) > 0 AND b.CHILDVALUE(+)=a.PARENTVALUE AND b.CHILDTYPE (+)=a.PARENTTYPE AND a.LASTOPTIMESTAMP(+) > 0 AND a.PARENTVALUE= ? AND a.PARENTTYPE= ?

Though it is advised to replace self joins with joins over views, how do we solve the issue of having LEFT OUTER JOINS more than once ?
Appreciate help in this regard.
pmartel
Dec 4, 2013
VoltDB supports the sql standard query syntax for outer joins, so your query would have to be something like:

SELECT a.PARENTTYPE,a.PARENTVALUE, b.PARENTTYPE,b.PARENTVALUE, c.PARENTTYPE,c.PARENTVALUE, d.PARENTTYPE,d.PARENTVALUE
from idstorage d right join idstorage c
on d.LASTOPTIMESTAMP > 0 AND d.CHILDVALUE = c.PARENTVALUE AND d.CHILDTYPE = c.PARENTTYPE
right join idstorage b,
on c.LASTOPTIMESTAMP > 0 AND c.CHILDVALUE = b.PARENTVALUE AND c.CHILDTYPE = b.PARENTTYPE
right join idstorage a
on b.LASTOPTIMESTAMP > 0 AND b.CHILDVALUE = a.PARENTVALUE AND b.CHILDTYPE = a.PARENTTYPE
where a.LASTOPTIMESTAMP > 0 AND a.PARENTVALUE = ? AND a.PARENTTYPE = ?;

I say "something like" for 2 reasons:
- as you pointed out, until our upcoming release 4.0, VoltDB does not support self-join, so some use of materialized views or other form of data duplication is required
within the SQL -- a more effective solution might be to write a VoltDB stored procedure that used java code to combine simpler queries to avoid the self-join -- or at least limit the queries to pair-wise self-joins against a base table and a single view to cut down on storage redundancy. VoltDB's extension to support variable-length IN LISTS passed as java arrays into parameters using the syntax "CHILDVALUE IN ?" might be be useful here.
- I'm not sure what the effect of "a.LASTOPTIMESTAMP(+) > 0" is vs. "a.LASTOPTIMESTAMP > 0" since it doesn't seem like a proper join filter, and with the other filters in place on "a", I don't think the "(+)" matters, here. Strictly speaking, if there IS a difference, my re-write implements the latter condition.

--paul
prashanth
Dec 5, 2013
Hi paul,

Thanks for the reply. one question is does VoltDB support OUTER JOINS ? If it supports, whats the limit on the number of table joins ?
pmartel
Dec 5, 2013
Yes, VoltDB supports left and right outer joins using the standard LEFT JOIN and RIGHT JOIN syntax (as shown). The number of joined tables is limited to 5 except in the context of a java stored procedure where the SQLStmt constructor allows specifying an explicit table join order.
There is no limit to the number of tables when the join order is explicitly specified. For example,

public final SQLStmt bigJoin = new SQLStmt("SELECT * from A LEFT JOIN B ON (A.b = B.b) LEFT JOIN C ON (A.c = C.c) LEFT JOIN D ON (A.d = D.d), LEFT JOIN E ON (A.e = E.e) LEFT JOIN F ON (A.f = F.f) where A.a = ?", "A,B,C,D,E,F");
prashanth
Dec 6, 2013
Are there plans to support more than 5 tables in the upcoming release of VoltDB ?
prashanth
Dec 16, 2013
Is partitioning supported for materialized views also ? can it run with k=safety model ?
jpiekos
Dec 16, 2013
Yes, Materialized Views are partitioned (via the table they are defined on). And yes, they work with k-safety.

You can see our voter sample (examples/voter), for an example.

John