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.
Given the following query:
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
userincludes the join predicates. This results in incorrect outputs because rows from theusertable are being filtered based on the outer join conditions. Theuserside of the join should not be filtered by the predicates of the outer join.