Cartesian product when working with stored queries or data partitions
Article ID: 15895
Updated On:
Products
Issue/Introduction
Sometimes, when creating a data partition or a scoreboard query, SDM will stop its creation as it will warn:
"AHD05800:Bad where clause. AHD03106:Where clause results in a Cartesian product"
For example, for the following constraint clause for a Data Partition, Service Desk returns the error message "AHD05800:Bad where clause. AHD03106:Where clause results in a Cartesian product":
Table: Change_Request
Type: View
Constraint: chg_ref_num LIKE '%123%' OR (requestor.organization = @root.organization)
stdlog would say: Clause "chg_ref_num LIKE '%123%' OR (requestor.organization = @root.organization)" rejected due to Cartesian product
If one were to make a data partition of the constituent components, "chg_ref_num LIKE '%123%'" by itself or "(requestor.organization = @root.organization)" also by itself, there should not be a problem. Putting them together seemingly as described above is an issue.
What is a Cartesian Product and why do I get warned for such an occurrence when creating a new data partition?
Environment
Component:
Resolution
The thing to understand with regards to data partitions is that they are merely add-ons to the backend query. Whenever a search is performed of any kind, all that happens is this query execution on the database:
Select * from TABLES Where X AND Y
X is the arguments one may introduce in the SDM operation in question as introduced by end user input. Y is the extra conditions introduced by data partitioning. It is important to understand that Y is always attached to a given search as an "AND" operator as it is a data partition and applies additional filtration to a given query to enforce what a user is allowed (and not allowed) to see based on Y's condition as introduced as the constraint in the Data Partition.
For example, someone is searching for all Active P1 CO's but there is the following Data Partition:
Table: Change_Request
Type: View
Constraint: chg_ref_num LIKE '%123%'
"X" is the user defined search (all Active P1 CO's) and Y is the data constraint (chg_ref_num LIKE '%123%')
Then the backend search is going to be:
Select * from Change_Request Where (CO is active and a P1) AND (chg_ref_num LIKE '%123%')
To understand why Cartesian Products are a problem, it's best to understand their concern at the database level, which is defined as a cross-join.
A Cartesian product will involve two tables in the database who do not have a relationship defined between the two tables. In such a case, the end result will be that each row in the first table winds up being paired with the rows in the second table. This is a very costly query that could take place as a result.
The key here in the initial example is the OR operator. The data partition in question again:
Table: Change_Request
Type: View
Constraint: chg_ref_num LIKE '%123%' OR (requestor.organization = @root.organization)
What winds up happening is that every entry in the first table result (change order) will be paired with the second table (contact, to accommodate for the requestor argument in the Constraint) to compare organization values.
This is problematic when one considers that most contact tables and most Change Requests contain large amounts of data and as a result, the execution of such a query will put a strain on the database, hence the checking if the query is safe from Cartesian products being introduced.
If one did a data partition that involved an AND operator instead of an OR, ie:
Table: Change_Request
Type: View
Constraint: chg_ref_num LIKE '%123%' AND (requestor.organization = @root.organization)
Then there is no Cartesian Product since it's a filter activity to narrow down the result further based on the chg_ref_num field and the requestor.organization.
Additional Information
Feedback
