See below: > On Sep 21, 2015, at 8:22 AM, USC <[email protected]> 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 <[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? >>>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>
