Was the result I saw expected? Seems like I got back an empty string and not a NULL.
Chris Matta [email protected] 215-701-3146 On Tue, Sep 22, 2015 at 1:48 PM, Sudheesh Katkam <[email protected]> wrote: > 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? > >>>>>>>> > >>>>>>> > >>>>>> > >>>>> > >>>>> > >>>> > >>> > >
