Not null query failure.

[nasali]

Hi there,

Executing a query that has a not null check, against xsodata source that defines a Heffalumps collection with a nullable COLOR string property, fails like so:

<OData::QueryBuilder:0x007fe5f483aa40 @root="/Heffalumps", @expands=[], @filters=["COLOR+ne+null"], @order_bys=["ID+asc"], @navigation_paths=[], @skip=nil, @top=nil, @count=nil, @links_navigation_property=nil, @additional_params={}>
 ~ Failed to query: 400 Bad Request
 ~ /Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/abstract_response.rb:48:in `return!'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/request.rb:230:in `process_result'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/request.rb:178:in `block in transmit'
/Users/admin/.rvm/rubies/ruby-2.0.0-p195/lib/ruby/2.0.0/net/http.rb:852:in `start'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/request.rb:172:in `transmit'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/request.rb:64:in `execute'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/request.rb:33:in `execute'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/gems/rest-client-1.6.7/lib/restclient/resource.rb:51:in `get'
/Users/admin/.rvm/gems/ruby-2.0.0-p195/bundler/gems/ruby_odata-763d054fe34d/lib/ruby_odata/service.rb:100:in `execute'
...

URI Conventions state: "In addition to operators, a set of functions are also defined for use with the filter query string operator. The following table lists the available functions. Note: ISNULL or COALESCE operators are not defined. Instead, there is a null literal which can be used in comparisons."

What am I missing?

[visoft]

Hmm, that should be ok. What's the resulting query look like? You can call .query on the QueryBuilder object to see what is going to be called.

[nasali]

Thanks for the response. I grabbed .query on a newly created QueryBuilder to check for null colors and this is what I see:

Query is /Heffalumps?$filter=COLOR+eq+null

I'm starting to wonder whether the xsodata server simply does not support null queries.

[visoft]

The query looks ok to me. Try executing that query directly with something like Postman or Fiddler. Both should give you more of a clue to the problem.

[nasali]

I just tried executing the query with Postman and the response is the following:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error 
    xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code/>
    <message xml:lang="en-US">No property &#x0027;null&#x0027; exists in type &#x0027;datamapper.heffalumps.HeffalumpsType&#x0027;.</message>
</error>

Thanks again.

[visoft]

Maybe it has to be NULL in all caps? OData is very case-sensitive.

[nasali]

Yeah, I've tried all different combinations, and they all result in that same error like so:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error 
    xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code/>
    <message xml:lang="en-US">No property &#x0027;NULL&#x0027; exists in type &#x0027;datamapper.heffalumps.HeffalumpsType&#x0027;.</message>
</error>

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error 
    xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code/>
    <message xml:lang="en-US">No property &#x0027;Null&#x0027; exists in type &#x0027;datamapper.heffalumps.HeffalumpsType&#x0027;.</message>
</error>

It seems like the server-side code thinks it's a property, which probably means it doesn't support null. I'm posting a question on the HANA forums to find out what the deal is on the server.

Thank you.

[visoft]

Any more information about this?

[nasali]

As it turns out, the SAP HANA SP6 implementation of OData V2 is incomplete and does not yet support null keyword. Their next release is supposed to address that.