You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Anup Tiwari <an...@games24x7.com> on 2016/10/14 08:01:12 UTC
[Drill 1.6] : Number format exception due to Empty String
Hi Team,
I got number format exception for empty string and after googling i found
that this issue is bit related to DRILL-1874
<https://issues.apache.org/jira/browse/DRILL-1874> .
Q) Is it fixed in 1.6 ? Since it is very basic problem which can occur
anytime.
After reading comments of drill-1874 , i used *alter session set
planner.safe_cast_varchar = true;* but it is giving me below error, Please
let me know if i am missing anything :
0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
exist.
[Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
(state=,code=0)
java.sql.SQLException: VALIDATION ERROR: The option
'planner.safe_cast_varchar' does not exist.
[Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
at
org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:247)
at
org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:290)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1923)
at
org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:73)
at
net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404)
at
net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:355)
at
net.hydromatic.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:338)
at
net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
at
org.apache.drill.jdbc.impl.DrillStatementImpl.execute(DrillStatementImpl.java:101)
at sqlline.Commands.execute(Commands.java:841)
at sqlline.Commands.sql(Commands.java:751)
at sqlline.SqlLine.dispatch(SqlLine.java:746)
at sqlline.SqlLine.begin(SqlLine.java:621)
at sqlline.SqlLine.start(SqlLine.java:375)
at sqlline.SqlLine.main(SqlLine.java:268)
Caused by: org.apache.drill.common.exceptions.UserRemoteException:
VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not exist.
[Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
at
org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:119)
at
org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:113)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:46)
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:31)
at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:374)
at
org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
at
org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
at
org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:89)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:254)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:242)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
at
io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:847)
at
io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:618)
at
io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:329)
at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:250)
at
io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111)
at java.lang.Thread.run(Thread.java:745)
Also when i am trying to exclude empty string i.e. *col_name <> ''* then it
is excluding null values as well.
Regards,
*Anup Tiwari*
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Jason Altekruse <ja...@dremio.com>.
This option cannot be set at the session level, it must be set at the
system level. There is a JIRA open for giving a warning [1], but
unfortunately right now the alter session statement just acts as a no-op.
You can also write a case statement like this if you don't want to set the
option at the system level.
CASE WHEN trim(column_a) = '' THEN NULL ELSE cast(column_a as INTEGER) END
[1] - https://issues.apache.org/jira/browse/DRILL-3363
Jason Altekruse
Software Engineer at Dremio
Apache Drill Committer
On Sat, Oct 15, 2016 at 6:47 AM, Khurram Faraaz <kf...@maprtech.com>
wrote:
> Anup, can you please
>
> - share your parquet data file (also your CTAS statement that you used to
> create that parquet file)
> - and the SQL query you are running
>
> That way we can run your query against your data file on latest Drill
> 1.9.0, and share results with you.
>
>
>
> On Sat, Oct 15, 2016 at 4:36 PM, Nitin Pawar <ni...@gmail.com>
> wrote:
>
> > not sure if this helps
> > try setting this ..i am nit aware which version it was included
> > alter session set `drill.exec.functions.cast_empty_string_to_null=true;
> >
> > and then you can try cast operation as well
> >
> > On Oct 15, 2016 4:28 PM, "Anup Tiwari" <an...@games24x7.com>
> wrote:
> >
> > > We will surely test this on 1.8 and let you know.. but by looking at
> > JIRA ,
> > > I thought it got fixed in 0.8, is it not the case?
> > > Let me know if i missed anything and pls provide some quick fix which
> can
> > > be applicable just by changing some variable like alter session
> mentioned
> > > in trail mail..
> > >
> > > On 15-Oct-2016 11:35 AM, "Nitin Pawar" <ni...@gmail.com>
> wrote:
> > >
> > > is there an option where you can upgrade to 1.8 and test it?
> > >
> > >
> > > On Sat, Oct 15, 2016 at 10:23 AM, Anup Tiwari <
> anup.tiwari@games24x7.com
> > >
> > > wrote:
> > >
> > > > No.. on a parquet table..
> > > >
> > > > Regards,
> > > > *Anup Tiwari*
> > > >
> > > > On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <
> nitinpawar432@gmail.com>
> > > > wrote:
> > > >
> > > > > are you querying on csv files?
> > > > >
> > > > > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <
> > > anup.tiwari@games24x7.com>
> > > > > wrote:
> > > > >
> > > > > > Hi Team,
> > > > > >
> > > > > > I got number format exception for empty string and after
> googling i
> > > > found
> > > > > > that this issue is bit related to DRILL-1874
> > > > > > <https://issues.apache.org/jira/browse/DRILL-1874> .
> > > > > >
> > > > > > Q) Is it fixed in 1.6 ? Since it is very basic problem which can
> > > occur
> > > > > > anytime.
> > > > > >
> > > > > > After reading comments of drill-1874 , i used *alter session set
> > > > > > planner.safe_cast_varchar = true;* but it is giving me below
> error,
> > > > > Please
> > > > > > let me know if i am missing anything :
> > > > > >
> > > > > >
> > > > > > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar =
> > true*;
> > > > > > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar'
> > does
> > > > not
> > > > > > exist.
> > > > > >
> > > > > >
> > > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> > datanode3:31010]
> > > > > > (state=,code=0)
> > > > > > java.sql.SQLException: VALIDATION ERROR: The option
> > > > > > 'planner.safe_cast_varchar' does not exist.
> > > > > >
> > > > > >
> > > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> > datanode3:31010]
> > > > > >
> > > > > >
> > > > > > at
> > > > > > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > > > > > DrillCursor.java:247)
> > > > > > at
> > > > > > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > > > > > DrillCursor.java:290)
> > > > > > at
> > > > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > > > DrillResultSetImpl.java:1923)
> > > > > > at
> > > > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > > > DrillResultSetImpl.java:73)
> > > > > > at
> > > > > > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > > > > > AvaticaConnection.java:404)
> > > > > > at
> > > > > > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > > > > > AvaticaStatement.java:355)
> > > > > > at
> > > > > > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > > > > > AvaticaStatement.java:338)
> > > > > > at
> > > > > > net.hydromatic.avatica.AvaticaStatement.execute(
> > > > > AvaticaStatement.java:69)
> > > > > > at
> > > > > > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > > > > > DrillStatementImpl.java:101)
> > > > > > at sqlline.Commands.execute(Commands.java:841)
> > > > > > at sqlline.Commands.sql(Commands.java:751)
> > > > > > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > > > > > at sqlline.SqlLine.begin(SqlLine.java:621)
> > > > > > at sqlline.SqlLine.start(SqlLine.java:375)
> > > > > > at sqlline.SqlLine.main(SqlLine.java:268)
> > > > > > Caused by: org.apache.drill.common.exceptions.
> UserRemoteException:
> > > > > > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> > > > exist.
> > > > > >
> > > > > >
> > > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> > datanode3:31010]
> > > > > >
> > > > > >
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > > > > > QueryResultHandler.java:119)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > > > > > UserClient.java:113)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > > > BasicClientWithConnection.java:46)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > > > BasicClientWithConnection.java:31)
> > > > > > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > > > > > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> > > > > RpcBus.java:374)
> > > > > > at
> > > > > > org.apache.drill.common.SerializedExecutor$
> RunnableProcessor.run(
> > > > > > SerializedExecutor.java:89)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(
> > > RpcBus.java:252)
> > > > > > at
> > > > > > org.apache.drill.common.SerializedExecutor.execute(
> > > > > > SerializedExecutor.java:123)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > > > RpcBus.java:285)
> > > > > > at
> > > > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > > > RpcBus.java:257)
> > > > > > at
> > > > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > > > MessageToMessageDecoder.java:89)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.
> invokeChannelRead(
> > > > > > AbstractChannelHandlerContext.java:339)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > > AbstractChannelHandlerContext.java:324)
> > > > > > at
> > > > > > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > > > > > IdleStateHandler.java:254)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.
> invokeChannelRead(
> > > > > > AbstractChannelHandlerContext.java:339)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > > AbstractChannelHandlerContext.java:324)
> > > > > > at
> > > > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > > > MessageToMessageDecoder.java:103)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.
> invokeChannelRead(
> > > > > > AbstractChannelHandlerContext.java:339)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > > AbstractChannelHandlerContext.java:324)
> > > > > > at
> > > > > > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > > > > > ByteToMessageDecoder.java:242)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.
> invokeChannelRead(
> > > > > > AbstractChannelHandlerContext.java:339)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > > AbstractChannelHandlerContext.java:324)
> > > > > > at
> > > > > > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > > > > > ChannelInboundHandlerAdapter.java:86)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.
> invokeChannelRead(
> > > > > > AbstractChannelHandlerContext.java:339)
> > > > > > at
> > > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > > AbstractChannelHandlerContext.java:324)
> > > > > > at
> > > > > > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > > > > > DefaultChannelPipeline.java:847)
> > > > > > at
> > > > > > io.netty.channel.epoll.AbstractEpollStreamChannel$
> > EpollStreamUnsafe.
> > > > > > epollInReady(AbstractEpollStreamChannel.java:618)
> > > > > > at
> > > > > > io.netty.channel.epoll.EpollEventLoop.processReady(
> > > > > > EpollEventLoop.java:329)
> > > > > > at io.netty.channel.epoll.EpollEventLoop.run(
> > > > > EpollEventLoop.java:250)
> > > > > > at
> > > > > > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > > > > > run(SingleThreadEventExecutor.java:111)
> > > > > > at java.lang.Thread.run(Thread.java:745)
> > > > > >
> > > > > >
> > > > > > Also when i am trying to exclude empty string i.e. *col_name <>
> ''*
> > > > then
> > > > > it
> > > > > > is excluding null values as well.
> > > > > >
> > > > > > Regards,
> > > > > > *Anup Tiwari*
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Nitin Pawar
> > > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Nitin Pawar
> > >
> >
>
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Khurram Faraaz <kf...@maprtech.com>.
Anup, can you please
- share your parquet data file (also your CTAS statement that you used to
create that parquet file)
- and the SQL query you are running
That way we can run your query against your data file on latest Drill
1.9.0, and share results with you.
On Sat, Oct 15, 2016 at 4:36 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> not sure if this helps
> try setting this ..i am nit aware which version it was included
> alter session set `drill.exec.functions.cast_empty_string_to_null=true;
>
> and then you can try cast operation as well
>
> On Oct 15, 2016 4:28 PM, "Anup Tiwari" <an...@games24x7.com> wrote:
>
> > We will surely test this on 1.8 and let you know.. but by looking at
> JIRA ,
> > I thought it got fixed in 0.8, is it not the case?
> > Let me know if i missed anything and pls provide some quick fix which can
> > be applicable just by changing some variable like alter session mentioned
> > in trail mail..
> >
> > On 15-Oct-2016 11:35 AM, "Nitin Pawar" <ni...@gmail.com> wrote:
> >
> > is there an option where you can upgrade to 1.8 and test it?
> >
> >
> > On Sat, Oct 15, 2016 at 10:23 AM, Anup Tiwari <anup.tiwari@games24x7.com
> >
> > wrote:
> >
> > > No.. on a parquet table..
> > >
> > > Regards,
> > > *Anup Tiwari*
> > >
> > > On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <ni...@gmail.com>
> > > wrote:
> > >
> > > > are you querying on csv files?
> > > >
> > > > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <
> > anup.tiwari@games24x7.com>
> > > > wrote:
> > > >
> > > > > Hi Team,
> > > > >
> > > > > I got number format exception for empty string and after googling i
> > > found
> > > > > that this issue is bit related to DRILL-1874
> > > > > <https://issues.apache.org/jira/browse/DRILL-1874> .
> > > > >
> > > > > Q) Is it fixed in 1.6 ? Since it is very basic problem which can
> > occur
> > > > > anytime.
> > > > >
> > > > > After reading comments of drill-1874 , i used *alter session set
> > > > > planner.safe_cast_varchar = true;* but it is giving me below error,
> > > > Please
> > > > > let me know if i am missing anything :
> > > > >
> > > > >
> > > > > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar =
> true*;
> > > > > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar'
> does
> > > not
> > > > > exist.
> > > > >
> > > > >
> > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> datanode3:31010]
> > > > > (state=,code=0)
> > > > > java.sql.SQLException: VALIDATION ERROR: The option
> > > > > 'planner.safe_cast_varchar' does not exist.
> > > > >
> > > > >
> > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> datanode3:31010]
> > > > >
> > > > >
> > > > > at
> > > > > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > > > > DrillCursor.java:247)
> > > > > at
> > > > > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > > > > DrillCursor.java:290)
> > > > > at
> > > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > > DrillResultSetImpl.java:1923)
> > > > > at
> > > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > > DrillResultSetImpl.java:73)
> > > > > at
> > > > > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > > > > AvaticaConnection.java:404)
> > > > > at
> > > > > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > > > > AvaticaStatement.java:355)
> > > > > at
> > > > > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > > > > AvaticaStatement.java:338)
> > > > > at
> > > > > net.hydromatic.avatica.AvaticaStatement.execute(
> > > > AvaticaStatement.java:69)
> > > > > at
> > > > > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > > > > DrillStatementImpl.java:101)
> > > > > at sqlline.Commands.execute(Commands.java:841)
> > > > > at sqlline.Commands.sql(Commands.java:751)
> > > > > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > > > > at sqlline.SqlLine.begin(SqlLine.java:621)
> > > > > at sqlline.SqlLine.start(SqlLine.java:375)
> > > > > at sqlline.SqlLine.main(SqlLine.java:268)
> > > > > Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> > > > > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> > > exist.
> > > > >
> > > > >
> > > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on
> datanode3:31010]
> > > > >
> > > > >
> > > > > at
> > > > > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > > > > QueryResultHandler.java:119)
> > > > > at
> > > > > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > > > > UserClient.java:113)
> > > > > at
> > > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > > BasicClientWithConnection.java:46)
> > > > > at
> > > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > > BasicClientWithConnection.java:31)
> > > > > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > > > > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> > > > RpcBus.java:374)
> > > > > at
> > > > > org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> > > > > SerializedExecutor.java:89)
> > > > > at
> > > > > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(
> > RpcBus.java:252)
> > > > > at
> > > > > org.apache.drill.common.SerializedExecutor.execute(
> > > > > SerializedExecutor.java:123)
> > > > > at
> > > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > > RpcBus.java:285)
> > > > > at
> > > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > > RpcBus.java:257)
> > > > > at
> > > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > > MessageToMessageDecoder.java:89)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > > AbstractChannelHandlerContext.java:339)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > AbstractChannelHandlerContext.java:324)
> > > > > at
> > > > > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > > > > IdleStateHandler.java:254)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > > AbstractChannelHandlerContext.java:339)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > AbstractChannelHandlerContext.java:324)
> > > > > at
> > > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > > MessageToMessageDecoder.java:103)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > > AbstractChannelHandlerContext.java:339)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > AbstractChannelHandlerContext.java:324)
> > > > > at
> > > > > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > > > > ByteToMessageDecoder.java:242)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > > AbstractChannelHandlerContext.java:339)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > AbstractChannelHandlerContext.java:324)
> > > > > at
> > > > > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > > > > ChannelInboundHandlerAdapter.java:86)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > > AbstractChannelHandlerContext.java:339)
> > > > > at
> > > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > > AbstractChannelHandlerContext.java:324)
> > > > > at
> > > > > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > > > > DefaultChannelPipeline.java:847)
> > > > > at
> > > > > io.netty.channel.epoll.AbstractEpollStreamChannel$
> EpollStreamUnsafe.
> > > > > epollInReady(AbstractEpollStreamChannel.java:618)
> > > > > at
> > > > > io.netty.channel.epoll.EpollEventLoop.processReady(
> > > > > EpollEventLoop.java:329)
> > > > > at io.netty.channel.epoll.EpollEventLoop.run(
> > > > EpollEventLoop.java:250)
> > > > > at
> > > > > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > > > > run(SingleThreadEventExecutor.java:111)
> > > > > at java.lang.Thread.run(Thread.java:745)
> > > > >
> > > > >
> > > > > Also when i am trying to exclude empty string i.e. *col_name <> ''*
> > > then
> > > > it
> > > > > is excluding null values as well.
> > > > >
> > > > > Regards,
> > > > > *Anup Tiwari*
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Nitin Pawar
> > > >
> > >
> >
> >
> >
> > --
> > Nitin Pawar
> >
>
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Nitin Pawar <ni...@gmail.com>.
not sure if this helps
try setting this ..i am nit aware which version it was included
alter session set `drill.exec.functions.cast_empty_string_to_null=true;
and then you can try cast operation as well
On Oct 15, 2016 4:28 PM, "Anup Tiwari" <an...@games24x7.com> wrote:
> We will surely test this on 1.8 and let you know.. but by looking at JIRA ,
> I thought it got fixed in 0.8, is it not the case?
> Let me know if i missed anything and pls provide some quick fix which can
> be applicable just by changing some variable like alter session mentioned
> in trail mail..
>
> On 15-Oct-2016 11:35 AM, "Nitin Pawar" <ni...@gmail.com> wrote:
>
> is there an option where you can upgrade to 1.8 and test it?
>
>
> On Sat, Oct 15, 2016 at 10:23 AM, Anup Tiwari <an...@games24x7.com>
> wrote:
>
> > No.. on a parquet table..
> >
> > Regards,
> > *Anup Tiwari*
> >
> > On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <ni...@gmail.com>
> > wrote:
> >
> > > are you querying on csv files?
> > >
> > > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <
> anup.tiwari@games24x7.com>
> > > wrote:
> > >
> > > > Hi Team,
> > > >
> > > > I got number format exception for empty string and after googling i
> > found
> > > > that this issue is bit related to DRILL-1874
> > > > <https://issues.apache.org/jira/browse/DRILL-1874> .
> > > >
> > > > Q) Is it fixed in 1.6 ? Since it is very basic problem which can
> occur
> > > > anytime.
> > > >
> > > > After reading comments of drill-1874 , i used *alter session set
> > > > planner.safe_cast_varchar = true;* but it is giving me below error,
> > > Please
> > > > let me know if i am missing anything :
> > > >
> > > >
> > > > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
> > > > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does
> > not
> > > > exist.
> > > >
> > > >
> > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > > > (state=,code=0)
> > > > java.sql.SQLException: VALIDATION ERROR: The option
> > > > 'planner.safe_cast_varchar' does not exist.
> > > >
> > > >
> > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > > >
> > > >
> > > > at
> > > > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > > > DrillCursor.java:247)
> > > > at
> > > > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > > > DrillCursor.java:290)
> > > > at
> > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > DrillResultSetImpl.java:1923)
> > > > at
> > > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > > DrillResultSetImpl.java:73)
> > > > at
> > > > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > > > AvaticaConnection.java:404)
> > > > at
> > > > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > > > AvaticaStatement.java:355)
> > > > at
> > > > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > > > AvaticaStatement.java:338)
> > > > at
> > > > net.hydromatic.avatica.AvaticaStatement.execute(
> > > AvaticaStatement.java:69)
> > > > at
> > > > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > > > DrillStatementImpl.java:101)
> > > > at sqlline.Commands.execute(Commands.java:841)
> > > > at sqlline.Commands.sql(Commands.java:751)
> > > > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > > > at sqlline.SqlLine.begin(SqlLine.java:621)
> > > > at sqlline.SqlLine.start(SqlLine.java:375)
> > > > at sqlline.SqlLine.main(SqlLine.java:268)
> > > > Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> > > > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> > exist.
> > > >
> > > >
> > > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > > >
> > > >
> > > > at
> > > > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > > > QueryResultHandler.java:119)
> > > > at
> > > > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > > > UserClient.java:113)
> > > > at
> > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > BasicClientWithConnection.java:46)
> > > > at
> > > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > > BasicClientWithConnection.java:31)
> > > > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > > > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> > > RpcBus.java:374)
> > > > at
> > > > org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> > > > SerializedExecutor.java:89)
> > > > at
> > > > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(
> RpcBus.java:252)
> > > > at
> > > > org.apache.drill.common.SerializedExecutor.execute(
> > > > SerializedExecutor.java:123)
> > > > at
> > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > RpcBus.java:285)
> > > > at
> > > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> > RpcBus.java:257)
> > > > at
> > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > MessageToMessageDecoder.java:89)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > AbstractChannelHandlerContext.java:339)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > AbstractChannelHandlerContext.java:324)
> > > > at
> > > > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > > > IdleStateHandler.java:254)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > AbstractChannelHandlerContext.java:339)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > AbstractChannelHandlerContext.java:324)
> > > > at
> > > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > > MessageToMessageDecoder.java:103)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > AbstractChannelHandlerContext.java:339)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > AbstractChannelHandlerContext.java:324)
> > > > at
> > > > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > > > ByteToMessageDecoder.java:242)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > AbstractChannelHandlerContext.java:339)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > AbstractChannelHandlerContext.java:324)
> > > > at
> > > > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > > > ChannelInboundHandlerAdapter.java:86)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > > AbstractChannelHandlerContext.java:339)
> > > > at
> > > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > > AbstractChannelHandlerContext.java:324)
> > > > at
> > > > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > > > DefaultChannelPipeline.java:847)
> > > > at
> > > > io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.
> > > > epollInReady(AbstractEpollStreamChannel.java:618)
> > > > at
> > > > io.netty.channel.epoll.EpollEventLoop.processReady(
> > > > EpollEventLoop.java:329)
> > > > at io.netty.channel.epoll.EpollEventLoop.run(
> > > EpollEventLoop.java:250)
> > > > at
> > > > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > > > run(SingleThreadEventExecutor.java:111)
> > > > at java.lang.Thread.run(Thread.java:745)
> > > >
> > > >
> > > > Also when i am trying to exclude empty string i.e. *col_name <> ''*
> > then
> > > it
> > > > is excluding null values as well.
> > > >
> > > > Regards,
> > > > *Anup Tiwari*
> > > >
> > >
> > >
> > >
> > > --
> > > Nitin Pawar
> > >
> >
>
>
>
> --
> Nitin Pawar
>
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Anup Tiwari <an...@games24x7.com>.
We will surely test this on 1.8 and let you know.. but by looking at JIRA ,
I thought it got fixed in 0.8, is it not the case?
Let me know if i missed anything and pls provide some quick fix which can
be applicable just by changing some variable like alter session mentioned
in trail mail..
On 15-Oct-2016 11:35 AM, "Nitin Pawar" <ni...@gmail.com> wrote:
is there an option where you can upgrade to 1.8 and test it?
On Sat, Oct 15, 2016 at 10:23 AM, Anup Tiwari <an...@games24x7.com>
wrote:
> No.. on a parquet table..
>
> Regards,
> *Anup Tiwari*
>
> On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <ni...@gmail.com>
> wrote:
>
> > are you querying on csv files?
> >
> > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <an...@games24x7.com>
> > wrote:
> >
> > > Hi Team,
> > >
> > > I got number format exception for empty string and after googling i
> found
> > > that this issue is bit related to DRILL-1874
> > > <https://issues.apache.org/jira/browse/DRILL-1874> .
> > >
> > > Q) Is it fixed in 1.6 ? Since it is very basic problem which can occur
> > > anytime.
> > >
> > > After reading comments of drill-1874 , i used *alter session set
> > > planner.safe_cast_varchar = true;* but it is giving me below error,
> > Please
> > > let me know if i am missing anything :
> > >
> > >
> > > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
> > > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does
> not
> > > exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > > (state=,code=0)
> > > java.sql.SQLException: VALIDATION ERROR: The option
> > > 'planner.safe_cast_varchar' does not exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > >
> > >
> > > at
> > > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > > DrillCursor.java:247)
> > > at
> > > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > > DrillCursor.java:290)
> > > at
> > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > DrillResultSetImpl.java:1923)
> > > at
> > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > DrillResultSetImpl.java:73)
> > > at
> > > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > > AvaticaConnection.java:404)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > > AvaticaStatement.java:355)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > > AvaticaStatement.java:338)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.execute(
> > AvaticaStatement.java:69)
> > > at
> > > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > > DrillStatementImpl.java:101)
> > > at sqlline.Commands.execute(Commands.java:841)
> > > at sqlline.Commands.sql(Commands.java:751)
> > > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > > at sqlline.SqlLine.begin(SqlLine.java:621)
> > > at sqlline.SqlLine.start(SqlLine.java:375)
> > > at sqlline.SqlLine.main(SqlLine.java:268)
> > > Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> > > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > >
> > >
> > > at
> > > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > > QueryResultHandler.java:119)
> > > at
> > > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > > UserClient.java:113)
> > > at
> > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > BasicClientWithConnection.java:46)
> > > at
> > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > BasicClientWithConnection.java:31)
> > > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> > RpcBus.java:374)
> > > at
> > > org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> > > SerializedExecutor.java:89)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> > > at
> > > org.apache.drill.common.SerializedExecutor.execute(
> > > SerializedExecutor.java:123)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> RpcBus.java:285)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> RpcBus.java:257)
> > > at
> > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > MessageToMessageDecoder.java:89)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > > IdleStateHandler.java:254)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > MessageToMessageDecoder.java:103)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > > ByteToMessageDecoder.java:242)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > > ChannelInboundHandlerAdapter.java:86)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > > DefaultChannelPipeline.java:847)
> > > at
> > > io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.
> > > epollInReady(AbstractEpollStreamChannel.java:618)
> > > at
> > > io.netty.channel.epoll.EpollEventLoop.processReady(
> > > EpollEventLoop.java:329)
> > > at io.netty.channel.epoll.EpollEventLoop.run(
> > EpollEventLoop.java:250)
> > > at
> > > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > > run(SingleThreadEventExecutor.java:111)
> > > at java.lang.Thread.run(Thread.java:745)
> > >
> > >
> > > Also when i am trying to exclude empty string i.e. *col_name <> ''*
> then
> > it
> > > is excluding null values as well.
> > >
> > > Regards,
> > > *Anup Tiwari*
> > >
> >
> >
> >
> > --
> > Nitin Pawar
> >
>
--
Nitin Pawar
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Nitin Pawar <ni...@gmail.com>.
is there an option where you can upgrade to 1.8 and test it?
On Sat, Oct 15, 2016 at 10:23 AM, Anup Tiwari <an...@games24x7.com>
wrote:
> No.. on a parquet table..
>
> Regards,
> *Anup Tiwari*
>
> On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <ni...@gmail.com>
> wrote:
>
> > are you querying on csv files?
> >
> > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <an...@games24x7.com>
> > wrote:
> >
> > > Hi Team,
> > >
> > > I got number format exception for empty string and after googling i
> found
> > > that this issue is bit related to DRILL-1874
> > > <https://issues.apache.org/jira/browse/DRILL-1874> .
> > >
> > > Q) Is it fixed in 1.6 ? Since it is very basic problem which can occur
> > > anytime.
> > >
> > > After reading comments of drill-1874 , i used *alter session set
> > > planner.safe_cast_varchar = true;* but it is giving me below error,
> > Please
> > > let me know if i am missing anything :
> > >
> > >
> > > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
> > > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does
> not
> > > exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > > (state=,code=0)
> > > java.sql.SQLException: VALIDATION ERROR: The option
> > > 'planner.safe_cast_varchar' does not exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > >
> > >
> > > at
> > > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > > DrillCursor.java:247)
> > > at
> > > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > > DrillCursor.java:290)
> > > at
> > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > DrillResultSetImpl.java:1923)
> > > at
> > > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > > DrillResultSetImpl.java:73)
> > > at
> > > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > > AvaticaConnection.java:404)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > > AvaticaStatement.java:355)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > > AvaticaStatement.java:338)
> > > at
> > > net.hydromatic.avatica.AvaticaStatement.execute(
> > AvaticaStatement.java:69)
> > > at
> > > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > > DrillStatementImpl.java:101)
> > > at sqlline.Commands.execute(Commands.java:841)
> > > at sqlline.Commands.sql(Commands.java:751)
> > > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > > at sqlline.SqlLine.begin(SqlLine.java:621)
> > > at sqlline.SqlLine.start(SqlLine.java:375)
> > > at sqlline.SqlLine.main(SqlLine.java:268)
> > > Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> > > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> exist.
> > >
> > >
> > > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > >
> > >
> > > at
> > > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > > QueryResultHandler.java:119)
> > > at
> > > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > > UserClient.java:113)
> > > at
> > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > BasicClientWithConnection.java:46)
> > > at
> > > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > > BasicClientWithConnection.java:31)
> > > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> > RpcBus.java:374)
> > > at
> > > org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> > > SerializedExecutor.java:89)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> > > at
> > > org.apache.drill.common.SerializedExecutor.execute(
> > > SerializedExecutor.java:123)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> RpcBus.java:285)
> > > at
> > > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(
> RpcBus.java:257)
> > > at
> > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > MessageToMessageDecoder.java:89)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > > IdleStateHandler.java:254)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > > MessageToMessageDecoder.java:103)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > > ByteToMessageDecoder.java:242)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > > ChannelInboundHandlerAdapter.java:86)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > > AbstractChannelHandlerContext.java:339)
> > > at
> > > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > > AbstractChannelHandlerContext.java:324)
> > > at
> > > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > > DefaultChannelPipeline.java:847)
> > > at
> > > io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.
> > > epollInReady(AbstractEpollStreamChannel.java:618)
> > > at
> > > io.netty.channel.epoll.EpollEventLoop.processReady(
> > > EpollEventLoop.java:329)
> > > at io.netty.channel.epoll.EpollEventLoop.run(
> > EpollEventLoop.java:250)
> > > at
> > > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > > run(SingleThreadEventExecutor.java:111)
> > > at java.lang.Thread.run(Thread.java:745)
> > >
> > >
> > > Also when i am trying to exclude empty string i.e. *col_name <> ''*
> then
> > it
> > > is excluding null values as well.
> > >
> > > Regards,
> > > *Anup Tiwari*
> > >
> >
> >
> >
> > --
> > Nitin Pawar
> >
>
--
Nitin Pawar
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Anup Tiwari <an...@games24x7.com>.
No.. on a parquet table..
Regards,
*Anup Tiwari*
On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> are you querying on csv files?
>
> On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <an...@games24x7.com>
> wrote:
>
> > Hi Team,
> >
> > I got number format exception for empty string and after googling i found
> > that this issue is bit related to DRILL-1874
> > <https://issues.apache.org/jira/browse/DRILL-1874> .
> >
> > Q) Is it fixed in 1.6 ? Since it is very basic problem which can occur
> > anytime.
> >
> > After reading comments of drill-1874 , i used *alter session set
> > planner.safe_cast_varchar = true;* but it is giving me below error,
> Please
> > let me know if i am missing anything :
> >
> >
> > 0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
> > Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> > exist.
> >
> >
> > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> > (state=,code=0)
> > java.sql.SQLException: VALIDATION ERROR: The option
> > 'planner.safe_cast_varchar' does not exist.
> >
> >
> > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> >
> >
> > at
> > org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> > DrillCursor.java:247)
> > at
> > org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> > DrillCursor.java:290)
> > at
> > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > DrillResultSetImpl.java:1923)
> > at
> > org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> > DrillResultSetImpl.java:73)
> > at
> > net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> > AvaticaConnection.java:404)
> > at
> > net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> > AvaticaStatement.java:355)
> > at
> > net.hydromatic.avatica.AvaticaStatement.executeInternal(
> > AvaticaStatement.java:338)
> > at
> > net.hydromatic.avatica.AvaticaStatement.execute(
> AvaticaStatement.java:69)
> > at
> > org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> > DrillStatementImpl.java:101)
> > at sqlline.Commands.execute(Commands.java:841)
> > at sqlline.Commands.sql(Commands.java:751)
> > at sqlline.SqlLine.dispatch(SqlLine.java:746)
> > at sqlline.SqlLine.begin(SqlLine.java:621)
> > at sqlline.SqlLine.start(SqlLine.java:375)
> > at sqlline.SqlLine.main(SqlLine.java:268)
> > Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> > VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not exist.
> >
> >
> > [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> >
> >
> > at
> > org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> > QueryResultHandler.java:119)
> > at
> > org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> > UserClient.java:113)
> > at
> > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > BasicClientWithConnection.java:46)
> > at
> > org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> > BasicClientWithConnection.java:31)
> > at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> > at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(
> RpcBus.java:374)
> > at
> > org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> > SerializedExecutor.java:89)
> > at
> > org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> > at
> > org.apache.drill.common.SerializedExecutor.execute(
> > SerializedExecutor.java:123)
> > at
> > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
> > at
> > org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
> > at
> > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > MessageToMessageDecoder.java:89)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > AbstractChannelHandlerContext.java:339)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > AbstractChannelHandlerContext.java:324)
> > at
> > io.netty.handler.timeout.IdleStateHandler.channelRead(
> > IdleStateHandler.java:254)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > AbstractChannelHandlerContext.java:339)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > AbstractChannelHandlerContext.java:324)
> > at
> > io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> > MessageToMessageDecoder.java:103)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > AbstractChannelHandlerContext.java:339)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > AbstractChannelHandlerContext.java:324)
> > at
> > io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> > ByteToMessageDecoder.java:242)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > AbstractChannelHandlerContext.java:339)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > AbstractChannelHandlerContext.java:324)
> > at
> > io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> > ChannelInboundHandlerAdapter.java:86)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> > AbstractChannelHandlerContext.java:339)
> > at
> > io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> > AbstractChannelHandlerContext.java:324)
> > at
> > io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> > DefaultChannelPipeline.java:847)
> > at
> > io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.
> > epollInReady(AbstractEpollStreamChannel.java:618)
> > at
> > io.netty.channel.epoll.EpollEventLoop.processReady(
> > EpollEventLoop.java:329)
> > at io.netty.channel.epoll.EpollEventLoop.run(
> EpollEventLoop.java:250)
> > at
> > io.netty.util.concurrent.SingleThreadEventExecutor$2.
> > run(SingleThreadEventExecutor.java:111)
> > at java.lang.Thread.run(Thread.java:745)
> >
> >
> > Also when i am trying to exclude empty string i.e. *col_name <> ''* then
> it
> > is excluding null values as well.
> >
> > Regards,
> > *Anup Tiwari*
> >
>
>
>
> --
> Nitin Pawar
>
Re: [Drill 1.6] : Number format exception due to Empty String
Posted by Nitin Pawar <ni...@gmail.com>.
are you querying on csv files?
On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <an...@games24x7.com>
wrote:
> Hi Team,
>
> I got number format exception for empty string and after googling i found
> that this issue is bit related to DRILL-1874
> <https://issues.apache.org/jira/browse/DRILL-1874> .
>
> Q) Is it fixed in 1.6 ? Since it is very basic problem which can occur
> anytime.
>
> After reading comments of drill-1874 , i used *alter session set
> planner.safe_cast_varchar = true;* but it is giving me below error, Please
> let me know if i am missing anything :
>
>
> 0: jdbc:drill:> *alter session set planner.safe_cast_varchar = true*;
> Error: VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not
> exist.
>
>
> [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
> (state=,code=0)
> java.sql.SQLException: VALIDATION ERROR: The option
> 'planner.safe_cast_varchar' does not exist.
>
>
> [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
>
>
> at
> org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(
> DrillCursor.java:247)
> at
> org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(
> DrillCursor.java:290)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:1923)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(
> DrillResultSetImpl.java:73)
> at
> net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(
> AvaticaConnection.java:404)
> at
> net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(
> AvaticaStatement.java:355)
> at
> net.hydromatic.avatica.AvaticaStatement.executeInternal(
> AvaticaStatement.java:338)
> at
> net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69)
> at
> org.apache.drill.jdbc.impl.DrillStatementImpl.execute(
> DrillStatementImpl.java:101)
> at sqlline.Commands.execute(Commands.java:841)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:746)
> at sqlline.SqlLine.begin(SqlLine.java:621)
> at sqlline.SqlLine.start(SqlLine.java:375)
> at sqlline.SqlLine.main(SqlLine.java:268)
> Caused by: org.apache.drill.common.exceptions.UserRemoteException:
> VALIDATION ERROR: The option 'planner.safe_cast_varchar' does not exist.
>
>
> [Error Id: b9a8bcc4-91e3-46e9-8cf8-55aada95383b on datanode3:31010]
>
>
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(
> QueryResultHandler.java:119)
> at
> org.apache.drill.exec.rpc.user.UserClient.handleReponse(
> UserClient.java:113)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:46)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(
> BasicClientWithConnection.java:31)
> at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:374)
> at
> org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(
> SerializedExecutor.java:89)
> at
> org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> at
> org.apache.drill.common.SerializedExecutor.execute(
> SerializedExecutor.java:123)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> MessageToMessageDecoder.java:89)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.handler.timeout.IdleStateHandler.channelRead(
> IdleStateHandler.java:254)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.handler.codec.MessageToMessageDecoder.channelRead(
> MessageToMessageDecoder.java:103)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.handler.codec.ByteToMessageDecoder.channelRead(
> ByteToMessageDecoder.java:242)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.channel.ChannelInboundHandlerAdapter.channelRead(
> ChannelInboundHandlerAdapter.java:86)
> at
> io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(
> AbstractChannelHandlerContext.java:339)
> at
> io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(
> AbstractChannelHandlerContext.java:324)
> at
> io.netty.channel.DefaultChannelPipeline.fireChannelRead(
> DefaultChannelPipeline.java:847)
> at
> io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.
> epollInReady(AbstractEpollStreamChannel.java:618)
> at
> io.netty.channel.epoll.EpollEventLoop.processReady(
> EpollEventLoop.java:329)
> at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:250)
> at
> io.netty.util.concurrent.SingleThreadEventExecutor$2.
> run(SingleThreadEventExecutor.java:111)
> at java.lang.Thread.run(Thread.java:745)
>
>
> Also when i am trying to exclude empty string i.e. *col_name <> ''* then it
> is excluding null values as well.
>
> Regards,
> *Anup Tiwari*
>
--
Nitin Pawar