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/05 21:36:00 UTC
[jira] [Comment Edited] (DRILL-7980) Pushdowns subquery results as
parameters
[ https://issues.apache.org/jira/browse/DRILL-7980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17394315#comment-17394315 ]
Vitalii Diravka edited comment on DRILL-7980 at 8/5/21, 9:35 PM:
-----------------------------------------------------------------
Looks like the query works fine (thanks [~volodymyr] for pointing this):
{code:java}
apache drill> SELECT first_name FROM cp.`employee.json` where first_name in (SELECT first_name FROM cp.`employee.json` where first_name in ('Sheri', 'Derrick'));
+------------+
| first_name |
+------------+
| Sheri |
| Derrick |
+------------+{code}
the query with start can't be submitted, because the number of columns are unknown:
{code:java}
apache drill> SELECT * FROM cp.`employee.json` where first_name in (SELECT * FROM cp.`employee.json` where first_name in ('Sheri', 'Derrick'));
Error: SYSTEM ERROR: NullPointerException: Cannot invoke "org.apache.drill.exec.record.RecordBatchSizer$ColumnSize.getStdNetOrNetSizePerEntry()" because "columnSize" is null{code}
For HTTP plugin the planner works in the same manner.
Mark the ticket as invalid.
was (Author: vitalii):
Looks like the query works fine (thanks [~volodymyr] for pointing this):
{code:java}
apache drill> SELECT first_name FROM cp.`employee.json` where first_name in (SELECT first_name FROM cp.`employee.json` where first_name in ('Sheri', 'Derrick'));
+------------+
| first_name |
+------------+
| Sheri |
| Derrick |
+------------+{code}
the query with start can't be submitted, because the number of columns are unknown:
{code:java}
apache drill> SELECT * FROM cp.`employee.json` where first_name in (SELECT * FROM cp.`employee.json` where first_name in ('Sheri', 'Derrick'));
Error: SYSTEM ERROR: NullPointerException: Cannot invoke "org.apache.drill.exec.record.RecordBatchSizer$ColumnSize.getStdNetOrNetSizePerEntry()" because "columnSize" is null{code}
Mark the ticket as 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: 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.
> 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)