AdvacncedCriteria is adding not null constraint in generated query.

[zahid2948]

Hi
I am facing an issue where query is getting generated with not null constraint for supplied advancedCriteria.

below is the log

2023-11-16 12:48:01,218 INFO [com.isomorphic.servlet.IDACall] (default task-41) Performing 1 operation(s) [UA: Java/1.6.0_07]
2023-11-16 12:48:01,226 INFO [com.isomorphic.datasource.DSRequest] (default task-41) Executing isc_ListGrid_3->code.fetch[fetch-by-designator-and-value] rows: 0->-1 with
criteria: {
operator:"or",
_constructor:"AdvancedCriteria",
criteria:[
{
operator:"and",
criteria:[
{fieldName:"code_value", operator:"equals", value:"0016070"},
{fieldName:"code_designator", operator:"equals", value:"I10P"}
]
},
{
operator:"and",
criteria:[
{fieldName:"code_value", operator:"equals", value:"0016071"},
{fieldName:"code_designator", operator:"equals", value:"I10P"}
]
}
]
}
sortBy: [pk]
2023-11-16 12:48:01,259 INFO [com.isomorphic.sql.SQLDataSource] (default task-41) derived query: SELECT code.pk, code.code_value, code.code_designator, code.code_version, code.code_meaning, code.clinic_pk FROM code WHERE ($defaultWhereClause) and (code.clinic_pk is null or code.clinic_pk in (select c.pk from sono_user su join clinic c on (su.clinic_fk = c.pk) where su.user_id=$servletRequest.remoteUser )) ORDER BY $defaultOrderClause
2023-11-16 12:48:01,266 DEBUG [com.isomorphic.sql.SQLDataSource] (default task-41) 156: Executing SQL query on 'PostgreSQL': SELECT code.pk, code.code_value, code.code_designator, code.code_version, code.code_meaning, code.clinic_pk FROM code WHERE ((((code.code_value = '0016070' AND code.code_value IS NOT NULL) AND (code.code_designator = 'I10P' AND code.code_designator IS NOT NULL)) OR ((code.code_value = '0016071' AND code.code_value IS NOT NULL) AND (code.code_designator = 'I10P' AND code.code_designator IS NOT NULL)))) and (code.clinic_pk is null or code.clinic_pk in (select c.pk from sono_user su join clinic c on (su.clinic_fk = c.pk) where su.user_id='admin2' )) ORDER BY code.pk
2023-11-16 12:48:01,268 DEBUG [com.isomorphic.sql.PoolableSQLConnectionFactory] (default task-41) makeObject() created an unpooled Connection '426260436'
2023-11-16 12:48:01,268 DEBUG [com.isomorphic.sql.SQLConnectionManager] (default task-41) Borrowed connection '426260436'
2023-11-16 12:48:01,269 DEBUG [com.isomorphic.sql.SQLTransaction] (default task-41) Started new PostgreSQL transaction "426260436"
2023-11-16 12:48:01,269 DEBUG [com.isomorphic.sql.SQLDataSource] (default task-41) Setting DSRequest as being part of a transaction on connection 426260436


I dont want IS NOT NULL constraint to be included in query

[Isomorphic]

You can remove this via setting strictSQLFiltering:true:

https://smartclient.com/smartclient-...ctSQLFiltering

However note that this will cause filtering behavior that most people consider counter-intuitive.

If what you actually want is to include records that have a null value, you can also do that, for a specific query, by just adding an "or" with an isNull Criterion.