MaxCompute:ODPS-0130252

Error: cartesian product is not allowed without mapjoin

Error message example

ODPS-0130252:[m,n] Cartesian product is not allowed - cartesian product is not allowed without mapjoin

Cause

A JOIN without an ON clause creates a Cartesian product. To prevent data bloat and performance degradation, MaxCompute prohibits this operation by default. This behavior is controlled by the odps.sql.allow.cartesian setting, which is false by default.

Solution

The primary solution is to add a join condition to your query. Depending on the join type and table sizes, consider the following approaches:

• When one of the tables in the JOIN is a small table, you can either add a join condition or set odps.sql.allow.cartesian to true.

• If both tables are large, you must add a join condition. Setting odps.sql.allow.cartesian to true is strongly discouraged because it can cause data bloat.

Query examples

-- Fails: This query attempts a Cartesian product, which is disabled by default.
odps> SET odps.sql.allow.cartesian=false;
odps> SELECT t1. *
FROM src t1
JOIN src t2;

FAILED: ODPS-0130252:[3,1] Cartesian product is not allowed - cartesian product is not allowed without mapjoin

-- Correct: Add an ON clause for an equi-join.
odps> SELECT t1. *
FROM src t1
JOIN src t2
ON t1.key = t2.key;

-- Correct: For non-equi joins, specify the small table in a MAPJOIN hint.
odps> SELECT /*+mapjoin(t1) * / t1.*
FROM src t1
JOIN src t2
ON t1.key > t2.key;