You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Vitalii Diravka (Jira)" <ji...@apache.org> on 2021/08/03 07:24:00 UTC
[jira] [Updated] (DRILL-7980) Pushdowns subquery results as
parameters
[ https://issues.apache.org/jira/browse/DRILL-7980?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vitalii Diravka updated DRILL-7980:
-----------------------------------
Description:
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.
was:
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}
> Pushdowns subquery results as parameters
> ----------------------------------------
>
> Key: DRILL-7980
> URL: https://issues.apache.org/jira/browse/DRILL-7980
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)