[jira] [Created] (FLINK-38444) Join calculates upsert keys with null keys for left join

["Gustavo de Morais (Jira)" <ji...@apache.org>]

Gustavo de Morais created FLINK-38444:
-----------------------------------------

             Summary: Join calculates upsert keys with null keys for left join
                 Key: FLINK-38444
                 URL: https://issues.apache.org/jira/browse/FLINK-38444
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / Planner
            Reporter: Gustavo de Morais
             Fix For: 2.2.0


The planner currently considers a union of both the upsert keys from the left and from the right to be a valid resulting upsert key. That's true for inner joins but for left joins that leads to a resulting upsert key that contains columns that can be null, which is not valid.

Example and simplified repro steps:
{code:java}
-- Table 'orders' with an incomplete primary key
CREATE TABLE `orders_with_composite_key` (
  `order_id` BIGINT NOT NULL,
  `user_id` INT NOT NULL,
  `item_name` STRING,
  CONSTRAINT `PRIMARY` PRIMARY KEY (`order_id`, `user_id`) NOT ENFORCED
);
                                            
-- Table 'users'
CREATE TABLE `users` (
  `user_id` INT NOT NULL,
  `other_data` STRING,
  CONSTRAINT `PRIMARY` PRIMARY KEY (`user_id`) NOT ENFORCED
);

SELECT
    o.user_id,
    o.order_id,
    u.user_id
FROM `users` AS u
  LEFT JOIN `orders_with_composite_key` AS o
  ON o.user_id = u.user_id

-- Bug: this incorrectly infers the following upsert join from the join (user_id,order_id,user_id0)
{code}
This is not valid since the columns on the right can be null and can't be used as part of an upsert key.

This is where we calculate the unique keys and where we need to consider the nullability of the fields as we do for the other cases below [https://github.com/apache/flink/blob/master/flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/plan/metadata/FlinkRelMdUniqueKeys.scala#L593]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)