drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vitalii Diravka (Jira)" <j...@apache.org>
Subject [jira] [Resolved] (DRILL-7980) Pushdowns subquery results as parameters
Date Thu, 05 Aug 2021 21:35:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-7980?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Vitalii Diravka resolved DRILL-7980.
------------------------------------
      Reviewer: Vova Vysotskyi
    Resolution: Invalid

> Pushdowns subquery results as parameters
> ----------------------------------------
>
>                 Key: DRILL-7980
>                 URL: https://issues.apache.org/jira/browse/DRILL-7980
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Web Server
>    Affects Versions: 1.19.0
>            Reporter: Vitalii Diravka
>            Assignee: Vitalii Diravka
>            Priority: Major
>
> If a sub query is used to generate parameters for an API call you get 400 errors and
the paramters don't show up on URL in the error.
>   
>  For Example:
>  If you have an API config that requires a "q=" paramter and want to populate that with
a sub query like this...
>   
> {code:java}
> SELECT *
> FROM api.example
> WHERE q IN (SELECT col FROM source){code}
> It doesn't work. The expected behavior would be multiple API calls (one for each value
in "col") with the results aggregated back together for the projection.
> Query example:
> {code:java}
> vitalii@vitalii-UX331UN:~/IdeaProjects/drill$ distribution/target/apache-drill-1.20.0-SNAPSHOT/apache-drill-1.20.0-SNAPSHOT/bin/drill-embedded
> Apache Drill 1.20.0-SNAPSHOT
> "A Drill is a terrible thing to waste."
> apache drill> SELECT * FROM cp.`employee.json` where first_name in ('Sheri', 'Derrick');
> +-------------+-----------------+------------+-----------+-------------+--------------------+----------+---------------+------------+-----------------------+---------+---------------+-----------------+----------------+--------+-------------------+
> | employee_id | full_name | first_name | last_name | position_id | position_title | store_id
| department_id | birth_date | hire_date | salary | supervisor_id | education_level | marital_status
| gender | management_role |
> +-------------+-----------------+------------+-----------+-------------+--------------------+----------+---------------+------------+-----------------------+---------+---------------+-----------------+----------------+--------+-------------------+
> | 1 | Sheri Nowmer | Sheri | Nowmer | 1 | President | 0 | 1 | 1961-08-26 | 1994-12-01
00:00:00.0 | 80000.0 | 0 | Graduate Degree | S | F | Senior Management |
> | 2 | Derrick Whelply | Derrick | Whelply | 2 | VP Country Manager | 0 | 1 | 1915-07-03
| 1994-12-01 00:00:00.0 | 40000.0 | 1 | Graduate Degree | M | M | Senior Management |
> +-------------+-----------------+------------+-----------+-------------+--------------------+----------+---------------+------------+-----------------------+---------+---------------+-----------------+----------------+--------+-------------------+
> 2 rows selected (0.156 seconds)
> apache drill> select * from (VALUES('Sheri', 'Derrick'));
> +--------+---------+
> | EXPR$0 | EXPR$1 |
> +--------+---------+
> | Sheri | Derrick |
> +--------+---------+
> 1 row selected (0.143 seconds)
> apache drill> SELECT * FROM cp.`employee.json` where first_name in (select * from
(VALUES('Sheri', 'Derrick')));
> Error: VALIDATION ERROR: From line 1, column 40 to line 1, column 96: Values passed to
IN operator must have compatible types
> [Error Id: 20ac0097-4ed6-4c0d-b272-62960df6831a ] (state=,code=0)
> {code}
> error in logs:
> {code:java}
> 2021-08-02 16:45:10,891 [1ef80919-7587-a1b6-fe56-16f85aa14c1c:foreman] INFO o.a.d.e.p.s.conversion.SqlConverter
- User Error Occurred: From line 1, column 40 to line 1, column 96: Values passed to IN operator
must have compatible types (From line 1, column 40 to line 1, column 96: Values passed to
IN operator must have compatible types)
> org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From line 1, column
40 to line 1, column 96: Values passed to IN operator must have compatible types
> [Error Id: af82b068-fb94-4901-9a0b-20d66e054425 ]
> at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:657)
> at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:196)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:647)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:195)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:169)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:283)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:163)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:128)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:93)
> at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:592)
> at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:273)
> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
> at java.base/java.lang.Thread.run(Thread.java:832)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 40
to line 1, column 96: Values passed to IN operator must have compatible types
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:64)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:835)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:820)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4881)
> at org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperator.java:166)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5640)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5627)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1692)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1677)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4058)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4044)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3386)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:944)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:651)
> at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:189)
> ... 12 common frames omitted
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Values passed to IN
operator must have compatible types
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:64)
> at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
> ... 32 common frames omitted
> 2021-08-02 16:45:10,895 [Client-1] INFO o.a.d.j.i.DrillCursor$ResultsListener - [#1]
Query failed:
> org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1,
column 40 to line 1, column 96: Values passed to IN operator must have compatible types
> [Error Id: af82b068-fb94-4901-9a0b-20d66e054425 ]
> at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:125)
> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:422)
> at org.apache.drill.exec.rpc.user.UserClient.handle(UserClient.java:96)
> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:273)
> at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:243)
> at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
> at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
> at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
> at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
> at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
> at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
> at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
> at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
> at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:655)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:581)
> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
> at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
> at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
> at java.base/java.lang.Thread.run(Thread.java:832)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 40
to line 1, column 96: Values passed to IN operator must have compatible types: Values passed
to IN operator must have compatible types
> at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:64)
> at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at .......(:0)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:835)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:820)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4881)
> at org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperator.java:166)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5640)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5627)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1692)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1677)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4058)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4044)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3386)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:944)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:651)
> at org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:189)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:647)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:195)
> at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:169)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:283)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:163)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:128)
> at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:93)
> at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:592)
> at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:273)
> at .......(:0)
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Values passed to IN
operator must have compatible types
> at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:64)
> at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at .......(:0)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
> ... 30 common frames omitte{code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message