Transformations: Outer Join (tabular) is not joining consistent with SQL-style Outer Join

[baldm0mma]

What happened?

As title suggests, Outer Join (tabular) - Join by field Transformation - is not joining consistent with SQL-style Outer Join.

Escalation.

PR that introduced the behavior.

Consider the following tables... Table 1:

[
{
"Name": "Mithil",
"Value": "iPhone"
},
{
"Name": "Mithil",
"Value": "Android"
}
]

Table2:

[
{
"Name": "Mithil",
"Val": "Phone1"
},
{
"Name": "Mithil",
"Val": "Phone2"
},
{
"Name": "Mithil",
"Val": "Phone3"
},
{
"Name": "Mithil",
"Val": "Phone4"
},
{
"Name": "Mithil",
"Val": "Phone5"
}
]

With a "traditional" SQL-style Full Outer Join which joins on the Name field, which this functionality is attempting to mimic. We should see the following output:

Name  Value    Val
Mithil  Iphone  Phone1
Mithil  Android Phone1
Mithil  Iphone  Phone2
Mithil  Android Phone2
Mithil  Iphone  Phone3
Mithil  Android Phone3
Mithil  Iphone  Phone4
Mithil  Android Phone4
Mithil  Iphone  Phone5
Mithil  Android Phone5

However, this is the output instead:

Name   Value   Val
Mithil  Iphone  Phone1
Mithil  Android Phone1
Mithil  Iphone  Phone2
Mithil  Iphone  Phone3
Mithil  Iphone  Phone4
Mithil  Iphone  Phone5

Import this Dash to verify: debug-Transformation.join.by.fields-2024-05-02.02_11_05.json.txt

What did you expect to happen?

For the Transform to behave like a SQL-style Full Outer Join.

Did this work before?

This fiunctionality didn't exist before.

How do we reproduce it?

1. Download debug dash
2. See issue
3. ...
4. Profit

Is the bug inside a dashboard panel?

No response

Environment (with versions)?

ENVIRONMENT DETAILS On-Prem or HG: On-Prem (On-prem) Platform (Linux, Windows, K8s, AMG, etc): (Cloud) Instance Slug: N/A (HG) Permission to access instance (Y/N): N/A (HG) Link to affected page: N/A

Grafana Version: 10.4 License: Grafana Enterprise

Product: Grafana Component: Panel (Transformation)

Grafana platform?

I use Grafana Cloud

Datasource(s)?

No response