Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

BUG: Outer join conditions for sharded queries #16471

Closed
systay opened this issue Jul 25, 2024 · 0 comments · Fixed by #16472
Closed

BUG: Outer join conditions for sharded queries #16471

systay opened this issue Jul 25, 2024 · 0 comments · Fixed by #16472

Comments

@systay
Copy link
Collaborator

systay commented Jul 25, 2024

Given the following query:

select 1 from user left join user_extra on user.foo = 42 and user.bar = user_extra.bar

Our planner currently generates this plan:

{
  "OperatorType": "Projection",
  "Expressions": [
    "1 as 1"
  ],
  "Inputs": [
    {
      "OperatorType": "Join",
      "Variant": "LeftJoin",
      "JoinVars": {
        "user_bar": 0
      },
      "TableName": "`user`_user_extra",
      "Inputs": [
        {
          "OperatorType": "Route",
          "Variant": "Scatter",
          "Keyspace": {
            "Name": "user",
            "Sharded": true
          },
          "FieldQuery": "select `user`.bar from `user` where 1 != 1",
          "Query": "select `user`.bar from `user` where `user`.foo = 42",
          "Table": "`user`"
        },
        {
          "OperatorType": "Route",
          "Variant": "Scatter",
          "Keyspace": {
            "Name": "user",
            "Sharded": true
          },
          "FieldQuery": "select 1 from user_extra where 1 != 1",
          "Query": "select 1 from user_extra where user_extra.bar = :user_bar",
          "Table": "user_extra"
        }
      ]
    }
  ]
}

The problem is that the query sent to user includes the join predicates. This results in incorrect outputs because rows from the user table are being filtered based on the outer join conditions. The user side of the join should not be filtered by the predicates of the outer join.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant