About Question enthuware.oce-jpad.v6.2.501 :

[swiss-chris]

Path expressions that contain NULL values during evaluation return NULL values.

what does this mean?

[admin]

For example, if you have e.contactInfo.address.zipcode in a query and if address is null for a particular employee, e.contactInfo.address.zipcode will return null.

[swiss-chris]

Got it. Thanks for the clarification.

[unvector]

Comparison or arithmetic operations with a NULL value always yield an unknown value.

Could you give some examples for such comparisons?

[admin]

For example, if you have AGE>30 in your sql, and if the AGE column contains nulls, then it is not guarantee whether the comparison will return true or false. Similarly for arithmetic operations, something like select AGE+10 from EMPLOYEE. Here, you should not rely on the values returned by the query.

[himaiMinh]

Just want to add my notes to explain these options.
I create three rows in InterestGroup table (id, description, name):
1 null null
2 null bigdata
3. AI AI

To demo option 1 compare non null with null , I create a JPQL :

Code: Select all

 select ig from InterestGroup ig where ig.description != 'AI' 

.
You may think the result is 1 and 2.
But the result is

Code: Select all

 [ArrayList Size=0]

1 and 2 are not returned because their null description compares with 'AI' is unknown.

To demo option 2 compare two null value, I create

Code: Select all

select ig from InterestGroup ig where ig.description = ig.name

You may think 1 is returned.
But the result is

Code: Select all

[InterestGroup : asso_attrib_overrides.InterestGroup[ id=3 ]]

1 is not returned because a null description is not equal to a null name.

To demo option 3,

Code: Select all

select ig from InterestGroup ig where ig.description is NULL

Result :

Code: Select all

[InterestGroup : asso_attrib_overrides.InterestGroup[ id=1 ]]
  [InterestGroup : asso_attrib_overrides.InterestGroup[ id=2 ]]

or

Code: Select all

select ig from InterestGroup ig where ig.description is NOT NULL

Result

Code: Select all

  [InterestGroup : asso_attrib_overrides.InterestGroup[ id=3 ]]