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] [Created] (DRILL-7980) Pushdowns subquery results as parameters
Date Mon, 02 Aug 2021 13:52:00 GMT
Vitalii Diravka created DRILL-7980:
--------------------------------------

             Summary: 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 &amp; Optimization
    Affects Versions: 1.19.0
            Reporter: Vitalii Diravka
            Assignee: Vitalii Diravka
             Fix For: 1.20.0


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