Forum: Building VoltDB Applications

Post: Union is not working in voltdb 3.7

Union is not working in voltdb 3.7
tariqrahiman
Jun 13, 2014
This has taken a lot of time so far - to understand the union clause in VoltDB and we are not able to figure it out. It would be really great if you could shed some light on this. I searched the Forum and couldnt find anything related.

According to the Documentation:

The UNION, INTERSECT, and EXCEPT operations obey the same rules that apply to joins:

All tables in the SELECT statements must either be replicated tables or partitioned tables partitioned on the same column value, using equality of the partitioning column in the WHERE clause

Sample tables from Documentation

CREATE TABLE Inventory (
Company VARCHAR(32) NOT NULL,
ProductID BIGINT NOT NULL,
Price DECIMAL,
Category VARCHAR(32),
Description VARCHAR(256),
PRIMARY KEY (Company, ProductID)
);
PARTITION TABLE Inventory ON COLUMN Company;

CREATE TABLE Inventory1 (
Company VARCHAR(32) NOT NULL,
ProductID BIGINT NOT NULL,
Price DECIMAL,
Category VARCHAR(32),
Description VARCHAR(256),
PRIMARY KEY (Company, ProductID)
);
PARTITION TABLE Inventory1 ON COLUMN Company;


Our Union SQL

SELECT * FROM INVENTORY WHERE COMPANY='APPLE'
UNION
SELECT * FROM INVENTORY1 WHERE COMPANY='APPLE'


Error message from voltdb
Error: Unexpected Ad Hoc Planning Error: java.lang.RuntimeException: Error compiling query: org.voltdb.planner.PlanningErrorException: unexpected end of statement


SELECT Company FROM INVENTORY WHERE COMPANY='APPLE'
UNION
SELECT Company FROM INVENTORY1 WHERE COMPANY='APPLE'
pmartel
Jun 13, 2014
This appears to be a known issue in our interactive ad hoc sql front ends -- are you entering this query into sqlcmd?

It may be possible to get the front end to accept the union syntax by wrapping parentheses around the second select -- with no space between the '(' and the 'SELECT'.
So, your query would be:

SELECT Company FROM INVENTORY WHERE COMPANY='APPLE'
UNION
(SELECT Company FROM INVENTORY1 WHERE COMPANY='APPLE' )

The problem is that the sqlcmd parser sees the second "SELECT" as a start of a new statement.
This should not be a problem when using our programmatic client API.

Also, I believe that this has been corrected in a recent release of VoltDB.
tariqrahiman
Jun 13, 2014
Thanks.. that worked
Tariq