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:
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:
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:
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.