InputColumn class is ignoring index

[mamonu]

What happens?

I cannot access an array based column properly on Athena ( lat_lng_uncommon.lat , lat_lng_uncommon.long).
@ThomasHepworth has pinned the issue to InputColumn class

To Reproduce

import sqlglot
from splink.input_column import InputColumn
InputColumn("lat_long.test").input_name_as_tree
sqlglot.parse_one("lat_long.test")

this should give similar trees but it doesnt

OS:

Linux

Splink version:

3.4.1

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[ThomasHepworth]

Ok, so, to summarise the issue and my opinions.

The basic problem is that the generated sql tree from lat_long.test is analogous to that which would be generated if you were referencing tables in a simple selection or join scenario.

i.e. sqlglot evaluates:

sql = "lat_long.test"
sqlglot.parse_one(sql, read="duckdb")

in the same manner it would:

select lat_long.test
from lat_long_df as lat_long

Taking the lat_long portion to be a table reference, rather than the name of a column followed by the struct index.

This also means that simple tweaks such as changing the allowed signatures to include Column Identifier Identifier, do not fix the issue. Sqlglot will still incorrectly identify test as the column in the above, and as such will try to add the _l and _r suffix to that, instead of lat_long.

While we could add additional arguments and make tweaks to the various methods inside the InputColumn class to ensure it works, I'd recommend that we leave it alone.

My view is that this is all currently working as intended and any changes we make have the potential to break something else that we hadn't considered. Adding additional arguments to ensure this specific use case works seems excessive here and adds another consideration and potential area of confusion for users.

This issue is also easily overcome by the user putting in a little bit of extra legwork, which I'll add in the footnote below.

---

My suggestion for anyone who encounters this issue going forward would be to either:

1. Generate an array, instead of a struct. Index the array as you normally would lat_long[1].
2. Expand the struct to be multiple columns. In this instance, separate latitudinal and longitudinal columns.

[RossKen]

Add correct syntax to docstrings