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 <[email protected]> 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 > [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? >>>>>>> >>>>>> >>>>> >>>> >>>> >>> >>
