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 cma...@mapr.com 215-701-3146 On Tue, Sep 22, 2015 at 2:33 PM, Christopher Matta <cma...@mapr.com> wrote: > Was the result I saw expected? Seems like I got back an empty string and > not a NULL. > > Chris Matta > cma...@mapr.com > 215-701-3146 > > On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <skat...@maprtech.com> > wrote: > >> See below: >> >> > On Sep 21, 2015, at 8:22 AM, USC <hsua...@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 <cma...@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 >> >> cma...@mapr.com >> >> 215-701-3146 >> >> >> >>> On Fri, Sep 18, 2015 at 9:56 PM, Jacques Nadeau <jacq...@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 <cma...@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 >> >>>> cma...@mapr.com >> >>>> 215-701-3146 >> >>>> >> >>>> On Tue, Sep 15, 2015 at 3:09 PM, Christopher Matta <cma...@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 >> >>>>> cma...@mapr.com >> >>>>> 215-701-3146 >> >>>>> >> >>>>> On Thu, Sep 10, 2015 at 5:03 PM, Jason Altekruse < >> >>>> altekruseja...@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 < >> cma...@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 >> >>>>>>> cma...@mapr.com >> >>>>>>> 215-701-3146 >> >>>>>>> >> >>>>>>> On Thu, Sep 10, 2015 at 12:49 PM, Nathaniel Auvil < >> >>>>>>> nathaniel.au...@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? >> >>>>>>>> >> >>>>>>> >> >>>>>> >> >>>>> >> >>>>> >> >>>> >> >>> >> >> >
