Cannot Update Project or Project Subobject Attributes

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

After upgrade, when grid-updating or creating a new project or its subobject custom object instance, the UI error appears:

Error 500 - Internal Server Error. The Server could not retrieve the document due to server-configuration or the other technical problem. Contact your administrator

*This can happen in other workspaces as well

Steps To Reproduce:

Create an MVL attribute on Project Object. You can use the lookup ID :- LOOKUP_SEC_RESMGR for simplicity. Let's call this "Test MVL"
Create a Lookup Attribute on Project Object. You can use the same lookup as number 1. Let's call this "Test Lookup"
Create a String Attribute on "Project Financial Properties" Object. Let's call this "Test Attribute Fin"
Create a Process on Project Object which is auto-start enabled on Update. Save it.
Now Click on Set Condition to Open the Process Condition Builder
- Here we have to Build conditions for few of the attributes as listed below.
  - Project Name. build condition of Project Name = "xyz"
  - Manager. add the condition using OR as Manager != Previous value of Manager
  - Finish. add the condition using OR as Finish != Previous Value of Finish.
  - Baseline Finish. add the condition using OR as Baseline Finish != Previous value of Baseline Finish
  - Created By. add the condition using OR as Created by != Previous value of Created By
  - Last Updated By. add the condition using OR as Last Updated by != Previous value of Last Updated By.
  - Test MVL. Add the condition using OR as Test MVL = select 1 or 2 values from the lookup.
  - Page Layout. Add the condition using OR as Page Layout = Select a constant value from the lookup.
  - Test Attribute Fin. Add the condition using OR as Test Attribute Fin != Previous Value of Test Attribute Fin
- Resultant Condition looks something like this
- project.name == "xyz" || ( project.manager_id != project.manager_id__oldValue ) || project.schedule_finish != project.schedule_finish__oldValue || project.baseline_finish != project.baseline_finish__oldValue || project.created_by != project.created_by__oldValue || project.updated_by != project.updated_by__oldValue || ( mvLookup( project.z_test_attr_mvl, "LOOKUP_SEC_RESMGR", 5033053, "EQUALS" ) || mvLookup( project.z_test_attr_mvl, "LOOKUP_SEC_RESMGR", 5033052, "EQUALS" ) ) || ( project.page_layout == lookup("LOOKUP_PROJECT_PAGE_LAYOUT", 5104440) ) || project.z_test_attr1 != project.z_test_attr1__oldValue
Save the Conditions and Validate the Process.
Go to a Project and Save.

Expected Results: Project or its sub object instances should get saved upon changes.

Actual Results:- Error 500 - Internal Server Error. The Server could not retrieve the document due to server-configuration or the other technical problem. Contact your administrator is thrown.

Below are the logs generated in app ca.log

WARN 2022-05-18 06:48:08,399 [http-nio-8080-exec-2] com.niku (clarity:admin:session:odf.subObjectUpdate) SQL message: ERROR: missing FROM-clause entry for table "t0"
Position: 2120

Caused by: com.niku.union.persistence.PersistenceException:
SQL error code: 0
Error message: ERROR: current transaction is aborted, commands ignored until end of transaction block
Executed:
INSERT INTO NMS_MESSAGES (
ID, TOPIC, MESSAGE_CODE, OBJECT_ID, OBJECT_CODE, USER_ID,
MESSAGE_TYPE_CODE, MESSAGE, SEND_DATE, EXPIRATION_DATE,
CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

For ON Premise SQL Server Customers we see something like

SQL Exception with error code : 4104 message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The multi-part identifier "t0.id" could not be bound.]

Environment

Release : 16.0.2

Resolution

This is DE65166, fixed in 16.0.3 and 16.0.2.1

Workaround:

Run the below command in the database
- admin toggle-feature S207096_ANSI_JOIN 0
Flush the clarity cache by following the information in Flush Clarity cache

Important Warning: This workaround has to be rolled back on 16.0.3, 16.1.0, 16.1.1 and 16.1.2 due to known issues: