Problem with CMIS query with int metadata

[luca_ow]

Hi,

I have a problem when tring to search an integer metadata with a CMIS query; in particular when try to use "IS NULL" predicate.

To test the issue you can run the integration tests in the project (based on Alfresco SDK 4) shared at URL: https://github.com/luca86r/alfresco-error-case-query-cmis-intfield

As you can see, the test alfresco.errorcase.query.cmis.intfield.platformsample.TestCaseErrorIT#testQueryNull() fails.

Essentialy the project:

• declares the new metadata acme:testInt of type d:int
• defines a new Unit test class called TestCaseErrorIT

The test class TestCaseErrorIT run 3 separated tests:

• testQuery0 [TEST OK]
  • creates a new node setting the metadata acme:testInt to value 0
  • executes a CMIS query searching nodes with acme:testInt = 0
• testQuery1 [TEST OK]
  • creates a new node setting the metadata acme:testInt to value 1
  • executes a CMIS query searching nodes with acme:testInt = 1
• testQueryNull [TEST KO]
  • creates a new node setting the metadata acme:testInt to null value
  • executes a CMIS query searching nodes with acme:testInt set to null

I found that in testQuery0() and testQuery1() the CMIS query is converted in the corresponding SQL query:

select node.id as id 
from alf_node node 
where node.type_qname_id <> ? 
AND node.store_id = ? 
AND ( node.type_qname_id IN ( ? ) ) 
AND node.id IN 
( 
select PROP.node_id 
from alf_node_properties PROP 
where (? = PROP.qname_id) AND PROP.long_value = ? 
) 

Instead in testQueryNull() the CMIS query is converted in the corresponding SQL query:

select node.id as id 
from alf_node node 
where node.type_qname_id <> ? 
AND node.store_id = ? 
AND ( node.type_qname_id IN ( ? ) ) 
AND node.id NOT IN 
( 
select PROP.node_id 
from alf_node_properties PROP 
where (? = PROP.qname_id) AND PROP.long_value IS NOT NULL 
) 

As I can see, Alfresco never set the DB column alf_node_properties.long_value to null. Instead Alfresco uses alf_node_properties.actual_type_n and alf_node_properties.persisted_type_n to store null value for an integer metadata and conseguentelly the query doesn't return the expected results. It's correct?

Is this a bug of CMIS query?

Many thanks!

[angelborroy]

This looks like an issue.

You can open it at https://issues.alfresco.com

[luca_ow]

https://issues.alfresco.com/jira/browse/ALF-22106

[afaust]

In my eyes, the bug is the result of the stupid behaviour of Alfresco to actually store "null" at all. Setting a property to null should result in that property being deleted from the database, and in that case, the query would run successfully, as the result of the JOIN would find no matching alf_node_property row for the property in the first place.

In various projects I have run Alfresco with a custom behaviour and/or AOP interceptor on the NodeDAO which makes sure that upon a property being set to null, it is actually deleted from the database instead. This avoids such nonsense bugs / issues and additionally improves performance of the database by not bloating it (each row adds static overhead, even if the value is null).