Trino lineage fails to capture upstream columns when join and transformation is used

[amalakar]

Here is how to reproduce, the following query:

create table amalakar.new_query_log_1 as
with queries as
(
select * from 
hive.default.event_presto_query_logged p2
where ds='2021-12-08' and hr=3
)
SELECT
  p1.occurred_at as occurred_at,
  substr(p2.query_id, 1, 10) as new_query_id
  FROM queries p1 
  inner join queries p2
ON p1.query_id=p2.query_id

limit 10

Produces the following lineage:

{
  "hive.amalakar.new_query_log_1.new_query_id": [],
  "hive.amalakar.new_query_log_1.occurred_at": [
    {
      "columnName": "hive.default.event_presto_query_logged.occurred_at"
    }
  ]
}

Notice how the upstream of new_query_id is not being captured.

I did an impact analysis, and at lyft this bug impacts 79% of our lineage, only 21% is being captured accurately as of now.

num_valid_upstream	num_invalid_upstream	valid_percentage
111622	410457	21

[kokosing]

@Praveen2112 would you like to take a look?

[Praveen2112]

Taking a look at it. This is seen for columns with function where its argument are from a AliasedRelation.

[kokosing]

Does #10319 fixes your issue? @amalakar Are you able to test it in your setup?

[amalakar]

@Praveen2112 thanks for the amazing turnaround time, appreciate it. Least we can do is help test it. Our setup/deploy would have a turn-around time to test it. If it is okay, let's wait for the comments getting resolved and get the PR on approved state? Would be happy to deploy and give it a spin. Does that sound reasonable?

[kokosing]

Yes. Thank you for your help. I think you can test it now. It looks like there are comments about implementation details in PR. Addressing them should not change the scope of the fix. Testing now would prove if we covered all needed the cases in our tests.

[amalakar]

This is working now, thanks! I ran the following query again:

create table amalakar.new_query_log_new_patch

as
with queries as
(
select * from
hive.default.event_presto_query_logged p2
where ds='2019-01-20'
)
SELECT
  p1.occurred_at as occurred_at,
  substr(p2.query_id, 1, 10) as new_query_id
  FROM queries p1
  inner join queries p2
ON p1.query_id=p2.query_id

limit 10

Lineage I am seeing now is:

{
  "hive.amalakar.new_query_log_new_patch.occurred_at": [
    {
      "columnName": "hive.default.event_presto_query_logged.occurred_at"
    }
  ],
  "hive.amalakar.new_query_log_new_patch.new_query_id": [
    {
      "columnName": "hive.default.event_presto_query_logged.query_id"
    }
  ]
}

cc: Thanks @akashkatipally in helping test this!

[Praveen2112]

Fixed by #10319