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 <[email protected]> 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 > [email protected] > 215-701-3146 > > On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <[email protected]> > 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 > > [email protected] > > 215-701-3146 > > > > On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse < > [email protected] > > > 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 <[email protected]> > >> 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 > >> > [email protected] > >> > 215-701-3146 > >> > > >> > On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil < > >> > [email protected] > >> > > 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? > >> > > > >> > > >> > > > > >
