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 <[email protected]> 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 <[email protected]> > 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" <[email protected]> > 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" <[email protected]> > 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 < > [email protected] > > > > > > wrote: > > > > > > > No.. on a parquet table.. > > > > > > > > Regards, > > > > *Anup Tiwari* > > > > > > > > On Fri, Oct 14, 2016 at 6:23 PM, Nitin Pawar < > [email protected]> > > > > wrote: > > > > > > > > > are you querying on csv files? > > > > > > > > > > On Fri, Oct 14, 2016 at 1:31 PM, Anup Tiwari < > > > [email protected]> > > > > > 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 > > > > > >
