You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Nathaniel Auvil <na...@gmail.com> on 2015/09/10 18:49:21 UTC
NullPointers in type conversions
i have a MaprDB table which has nulls for some of the columns. When i
create a select where i CAST(table.cf.myColumn as INTEGER) and there are
nulls instead of simply returning null for the value, there is a
NullPointerException.
Why not simply return null for the value if it is missing from a row?
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Jason, using trim() did the trick:
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(trim(x.`a`.`c1`) as INTEGER) from maprfs.cmatta.`cmatta_test`
. . . . . . . > x ;
+----------+---------+
| row_key | EXPR$1 |
+----------+---------+
| row1 | 1 |
| row2 | null |
| row3 | 5 |
| row4 | 7 |
+----------+---------+
4 rows selected (0.542 seconds)
0: jdbc:drill:>
Chris Matta
cmatta@mapr.com
215-701-3146
On Wed, Sep 23, 2015 at 5:15 PM, Jason Altekruse <al...@gmail.com>
wrote:
> I actually noticed while I was testing this out that it is not working with
> casts to date, I will be opening a JIRA for it. That being said, I did find
> a unit test for integer, so this should be working. It is possible that the
> data in the column is actually a series of spaces instead of the empty
> string. Could you try to throw a call to trim() inside of the cast?
>
> It is also possible that this system option might only impact the cast
> functions from varchar to the various other types like double, int, etc.
> All of the data read out of HBase is placed in the varbinary type. We might
> have decent handling if the data is stored in Hbase in the format we happen
> to assume in the cast varbinary to int implementation, but we generally
> recommend using the convert_from functions when dealing with binary. I
> don't know exactly how this option was implemented, so it might only apply
> with varchar. We were primarily focused on issues with lots of case
> statements when querying text files when it was added.
>
> - Jason
>
> On Wed, Sep 23, 2015 at 1:49 PM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > This system setting was set, yet I still experience the
> > NumberFormatException with MapR-DB when a column contains a NULL value:
> >
> > 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> > CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> > . . . . . . . > x;
> > +----------+---------+
> > | row_key | EXPR$1 |
> > +----------+---------+
> > | row1 | 1 |
> > | row2 | |
> > | row3 | 5 |
> > | row4 | 7 |
> > +----------+---------+
> > 4 rows selected (0.64 seconds)
> > 0: jdbc:drill:> select name, type, bool_val from sys.options where
> > name like '%null%';
> > +-------------------------------------------------+---------+-----------+
> > | name | type | bool_val |
> > +-------------------------------------------------+---------+-----------+
> > | drill.exec.functions.cast_empty_string_to_null | SYSTEM | true |
> > +-------------------------------------------------+---------+-----------+
> > 1 row selected (0.661 seconds)
> > 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> > CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > Error: SYSTEM ERROR: NumberFormatException:
> >
> > Fragment 0:0
> >
> > [Error Id: 2a0d104c-33cd-4680-80fe-f908147b5c0a on
> > se-node11.se.lab:31010] (state=,code=0)
> >
> > Shouldn’t that have returned a null for row2?
> >
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Wed, Sep 23, 2015 at 3:14 PM, Jason Altekruse <
> altekrusejason@gmail.com
> > >
> > wrote:
> >
> > > Sudheesh,
> > >
> > > What you said is true for all of the options besides this one. When it
> > was
> > > added it was decided that users of Drill would likely want the results
> of
> > > their queries to be consistent for all users, so this option only takes
> > > effect at the system level. Unfortunately there is not an error
> returned
> > > when it is set at the session level, the option just does nothing.
> > >
> > > Since then it seems like users are expected it to function like the
> rest
> > of
> > > the options (they are afraid of changing other peoples results once
> > several
> > > users are all running queries), so they have been reluctant to set it
> at
> > > the system level.
> > >
> > > There is an outstanding JIRA to change this to make it consistent with
> > the
> > > others: https://issues.apache.org/jira/browse/DRILL-3259
> > >
> > > If this JIRA really isn't going to be scheduled before 1.4, I think it
> > > might be good to create a sub-task for improving the experience right
> now
> > > to at least tell users when they set it at the session level that it is
> > not
> > > allowed.
> > >
> > >
> > >
> > > On Wed, Sep 23, 2015 at 6:01 AM, Christopher Matta <cm...@mapr.com>
> > > wrote:
> > >
> > > > Just bumping this, I’m sure other users are going to be running
> across
> > > this
> > > > eventually and I wanted to see if what I’m experiencing is expected
> > (even
> > > > after setting ALTER SESSION SET
> > > > `drill.exec.functions.cast_empty_string_to_null` = true;), or if
> it’s a
> > > > bug.
> > > >
> > > >
> > > > Chris Matta
> > > > cmatta@mapr.com
> > > > 215-701-3146
> > > >
> > > > On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cm...@mapr.com>
> > > > wrote:
> > > >
> > > > > Was the result I saw expected? Seems like I got back an empty
> string
> > > and
> > > > > not a NULL.
> > > > >
> > > > > Chris Matta
> > > > > cmatta@mapr.com
> > > > > 215-701-3146
> > > > >
> > > > > On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <
> > skatkam@maprtech.com
> > > >
> > > > > wrote:
> > > > >
> > > > >> See below:
> > > > >>
> > > > >> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
> > > > >> >
> > > > >> > Hi,
> > > > >> > This is a system wide setting. Meaning, you need to say
> > > > >> >
> > > > >> > Alter system set
> `drill.exec.functions.cast_empty_string_to_null`
> > =
> > > > >> true;
> > > > >>
> > > > >> To clarify, all options available through sys.options (except some
> > > used
> > > > >> for testing) can be set at system and session level. So what Chris
> > did
> > > > >> (setting at session level) works.
> > > > >>
> > > > >> Thank you,
> > > > >> Sudheesh
> > > > >>
> > > > >> >
> > > > >> >
> > > > >> > Sent from my iPhone
> > > > >> >
> > > > >> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <
> cmatta@mapr.com>
> > > > >> wrote:
> > > > >> >>
> > > > >> >> I’m not sure if it worked, the result looks the same when
> casting
> > > as
> > > > a
> > > > >> >> string (empty field, not a NULL value):
> > > > >> >>
> > > > >> >> 0: jdbc:drill:> ALTER SESSION SET
> > > > >> >> `drill.exec.functions.cast_empty_string_to_null` = true;
> > > > >> >>
> > > +-------+----------------------------------------------------------+
> > > > >> >> | ok | summary
> > > |
> > > > >> >>
> > > +-------+----------------------------------------------------------+
> > > > >> >> | true | drill.exec.functions.cast_empty_string_to_null
> updated.
> > > |
> > > > >> >>
> > > +-------+----------------------------------------------------------+
> > > > >> >> 1 row selected (1.606 seconds)
> > > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > `row_key`,
> > > > >> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > > > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > > > >> >>
> > > > >> >> Fragment 0:0
> > > > >> >>
> > > > >> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> > > > >> >> se-node10.se.lab:31010] (state=,code=0)
> > > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > `row_key`,
> > > > >> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER)
> fr
> > > > >> >> Command canceled.`cmatta_test` x;
> > > > >> >> 0: jdbc:drill:> select 'hello' from sys.version;
> > > > >> >> +---------+
> > > > >> >> | EXPR$0 |
> > > > >> >> +---------+
> > > > >> >> | hello |
> > > > >> >> +---------+
> > > > >> >> 1 row selected (0.417 seconds)
> > > > >> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> > > > >> >> +---------+
> > > > >> >> | EXPR$0 |
> > > > >> >> +---------+
> > > > >> >> | null |
> > > > >> >> +---------+
> > > > >> >> 1 row selected (0.4 seconds)
> > > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > `row_key`,
> > > > >> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from
> maprfs.cma
> > > > >> >> tta.`cmatta_test` x;
> > > > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > > > >> >>
> > > > >> >> Fragment 0:0
> > > > >> >>
> > > > >> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> > > > >> >> se-node10.se.lab:31010] (state=,code=0)
> > > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > `row_key`,
> > > > >> >> CAST(x.`a`.`c1` as varchar(64)) from
> maprfs.cmatta.`cmatta_test`
> > > > >> >> x;
> > > > >> >> +----------+---------+
> > > > >> >> | row_key | EXPR$1 |
> > > > >> >> +----------+---------+
> > > > >> >> | row1 | 1 |
> > > > >> >> | row2 | |
> > > > >> >> | row3 | 5 |
> > > > >> >> | row4 | 7 |
> > > > >> >> +----------+---------+
> > > > >> >> 4 rows selected (0.54 seconds)
> > > > >> >> 0: jdbc:drill:>
> > > > >> >>
> > > > >> >> Is this how it’s expected to work?
> > > > >> >>
> > > > >> >>
> > > > >> >> Chris Matta
> > > > >> >> cmatta@mapr.com
> > > > >> >> 215-701-3146
> > > > >> >>
> > > > >> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <
> > > jacques@dremio.com
> > > > >
> > > > >> wrote:
> > > > >> >>>
> > > > >> >>> Does this system option not work:
> > > > >> >>>
> > > > >> >>> ALTER SESSION SET
> > > `drill.exec.functions.cast_empty_string_to_null` =
> > > > >> true;
> > > > >> >>>
> > > > >> >>> The reason the bug was marked INVALID is that SQL engines (not
> > > sure
> > > > >> about
> > > > >> >>> the spec) don't allow casting from empty string to number. The
> > > > system
> > > > >> >>> option above is supposed to allow changing this behavior from
> > the
> > > > SQL
> > > > >> >>> standard for your type of situation. That being said, I see
> the
> > > docs
> > > > >> say
> > > > >> >>> "not supported in this release". Not sure why that is there.
> Can
> > > you
> > > > >> give
> > > > >> >>> it a try?
> > > > >> >>>
> > > > >> >>> That being said, it seems like the original issue was a NPE
> not
> > a
> > > > >> NFE. That
> > > > >> >>> definitely seems like something else.
> > > > >> >>>
> > > > >> >>>
> > > > >> >>> --
> > > > >> >>> Jacques Nadeau
> > > > >> >>> CTO and Co-Founder, Dremio
> > > > >> >>>
> > > > >> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <
> > > > cmatta@mapr.com>
> > > > >> >>> wrote:
> > > > >> >>>
> > > > >> >>>> Here is my attempt at building a reproduction, btw, it seems
> > like
> > > > >> this is
> > > > >> >>>> the same issue as DRILL-862
> > > > >> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where
> > Jacques
> > > > >> >>> determined
> > > > >> >>>> the error to be invalid. Is trying to cast an empty string,
> or
> > > null
> > > > >> value
> > > > >> >>>> to an integer invalid? What's the workaround?
> > > > >> >>>>
> > > > >> >>>> Data
> > > > >> >>>>
> > > > >> >>>> row1,1,2
> > > > >> >>>> row2,,4
> > > > >> >>>> row3,5,6
> > > > >> >>>> row4,7,8
> > > > >> >>>>
> > > > >> >>>> Create Table
> > > > >> >>>>
> > > > >> >>>> $ maprcli table create -path
> /user/cmatta/projects/cmatta_test
> > > > >> >>>> $ maprcli table cf create -path
> > /user/cmatta/projects/cmatta_test
> > > > >> >>> -cfname a
> > > > >> >>>>
> > > > >> >>>> Load into Hbase table:
> > > > >> >>>>
> > > > >> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> > > > >> >>>> -Dimporttsv.separator=','
> > > > -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> > > > >> >>>> /user/cmatta/projects/cmatta_test
> > > > >> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
> > > > >> >>>>
> > > > >> >>>> Query (error):
> > > > >> >>>>
> > > > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > >> `row_key`,
> > > > >> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test`
> x;
> > > > >> >>>> Error: SYSTEM ERROR: NumberFormatException:
> > > > >> >>>>
> > > > >> >>>> Fragment 0:0
> > > > >> >>>>
> > > > >> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> > > > >> >>>> se-node11.se.lab:31010] (state=,code=0)
> > > > >> >>>>
> > > > >> >>>> Query that works on the column (c2) that doesn’t have a NULL
> > > value:
> > > > >> >>>>
> > > > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > > >> `row_key`,
> > > > >> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test`
> x;
> > > > >> >>>> +----------+---------+
> > > > >> >>>> | row_key | EXPR$1 |
> > > > >> >>>> +----------+---------+
> > > > >> >>>> | row1 | 2 |
> > > > >> >>>> | row2 | 4 |
> > > > >> >>>> | row3 | 6 |
> > > > >> >>>> | row4 | 8 |
> > > > >> >>>> +----------+---------+
> > > > >> >>>> 4 rows selected (0.308 seconds)
> > > > >> >>>>
> > > > >> >>>>
> > > > >> >>>> Chris Matta
> > > > >> >>>> cmatta@mapr.com
> > > > >> >>>> 215-701-3146
> > > > >> >>>>
> > > > >> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <
> > > > cmatta@mapr.com>
> > > > >> >>>> wrote:
> > > > >> >>>>
> > > > >> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you
> > > provide
> > > > >> me
> > > > >> >>>> with
> > > > >> >>>>> your query and a sample data set so I can open one?
> > > > >> >>>>>
> > > > >> >>>>> Chris Matta
> > > > >> >>>>> cmatta@mapr.com
> > > > >> >>>>> 215-701-3146
> > > > >> >>>>>
> > > > >> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> > > > >> >>>> altekrusejason@gmail.com
> > > > >> >>>>>> wrote:
> > > > >> >>>>>
> > > > >> >>>>>> A SQL level null is different than a null at the JAVA level
> > > that
> > > > >> would
> > > > >> >>>> be
> > > > >> >>>>>> giving this exception (we don't represent nulls with an
> > actual
> > > > null
> > > > >> >>> java
> > > > >> >>>>>> object). There might be a way to work around it, but this
> is
> > a
> > > > bug
> > > > >> in
> > > > >> >>>>>> Drill. You should be able to make a cast between compatible
> > > types
> > > > >> even
> > > > >> >>>> if
> > > > >> >>>>>> there are nulls in the dataset.
> > > > >> >>>>>>
> > > > >> >>>>>> Could you open a JIRA and put as much description about
> your
> > > data
> > > > >> and
> > > > >> >>>>>> query
> > > > >> >>>>>> as possible? If you have the time to create a small repro,
> > > > loading
> > > > >> an
> > > > >> >>>>>> Hbase
> > > > >> >>>>>> table with a small set of data that produces the problem
> that
> > > > >> would be
> > > > >> >>>>>> very
> > > > >> >>>>>> helpful. In any case, whatever you can provide will be
> useful
> > > the
> > > > >> dev
> > > > >> >>>> who
> > > > >> >>>>>> picks up the JIRA.
> > > > >> >>>>>>
> > > > >> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
> > > > >> cmatta@mapr.com>
> > > > >> >>>>>> wrote:
> > > > >> >>>>>>
> > > > >> >>>>>>> Does changing the query to something like this work?:
> > > > >> >>>>>>>
> > > > >> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> > > > >> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
> > > > >> >>>>>>> ...
> > > > >> >>>>>>>
> > > > >> >>>>>>>
> > > > >> >>>>>>>
> > > > >> >>>>>>> Chris Matta
> > > > >> >>>>>>> cmatta@mapr.com
> > > > >> >>>>>>> 215-701-3146
> > > > >> >>>>>>>
> > > > >> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> > > > >> >>>>>>> nathaniel.auvil@gmail.com
> > > > >> >>>>>>>> wrote:
> > > > >> >>>>>>>
> > > > >> >>>>>>>> i have a MaprDB table which has nulls for some of the
> > > columns.
> > > > >> >>>> When i
> > > > >> >>>>>>>> create a select where i CAST(table.cf.myColumn as
> INTEGER)
> > > and
> > > > >> >>> there
> > > > >> >>>>>> are
> > > > >> >>>>>>>> nulls instead of simply returning null for the value,
> there
> > > is
> > > > a
> > > > >> >>>>>>>> NullPointerException.
> > > > >> >>>>>>>>
> > > > >> >>>>>>>> Why not simply return null for the value if it is missing
> > > from
> > > > a
> > > > >> >>>> row?
> > > > >> >>>>>>>>
> > > > >> >>>>>>>
> > > > >> >>>>>>
> > > > >> >>>>>
> > > > >> >>>>>
> > > > >> >>>>
> > > > >> >>>
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: NullPointers in type conversions
Posted by Jason Altekruse <al...@gmail.com>.
I actually noticed while I was testing this out that it is not working with
casts to date, I will be opening a JIRA for it. That being said, I did find
a unit test for integer, so this should be working. It is possible that the
data in the column is actually a series of spaces instead of the empty
string. Could you try to throw a call to trim() inside of the cast?
It is also possible that this system option might only impact the cast
functions from varchar to the various other types like double, int, etc.
All of the data read out of HBase is placed in the varbinary type. We might
have decent handling if the data is stored in Hbase in the format we happen
to assume in the cast varbinary to int implementation, but we generally
recommend using the convert_from functions when dealing with binary. I
don't know exactly how this option was implemented, so it might only apply
with varchar. We were primarily focused on issues with lots of case
statements when querying text files when it was added.
- Jason
On Wed, Sep 23, 2015 at 1:49 PM, Christopher Matta <cm...@mapr.com> wrote:
> This system setting was set, yet I still experience the
> NumberFormatException with MapR-DB when a column contains a NULL value:
>
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> . . . . . . . > x;
> +----------+---------+
> | row_key | EXPR$1 |
> +----------+---------+
> | row1 | 1 |
> | row2 | |
> | row3 | 5 |
> | row4 | 7 |
> +----------+---------+
> 4 rows selected (0.64 seconds)
> 0: jdbc:drill:> select name, type, bool_val from sys.options where
> name like '%null%';
> +-------------------------------------------------+---------+-----------+
> | name | type | bool_val |
> +-------------------------------------------------+---------+-----------+
> | drill.exec.functions.cast_empty_string_to_null | SYSTEM | true |
> +-------------------------------------------------+---------+-----------+
> 1 row selected (0.661 seconds)
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> Error: SYSTEM ERROR: NumberFormatException:
>
> Fragment 0:0
>
> [Error Id: 2a0d104c-33cd-4680-80fe-f908147b5c0a on
> se-node11.se.lab:31010] (state=,code=0)
>
> Shouldn’t that have returned a null for row2?
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Wed, Sep 23, 2015 at 3:14 PM, Jason Altekruse <altekrusejason@gmail.com
> >
> wrote:
>
> > Sudheesh,
> >
> > What you said is true for all of the options besides this one. When it
> was
> > added it was decided that users of Drill would likely want the results of
> > their queries to be consistent for all users, so this option only takes
> > effect at the system level. Unfortunately there is not an error returned
> > when it is set at the session level, the option just does nothing.
> >
> > Since then it seems like users are expected it to function like the rest
> of
> > the options (they are afraid of changing other peoples results once
> several
> > users are all running queries), so they have been reluctant to set it at
> > the system level.
> >
> > There is an outstanding JIRA to change this to make it consistent with
> the
> > others: https://issues.apache.org/jira/browse/DRILL-3259
> >
> > If this JIRA really isn't going to be scheduled before 1.4, I think it
> > might be good to create a sub-task for improving the experience right now
> > to at least tell users when they set it at the session level that it is
> not
> > allowed.
> >
> >
> >
> > On Wed, Sep 23, 2015 at 6:01 AM, Christopher Matta <cm...@mapr.com>
> > wrote:
> >
> > > Just bumping this, I’m sure other users are going to be running across
> > this
> > > eventually and I wanted to see if what I’m experiencing is expected
> (even
> > > after setting ALTER SESSION SET
> > > `drill.exec.functions.cast_empty_string_to_null` = true;), or if it’s a
> > > bug.
> > >
> > >
> > > Chris Matta
> > > cmatta@mapr.com
> > > 215-701-3146
> > >
> > > On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cm...@mapr.com>
> > > wrote:
> > >
> > > > Was the result I saw expected? Seems like I got back an empty string
> > and
> > > > not a NULL.
> > > >
> > > > Chris Matta
> > > > cmatta@mapr.com
> > > > 215-701-3146
> > > >
> > > > On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <
> skatkam@maprtech.com
> > >
> > > > wrote:
> > > >
> > > >> See below:
> > > >>
> > > >> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
> > > >> >
> > > >> > Hi,
> > > >> > This is a system wide setting. Meaning, you need to say
> > > >> >
> > > >> > Alter system set `drill.exec.functions.cast_empty_string_to_null`
> =
> > > >> true;
> > > >>
> > > >> To clarify, all options available through sys.options (except some
> > used
> > > >> for testing) can be set at system and session level. So what Chris
> did
> > > >> (setting at session level) works.
> > > >>
> > > >> Thank you,
> > > >> Sudheesh
> > > >>
> > > >> >
> > > >> >
> > > >> > Sent from my iPhone
> > > >> >
> > > >> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com>
> > > >> wrote:
> > > >> >>
> > > >> >> I’m not sure if it worked, the result looks the same when casting
> > as
> > > a
> > > >> >> string (empty field, not a NULL value):
> > > >> >>
> > > >> >> 0: jdbc:drill:> ALTER SESSION SET
> > > >> >> `drill.exec.functions.cast_empty_string_to_null` = true;
> > > >> >>
> > +-------+----------------------------------------------------------+
> > > >> >> | ok | summary
> > |
> > > >> >>
> > +-------+----------------------------------------------------------+
> > > >> >> | true | drill.exec.functions.cast_empty_string_to_null updated.
> > |
> > > >> >>
> > +-------+----------------------------------------------------------+
> > > >> >> 1 row selected (1.606 seconds)
> > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > `row_key`,
> > > >> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > > >> >>
> > > >> >> Fragment 0:0
> > > >> >>
> > > >> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> > > >> >> se-node10.se.lab:31010] (state=,code=0)
> > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > `row_key`,
> > > >> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
> > > >> >> Command canceled.`cmatta_test` x;
> > > >> >> 0: jdbc:drill:> select 'hello' from sys.version;
> > > >> >> +---------+
> > > >> >> | EXPR$0 |
> > > >> >> +---------+
> > > >> >> | hello |
> > > >> >> +---------+
> > > >> >> 1 row selected (0.417 seconds)
> > > >> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> > > >> >> +---------+
> > > >> >> | EXPR$0 |
> > > >> >> +---------+
> > > >> >> | null |
> > > >> >> +---------+
> > > >> >> 1 row selected (0.4 seconds)
> > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > `row_key`,
> > > >> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
> > > >> >> tta.`cmatta_test` x;
> > > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > > >> >>
> > > >> >> Fragment 0:0
> > > >> >>
> > > >> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> > > >> >> se-node10.se.lab:31010] (state=,code=0)
> > > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > `row_key`,
> > > >> >> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> > > >> >> x;
> > > >> >> +----------+---------+
> > > >> >> | row_key | EXPR$1 |
> > > >> >> +----------+---------+
> > > >> >> | row1 | 1 |
> > > >> >> | row2 | |
> > > >> >> | row3 | 5 |
> > > >> >> | row4 | 7 |
> > > >> >> +----------+---------+
> > > >> >> 4 rows selected (0.54 seconds)
> > > >> >> 0: jdbc:drill:>
> > > >> >>
> > > >> >> Is this how it’s expected to work?
> > > >> >>
> > > >> >>
> > > >> >> Chris Matta
> > > >> >> cmatta@mapr.com
> > > >> >> 215-701-3146
> > > >> >>
> > > >> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <
> > jacques@dremio.com
> > > >
> > > >> wrote:
> > > >> >>>
> > > >> >>> Does this system option not work:
> > > >> >>>
> > > >> >>> ALTER SESSION SET
> > `drill.exec.functions.cast_empty_string_to_null` =
> > > >> true;
> > > >> >>>
> > > >> >>> The reason the bug was marked INVALID is that SQL engines (not
> > sure
> > > >> about
> > > >> >>> the spec) don't allow casting from empty string to number. The
> > > system
> > > >> >>> option above is supposed to allow changing this behavior from
> the
> > > SQL
> > > >> >>> standard for your type of situation. That being said, I see the
> > docs
> > > >> say
> > > >> >>> "not supported in this release". Not sure why that is there. Can
> > you
> > > >> give
> > > >> >>> it a try?
> > > >> >>>
> > > >> >>> That being said, it seems like the original issue was a NPE not
> a
> > > >> NFE. That
> > > >> >>> definitely seems like something else.
> > > >> >>>
> > > >> >>>
> > > >> >>> --
> > > >> >>> Jacques Nadeau
> > > >> >>> CTO and Co-Founder, Dremio
> > > >> >>>
> > > >> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <
> > > cmatta@mapr.com>
> > > >> >>> wrote:
> > > >> >>>
> > > >> >>>> Here is my attempt at building a reproduction, btw, it seems
> like
> > > >> this is
> > > >> >>>> the same issue as DRILL-862
> > > >> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where
> Jacques
> > > >> >>> determined
> > > >> >>>> the error to be invalid. Is trying to cast an empty string, or
> > null
> > > >> value
> > > >> >>>> to an integer invalid? What's the workaround?
> > > >> >>>>
> > > >> >>>> Data
> > > >> >>>>
> > > >> >>>> row1,1,2
> > > >> >>>> row2,,4
> > > >> >>>> row3,5,6
> > > >> >>>> row4,7,8
> > > >> >>>>
> > > >> >>>> Create Table
> > > >> >>>>
> > > >> >>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
> > > >> >>>> $ maprcli table cf create -path
> /user/cmatta/projects/cmatta_test
> > > >> >>> -cfname a
> > > >> >>>>
> > > >> >>>> Load into Hbase table:
> > > >> >>>>
> > > >> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> > > >> >>>> -Dimporttsv.separator=','
> > > -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> > > >> >>>> /user/cmatta/projects/cmatta_test
> > > >> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
> > > >> >>>>
> > > >> >>>> Query (error):
> > > >> >>>>
> > > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > >> `row_key`,
> > > >> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > > >> >>>> Error: SYSTEM ERROR: NumberFormatException:
> > > >> >>>>
> > > >> >>>> Fragment 0:0
> > > >> >>>>
> > > >> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> > > >> >>>> se-node11.se.lab:31010] (state=,code=0)
> > > >> >>>>
> > > >> >>>> Query that works on the column (c2) that doesn’t have a NULL
> > value:
> > > >> >>>>
> > > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > > >> `row_key`,
> > > >> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > > >> >>>> +----------+---------+
> > > >> >>>> | row_key | EXPR$1 |
> > > >> >>>> +----------+---------+
> > > >> >>>> | row1 | 2 |
> > > >> >>>> | row2 | 4 |
> > > >> >>>> | row3 | 6 |
> > > >> >>>> | row4 | 8 |
> > > >> >>>> +----------+---------+
> > > >> >>>> 4 rows selected (0.308 seconds)
> > > >> >>>>
> > > >> >>>>
> > > >> >>>> Chris Matta
> > > >> >>>> cmatta@mapr.com
> > > >> >>>> 215-701-3146
> > > >> >>>>
> > > >> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <
> > > cmatta@mapr.com>
> > > >> >>>> wrote:
> > > >> >>>>
> > > >> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you
> > provide
> > > >> me
> > > >> >>>> with
> > > >> >>>>> your query and a sample data set so I can open one?
> > > >> >>>>>
> > > >> >>>>> Chris Matta
> > > >> >>>>> cmatta@mapr.com
> > > >> >>>>> 215-701-3146
> > > >> >>>>>
> > > >> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> > > >> >>>> altekrusejason@gmail.com
> > > >> >>>>>> wrote:
> > > >> >>>>>
> > > >> >>>>>> A SQL level null is different than a null at the JAVA level
> > that
> > > >> would
> > > >> >>>> be
> > > >> >>>>>> giving this exception (we don't represent nulls with an
> actual
> > > null
> > > >> >>> java
> > > >> >>>>>> object). There might be a way to work around it, but this is
> a
> > > bug
> > > >> in
> > > >> >>>>>> Drill. You should be able to make a cast between compatible
> > types
> > > >> even
> > > >> >>>> if
> > > >> >>>>>> there are nulls in the dataset.
> > > >> >>>>>>
> > > >> >>>>>> Could you open a JIRA and put as much description about your
> > data
> > > >> and
> > > >> >>>>>> query
> > > >> >>>>>> as possible? If you have the time to create a small repro,
> > > loading
> > > >> an
> > > >> >>>>>> Hbase
> > > >> >>>>>> table with a small set of data that produces the problem that
> > > >> would be
> > > >> >>>>>> very
> > > >> >>>>>> helpful. In any case, whatever you can provide will be useful
> > the
> > > >> dev
> > > >> >>>> who
> > > >> >>>>>> picks up the JIRA.
> > > >> >>>>>>
> > > >> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
> > > >> cmatta@mapr.com>
> > > >> >>>>>> wrote:
> > > >> >>>>>>
> > > >> >>>>>>> Does changing the query to something like this work?:
> > > >> >>>>>>>
> > > >> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> > > >> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
> > > >> >>>>>>> ...
> > > >> >>>>>>>
> > > >> >>>>>>>
> > > >> >>>>>>>
> > > >> >>>>>>> Chris Matta
> > > >> >>>>>>> cmatta@mapr.com
> > > >> >>>>>>> 215-701-3146
> > > >> >>>>>>>
> > > >> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> > > >> >>>>>>> nathaniel.auvil@gmail.com
> > > >> >>>>>>>> wrote:
> > > >> >>>>>>>
> > > >> >>>>>>>> i have a MaprDB table which has nulls for some of the
> > columns.
> > > >> >>>> When i
> > > >> >>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER)
> > and
> > > >> >>> there
> > > >> >>>>>> are
> > > >> >>>>>>>> nulls instead of simply returning null for the value, there
> > is
> > > a
> > > >> >>>>>>>> NullPointerException.
> > > >> >>>>>>>>
> > > >> >>>>>>>> Why not simply return null for the value if it is missing
> > from
> > > a
> > > >> >>>> row?
> > > >> >>>>>>>>
> > > >> >>>>>>>
> > > >> >>>>>>
> > > >> >>>>>
> > > >> >>>>>
> > > >> >>>>
> > > >> >>>
> > > >>
> > > >>
> > > >
> > >
> >
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
This system setting was set, yet I still experience the
NumberFormatException with MapR-DB when a column contains a NULL value:
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
. . . . . . . > x;
+----------+---------+
| row_key | EXPR$1 |
+----------+---------+
| row1 | 1 |
| row2 | |
| row3 | 5 |
| row4 | 7 |
+----------+---------+
4 rows selected (0.64 seconds)
0: jdbc:drill:> select name, type, bool_val from sys.options where
name like '%null%';
+-------------------------------------------------+---------+-----------+
| name | type | bool_val |
+-------------------------------------------------+---------+-----------+
| drill.exec.functions.cast_empty_string_to_null | SYSTEM | true |
+-------------------------------------------------+---------+-----------+
1 row selected (0.661 seconds)
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
Error: SYSTEM ERROR: NumberFormatException:
Fragment 0:0
[Error Id: 2a0d104c-33cd-4680-80fe-f908147b5c0a on
se-node11.se.lab:31010] (state=,code=0)
Shouldn’t that have returned a null for row2?
Chris Matta
cmatta@mapr.com
215-701-3146
On Wed, Sep 23, 2015 at 3:14 PM, Jason Altekruse <al...@gmail.com>
wrote:
> Sudheesh,
>
> What you said is true for all of the options besides this one. When it was
> added it was decided that users of Drill would likely want the results of
> their queries to be consistent for all users, so this option only takes
> effect at the system level. Unfortunately there is not an error returned
> when it is set at the session level, the option just does nothing.
>
> Since then it seems like users are expected it to function like the rest of
> the options (they are afraid of changing other peoples results once several
> users are all running queries), so they have been reluctant to set it at
> the system level.
>
> There is an outstanding JIRA to change this to make it consistent with the
> others: https://issues.apache.org/jira/browse/DRILL-3259
>
> If this JIRA really isn't going to be scheduled before 1.4, I think it
> might be good to create a sub-task for improving the experience right now
> to at least tell users when they set it at the session level that it is not
> allowed.
>
>
>
> On Wed, Sep 23, 2015 at 6:01 AM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > Just bumping this, I’m sure other users are going to be running across
> this
> > eventually and I wanted to see if what I’m experiencing is expected (even
> > after setting ALTER SESSION SET
> > `drill.exec.functions.cast_empty_string_to_null` = true;), or if it’s a
> > bug.
> >
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cm...@mapr.com>
> > wrote:
> >
> > > Was the result I saw expected? Seems like I got back an empty string
> and
> > > not a NULL.
> > >
> > > Chris Matta
> > > cmatta@mapr.com
> > > 215-701-3146
> > >
> > > On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <skatkam@maprtech.com
> >
> > > wrote:
> > >
> > >> See below:
> > >>
> > >> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
> > >> >
> > >> > Hi,
> > >> > This is a system wide setting. Meaning, you need to say
> > >> >
> > >> > Alter system set `drill.exec.functions.cast_empty_string_to_null` =
> > >> true;
> > >>
> > >> To clarify, all options available through sys.options (except some
> used
> > >> for testing) can be set at system and session level. So what Chris did
> > >> (setting at session level) works.
> > >>
> > >> Thank you,
> > >> Sudheesh
> > >>
> > >> >
> > >> >
> > >> > Sent from my iPhone
> > >> >
> > >> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com>
> > >> wrote:
> > >> >>
> > >> >> I’m not sure if it worked, the result looks the same when casting
> as
> > a
> > >> >> string (empty field, not a NULL value):
> > >> >>
> > >> >> 0: jdbc:drill:> ALTER SESSION SET
> > >> >> `drill.exec.functions.cast_empty_string_to_null` = true;
> > >> >>
> +-------+----------------------------------------------------------+
> > >> >> | ok | summary
> |
> > >> >>
> +-------+----------------------------------------------------------+
> > >> >> | true | drill.exec.functions.cast_empty_string_to_null updated.
> |
> > >> >>
> +-------+----------------------------------------------------------+
> > >> >> 1 row selected (1.606 seconds)
> > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > `row_key`,
> > >> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > >> >>
> > >> >> Fragment 0:0
> > >> >>
> > >> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> > >> >> se-node10.se.lab:31010] (state=,code=0)
> > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > `row_key`,
> > >> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
> > >> >> Command canceled.`cmatta_test` x;
> > >> >> 0: jdbc:drill:> select 'hello' from sys.version;
> > >> >> +---------+
> > >> >> | EXPR$0 |
> > >> >> +---------+
> > >> >> | hello |
> > >> >> +---------+
> > >> >> 1 row selected (0.417 seconds)
> > >> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> > >> >> +---------+
> > >> >> | EXPR$0 |
> > >> >> +---------+
> > >> >> | null |
> > >> >> +---------+
> > >> >> 1 row selected (0.4 seconds)
> > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > `row_key`,
> > >> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
> > >> >> tta.`cmatta_test` x;
> > >> >> Error: SYSTEM ERROR: NumberFormatException:
> > >> >>
> > >> >> Fragment 0:0
> > >> >>
> > >> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> > >> >> se-node10.se.lab:31010] (state=,code=0)
> > >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > `row_key`,
> > >> >> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> > >> >> x;
> > >> >> +----------+---------+
> > >> >> | row_key | EXPR$1 |
> > >> >> +----------+---------+
> > >> >> | row1 | 1 |
> > >> >> | row2 | |
> > >> >> | row3 | 5 |
> > >> >> | row4 | 7 |
> > >> >> +----------+---------+
> > >> >> 4 rows selected (0.54 seconds)
> > >> >> 0: jdbc:drill:>
> > >> >>
> > >> >> Is this how it’s expected to work?
> > >> >>
> > >> >>
> > >> >> Chris Matta
> > >> >> cmatta@mapr.com
> > >> >> 215-701-3146
> > >> >>
> > >> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <
> jacques@dremio.com
> > >
> > >> wrote:
> > >> >>>
> > >> >>> Does this system option not work:
> > >> >>>
> > >> >>> ALTER SESSION SET
> `drill.exec.functions.cast_empty_string_to_null` =
> > >> true;
> > >> >>>
> > >> >>> The reason the bug was marked INVALID is that SQL engines (not
> sure
> > >> about
> > >> >>> the spec) don't allow casting from empty string to number. The
> > system
> > >> >>> option above is supposed to allow changing this behavior from the
> > SQL
> > >> >>> standard for your type of situation. That being said, I see the
> docs
> > >> say
> > >> >>> "not supported in this release". Not sure why that is there. Can
> you
> > >> give
> > >> >>> it a try?
> > >> >>>
> > >> >>> That being said, it seems like the original issue was a NPE not a
> > >> NFE. That
> > >> >>> definitely seems like something else.
> > >> >>>
> > >> >>>
> > >> >>> --
> > >> >>> Jacques Nadeau
> > >> >>> CTO and Co-Founder, Dremio
> > >> >>>
> > >> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <
> > cmatta@mapr.com>
> > >> >>> wrote:
> > >> >>>
> > >> >>>> Here is my attempt at building a reproduction, btw, it seems like
> > >> this is
> > >> >>>> the same issue as DRILL-862
> > >> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
> > >> >>> determined
> > >> >>>> the error to be invalid. Is trying to cast an empty string, or
> null
> > >> value
> > >> >>>> to an integer invalid? What's the workaround?
> > >> >>>>
> > >> >>>> Data
> > >> >>>>
> > >> >>>> row1,1,2
> > >> >>>> row2,,4
> > >> >>>> row3,5,6
> > >> >>>> row4,7,8
> > >> >>>>
> > >> >>>> Create Table
> > >> >>>>
> > >> >>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
> > >> >>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
> > >> >>> -cfname a
> > >> >>>>
> > >> >>>> Load into Hbase table:
> > >> >>>>
> > >> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> > >> >>>> -Dimporttsv.separator=','
> > -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> > >> >>>> /user/cmatta/projects/cmatta_test
> > >> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
> > >> >>>>
> > >> >>>> Query (error):
> > >> >>>>
> > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > >> `row_key`,
> > >> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > >> >>>> Error: SYSTEM ERROR: NumberFormatException:
> > >> >>>>
> > >> >>>> Fragment 0:0
> > >> >>>>
> > >> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> > >> >>>> se-node11.se.lab:31010] (state=,code=0)
> > >> >>>>
> > >> >>>> Query that works on the column (c2) that doesn’t have a NULL
> value:
> > >> >>>>
> > >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> > >> `row_key`,
> > >> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > >> >>>> +----------+---------+
> > >> >>>> | row_key | EXPR$1 |
> > >> >>>> +----------+---------+
> > >> >>>> | row1 | 2 |
> > >> >>>> | row2 | 4 |
> > >> >>>> | row3 | 6 |
> > >> >>>> | row4 | 8 |
> > >> >>>> +----------+---------+
> > >> >>>> 4 rows selected (0.308 seconds)
> > >> >>>>
> > >> >>>>
> > >> >>>> Chris Matta
> > >> >>>> cmatta@mapr.com
> > >> >>>> 215-701-3146
> > >> >>>>
> > >> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <
> > cmatta@mapr.com>
> > >> >>>> wrote:
> > >> >>>>
> > >> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you
> provide
> > >> me
> > >> >>>> with
> > >> >>>>> your query and a sample data set so I can open one?
> > >> >>>>>
> > >> >>>>> Chris Matta
> > >> >>>>> cmatta@mapr.com
> > >> >>>>> 215-701-3146
> > >> >>>>>
> > >> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> > >> >>>> altekrusejason@gmail.com
> > >> >>>>>> wrote:
> > >> >>>>>
> > >> >>>>>> A SQL level null is different than a null at the JAVA level
> that
> > >> would
> > >> >>>> be
> > >> >>>>>> giving this exception (we don't represent nulls with an actual
> > null
> > >> >>> java
> > >> >>>>>> object). There might be a way to work around it, but this is a
> > bug
> > >> in
> > >> >>>>>> Drill. You should be able to make a cast between compatible
> types
> > >> even
> > >> >>>> if
> > >> >>>>>> there are nulls in the dataset.
> > >> >>>>>>
> > >> >>>>>> Could you open a JIRA and put as much description about your
> data
> > >> and
> > >> >>>>>> query
> > >> >>>>>> as possible? If you have the time to create a small repro,
> > loading
> > >> an
> > >> >>>>>> Hbase
> > >> >>>>>> table with a small set of data that produces the problem that
> > >> would be
> > >> >>>>>> very
> > >> >>>>>> helpful. In any case, whatever you can provide will be useful
> the
> > >> dev
> > >> >>>> who
> > >> >>>>>> picks up the JIRA.
> > >> >>>>>>
> > >> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
> > >> cmatta@mapr.com>
> > >> >>>>>> wrote:
> > >> >>>>>>
> > >> >>>>>>> Does changing the query to something like this work?:
> > >> >>>>>>>
> > >> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> > >> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
> > >> >>>>>>> ...
> > >> >>>>>>>
> > >> >>>>>>>
> > >> >>>>>>>
> > >> >>>>>>> Chris Matta
> > >> >>>>>>> cmatta@mapr.com
> > >> >>>>>>> 215-701-3146
> > >> >>>>>>>
> > >> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> > >> >>>>>>> nathaniel.auvil@gmail.com
> > >> >>>>>>>> wrote:
> > >> >>>>>>>
> > >> >>>>>>>> i have a MaprDB table which has nulls for some of the
> columns.
> > >> >>>> When i
> > >> >>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER)
> and
> > >> >>> there
> > >> >>>>>> are
> > >> >>>>>>>> nulls instead of simply returning null for the value, there
> is
> > a
> > >> >>>>>>>> NullPointerException.
> > >> >>>>>>>>
> > >> >>>>>>>> Why not simply return null for the value if it is missing
> from
> > a
> > >> >>>> row?
> > >> >>>>>>>>
> > >> >>>>>>>
> > >> >>>>>>
> > >> >>>>>
> > >> >>>>>
> > >> >>>>
> > >> >>>
> > >>
> > >>
> > >
> >
>
Re: NullPointers in type conversions
Posted by Jason Altekruse <al...@gmail.com>.
Sudheesh,
What you said is true for all of the options besides this one. When it was
added it was decided that users of Drill would likely want the results of
their queries to be consistent for all users, so this option only takes
effect at the system level. Unfortunately there is not an error returned
when it is set at the session level, the option just does nothing.
Since then it seems like users are expected it to function like the rest of
the options (they are afraid of changing other peoples results once several
users are all running queries), so they have been reluctant to set it at
the system level.
There is an outstanding JIRA to change this to make it consistent with the
others: https://issues.apache.org/jira/browse/DRILL-3259
If this JIRA really isn't going to be scheduled before 1.4, I think it
might be good to create a sub-task for improving the experience right now
to at least tell users when they set it at the session level that it is not
allowed.
On Wed, Sep 23, 2015 at 6:01 AM, Christopher Matta <cm...@mapr.com> wrote:
> Just bumping this, I’m sure other users are going to be running across this
> eventually and I wanted to see if what I’m experiencing is expected (even
> after setting ALTER SESSION SET
> `drill.exec.functions.cast_empty_string_to_null` = true;), or if it’s a
> bug.
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > Was the result I saw expected? Seems like I got back an empty string and
> > not a NULL.
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <sk...@maprtech.com>
> > wrote:
> >
> >> See below:
> >>
> >> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
> >> >
> >> > Hi,
> >> > This is a system wide setting. Meaning, you need to say
> >> >
> >> > Alter system set `drill.exec.functions.cast_empty_string_to_null` =
> >> true;
> >>
> >> To clarify, all options available through sys.options (except some used
> >> for testing) can be set at system and session level. So what Chris did
> >> (setting at session level) works.
> >>
> >> Thank you,
> >> Sudheesh
> >>
> >> >
> >> >
> >> > Sent from my iPhone
> >> >
> >> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com>
> >> wrote:
> >> >>
> >> >> I’m not sure if it worked, the result looks the same when casting as
> a
> >> >> string (empty field, not a NULL value):
> >> >>
> >> >> 0: jdbc:drill:> ALTER SESSION SET
> >> >> `drill.exec.functions.cast_empty_string_to_null` = true;
> >> >> +-------+----------------------------------------------------------+
> >> >> | ok | summary |
> >> >> +-------+----------------------------------------------------------+
> >> >> | true | drill.exec.functions.cast_empty_string_to_null updated. |
> >> >> +-------+----------------------------------------------------------+
> >> >> 1 row selected (1.606 seconds)
> >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> `row_key`,
> >> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >> >> Error: SYSTEM ERROR: NumberFormatException:
> >> >>
> >> >> Fragment 0:0
> >> >>
> >> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> >> >> se-node10.se.lab:31010] (state=,code=0)
> >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> `row_key`,
> >> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
> >> >> Command canceled.`cmatta_test` x;
> >> >> 0: jdbc:drill:> select 'hello' from sys.version;
> >> >> +---------+
> >> >> | EXPR$0 |
> >> >> +---------+
> >> >> | hello |
> >> >> +---------+
> >> >> 1 row selected (0.417 seconds)
> >> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> >> >> +---------+
> >> >> | EXPR$0 |
> >> >> +---------+
> >> >> | null |
> >> >> +---------+
> >> >> 1 row selected (0.4 seconds)
> >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> `row_key`,
> >> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
> >> >> tta.`cmatta_test` x;
> >> >> Error: SYSTEM ERROR: NumberFormatException:
> >> >>
> >> >> Fragment 0:0
> >> >>
> >> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> >> >> se-node10.se.lab:31010] (state=,code=0)
> >> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> `row_key`,
> >> >> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> >> >> x;
> >> >> +----------+---------+
> >> >> | row_key | EXPR$1 |
> >> >> +----------+---------+
> >> >> | row1 | 1 |
> >> >> | row2 | |
> >> >> | row3 | 5 |
> >> >> | row4 | 7 |
> >> >> +----------+---------+
> >> >> 4 rows selected (0.54 seconds)
> >> >> 0: jdbc:drill:>
> >> >>
> >> >> Is this how it’s expected to work?
> >> >>
> >> >>
> >> >> Chris Matta
> >> >> cmatta@mapr.com
> >> >> 215-701-3146
> >> >>
> >> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <jacques@dremio.com
> >
> >> wrote:
> >> >>>
> >> >>> Does this system option not work:
> >> >>>
> >> >>> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` =
> >> true;
> >> >>>
> >> >>> The reason the bug was marked INVALID is that SQL engines (not sure
> >> about
> >> >>> the spec) don't allow casting from empty string to number. The
> system
> >> >>> option above is supposed to allow changing this behavior from the
> SQL
> >> >>> standard for your type of situation. That being said, I see the docs
> >> say
> >> >>> "not supported in this release". Not sure why that is there. Can you
> >> give
> >> >>> it a try?
> >> >>>
> >> >>> That being said, it seems like the original issue was a NPE not a
> >> NFE. That
> >> >>> definitely seems like something else.
> >> >>>
> >> >>>
> >> >>> --
> >> >>> Jacques Nadeau
> >> >>> CTO and Co-Founder, Dremio
> >> >>>
> >> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <
> cmatta@mapr.com>
> >> >>> wrote:
> >> >>>
> >> >>>> Here is my attempt at building a reproduction, btw, it seems like
> >> this is
> >> >>>> the same issue as DRILL-862
> >> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
> >> >>> determined
> >> >>>> the error to be invalid. Is trying to cast an empty string, or null
> >> value
> >> >>>> to an integer invalid? What's the workaround?
> >> >>>>
> >> >>>> Data
> >> >>>>
> >> >>>> row1,1,2
> >> >>>> row2,,4
> >> >>>> row3,5,6
> >> >>>> row4,7,8
> >> >>>>
> >> >>>> Create Table
> >> >>>>
> >> >>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
> >> >>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
> >> >>> -cfname a
> >> >>>>
> >> >>>> Load into Hbase table:
> >> >>>>
> >> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> >> >>>> -Dimporttsv.separator=','
> -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> >> >>>> /user/cmatta/projects/cmatta_test
> >> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
> >> >>>>
> >> >>>> Query (error):
> >> >>>>
> >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> >> `row_key`,
> >> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >> >>>> Error: SYSTEM ERROR: NumberFormatException:
> >> >>>>
> >> >>>> Fragment 0:0
> >> >>>>
> >> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> >> >>>> se-node11.se.lab:31010] (state=,code=0)
> >> >>>>
> >> >>>> Query that works on the column (c2) that doesn’t have a NULL value:
> >> >>>>
> >> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
> >> `row_key`,
> >> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >> >>>> +----------+---------+
> >> >>>> | row_key | EXPR$1 |
> >> >>>> +----------+---------+
> >> >>>> | row1 | 2 |
> >> >>>> | row2 | 4 |
> >> >>>> | row3 | 6 |
> >> >>>> | row4 | 8 |
> >> >>>> +----------+---------+
> >> >>>> 4 rows selected (0.308 seconds)
> >> >>>>
> >> >>>>
> >> >>>> Chris Matta
> >> >>>> cmatta@mapr.com
> >> >>>> 215-701-3146
> >> >>>>
> >> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <
> cmatta@mapr.com>
> >> >>>> wrote:
> >> >>>>
> >> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you provide
> >> me
> >> >>>> with
> >> >>>>> your query and a sample data set so I can open one?
> >> >>>>>
> >> >>>>> Chris Matta
> >> >>>>> cmatta@mapr.com
> >> >>>>> 215-701-3146
> >> >>>>>
> >> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> >> >>>> altekrusejason@gmail.com
> >> >>>>>> wrote:
> >> >>>>>
> >> >>>>>> A SQL level null is different than a null at the JAVA level that
> >> would
> >> >>>> be
> >> >>>>>> giving this exception (we don't represent nulls with an actual
> null
> >> >>> java
> >> >>>>>> object). There might be a way to work around it, but this is a
> bug
> >> in
> >> >>>>>> Drill. You should be able to make a cast between compatible types
> >> even
> >> >>>> if
> >> >>>>>> there are nulls in the dataset.
> >> >>>>>>
> >> >>>>>> Could you open a JIRA and put as much description about your data
> >> and
> >> >>>>>> query
> >> >>>>>> as possible? If you have the time to create a small repro,
> loading
> >> an
> >> >>>>>> Hbase
> >> >>>>>> table with a small set of data that produces the problem that
> >> would be
> >> >>>>>> very
> >> >>>>>> helpful. In any case, whatever you can provide will be useful the
> >> dev
> >> >>>> who
> >> >>>>>> picks up the JIRA.
> >> >>>>>>
> >> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
> >> cmatta@mapr.com>
> >> >>>>>> wrote:
> >> >>>>>>
> >> >>>>>>> Does changing the query to something like this work?:
> >> >>>>>>>
> >> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> >> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
> >> >>>>>>> ...
> >> >>>>>>>
> >> >>>>>>>
> >> >>>>>>>
> >> >>>>>>> Chris Matta
> >> >>>>>>> cmatta@mapr.com
> >> >>>>>>> 215-701-3146
> >> >>>>>>>
> >> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> >> >>>>>>> nathaniel.auvil@gmail.com
> >> >>>>>>>> wrote:
> >> >>>>>>>
> >> >>>>>>>> i have a MaprDB table which has nulls for some of the columns.
> >> >>>> When i
> >> >>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER) and
> >> >>> there
> >> >>>>>> are
> >> >>>>>>>> nulls instead of simply returning null for the value, there is
> a
> >> >>>>>>>> NullPointerException.
> >> >>>>>>>>
> >> >>>>>>>> Why not simply return null for the value if it is missing from
> a
> >> >>>> row?
> >> >>>>>>>>
> >> >>>>>>>
> >> >>>>>>
> >> >>>>>
> >> >>>>>
> >> >>>>
> >> >>>
> >>
> >>
> >
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Just bumping this, I’m sure other users are going to be running across this
eventually and I wanted to see if what I’m experiencing is expected (even
after setting ALTER SESSION SET
`drill.exec.functions.cast_empty_string_to_null` = true;), or if it’s a bug.
Chris Matta
cmatta@mapr.com
215-701-3146
On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cm...@mapr.com> wrote:
> Was the result I saw expected? Seems like I got back an empty string and
> not a NULL.
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <sk...@maprtech.com>
> wrote:
>
>> See below:
>>
>> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
>> >
>> > Hi,
>> > This is a system wide setting. Meaning, you need to say
>> >
>> > Alter system set `drill.exec.functions.cast_empty_string_to_null` =
>> true;
>>
>> To clarify, all options available through sys.options (except some used
>> for testing) can be set at system and session level. So what Chris did
>> (setting at session level) works.
>>
>> Thank you,
>> Sudheesh
>>
>> >
>> >
>> > Sent from my iPhone
>> >
>> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com>
>> wrote:
>> >>
>> >> I’m not sure if it worked, the result looks the same when casting as a
>> >> string (empty field, not a NULL value):
>> >>
>> >> 0: jdbc:drill:> ALTER SESSION SET
>> >> `drill.exec.functions.cast_empty_string_to_null` = true;
>> >> +-------+----------------------------------------------------------+
>> >> | ok | summary |
>> >> +-------+----------------------------------------------------------+
>> >> | true | drill.exec.functions.cast_empty_string_to_null updated. |
>> >> +-------+----------------------------------------------------------+
>> >> 1 row selected (1.606 seconds)
>> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>> >> Error: SYSTEM ERROR: NumberFormatException:
>> >>
>> >> Fragment 0:0
>> >>
>> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
>> >> se-node10.se.lab:31010] (state=,code=0)
>> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
>> >> Command canceled.`cmatta_test` x;
>> >> 0: jdbc:drill:> select 'hello' from sys.version;
>> >> +---------+
>> >> | EXPR$0 |
>> >> +---------+
>> >> | hello |
>> >> +---------+
>> >> 1 row selected (0.417 seconds)
>> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
>> >> +---------+
>> >> | EXPR$0 |
>> >> +---------+
>> >> | null |
>> >> +---------+
>> >> 1 row selected (0.4 seconds)
>> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
>> >> tta.`cmatta_test` x;
>> >> Error: SYSTEM ERROR: NumberFormatException:
>> >>
>> >> Fragment 0:0
>> >>
>> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
>> >> se-node10.se.lab:31010] (state=,code=0)
>> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> >> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
>> >> x;
>> >> +----------+---------+
>> >> | row_key | EXPR$1 |
>> >> +----------+---------+
>> >> | row1 | 1 |
>> >> | row2 | |
>> >> | row3 | 5 |
>> >> | row4 | 7 |
>> >> +----------+---------+
>> >> 4 rows selected (0.54 seconds)
>> >> 0: jdbc:drill:>
>> >>
>> >> Is this how it’s expected to work?
>> >>
>> >>
>> >> Chris Matta
>> >> cmatta@mapr.com
>> >> 215-701-3146
>> >>
>> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <ja...@dremio.com>
>> wrote:
>> >>>
>> >>> Does this system option not work:
>> >>>
>> >>> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` =
>> true;
>> >>>
>> >>> The reason the bug was marked INVALID is that SQL engines (not sure
>> about
>> >>> the spec) don't allow casting from empty string to number. The system
>> >>> option above is supposed to allow changing this behavior from the SQL
>> >>> standard for your type of situation. That being said, I see the docs
>> say
>> >>> "not supported in this release". Not sure why that is there. Can you
>> give
>> >>> it a try?
>> >>>
>> >>> That being said, it seems like the original issue was a NPE not a
>> NFE. That
>> >>> definitely seems like something else.
>> >>>
>> >>>
>> >>> --
>> >>> Jacques Nadeau
>> >>> CTO and Co-Founder, Dremio
>> >>>
>> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com>
>> >>> wrote:
>> >>>
>> >>>> Here is my attempt at building a reproduction, btw, it seems like
>> this is
>> >>>> the same issue as DRILL-862
>> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
>> >>> determined
>> >>>> the error to be invalid. Is trying to cast an empty string, or null
>> value
>> >>>> to an integer invalid? What's the workaround?
>> >>>>
>> >>>> Data
>> >>>>
>> >>>> row1,1,2
>> >>>> row2,,4
>> >>>> row3,5,6
>> >>>> row4,7,8
>> >>>>
>> >>>> Create Table
>> >>>>
>> >>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
>> >>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
>> >>> -cfname a
>> >>>>
>> >>>> Load into Hbase table:
>> >>>>
>> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
>> >>>> -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
>> >>>> /user/cmatta/projects/cmatta_test
>> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
>> >>>>
>> >>>> Query (error):
>> >>>>
>> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
>> `row_key`,
>> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>> >>>> Error: SYSTEM ERROR: NumberFormatException:
>> >>>>
>> >>>> Fragment 0:0
>> >>>>
>> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
>> >>>> se-node11.se.lab:31010] (state=,code=0)
>> >>>>
>> >>>> Query that works on the column (c2) that doesn’t have a NULL value:
>> >>>>
>> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as
>> `row_key`,
>> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>> >>>> +----------+---------+
>> >>>> | row_key | EXPR$1 |
>> >>>> +----------+---------+
>> >>>> | row1 | 2 |
>> >>>> | row2 | 4 |
>> >>>> | row3 | 6 |
>> >>>> | row4 | 8 |
>> >>>> +----------+---------+
>> >>>> 4 rows selected (0.308 seconds)
>> >>>>
>> >>>>
>> >>>> Chris Matta
>> >>>> cmatta@mapr.com
>> >>>> 215-701-3146
>> >>>>
>> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
>> >>>> wrote:
>> >>>>
>> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you provide
>> me
>> >>>> with
>> >>>>> your query and a sample data set so I can open one?
>> >>>>>
>> >>>>> Chris Matta
>> >>>>> cmatta@mapr.com
>> >>>>> 215-701-3146
>> >>>>>
>> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
>> >>>> altekrusejason@gmail.com
>> >>>>>> wrote:
>> >>>>>
>> >>>>>> A SQL level null is different than a null at the JAVA level that
>> would
>> >>>> be
>> >>>>>> giving this exception (we don't represent nulls with an actual null
>> >>> java
>> >>>>>> object). There might be a way to work around it, but this is a bug
>> in
>> >>>>>> Drill. You should be able to make a cast between compatible types
>> even
>> >>>> if
>> >>>>>> there are nulls in the dataset.
>> >>>>>>
>> >>>>>> Could you open a JIRA and put as much description about your data
>> and
>> >>>>>> query
>> >>>>>> as possible? If you have the time to create a small repro, loading
>> an
>> >>>>>> Hbase
>> >>>>>> table with a small set of data that produces the problem that
>> would be
>> >>>>>> very
>> >>>>>> helpful. In any case, whatever you can provide will be useful the
>> dev
>> >>>> who
>> >>>>>> picks up the JIRA.
>> >>>>>>
>> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
>> cmatta@mapr.com>
>> >>>>>> wrote:
>> >>>>>>
>> >>>>>>> Does changing the query to something like this work?:
>> >>>>>>>
>> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
>> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
>> >>>>>>> ...
>> >>>>>>>
>> >>>>>>>
>> >>>>>>>
>> >>>>>>> Chris Matta
>> >>>>>>> cmatta@mapr.com
>> >>>>>>> 215-701-3146
>> >>>>>>>
>> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
>> >>>>>>> nathaniel.auvil@gmail.com
>> >>>>>>>> wrote:
>> >>>>>>>
>> >>>>>>>> i have a MaprDB table which has nulls for some of the columns.
>> >>>> When i
>> >>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER) and
>> >>> there
>> >>>>>> are
>> >>>>>>>> nulls instead of simply returning null for the value, there is a
>> >>>>>>>> NullPointerException.
>> >>>>>>>>
>> >>>>>>>> Why not simply return null for the value if it is missing from a
>> >>>> row?
>> >>>>>>>>
>> >>>>>>>
>> >>>>>>
>> >>>>>
>> >>>>>
>> >>>>
>> >>>
>>
>>
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Was the result I saw expected? Seems like I got back an empty string and
not a NULL.
Chris Matta
cmatta@mapr.com
215-701-3146
On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <sk...@maprtech.com>
wrote:
> See below:
>
> > On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
> >
> > Hi,
> > This is a system wide setting. Meaning, you need to say
> >
> > Alter system set `drill.exec.functions.cast_empty_string_to_null` = true;
>
> To clarify, all options available through sys.options (except some used
> for testing) can be set at system and session level. So what Chris did
> (setting at session level) works.
>
> Thank you,
> Sudheesh
>
> >
> >
> > Sent from my iPhone
> >
> >> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com> wrote:
> >>
> >> I’m not sure if it worked, the result looks the same when casting as a
> >> string (empty field, not a NULL value):
> >>
> >> 0: jdbc:drill:> ALTER SESSION SET
> >> `drill.exec.functions.cast_empty_string_to_null` = true;
> >> +-------+----------------------------------------------------------+
> >> | ok | summary |
> >> +-------+----------------------------------------------------------+
> >> | true | drill.exec.functions.cast_empty_string_to_null updated. |
> >> +-------+----------------------------------------------------------+
> >> 1 row selected (1.606 seconds)
> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >> Error: SYSTEM ERROR: NumberFormatException:
> >>
> >> Fragment 0:0
> >>
> >> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> >> se-node10.se.lab:31010] (state=,code=0)
> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
> >> Command canceled.`cmatta_test` x;
> >> 0: jdbc:drill:> select 'hello' from sys.version;
> >> +---------+
> >> | EXPR$0 |
> >> +---------+
> >> | hello |
> >> +---------+
> >> 1 row selected (0.417 seconds)
> >> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> >> +---------+
> >> | EXPR$0 |
> >> +---------+
> >> | null |
> >> +---------+
> >> 1 row selected (0.4 seconds)
> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
> >> tta.`cmatta_test` x;
> >> Error: SYSTEM ERROR: NumberFormatException:
> >>
> >> Fragment 0:0
> >>
> >> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> >> se-node10.se.lab:31010] (state=,code=0)
> >> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> >> x;
> >> +----------+---------+
> >> | row_key | EXPR$1 |
> >> +----------+---------+
> >> | row1 | 1 |
> >> | row2 | |
> >> | row3 | 5 |
> >> | row4 | 7 |
> >> +----------+---------+
> >> 4 rows selected (0.54 seconds)
> >> 0: jdbc:drill:>
> >>
> >> Is this how it’s expected to work?
> >>
> >>
> >> Chris Matta
> >> cmatta@mapr.com
> >> 215-701-3146
> >>
> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <ja...@dremio.com>
> wrote:
> >>>
> >>> Does this system option not work:
> >>>
> >>> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` =
> true;
> >>>
> >>> The reason the bug was marked INVALID is that SQL engines (not sure
> about
> >>> the spec) don't allow casting from empty string to number. The system
> >>> option above is supposed to allow changing this behavior from the SQL
> >>> standard for your type of situation. That being said, I see the docs
> say
> >>> "not supported in this release". Not sure why that is there. Can you
> give
> >>> it a try?
> >>>
> >>> That being said, it seems like the original issue was a NPE not a NFE.
> That
> >>> definitely seems like something else.
> >>>
> >>>
> >>> --
> >>> Jacques Nadeau
> >>> CTO and Co-Founder, Dremio
> >>>
> >>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com>
> >>> wrote:
> >>>
> >>>> Here is my attempt at building a reproduction, btw, it seems like
> this is
> >>>> the same issue as DRILL-862
> >>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
> >>> determined
> >>>> the error to be invalid. Is trying to cast an empty string, or null
> value
> >>>> to an integer invalid? What's the workaround?
> >>>>
> >>>> Data
> >>>>
> >>>> row1,1,2
> >>>> row2,,4
> >>>> row3,5,6
> >>>> row4,7,8
> >>>>
> >>>> Create Table
> >>>>
> >>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
> >>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
> >>> -cfname a
> >>>>
> >>>> Load into Hbase table:
> >>>>
> >>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> >>>> -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> >>>> /user/cmatta/projects/cmatta_test
> >>>> maprfs:///user/cmatta/projects/testdata_hbase_null
> >>>>
> >>>> Query (error):
> >>>>
> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >>>> Error: SYSTEM ERROR: NumberFormatException:
> >>>>
> >>>> Fragment 0:0
> >>>>
> >>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> >>>> se-node11.se.lab:31010] (state=,code=0)
> >>>>
> >>>> Query that works on the column (c2) that doesn’t have a NULL value:
> >>>>
> >>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> >>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> >>>> +----------+---------+
> >>>> | row_key | EXPR$1 |
> >>>> +----------+---------+
> >>>> | row1 | 2 |
> >>>> | row2 | 4 |
> >>>> | row3 | 6 |
> >>>> | row4 | 8 |
> >>>> +----------+---------+
> >>>> 4 rows selected (0.308 seconds)
> >>>>
> >>>>
> >>>> Chris Matta
> >>>> cmatta@mapr.com
> >>>> 215-701-3146
> >>>>
> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
> >>>> wrote:
> >>>>
> >>>>> Did a Jira get opened for this? If not, Nathaniel, can you provide me
> >>>> with
> >>>>> your query and a sample data set so I can open one?
> >>>>>
> >>>>> Chris Matta
> >>>>> cmatta@mapr.com
> >>>>> 215-701-3146
> >>>>>
> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> >>>> altekrusejason@gmail.com
> >>>>>> wrote:
> >>>>>
> >>>>>> A SQL level null is different than a null at the JAVA level that
> would
> >>>> be
> >>>>>> giving this exception (we don't represent nulls with an actual null
> >>> java
> >>>>>> object). There might be a way to work around it, but this is a bug
> in
> >>>>>> Drill. You should be able to make a cast between compatible types
> even
> >>>> if
> >>>>>> there are nulls in the dataset.
> >>>>>>
> >>>>>> Could you open a JIRA and put as much description about your data
> and
> >>>>>> query
> >>>>>> as possible? If you have the time to create a small repro, loading
> an
> >>>>>> Hbase
> >>>>>> table with a small set of data that produces the problem that would
> be
> >>>>>> very
> >>>>>> helpful. In any case, whatever you can provide will be useful the
> dev
> >>>> who
> >>>>>> picks up the JIRA.
> >>>>>>
> >>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <
> cmatta@mapr.com>
> >>>>>> wrote:
> >>>>>>
> >>>>>>> Does changing the query to something like this work?:
> >>>>>>>
> >>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> >>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
> >>>>>>> ...
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> Chris Matta
> >>>>>>> cmatta@mapr.com
> >>>>>>> 215-701-3146
> >>>>>>>
> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> >>>>>>> nathaniel.auvil@gmail.com
> >>>>>>>> wrote:
> >>>>>>>
> >>>>>>>> i have a MaprDB table which has nulls for some of the columns.
> >>>> When i
> >>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER) and
> >>> there
> >>>>>> are
> >>>>>>>> nulls instead of simply returning null for the value, there is a
> >>>>>>>> NullPointerException.
> >>>>>>>>
> >>>>>>>> Why not simply return null for the value if it is missing from a
> >>>> row?
> >>>>>>>>
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>
> >>>
>
>
Re: NullPointers in type conversions
Posted by Sudheesh Katkam <sk...@maprtech.com>.
See below:
> On Sep 21, 2015, at 8:22 AM, USC <hs...@usc.edu> wrote:
>
> Hi,
> This is a system wide setting. Meaning, you need to say
>
> Alter system set `drill.exec.functions.cast_empty_string_to_null` = true;
To clarify, all options available through sys.options (except some used for testing) can be set at system and session level. So what Chris did (setting at session level) works.
Thank you,
Sudheesh
>
>
> Sent from my iPhone
>
>> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com> wrote:
>>
>> I’m not sure if it worked, the result looks the same when casting as a
>> string (empty field, not a NULL value):
>>
>> 0: jdbc:drill:> ALTER SESSION SET
>> `drill.exec.functions.cast_empty_string_to_null` = true;
>> +-------+----------------------------------------------------------+
>> | ok | summary |
>> +-------+----------------------------------------------------------+
>> | true | drill.exec.functions.cast_empty_string_to_null updated. |
>> +-------+----------------------------------------------------------+
>> 1 row selected (1.606 seconds)
>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>> Error: SYSTEM ERROR: NumberFormatException:
>>
>> Fragment 0:0
>>
>> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
>> se-node10.se.lab:31010] (state=,code=0)
>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
>> Command canceled.`cmatta_test` x;
>> 0: jdbc:drill:> select 'hello' from sys.version;
>> +---------+
>> | EXPR$0 |
>> +---------+
>> | hello |
>> +---------+
>> 1 row selected (0.417 seconds)
>> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
>> +---------+
>> | EXPR$0 |
>> +---------+
>> | null |
>> +---------+
>> 1 row selected (0.4 seconds)
>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
>> tta.`cmatta_test` x;
>> Error: SYSTEM ERROR: NumberFormatException:
>>
>> Fragment 0:0
>>
>> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
>> se-node10.se.lab:31010] (state=,code=0)
>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
>> x;
>> +----------+---------+
>> | row_key | EXPR$1 |
>> +----------+---------+
>> | row1 | 1 |
>> | row2 | |
>> | row3 | 5 |
>> | row4 | 7 |
>> +----------+---------+
>> 4 rows selected (0.54 seconds)
>> 0: jdbc:drill:>
>>
>> Is this how it’s expected to work?
>>
>>
>> Chris Matta
>> cmatta@mapr.com
>> 215-701-3146
>>
>>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <ja...@dremio.com> wrote:
>>>
>>> Does this system option not work:
>>>
>>> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` = true;
>>>
>>> The reason the bug was marked INVALID is that SQL engines (not sure about
>>> the spec) don't allow casting from empty string to number. The system
>>> option above is supposed to allow changing this behavior from the SQL
>>> standard for your type of situation. That being said, I see the docs say
>>> "not supported in this release". Not sure why that is there. Can you give
>>> it a try?
>>>
>>> That being said, it seems like the original issue was a NPE not a NFE. That
>>> definitely seems like something else.
>>>
>>>
>>> --
>>> Jacques Nadeau
>>> CTO and Co-Founder, Dremio
>>>
>>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com>
>>> wrote:
>>>
>>>> Here is my attempt at building a reproduction, btw, it seems like this is
>>>> the same issue as DRILL-862
>>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
>>> determined
>>>> the error to be invalid. Is trying to cast an empty string, or null value
>>>> to an integer invalid? What's the workaround?
>>>>
>>>> Data
>>>>
>>>> row1,1,2
>>>> row2,,4
>>>> row3,5,6
>>>> row4,7,8
>>>>
>>>> Create Table
>>>>
>>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
>>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
>>> -cfname a
>>>>
>>>> Load into Hbase table:
>>>>
>>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
>>>> -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
>>>> /user/cmatta/projects/cmatta_test
>>>> maprfs:///user/cmatta/projects/testdata_hbase_null
>>>>
>>>> Query (error):
>>>>
>>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>>>> Error: SYSTEM ERROR: NumberFormatException:
>>>>
>>>> Fragment 0:0
>>>>
>>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
>>>> se-node11.se.lab:31010] (state=,code=0)
>>>>
>>>> Query that works on the column (c2) that doesn’t have a NULL value:
>>>>
>>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>>>> +----------+---------+
>>>> | row_key | EXPR$1 |
>>>> +----------+---------+
>>>> | row1 | 2 |
>>>> | row2 | 4 |
>>>> | row3 | 6 |
>>>> | row4 | 8 |
>>>> +----------+---------+
>>>> 4 rows selected (0.308 seconds)
>>>>
>>>>
>>>> Chris Matta
>>>> cmatta@mapr.com
>>>> 215-701-3146
>>>>
>>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
>>>> wrote:
>>>>
>>>>> Did a Jira get opened for this? If not, Nathaniel, can you provide me
>>>> with
>>>>> your query and a sample data set so I can open one?
>>>>>
>>>>> Chris Matta
>>>>> cmatta@mapr.com
>>>>> 215-701-3146
>>>>>
>>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
>>>> altekrusejason@gmail.com
>>>>>> wrote:
>>>>>
>>>>>> A SQL level null is different than a null at the JAVA level that would
>>>> be
>>>>>> giving this exception (we don't represent nulls with an actual null
>>> java
>>>>>> object). There might be a way to work around it, but this is a bug in
>>>>>> Drill. You should be able to make a cast between compatible types even
>>>> if
>>>>>> there are nulls in the dataset.
>>>>>>
>>>>>> Could you open a JIRA and put as much description about your data and
>>>>>> query
>>>>>> as possible? If you have the time to create a small repro, loading an
>>>>>> Hbase
>>>>>> table with a small set of data that produces the problem that would be
>>>>>> very
>>>>>> helpful. In any case, whatever you can provide will be useful the dev
>>>> who
>>>>>> picks up the JIRA.
>>>>>>
>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Does changing the query to something like this work?:
>>>>>>>
>>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
>>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
>>>>>>> ...
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Chris Matta
>>>>>>> cmatta@mapr.com
>>>>>>> 215-701-3146
>>>>>>>
>>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
>>>>>>> nathaniel.auvil@gmail.com
>>>>>>>> wrote:
>>>>>>>
>>>>>>>> i have a MaprDB table which has nulls for some of the columns.
>>>> When i
>>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER) and
>>> there
>>>>>> are
>>>>>>>> nulls instead of simply returning null for the value, there is a
>>>>>>>> NullPointerException.
>>>>>>>>
>>>>>>>> Why not simply return null for the value if it is missing from a
>>>> row?
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
Re: NullPointers in type conversions
Posted by USC <hs...@usc.edu>.
Hi,
This is a system wide setting. Meaning, you need to say
Alter system set `drill.exec.functions.cast_empty_string_to_null` = true;
Sent from my iPhone
> On Sep 21, 2015, at 7:18 AM, Christopher Matta <cm...@mapr.com> wrote:
>
> I’m not sure if it worked, the result looks the same when casting as a
> string (empty field, not a NULL value):
>
> 0: jdbc:drill:> ALTER SESSION SET
> `drill.exec.functions.cast_empty_string_to_null` = true;
> +-------+----------------------------------------------------------+
> | ok | summary |
> +-------+----------------------------------------------------------+
> | true | drill.exec.functions.cast_empty_string_to_null updated. |
> +-------+----------------------------------------------------------+
> 1 row selected (1.606 seconds)
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> Error: SYSTEM ERROR: NumberFormatException:
>
> Fragment 0:0
>
> [Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
> se-node10.se.lab:31010] (state=,code=0)
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
> Command canceled.`cmatta_test` x;
> 0: jdbc:drill:> select 'hello' from sys.version;
> +---------+
> | EXPR$0 |
> +---------+
> | hello |
> +---------+
> 1 row selected (0.417 seconds)
> 0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
> +---------+
> | EXPR$0 |
> +---------+
> | null |
> +---------+
> 1 row selected (0.4 seconds)
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
> tta.`cmatta_test` x;
> Error: SYSTEM ERROR: NumberFormatException:
>
> Fragment 0:0
>
> [Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
> se-node10.se.lab:31010] (state=,code=0)
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
> x;
> +----------+---------+
> | row_key | EXPR$1 |
> +----------+---------+
> | row1 | 1 |
> | row2 | |
> | row3 | 5 |
> | row4 | 7 |
> +----------+---------+
> 4 rows selected (0.54 seconds)
> 0: jdbc:drill:>
>
> Is this how it’s expected to work?
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <ja...@dremio.com> wrote:
>>
>> Does this system option not work:
>>
>> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` = true;
>>
>> The reason the bug was marked INVALID is that SQL engines (not sure about
>> the spec) don't allow casting from empty string to number. The system
>> option above is supposed to allow changing this behavior from the SQL
>> standard for your type of situation. That being said, I see the docs say
>> "not supported in this release". Not sure why that is there. Can you give
>> it a try?
>>
>> That being said, it seems like the original issue was a NPE not a NFE. That
>> definitely seems like something else.
>>
>>
>> --
>> Jacques Nadeau
>> CTO and Co-Founder, Dremio
>>
>> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com>
>> wrote:
>>
>>> Here is my attempt at building a reproduction, btw, it seems like this is
>>> the same issue as DRILL-862
>>> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
>> determined
>>> the error to be invalid. Is trying to cast an empty string, or null value
>>> to an integer invalid? What's the workaround?
>>>
>>> Data
>>>
>>> row1,1,2
>>> row2,,4
>>> row3,5,6
>>> row4,7,8
>>>
>>> Create Table
>>>
>>> $ maprcli table create -path /user/cmatta/projects/cmatta_test
>>> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
>> -cfname a
>>>
>>> Load into Hbase table:
>>>
>>> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
>>> -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
>>> /user/cmatta/projects/cmatta_test
>>> maprfs:///user/cmatta/projects/testdata_hbase_null
>>>
>>> Query (error):
>>>
>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>>> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>>> Error: SYSTEM ERROR: NumberFormatException:
>>>
>>> Fragment 0:0
>>>
>>> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
>>> se-node11.se.lab:31010] (state=,code=0)
>>>
>>> Query that works on the column (c2) that doesn’t have a NULL value:
>>>
>>> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
>>> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
>>> +----------+---------+
>>> | row_key | EXPR$1 |
>>> +----------+---------+
>>> | row1 | 2 |
>>> | row2 | 4 |
>>> | row3 | 6 |
>>> | row4 | 8 |
>>> +----------+---------+
>>> 4 rows selected (0.308 seconds)
>>>
>>>
>>> Chris Matta
>>> cmatta@mapr.com
>>> 215-701-3146
>>>
>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
>>> wrote:
>>>
>>>> Did a Jira get opened for this? If not, Nathaniel, can you provide me
>>> with
>>>> your query and a sample data set so I can open one?
>>>>
>>>> Chris Matta
>>>> cmatta@mapr.com
>>>> 215-701-3146
>>>>
>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
>>> altekrusejason@gmail.com
>>>>> wrote:
>>>>
>>>>> A SQL level null is different than a null at the JAVA level that would
>>> be
>>>>> giving this exception (we don't represent nulls with an actual null
>> java
>>>>> object). There might be a way to work around it, but this is a bug in
>>>>> Drill. You should be able to make a cast between compatible types even
>>> if
>>>>> there are nulls in the dataset.
>>>>>
>>>>> Could you open a JIRA and put as much description about your data and
>>>>> query
>>>>> as possible? If you have the time to create a small repro, loading an
>>>>> Hbase
>>>>> table with a small set of data that produces the problem that would be
>>>>> very
>>>>> helpful. In any case, whatever you can provide will be useful the dev
>>> who
>>>>> picks up the JIRA.
>>>>>
>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
>>>>> wrote:
>>>>>
>>>>>> Does changing the query to something like this work?:
>>>>>>
>>>>>> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
>>>>>> table.cf.myColumn ELSE NULL END as INTEGER)
>>>>>> ...
>>>>>>
>>>>>>
>>>>>>
>>>>>> Chris Matta
>>>>>> cmatta@mapr.com
>>>>>> 215-701-3146
>>>>>>
>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
>>>>>> nathaniel.auvil@gmail.com
>>>>>>> wrote:
>>>>>>
>>>>>>> i have a MaprDB table which has nulls for some of the columns.
>>> When i
>>>>>>> create a select where i CAST(table.cf.myColumn as INTEGER) and
>> there
>>>>> are
>>>>>>> nulls instead of simply returning null for the value, there is a
>>>>>>> NullPointerException.
>>>>>>>
>>>>>>> Why not simply return null for the value if it is missing from a
>>> row?
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
I’m not sure if it worked, the result looks the same when casting as a
string (empty field, not a NULL value):
0: jdbc:drill:> ALTER SESSION SET
`drill.exec.functions.cast_empty_string_to_null` = true;
+-------+----------------------------------------------------------+
| ok | summary |
+-------+----------------------------------------------------------+
| true | drill.exec.functions.cast_empty_string_to_null updated. |
+-------+----------------------------------------------------------+
1 row selected (1.606 seconds)
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
Error: SYSTEM ERROR: NumberFormatException:
Fragment 0:0
[Error Id: 33e94b4d-6450-40bf-9f2c-bbbfab9f5990 on
se-node10.se.lab:31010] (state=,code=0)
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CASE WHERE x.`a`.`c1` is not null CAST(x.`a`.`c1` as INTEGER) fr
Command canceled.`cmatta_test` x;
0: jdbc:drill:> select 'hello' from sys.version;
+---------+
| EXPR$0 |
+---------+
| hello |
+---------+
1 row selected (0.417 seconds)
0: jdbc:drill:> select cast(NULL as INTEGER) from sys.version;
+---------+
| EXPR$0 |
+---------+
| null |
+---------+
1 row selected (0.4 seconds)
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(CAST(x.`a`.`c1` as varchar(64)) as INTEGER) from maprfs.cma
tta.`cmatta_test` x;
Error: SYSTEM ERROR: NumberFormatException:
Fragment 0:0
[Error Id: 71593a43-54ac-4e1d-b3d8-21a2d4d4acd6 on
se-node10.se.lab:31010] (state=,code=0)
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c1` as varchar(64)) from maprfs.cmatta.`cmatta_test`
x;
+----------+---------+
| row_key | EXPR$1 |
+----------+---------+
| row1 | 1 |
| row2 | |
| row3 | 5 |
| row4 | 7 |
+----------+---------+
4 rows selected (0.54 seconds)
0: jdbc:drill:>
Is this how it’s expected to work?
Chris Matta
cmatta@mapr.com
215-701-3146
On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <ja...@dremio.com> wrote:
> Does this system option not work:
>
> ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` = true;
>
> The reason the bug was marked INVALID is that SQL engines (not sure about
> the spec) don't allow casting from empty string to number. The system
> option above is supposed to allow changing this behavior from the SQL
> standard for your type of situation. That being said, I see the docs say
> "not supported in this release". Not sure why that is there. Can you give
> it a try?
>
> That being said, it seems like the original issue was a NPE not a NFE. That
> definitely seems like something else.
>
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > Here is my attempt at building a reproduction, btw, it seems like this is
> > the same issue as DRILL-862
> > <https://issues.apache.org/jira/browse/DRILL-862> where Jacques
> determined
> > the error to be invalid. Is trying to cast an empty string, or null value
> > to an integer invalid? What's the workaround?
> >
> > Data
> >
> > row1,1,2
> > row2,,4
> > row3,5,6
> > row4,7,8
> >
> > Create Table
> >
> > $ maprcli table create -path /user/cmatta/projects/cmatta_test
> > $ maprcli table cf create -path /user/cmatta/projects/cmatta_test
> -cfname a
> >
> > Load into Hbase table:
> >
> > hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> > -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> > /user/cmatta/projects/cmatta_test
> > maprfs:///user/cmatta/projects/testdata_hbase_null
> >
> > Query (error):
> >
> > 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> > CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > Error: SYSTEM ERROR: NumberFormatException:
> >
> > Fragment 0:0
> >
> > [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> > se-node11.se.lab:31010] (state=,code=0)
> >
> > Query that works on the column (c2) that doesn’t have a NULL value:
> >
> > 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> > CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> > +----------+---------+
> > | row_key | EXPR$1 |
> > +----------+---------+
> > | row1 | 2 |
> > | row2 | 4 |
> > | row3 | 6 |
> > | row4 | 8 |
> > +----------+---------+
> > 4 rows selected (0.308 seconds)
> >
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
> > wrote:
> >
> > > Did a Jira get opened for this? If not, Nathaniel, can you provide me
> > with
> > > your query and a sample data set so I can open one?
> > >
> > > Chris Matta
> > > cmatta@mapr.com
> > > 215-701-3146
> > >
> > > On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> > altekrusejason@gmail.com
> > > > wrote:
> > >
> > >> A SQL level null is different than a null at the JAVA level that would
> > be
> > >> giving this exception (we don't represent nulls with an actual null
> java
> > >> object). There might be a way to work around it, but this is a bug in
> > >> Drill. You should be able to make a cast between compatible types even
> > if
> > >> there are nulls in the dataset.
> > >>
> > >> Could you open a JIRA and put as much description about your data and
> > >> query
> > >> as possible? If you have the time to create a small repro, loading an
> > >> Hbase
> > >> table with a small set of data that produces the problem that would be
> > >> very
> > >> helpful. In any case, whatever you can provide will be useful the dev
> > who
> > >> picks up the JIRA.
> > >>
> > >> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
> > >> wrote:
> > >>
> > >> > Does changing the query to something like this work?:
> > >> >
> > >> > SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> > >> > table.cf.myColumn ELSE NULL END as INTEGER)
> > >> > ...
> > >> >
> > >> >
> > >> >
> > >> > Chris Matta
> > >> > cmatta@mapr.com
> > >> > 215-701-3146
> > >> >
> > >> > On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> > >> > nathaniel.auvil@gmail.com
> > >> > > wrote:
> > >> >
> > >> > > i have a MaprDB table which has nulls for some of the columns.
> > When i
> > >> > > create a select where i CAST(table.cf.myColumn as INTEGER) and
> there
> > >> are
> > >> > > nulls instead of simply returning null for the value, there is a
> > >> > > NullPointerException.
> > >> > >
> > >> > > Why not simply return null for the value if it is missing from a
> > row?
> > >> > >
> > >> >
> > >>
> > >
> > >
> >
>
Re: NullPointers in type conversions
Posted by Jacques Nadeau <ja...@dremio.com>.
Does this system option not work:
ALTER SESSION SET `drill.exec.functions.cast_empty_string_to_null` = true;
The reason the bug was marked INVALID is that SQL engines (not sure about
the spec) don't allow casting from empty string to number. The system
option above is supposed to allow changing this behavior from the SQL
standard for your type of situation. That being said, I see the docs say
"not supported in this release". Not sure why that is there. Can you give
it a try?
That being said, it seems like the original issue was a NPE not a NFE. That
definitely seems like something else.
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Thu, Sep 17, 2015 at 10:53 AM, Christopher Matta <cm...@mapr.com> wrote:
> Here is my attempt at building a reproduction, btw, it seems like this is
> the same issue as DRILL-862
> <https://issues.apache.org/jira/browse/DRILL-862> where Jacques determined
> the error to be invalid. Is trying to cast an empty string, or null value
> to an integer invalid? What's the workaround?
>
> Data
>
> row1,1,2
> row2,,4
> row3,5,6
> row4,7,8
>
> Create Table
>
> $ maprcli table create -path /user/cmatta/projects/cmatta_test
> $ maprcli table cf create -path /user/cmatta/projects/cmatta_test -cfname a
>
> Load into Hbase table:
>
> hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
> -Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
> /user/cmatta/projects/cmatta_test
> maprfs:///user/cmatta/projects/testdata_hbase_null
>
> Query (error):
>
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> Error: SYSTEM ERROR: NumberFormatException:
>
> Fragment 0:0
>
> [Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
> se-node11.se.lab:31010] (state=,code=0)
>
> Query that works on the column (c2) that doesn’t have a NULL value:
>
> 0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
> CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
> +----------+---------+
> | row_key | EXPR$1 |
> +----------+---------+
> | row1 | 2 |
> | row2 | 4 |
> | row3 | 6 |
> | row4 | 8 |
> +----------+---------+
> 4 rows selected (0.308 seconds)
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > Did a Jira get opened for this? If not, Nathaniel, can you provide me
> with
> > your query and a sample data set so I can open one?
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <
> altekrusejason@gmail.com
> > > wrote:
> >
> >> A SQL level null is different than a null at the JAVA level that would
> be
> >> giving this exception (we don't represent nulls with an actual null java
> >> object). There might be a way to work around it, but this is a bug in
> >> Drill. You should be able to make a cast between compatible types even
> if
> >> there are nulls in the dataset.
> >>
> >> Could you open a JIRA and put as much description about your data and
> >> query
> >> as possible? If you have the time to create a small repro, loading an
> >> Hbase
> >> table with a small set of data that produces the problem that would be
> >> very
> >> helpful. In any case, whatever you can provide will be useful the dev
> who
> >> picks up the JIRA.
> >>
> >> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
> >> wrote:
> >>
> >> > Does changing the query to something like this work?:
> >> >
> >> > SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> >> > table.cf.myColumn ELSE NULL END as INTEGER)
> >> > ...
> >> >
> >> >
> >> >
> >> > Chris Matta
> >> > cmatta@mapr.com
> >> > 215-701-3146
> >> >
> >> > On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> >> > nathaniel.auvil@gmail.com
> >> > > wrote:
> >> >
> >> > > i have a MaprDB table which has nulls for some of the columns.
> When i
> >> > > create a select where i CAST(table.cf.myColumn as INTEGER) and there
> >> are
> >> > > nulls instead of simply returning null for the value, there is a
> >> > > NullPointerException.
> >> > >
> >> > > Why not simply return null for the value if it is missing from a
> row?
> >> > >
> >> >
> >>
> >
> >
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Here is my attempt at building a reproduction, btw, it seems like this is
the same issue as DRILL-862
<https://issues.apache.org/jira/browse/DRILL-862> where Jacques determined
the error to be invalid. Is trying to cast an empty string, or null value
to an integer invalid? What's the workaround?
Data
row1,1,2
row2,,4
row3,5,6
row4,7,8
Create Table
$ maprcli table create -path /user/cmatta/projects/cmatta_test
$ maprcli table cf create -path /user/cmatta/projects/cmatta_test -cfname a
Load into Hbase table:
hbase org.apache.hadoop.hbase.mapreduce.ImportTsv
-Dimporttsv.separator=',' -Dimporttsv.columns=HBASE_ROW_KEY,a:c1,a:c2
/user/cmatta/projects/cmatta_test
maprfs:///user/cmatta/projects/testdata_hbase_null
Query (error):
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c1` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
Error: SYSTEM ERROR: NumberFormatException:
Fragment 0:0
[Error Id: cbcb3327-3699-4191-9c26-9b95c9922690 on
se-node11.se.lab:31010] (state=,code=0)
Query that works on the column (c2) that doesn’t have a NULL value:
0: jdbc:drill:> select cast(x.`row_key` as varchar(128)) as `row_key`,
CAST(x.`a`.`c2` as INTEGER) from maprfs.cmatta.`cmatta_test` x;
+----------+---------+
| row_key | EXPR$1 |
+----------+---------+
| row1 | 2 |
| row2 | 4 |
| row3 | 6 |
| row4 | 8 |
+----------+---------+
4 rows selected (0.308 seconds)
Chris Matta
cmatta@mapr.com
215-701-3146
On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cm...@mapr.com> wrote:
> Did a Jira get opened for this? If not, Nathaniel, can you provide me with
> your query and a sample data set so I can open one?
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <altekrusejason@gmail.com
> > wrote:
>
>> A SQL level null is different than a null at the JAVA level that would be
>> giving this exception (we don't represent nulls with an actual null java
>> object). There might be a way to work around it, but this is a bug in
>> Drill. You should be able to make a cast between compatible types even if
>> there are nulls in the dataset.
>>
>> Could you open a JIRA and put as much description about your data and
>> query
>> as possible? If you have the time to create a small repro, loading an
>> Hbase
>> table with a small set of data that produces the problem that would be
>> very
>> helpful. In any case, whatever you can provide will be useful the dev who
>> picks up the JIRA.
>>
>> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
>> wrote:
>>
>> > Does changing the query to something like this work?:
>> >
>> > SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
>> > table.cf.myColumn ELSE NULL END as INTEGER)
>> > ...
>> >
>> >
>> >
>> > Chris Matta
>> > cmatta@mapr.com
>> > 215-701-3146
>> >
>> > On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
>> > nathaniel.auvil@gmail.com
>> > > wrote:
>> >
>> > > i have a MaprDB table which has nulls for some of the columns. When i
>> > > create a select where i CAST(table.cf.myColumn as INTEGER) and there
>> are
>> > > nulls instead of simply returning null for the value, there is a
>> > > NullPointerException.
>> > >
>> > > Why not simply return null for the value if it is missing from a row?
>> > >
>> >
>>
>
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Did a Jira get opened for this? If not, Nathaniel, can you provide me with
your query and a sample data set so I can open one?
Chris Matta
cmatta@mapr.com
215-701-3146
On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse <al...@gmail.com>
wrote:
> A SQL level null is different than a null at the JAVA level that would be
> giving this exception (we don't represent nulls with an actual null java
> object). There might be a way to work around it, but this is a bug in
> Drill. You should be able to make a cast between compatible types even if
> there are nulls in the dataset.
>
> Could you open a JIRA and put as much description about your data and query
> as possible? If you have the time to create a small repro, loading an Hbase
> table with a small set of data that produces the problem that would be very
> helpful. In any case, whatever you can provide will be useful the dev who
> picks up the JIRA.
>
> On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > Does changing the query to something like this work?:
> >
> > SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> > table.cf.myColumn ELSE NULL END as INTEGER)
> > ...
> >
> >
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> > nathaniel.auvil@gmail.com
> > > wrote:
> >
> > > i have a MaprDB table which has nulls for some of the columns. When i
> > > create a select where i CAST(table.cf.myColumn as INTEGER) and there
> are
> > > nulls instead of simply returning null for the value, there is a
> > > NullPointerException.
> > >
> > > Why not simply return null for the value if it is missing from a row?
> > >
> >
>
Re: NullPointers in type conversions
Posted by Jason Altekruse <al...@gmail.com>.
A SQL level null is different than a null at the JAVA level that would be
giving this exception (we don't represent nulls with an actual null java
object). There might be a way to work around it, but this is a bug in
Drill. You should be able to make a cast between compatible types even if
there are nulls in the dataset.
Could you open a JIRA and put as much description about your data and query
as possible? If you have the time to create a small repro, loading an Hbase
table with a small set of data that produces the problem that would be very
helpful. In any case, whatever you can provide will be useful the dev who
picks up the JIRA.
On Thu, Sep 10, 2015 at 12:49 PM, Christopher Matta <cm...@mapr.com> wrote:
> Does changing the query to something like this work?:
>
> SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
> table.cf.myColumn ELSE NULL END as INTEGER)
> ...
>
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <
> nathaniel.auvil@gmail.com
> > wrote:
>
> > i have a MaprDB table which has nulls for some of the columns. When i
> > create a select where i CAST(table.cf.myColumn as INTEGER) and there are
> > nulls instead of simply returning null for the value, there is a
> > NullPointerException.
> >
> > Why not simply return null for the value if it is missing from a row?
> >
>
Re: NullPointers in type conversions
Posted by Christopher Matta <cm...@mapr.com>.
Does changing the query to something like this work?:
SELECT CAST(CASE WHEN table.cf.myColumn IS NOT NULL THEN
table.cf.myColumn ELSE NULL END as INTEGER)
...
Chris Matta
cmatta@mapr.com
215-701-3146
On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil <nathaniel.auvil@gmail.com
> wrote:
> i have a MaprDB table which has nulls for some of the columns. When i
> create a select where i CAST(table.cf.myColumn as INTEGER) and there are
> nulls instead of simply returning null for the value, there is a
> NullPointerException.
>
> Why not simply return null for the value if it is missing from a row?
>