MapD bucket() : MapD backend doesn't support <class 'ibis.expr.operations.NullLiteral'> operation!

[565ee]

>>> buckets = [0, 1, 3]
>>> bucketed = table.price_col.bucket(buckets).name('bucket')
>>> bucketed.value_counts().execute()
return error :
File "/home/sw/anaconda3/lib/python3.6/site-packages/ibis/mapd/operations.py", line 365, in raise_unsupported_op_error
raise com.UnsupportedOperationError(msg.format(type(op)))
ibis.common.UnsupportedOperationError: MapD backend doesn't support <class 'ibis.expr.operations.NullLiteral'> operation!

Backend=MapD MapD=4.1.1 ibis=0.14 Python=3.6
Ubuntu=18.04 Anaconda=5.1

[cpcloud]

cc @xmnlab

[xmnlab]

@randyzwitch it seems MapD doesn't allow Null literal, is it right?

ex:

mapdql> select *, NULL as "t" from tb10;
Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 1, column 11 to line 1, column 14: Illegal use of 'NULL'

[randyzwitch]

That seems right @xmnlab, I've never seen a query try to do that :)

[xmnlab]

@cpcloud so this error is expected? should we add some operations in MapD unsupported_op dictionary?

[cpcloud]

That seems right @xmnlab, I've never seen a query try to do that :)

This isn't totally crazy and isn't very different from something like select *, 1 as "t" from tb10;. Does MapD plan to support NULL literals in the future?

[randyzwitch]

I can't comment on whether we'd ever support NULL as a literal, as I don't manage the product development. Personally, I suspect we wouldn't, 1) as it doesn't seem to have a whole lot of value and 2) what type is a column of constant NULL? I suspect that's would be the bigger reason we wouldn't support it; while supporting a constant like 1 as "t" in a query might be memory inefficient, we at least know it's a smallint.

But, I'm not one of the OmniSci backend engineers either :)

[cpcloud]

what type is a column of constant NULL

Often a database will require a cast when using a null literal.

[randyzwitch]

If it's the case that this is really a desirable operation, my suggestion would be to submit an issue and someone with the proper background (either product or engineering) can comment on the specifics:

https://github.com/omnisci/mapd-core/issues

[xmnlab]

done! heavyai/heavydb#269 :)

[xmnlab]

@cpcloud should we change anything in the code related to this issue? or could this issue be closed?

[cpcloud]

@xmnlab If you write CAST(NULL AS FLOAT64) does that work? Or whatever the syntax is for a double precision floating point value is.

[xmnlab]

awesome! very good idea!

mapdql> select created_at, CAST(NULL AS FLOAT) as "t" from nyc_trees_2015_683k limit 1;
created_at|t
2015-10-01|NULL

[cpcloud]

@xmnlab Can you dig into what the generated code here is and see if it's reasonable to cast? I suspect that it is.

[xmnlab]

sure! I am working on that :)