Using JOIN with parenthesis in FROM clause is failing

Dremio University
1

I got “Failure parsing the query.” for using parenthesis in the FROM clause.

Do the following to reproduce:
I used the Samples dataset (“Samples.samples.dremio.com.Dremio University.D102 - Data Reflections”)

  1. Create 3 VDSs:
    CREATE VDS TEST.sample_data AS SELECT * FROM sample_data
    CREATE VDS TEST.payment_lookup AS SELECT * FROM “payment_lookup.csv”
    CREATE VDS TEST.vendor_lookup AS SELECT * FROM “vendor_lookup.csv”

  2. Run the next query:
    SELECT *
    FROM
    (
    TEST.“vendor_lookup” as vendor_lookup
    JOIN TEST.“sample_data” as sample_data1
    ON sample_data1.vendor_id = vendor_lookup.vendor_id
    )
    JOIN
    (
    TEST.“sample_data” as sample_data2
    JOIN TEST.“payment_lookup” AS payment_lookup
    ON sample_data2.payment_type = payment_lookup.payment_type
    )
    ON vendor_lookup.vendor_id = sample_data2.vendor_id

  3. results: “PARSE ERROR: Failure parsing the query.”

Output from the profiler (VERBOSE):

     PARSE ERROR: Failure parsing the query.

SQL Query SELECT *
FROM
(
TEST.“vendor_lookup” as vendor_lookup
JOIN TEST.“sample_data” as sample_data1
ON sample_data1.vendor_id = vendor_lookup.vendor_id
)
JOIN
(
TEST.“sample_data” as sample_data2
JOIN TEST.“payment_lookup” AS payment_lookup
ON sample_data2.payment_type = payment_lookup.payment_type
)
ON vendor_lookup.vendor_id = sample_data2.vendor_id
startLine 4
startColumn 14
endLine 4
endColumn 28
SQL Query SELECT *
FROM
(
TEST.“vendor_lookup” as vendor_lookup
JOIN TEST.“sample_data” as sample_data1
ON sample_data1.vendor_id = vendor_lookup.vendor_id
)
JOIN
(
TEST.“sample_data” as sample_data2
JOIN TEST.“payment_lookup” AS payment_lookup
ON sample_data2.payment_type = payment_lookup.payment_type
)
ON vendor_lookup.vendor_id = sample_data2.vendor_id

(org.apache.calcite.sql.parser.SqlParseException) Non-query expression encountered in illegal context
com.dremio.exec.planner.sql.parser.impl.ParserImpl.convertException():349
com.dremio.exec.planner.sql.parser.impl.ParserImpl.normalizeException():135
org.apache.calcite.sql.parser.SqlParser.handleException():130
org.apache.calcite.sql.parser.SqlParser.parseStmtList():185
com.dremio.exec.planner.sql.SqlConverter.parseMultipleStatementsImpl():184
com.dremio.exec.planner.sql.SqlConverter.parseSingleStatementImpl():199
com.dremio.exec.planner.sql.SqlConverter.parse():215
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():280
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():232
com.dremio.exec.work.foreman.AttemptManager.plan():449
com.dremio.exec.work.foreman.AttemptManager.lambda$run$1():361
com.dremio.service.commandpool.CommandWrapper.run():62
com.dremio.context.RequestContext.run():95
com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$3():199
com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():180
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
Caused By (org.apache.calcite.runtime.CalciteException) Non-query expression encountered in illegal context
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():62
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():803
org.apache.calcite.sql.SqlUtil.newContextException():788
com.dremio.exec.planner.sql.parser.impl.ParserImpl.checkNonQueryExpression():293
com.dremio.exec.planner.sql.parser.impl.ParserImpl.Expression3():4673
com.dremio.exec.planner.sql.parser.impl.ParserImpl.Expression2b():4419
com.dremio.exec.planner.sql.parser.impl.ParserImpl.Expression2():4445
com.dremio.exec.planner.sql.parser.impl.ParserImpl.Expression():4398
com.dremio.exec.planner.sql.parser.impl.ParserImpl.LeafQueryOrExpr():4380
com.dremio.exec.planner.sql.parser.impl.ParserImpl.QueryOrExpr():4302
com.dremio.exec.planner.sql.parser.impl.ParserImpl.OrderedQueryOrExpr():465
com.dremio.exec.planner.sql.parser.impl.ParserImpl.ParenthesizedExpression():585
com.dremio.exec.planner.sql.parser.impl.ParserImpl.TableRef2():3162
com.dremio.exec.planner.sql.parser.impl.ParserImpl.TableRef():3106
com.dremio.exec.planner.sql.parser.impl.ParserImpl.FromClause():3007
com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlSelect():2268
com.dremio.exec.planner.sql.parser.impl.ParserImpl.LeafQuery():554
com.dremio.exec.planner.sql.parser.impl.ParserImpl.LeafQueryOrExpr():4383
com.dremio.exec.planner.sql.parser.impl.ParserImpl.QueryOrExpr():4302
com.dremio.exec.planner.sql.parser.impl.ParserImpl.OrderedQueryOrExpr():465
com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlStmt():905
com.dremio.exec.planner.sql.parser.impl.ParserImpl.SqlStmtList():841
com.dremio.exec.planner.sql.parser.impl.ParserImpl.parseSqlStmtList():186
com.dremio.exec.planner.sql.parser.impl.ParserWithCompoundIdConverter.parseSqlStmtList():73
org.apache.calcite.sql.parser.SqlParser.parseStmtList():183
com.dremio.exec.planner.sql.SqlConverter.parseMultipleStatementsImpl():184
com.dremio.exec.planner.sql.SqlConverter.parseSingleStatementImpl():199
com.dremio.exec.planner.sql.SqlConverter.parse():215
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.getSqlCommand():280
com.dremio.exec.planner.sql.handlers.commands.CommandCreator.toCommand():232
com.dremio.exec.work.foreman.AttemptManager.plan():449
com.dremio.exec.work.foreman.AttemptManager.lambda$run$1():361
com.dremio.service.commandpool.CommandWrapper.run():62
com.dremio.context.RequestContext.run():95
com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$3():199
com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():180
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748