[SQL] Join queries can fail if column PK names are the same across tables

[seanstory]

Bug Description

• Reported in MSSQL by a discuss user
• Reported in Postgres by @poremland

An advanced sync rules query like:

SELECT TOP 10 Bird.Name, Chicken.Food FROM Bird INNER JOIN Chicken ON Chicken.TagId = Bird.Id;

Should work, and we expect Joins to be one of the most common use cases for Advanced Sync Rules on SQL connectors. However, this type of Advanced Sync Rule leads to warnings in the logs like:

[FMWK][06:31:05][WARNING] [Connector id: u****************L, index name: search-m********, Sync job id: **************] Skipping custom query for tables Bird, Chicken as there are multiple tables with same primary key column name.

Blocks like

connectors/connectors/sources/mysql.py

Lines 564 to 568 in 6647771

	if has_duplicates(primary_key_columns):
	self._logger.warning(
	f"Skipping custom query for tables {format_list(tables)} as there are multiple primary key columns with the same name. Consider using 'AS' to uniquely identify primary key columns from different tables."
	)
	return

are in all our SQL connectors, but it's unclear to me what the purpose is, and why we're checking uniqueness for table PKs.

Both Paul and the discuss user have validated that using AS in the query to rename selected fields does not help.

To Reproduce

1. create a SQL db with two tables that both have a PK on an ID column
2. create a connector for that database
3. setup advanced sync rules to join your two tables on ID
4. note that your sync results in no data

Expected behavior

You should be able to use advanced sync rules for joins, even if the PK columns have the same name.

Environment

8.11-8.13

Additional context

Slack thread: https://elastic.slack.com/archives/C01795T48LQ/p1702570480692019

[seanstory]

Related issue: #467

[seanstory]

@timgrein is this still on your radar? Looks like MSSQL and Oracle are still incomplete?

[timgrein]

PostgreSQL fix: #2007
MySQL fix: #2005

[timgrein]

MSSQL fix: #2209

[timgrein]

Closing as all PRs for PostgreSQL, MySQL and MSSQL were merged