Why does Hive warn that this subquery would cause a Cartesian product?

user_3044 0
Viewed 4922

According to Hive's documentation it supports NOT IN subqueries in a WHERE clause, provided that the subquery is an uncorrelated subquery (does not reference columns from the main query).

However, when I attempt to run the trivial query below, I get an error FAILED: SemanticException Cartesian products are disabled for safety reasons. 

-- sample data
CREATE TEMPORARY TABLE foods (name STRING);
CREATE TEMPORARY TABLE vegetables (name STRING);

INSERT INTO foods VALUES ('steak'), ('eggs'), ('celery'), ('onion'), ('carrot');
INSERT INTO vegetables VALUES ('celery'), ('onion'), ('carrot');

-- the problematic query
SELECT *
FROM foods
WHERE foods.name NOT IN (SELECT vegetables.name FROM vegetables)

Note that if I use an IN clause instead of a NOT IN clause, it actually works fine, which is perplexing because the query evaluation structure should be the same in either case.

Is there a workaround for this, or another way to filter values from a query based on their presence in another table?

This is Hive 2.3.4 btw, running on an Amazon EMR cluster.

hiveql notin hive cross-join join
2 Answers

Not sure why you would get that error. One work around is to use not exists.

SELECT f.*
FROM foods f
WHERE NOT EXISTS (SELECT 1 
                  FROM vegetables v
                  WHERE v.name = f.name)

or a left join

SELECT f.*
FROM foods f 
LEFT JOIN vegetables v ON v.name = f.name
WHERE v.name is NULL
user_34318690

You got cartesian join because this is what Hive does in this case. vegetables table is very small (just one row) and it is being broadcasted to perform the cross (most probably map-join, check the plan) join. Hive does cross (map) join first and then applies filter. Explicit left join syntax with filter as @VamsiPrabhala said will force to perform left join, but in this case it works the same, because the table is very small and CROSS JOIN does not multiply rows.

Execute EXPLAIN on your query and you will see what is exactly happening.

user_27003440
Related