Why using a UDF in a SQL query leads to cartesian product?

by

Why using UDFs leads to a Cartesian product instead of a full outer join?

The reason why using UDFs require Cartesian product is quite simple. Since you pass an arbitrary function with possibly infinite domain and non-deterministic behavior the only way to determine its value is to pass arguments and evaluate. It means you simply have to check all possible pairs.

Simple equality from the other hand has a predictable behavior. If you use t1.foo = t2.bar condition you can simply shuffle t1 and t2 rows by foo and bar respectively to get expected result.

And just to be precise in the relational algebra outer join is actually expressed using natural join. Anything beyond that is simply an optimization.

Any way to force an outer join over the Cartesian product

Not really, unless you want to modify Spark SQL engine.

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. Concatenate columns in Apache Spark DataFrame
  4. Spark Window Functions – rangeBetween dates
  5. Spark SQL window function with complex condition
  6. Does SparkSQL support subquery?
  7. Including null values in an Apache Spark Join
  8. Spark sql queries vs dataframe functions
  9. Explode (transpose?) multiple columns in Spark SQL table
  10. How to get other columns when using Spark DataFrame groupby?
  11. get TopN of all groups after group by using Spark DataFrame
  12. How to aggregate over rolling time window with groups in Spark
  13. What is the difference between cube, rollup and groupBy operators?
  14. Difference between === null and isNull in Spark DataDrame
  15. SPARK SQL – case when then
  16. How do I get a SQL row_number equivalent for a Spark RDD?
  17. How to measure the execution time of a query on Spark
  18. Spark replacement for EXISTS and IN
  19. SQL query to group by day
  20. PySpark: How to fillna values in dataframe for specific columns?
  21. Oracle DB: How can I write query ignoring case?
  22. Doesn’t Linq to SQL miss the point? Aren’t ORM-mappers (SubSonic, etc.) sub-optimal solutions?
  23. SQL ORDER chars numerically
  24. Should I use the CASCADE DELETE rule? [duplicate]
  25. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  26. Return rows from INSERT with ON CONFLICT without needing to update
  27. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  28. Perform JOIN query in google cloud firestore
  29. SQL, How to Concatenate results?
  30. ALTER TABLE without locking the table?

Leave a Comment