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? >> > > >> > >> > >
