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 <kfar...@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 <nitinpawar...@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" <anup.tiw...@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" <nitinpawar...@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.tiw...@games24x7.com
> > >
> > > wrote:
> > >
> > > > No.. on a parquet table..
> > > >
> > > > Regards,
> > > > *Anup Tiwari*
> > > >
> > > > On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar <
> nitinpawar...@gmail.com>
> > > > wrote:
> > > >
> > > > > are you querying on csv files?
> > > > >
> > > > > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari <
> > > anup.tiw...@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
> > >
> >
>

Reply via email to