[
https://issues.apache.org/jira/browse/DRILL-207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13758513#comment-13758513
]
Julian Hyde commented on DRILL-207:
-----------------------------------
That is not valid SQL. You can't reference one table in the FROM clause from
another table in the FROM clause. The FROM clause is like "let" in Lisp -- all
of the tables are (in principle) evaluated in parallel, whereas this kind of
correlation requires something more like "let*".
The SQL standard introduces the LATERAL keyword to allow this. Optiq does not
currently support it.
> Correlated sub-query doesn't work
> ---------------------------------
>
> Key: DRILL-207
> URL: https://issues.apache.org/jira/browse/DRILL-207
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Ted Dunning
>
> For this query:
> {code}
> select x.region, x.region_id, y.total
> from
> (select _MAP['R_REGIONKEY'] as region_id, _MAP['R_NAME'] as region from
> "sample-data/region.parquet") x
> join
> (select sum(nation_id) total from
> (select _MAP['N_NATIONKEY'] as nation_id, _MAP['N_REGIONKEY'] region_id
> from "sample-data/nation.parquet") nation
> where region_id = x.region_id) y
> {code}
> I get this message:
> {code}
> Sep 04, 2013 1:36:48 PM org.eigenbase.sql.validate.SqlValidatorException
> <init>
> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'X' not found
> Sep 04, 2013 1:36:48 PM org.eigenbase.util.EigenbaseException <init>
> SEVERE: org.eigenbase.util.EigenbaseContextException: At line 7, column 25
> org.eigenbase.util.EigenbaseContextException: At line 7, column 25
> at
> org.eigenbase.resource.EigenbaseResource$_Def11.ex(EigenbaseResource.java:1006)
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:739)
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:726)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3830)
> at
> org.eigenbase.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:176)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateIdentifier(SqlValidatorImpl.java:2585)
> at org.eigenbase.sql.SqlIdentifier.validateExpr(SqlIdentifier.java:241)
> at org.eigenbase.sql.SqlOperator.validateCall(SqlOperator.java:419)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:3984)
> at org.eigenbase.sql.SqlCall.validate(SqlCall.java:159)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:3230)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:3217)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3018)
> at
> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:69)
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:90)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:802)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:790)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2776)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2761)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:2800)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2770)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3013)
> at
> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:69)
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:90)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:802)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:790)
> at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:154)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:753)
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:444)
> at
> org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:445)
> at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:160)
> at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:129)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:255)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:195)
> at
> net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:168)
> at
> net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
> at
> net.hydromatic.optiq.jdbc.OptiqStatement.execute(OptiqStatement.java:192)
> at sqlline.SqlLine$Commands.execute(SqlLine.java:3825)
> at sqlline.SqlLine$Commands.sql(SqlLine.java:3738)
> at sqlline.SqlLine.dispatch(SqlLine.java:882)
> at sqlline.SqlLine.begin(SqlLine.java:717)
> at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:460)
> at sqlline.SqlLine.main(SqlLine.java:443)
> Caused by: org.eigenbase.sql.validate.SqlValidatorException: Table 'X' not
> found
> at
> org.eigenbase.resource.EigenbaseResource$_Def9.ex(EigenbaseResource.java:963)
> ... 39 more
> {code}
>
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
