Chaining join methods doesn't return an expected result

[nikolamaric]

Calling the join method in a chain doesn't work properly.

E.g.

query_A = Dataset.get_by_full_path('~/ds_A').query()
query_B = Dataset.get_by_full_path('~/ds_B').query()

print('\nquery_a:\n')
for i in query_A:
    print(i)

print('\nquery_b:\n')
for i in query_B:
    print(i)

print("\nquery_join = query_a.join(query_b, prefix='a_'): \n")
query_join = query_A.join(query_B, key='name', prefix='a_')
for item in query_join:
    print(item)

print("\nquery_c = query_join.join(query_b, prefix='b_'): \n")
for item in query_join.join(query_B, key='name', prefix='b_'):
    print(item)

returns the following results:

query_a:

{'_commit': '1329011331274567012', '_id': 'AXRFGPHQdjUM_98fiLSu', 'name': 'Francis Crick', 'id': '1'}
{'_commit': '1329011331274567012', '_id': 'AXRFGPHQdjUM_98fiLSv', 'name': 'James Watson', 'id': '2'}
{'_commit': '1329011331274567012', '_id': 'AXRFGPHQdjUM_98fiLSw', 'name': 'Rosalind Franklin', 'id': '3'}

query_b:

{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSi', 'name': 'Francis Crick', 'id': '1'}
{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSj', 'name': 'James Watson', 'id': '2'}
{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSk', 'name': 'Rosalind Franklin', 'id': '3'}
{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSl', 'name': 'Rosalind Franklin', 'id': '3'}
{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSm', 'name': 'Rosalind Franklin', 'id': '3'}
{'_commit': '1329009293354399140', '_id': 'AXRFFT2IdjUM_98fiLSn', 'name': 'James Watson', 'id': '2'}

query_join = query_a.join(query_b, prefix='a_'): 

{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'Francis Crick', 'a_id': '1', '_id': 'AXRFGPHQdjUM_98fiLSu_0', 'id': '1'}
{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'James Watson', 'a_id': '2', '_id': 'AXRFGPHQdjUM_98fiLSv_0', 'id': '2'}
{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'James Watson', 'a_id': '2', '_id': 'AXRFGPHQdjUM_98fiLSv_1', 'id': '2'}
{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'Rosalind Franklin', 'a_id': '3', '_id': 'AXRFGPHQdjUM_98fiLSw_0', 'id': '3'}
{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'Rosalind Franklin', 'a_id': '3', '_id': 'AXRFGPHQdjUM_98fiLSw_1', 'id': '3'}
{'a__commit': '1329009293354399140', '_commit': '1329011331274567012', 'name': 'Rosalind Franklin', 'a_id': '3', '_id': 'AXRFGPHQdjUM_98fiLSw_2', 'id': '3'}

query_c = query_join.join(query_b, prefix='b_'): 

{'_id': 'AXRFGPHQdjUM_98fiLSu_0', 'name': 'Francis Crick', 'b_id': '1', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140'], 'a_id': ['1'], '_commit': '1329011331274567012', 'id': '1'}
{'_id': 'AXRFGPHQdjUM_98fiLSv_0', 'name': 'James Watson', 'b_id': '2', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140', '1329009293354399140'], 'a_id': ['2', '2'], '_commit': '1329011331274567012', 'id': '2'}
{'_id': 'AXRFGPHQdjUM_98fiLSv_1', 'name': 'James Watson', 'b_id': '2', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140', '1329009293354399140'], 'a_id': ['2', '2'], '_commit': '1329011331274567012', 'id': '2'}
{'_id': 'AXRFGPHQdjUM_98fiLSw_0', 'name': 'Rosalind Franklin', 'b_id': '3', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140', '1329009293354399140', '1329009293354399140'], 'a_id': ['3', '3', '3'], '_commit': '1329011331274567012', 'id': '3'}
{'_id': 'AXRFGPHQdjUM_98fiLSw_1', 'name': 'Rosalind Franklin', 'b_id': '3', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140', '1329009293354399140', '1329009293354399140'], 'a_id': ['3', '3', '3'], '_commit': '1329011331274567012', 'id': '3'}
{'_id': 'AXRFGPHQdjUM_98fiLSw_2', 'name': 'Rosalind Franklin', 'b_id': '3', 'b__commit': '1329009293354399140', 'a__commit': ['1329009293354399140', '1329009293354399140', '1329009293354399140'], 'a_id': ['3', '3', '3'], '_commit': '1329011331274567012', 'id': '3'}

The result of the query_c is not good because it has only 6 records instead of 14. Why 14? query_join has 6 records and if we go through them and check how many records from query_b are matched with each record from query_join by key name, we will get the following results:

1. record: 1
2. record: 2
3. record: 2
4. record: 3
5. record: 3
6. record: 3

which is 14 in total.

[nikolamaric]

Seems that we do not have this issue, as multiple left joins do not work in the way I described in the previous comment, therefore I am closing the issue.