How to do Left Joins when compiling to PostgreSQL?

[katelynrwalker]

I saw the previous discussions (#71 and #186) on left joins, and the example in the tutorial (examples/scripts/outer_join.l). However when I follow those instructions, I'm getting an error when the compiled SQL tries to run on PostgreSQL: "HINT: No function matches the given name and argument types. You might need to add explicit type casts."

The goal is to left join one column(d) based on 3 keys (a, b, c). I've tried writing it a couple ways, all resulting in the same error:

Query(a:, b:, c:, d:) :- 
    Table1(a:, b:, c:),
    d AnyValue = (x:- Table2(a:, b:, c:, d: x));

GetD(a:, b:, c:) = x :-
  x AnyValue= (d :- Table2(a:, b:, c:, d:));

Query(a:, b:, c:, d:GetD(a:, b:, c:) :- 
    Table1(a:, b:, c:);

I also tried replacing AnyValue with String, just in case, but got the same error.

I'm unsure if this is a bug, if I'm writing the query wrong, or if there is an alternative way to write left joins that gets around this.

[EvgSkv]

Thanks for reporting this. I've fixed it in the main branch. Your code should work now if you are running main branch.

But it was not released to pip yet. To make it work right away you can define a custom function for taking any value.
Simplest is just to use Min:

TakeAnyValue(x) = Min(x);
Query(a:, b:, c:, d:) :- 
    Table1(a:, b:, c:),
    d TakeAnyValue= (x:- Table2(a:, b:, c:, d: x));

But Min won't work if the taken value is a struct or a list. For that use ArgMin with a constant value.

TakeAnyValue(x) = ArgMin(x -> 1);

Query(a:, b:, c:, d:) :- 
    Table1(a:, b:, c:),
    d TakeAnyValue= (x:- Table2(a:, b:, c:, d: x));

CoLab:
https://colab.research.google.com/drive/1afE6Kayh9vhSFmVTDlGYX3EZXA7YKfu6?usp=sharing

Please let me know if you have any further questions, or encouner any issues.

[katelynrwalker]

Thank you! The TakeAnyValue custom function worked. I am using pip install, so I'll make a note to check back.

[rvalek]

@EvgSkv just curious -- why here did you choose to implement AnyValue for Posgres like this:
'AnyValue': '(ARRAY_AGG(%s))[1]'
instead of the usual
'AnyValue': 'ANY_VALUE(%s)',
(which Postgres supports?

[EvgSkv]

@rvalek it seems that this is a recent addition. In CoLab any_value doesn't seem to work:
https://colab.research.google.com/drive/1bxulybLehV37qwU136nKX58t7eoHiDvy?usp=sharing

I am guessing the new version of postgres isn't propagated to the commonly used linux distros. Is that so?

If that's the case we'd just need to wait and then switch to use any_value.

[rvalek]

Oh, interesting, could be!
I just wanted to make sure there isn't a hidden reason to prefer (ARRAY_AGG(%s))[1]. Thank you, sir!