Forum: Other

Post: voltdb subquery

voltdb subquery
tariqrahiman
Nov 20, 2014
Can you give a sample for the VoltDB SubQuery Structure ? I am using below structure but not able to create it successfully. I am referring to http://docs.voltdb.com/UsingVoltDB/sqlref_select.php

SELECT
( SELECT
*
FROM
CONTESTANTS C1) Table_alias
FROM
CONTESTANTS C
xin
Nov 24, 2014
Hi,

First I do not understand what you are trying to do, but let me guess what you want and offer you the suggestions.

1. You may just want to try sub-select from table CONTESTANTS, Then the SQL could be:
SELECT * FROM ( SELECT * FROM CONTESTANTS C1) Table_alias;

2. You may want to do a tricky sub-select join test, then:
SELECT * FROM ( SELECT * FROM CONTESTANTS C1) Table_alias, CONTESTANTS C;

Please let us know if you have more problems.

Thanks,
Xin
tariqrahiman
Nov 24, 2014
Both your sample query gives error message: Submitted query fails parsing. See my attached screenshot
rmorgenstein
Nov 24, 2014
What version of VoltDB are you using?
tariqrahiman
Nov 24, 2014
Build: Voltdb 4.8 voltdb-4.8.0-g895def8
xin
Nov 24, 2014
Weird, the queries work for me through SQLCMD and Studio.

Can you tell me what kind of client do you use? Can you also try our SQLCMD tool or WebStudio tool also?

FYI,
SQLCMD: http://docs.voltdb.com/UsingVoltDB/clisqlcmd.php
Studio: http://stage.voltdb.com/docs/AdminGuide/MonitorChap.php#MonitorWebStudio
tariqrahiman
Nov 24, 2014
I tried both your queries in the VoltDB Web Studio and it works fine. Well, I use Aqua Data Studio to view the results. I think both the WebStudio tool and SQLCMD does NOT rely on the jdbc driver to get the results.
rmorgenstein
Nov 25, 2014
It looks like the JDBC driver is getting confused by the space after the parenthesis.

Try them again without the space before the SELECT:
SELECT * FROM (SELECT * FROM CONTESTANTS C1) Table_alias;
SELECT * FROM (SELECT * FROM CONTESTANTS C1) Table_alias, CONTESTANTS C;

I filed a product bug for this.

Ruth
tariqrahiman
Nov 26, 2014
Thanks Ruth for filing a bug for this. You were correct. Both the queries worked. See attached screenshot.
Also, would it be possible to polish the documentation with Sub Query examples and explanations ? It is confusing to normal users.
Anton
May 27, 2015
I have problem with compilation of Stored Procedure. Compiler says "Unsupported subquery syntax within an expression."
Request looks like this

DELETE FROM Table1 WHERE TableID_1 IN (SELECT TableID_2 FROM Table2)

I tried to use version 4.7, 5.0, 5.2 - it does not work.
jpiekos
May 27, 2015
Hi Anton,

Sub queries are only supported in the SELECT statement at this time.

For reference, see page: http://docs.voltdb.com/UsingVoltDB/sqlref_select.php

The SELECT statement supports subqueries as a table reference in the FROM clause. Subqueries must be enclosed in parentheses and must be assigned a table alias. Note that subqueries are only supported in the SELECT statement; they cannot be used in data manipulation statements such UPDATE or DELETE
Anton
May 27, 2015
Such query is not compiled neither:
SELECT * FROM TABLE_1 WHERE TABLE_ID_1 IN (SELECT TABLE_ID_2 FROM TABLE_2)
jpiekos
May 27, 2015
sub-query support in the IN clause is in our v5.3 release (which is available now on our website). Please upgrade and let us know how it goes.

Thanks,

John
pzhao
May 27, 2015
Anton,

Currently, we do not support delete or other DML syntax with the subquery. We just shipped v5.3 which has increased support for subqueries with select statement. For more v5.3 changes, please see here.

Peter Zhao