[GitHub] [spark] cloud-fan opened a new pull request #25107: [SPARK-28344][SQL] detect ambiguous self-join and fail the query

[GitBox <gi...@apache.org>]

cloud-fan opened a new pull request #25107: [SPARK-28344][SQL] detect ambiguous self-join and fail the query
URL: https://github.com/apache/spark/pull/25107
 
 
   ## What changes were proposed in this pull request?
   
   This is an alternative solution of https://github.com/apache/spark/pull/24442 . It fails the query if ambiguous self join is detected, instead of trying to de-ambiguous. The problem is that, it's hard to come up with a reasonable rule to de-ambiguous, the rule proposed by #24442 is mostly a heuristic.
   
   ### background of the self-join problem:
   This is a long-standing bug and I've seen many people complaining about it in JIRA/dev list.
   
   A typical example:
   ```
   val df1 = …
   val df2 = df1.filter(...)
   df1.join(df2, df1("a") > df2("a")) // returns empty result
   ```
   The root cause is, `Dataset.apply` is so powerful that users think it returns a column reference which can point to the column of the Dataset at anywhere. This is not true in many cases. `Dataset.apply` returns an `AttributeReference` . Different Datasets may share the same `AttributeReference`. In the example above, `df2` adds a Filter operator above the logical plan of `df1`, and the Filter operator reserves the output `AttributeReference` of its child. This means, `df1("a")` is exactly the same as `df2("a")`, and `df1("a") > df2("a")` always evaluates to false.
   
   ### The rule to detect ambiguous column reference caused by self join:
   We can reuse the infra in #24442 :
   1. each Dataset has a globally unique id.
   2. the `AttributeReference` returned by `Dataset.apply` carries the ID and column position(e.g. 3rd column of the Dataset) via metadata.
   3. the logical plan of a `Dataset` carries the ID via `TreeNodeTag`
   
   When self-join happens, the analyzer asks the right side plan of join to re-generate output attributes with new exprIds. Based on it, a simple rule to detect ambiguous self join is:
   1. find all column references (i.e. `AttributeReference`s with Dataset ID and col position) in the root node of a query plan.
   2. for each column reference, traverse the query plan tree, find a sub-plan that carries Dataset ID and the ID is the same as the one in the column reference.
   3. get the corresponding output attribute of the sub-plan by the col position in the column reference.
   4. if the corresponding output attribute has a different exprID than the column reference, then it means this sub-plan is in the right side of a self-join and has regenerated its output attributes. This is an ambiguous self join because the column reference points to a table being self-joined.
   
   
   ## How was this patch tested?
   
   existing tests and new test cases

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org