How To Return a NULL Value in Select?

[Bill Blondin]

When querying a vertex with a null property value (e.g. Address.City where City is null), it appears that the null value is stripped from the response, even if the property is explicitly stated in the projection.

e.g.

SELECT Street1, Street2, City FROM Address WHERE @rid=#14:0

If City is null, the response will only include Street1 and Street2 property values.

How can I force the query to return a null for the City property?

I have tried IfNull(City, null) which doesn't trick it to return a null.  I can do IfNull(City, '') but empty string and null are not the same thing, so this solution is less than acceptable.

Thanks

Bill

[Bill Blondin]

I figured out what is causing the issue - when I inserted the record, I omitted explicitly setting the property value to NULL.  Instead, I left the property out of the insert completely, so when reading, it assumed the property simply doesn't exist, and therefore does not return the property.

I suppose this is correct behavior, even if it is a bit strange?

[Bill Blondin]

In further testing, there's actually a bug here.

If I specify the property name in the projection, I don't get the null properties back.  e.g. SELECT @rid, Street1, Street2, City, State, Postal FROM Address WHERE (@rid=#14:1) will omit Street2 from the response if it is null.

If use wildcard, then I get the null properties back.  e.g. SELECT FROM Address WHERE (@rid = #14:1)

[Luigi Dell'Aquila]

Hi Bill,

which version are you using? I think I fixed something like this in last 2.1 release candidate

Luigi

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bill Blondin]

Sorry - I don't get emails on comments so I didn't see this until today.

I'm using 2.0.11

[Hung Tran]

Hi Luigi,

I am running the same issue since 1.4+, and it's still an issue in 2.1.8. The issue is, if a field returns null in all rows in result set, it will be discarded. If null only at the 1st row, the field will be added at the last position in result set.

Remarks, it is just an issue for projection query only, and there is no issue with "SELECT FROM ..."

At the moment, until now I have to use IFNULL() as a workaround to get field values in the right order ;(

My Best,

Hung Tran