Transformations: Fix true inner join in joinByField transformation

[baldm0mma]

What is this feature?

This fixes the joinByField's "inner" join option to perform like an actual SQL-like inner join.

Example:

Students

StudentID	Name	Major
1	John	Computer Science
2	Emily	Mathematics
3	Michael	Physics
4	Jennifer	Chemistry

Enrollments

StudentID	CourseID	Grade
1	CS101	A
1	CS102	B
2	MATH201	A
3	PHYS101	B
5	HIST101	B

The result after applying the inner join transformation looks like the following:

StudentID	Name	Major	CourseID	Grade
1	John	Computer Science	CS101	A
1	John	Computer Science	CS102	B
2	Emily	Mathematics	MATH201	A
3	Michael	Physics	PHYS101	B

The inner join only includes rows where there is a match between the "StudentID" in both tables. In this case, the result does not include "Jennifer" from the "Students" table because there are no matching enrollments for her in the "Enrollments" table.

Why do we need this feature?

It is used by users, but has not been working as expected. Also, an escalation was created for it.

Who is this feature for?

All users.

Which issue(s) does this PR fix?:

Fixes #87361

Special notes for your reviewer:

Please check that:

• It works as expected from a user's perspective.
• If this is a pre-GA feature, it is behind a feature toggle.
• The docs are updated, and if this is a notable improvement, it's added to our What's New doc.

[baldm0mma]

Not relevant with the new behavior. seriesToColumns has been deprecated.

[nmarrs]

Looks great overall!

Test dashboard
Tabular Inner and Outer Join Test-1715211190626.json

[nmarrs]

Suggested change

	function joinInner(tables: AlignedData[]): Array<Array<string | number | null | undefined>> {
	function joinInnerTabular(tables: AlignedData[]): Array<Array<string | number | null | undefined>> {

Should we keep naming convention the same as the outer join for tabular data? 🤔

[nmarrs]

update: it seems that for inner we can do both series + tabular data while for some reason outer is differentiated and split out 🤔

[baldm0mma]

Correct. I was originally going to add an INNER (TABULAR) option, but the previous INNER wasn't actually behaving like an INNER anyway, so instead of adding yet another option, @leeoniya suggested we just correct the original INNER's behavior, which I think was the right move. The reason we split the 2 OUTER joins - I believe - is because they behave significantly differently then the other, depending on the data (time series or SQL-like). The INNER, however, due to the nature of an INNER join itself, will behave very similarly for both data types - sans a few edge cases.

[codeincarnate]

Gave it a spin and looks good! 🚀