You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Abhishek Girish (JIRA)" <ji...@apache.org> on 2019/01/08 00:54:00 UTC
[jira] [Comment Edited] (DRILL-6924) Validation error - Column
ambiguous in join queries
[ https://issues.apache.org/jira/browse/DRILL-6924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16736535#comment-16736535 ]
Abhishek Girish edited comment on DRILL-6924 at 1/8/19 12:53 AM:
-----------------------------------------------------------------
I agree - I think this behavior is expected. You have a '*' in your projection list and referring to that column in the outer query. + aliasing needs to be used. If you change that it works:
Ex:
{code}
> select c.c_name from customer c left outer join (select o.o_orderkey, o.o_custkey,* from orders o, lineitem l where o.o_custkey = l.l_partkey ) as o1 on c.c_custkey = o1.o_custkey order by o1.o_orderkey limit 2;
+---------------------+
| c_name |
+---------------------+
| Customer#000000370 |
| Customer#000000370 |
+---------------------+
2 rows selected (0.782 seconds)
{code}
was (Author: agirish):
I agree - I think this behavior is expected. You have a '*' in your projection list and referring to that column in the outer query. + aliasing needs to be used. If you change that it works:
Ex:
{code}
> set planner.enable_nljoin_for_scalar_only = false;
+-------+-------------------------------------------------+
| ok | summary |
+-------+-------------------------------------------------+
| true | planner.enable_nljoin_for_scalar_only updated. |
+-------+-------------------------------------------------+
1 row selected (0.114 seconds)
{code}
{code}
> select c.c_name from customer c left outer join (select o.o_orderkey, o.o_custkey,* from orders o, lineitem l where o.o_custkey = l.l_partkey ) as o1 on c.c_custkey = o1.o_custkey order by o1.o_orderkey limit 2;
+---------------------+
| c_name |
+---------------------+
| Customer#000000370 |
| Customer#000000370 |
+---------------------+
2 rows selected (0.782 seconds)
{code}
> Validation error - Column ambiguous in join queries
> ---------------------------------------------------
>
> Key: DRILL-6924
> URL: https://issues.apache.org/jira/browse/DRILL-6924
> Project: Apache Drill
> Issue Type: Bug
> Affects Versions: 1.15.0
> Reporter: Kedar Sankar Behera
> Assignee: Hanumath Rao Maduri
> Priority: Major
>
> Happen with not only with cross join but others as well
> the examples are given below.
> q1 -
> {code}
> select * from customer c cross join (select * from orders, lineitem where orders.o_custkey = lineitem.l_partkey ) as o1 order by o1.o_orderkey limit 2;
> {code}
> Result -
> {code}
> Error: VALIDATION ERROR: From line 1, column 170 to line 1, column 171: Column 'o_orderkey' is ambiguous
> [Error Id: eff986cd-50e1-47e6-a848-b0976bf44bff on drill182:31010] (state=,code=0)
> java.sql.SQLException: VALIDATION ERROR: From line 1, column 170 to line 1, column 171: Column 'o_orderkey' is ambiguous
> [Error Id: eff986cd-50e1-47e6-a848-b0976bf44bff on drill182:31010]
> at org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:536)
> at org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:608)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1288)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:61)
> at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:667)
> at org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1109)
> at org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1120)
> at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at org.apache.drill.jdbc.impl.DrillConnectionImpl.prepareAndExecuteInternal(DrillConnectionImpl.java:196)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
> at sqlline.Commands.execute(Commands.java:814)
> at sqlline.Commands.sql(Commands.java:754)
> at sqlline.SqlLine.dispatch(SqlLine.java:646)
> at sqlline.SqlLine.begin(SqlLine.java:510)
> at sqlline.SqlLine.start(SqlLine.java:233)
> at sqlline.SqlLine.main(SqlLine.java:175)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 170 to line 1, column 171: Column 'o_orderkey' is ambiguous
> [Error Id: eff986cd-50e1-47e6-a848-b0976bf44bff on drill182:31010]
> at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
> 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:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
> at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
> at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
> at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
> at java.lang.Thread.run(Thread.java:748)
> {code}
> q2-
> {code}
> select * from customer c, (select * from orders, lineitem where orders.o_custkey = lineitem.l_partkey) as o1 where c.c_custkey = o1.o_custkey order by o1.o_orderkey limit 2;
> {code}
> result -
> {code}
> Error: VALIDATION ERROR: From line 1, column 172 to line 1, column 173: Column 'o_custkey' is ambiguous
> [Error Id: 484639a7-e72a-46b8-9a0c-bfd3702b5a81 on drill182:31010] (state=,code=0)
> java.sql.SQLException: VALIDATION ERROR: From line 1, column 172 to line 1, column 173: Column 'o_custkey' is ambiguous
> [Error Id: 484639a7-e72a-46b8-9a0c-bfd3702b5a81 on drill182:31010]
> at org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:536)
> at org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:608)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1288)
> at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:61)
> at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:667)
> at org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1109)
> at org.apache.drill.jdbc.impl.DrillMetaImpl.prepareAndExecute(DrillMetaImpl.java:1120)
> at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at org.apache.drill.jdbc.impl.DrillConnectionImpl.prepareAndExecuteInternal(DrillConnectionImpl.java:196)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
> at sqlline.Commands.execute(Commands.java:814)
> at sqlline.Commands.sql(Commands.java:754)
> at sqlline.SqlLine.dispatch(SqlLine.java:646)
> at sqlline.SqlLine.begin(SqlLine.java:510)
> at sqlline.SqlLine.start(SqlLine.java:233)
> at sqlline.SqlLine.main(SqlLine.java:175)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 172 to line 1, column 173: Column 'o_custkey' is ambiguous
> [Error Id: 484639a7-e72a-46b8-9a0c-bfd3702b5a81 on drill182:31010]
> at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:123)
> 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:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:102)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:312)
> at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:286)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335)
> at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356)
> at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342)
> at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911)
> at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
> at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580)
> at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497)
> at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459)
> at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131)
> at java.lang.Thread.run(Thread.java:748)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)