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 [email protected] 215-701-3146 On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <[email protected]> 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 <[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? > > >> > > > > >> > > > >> > > > > > > > > >
