NOT IN table

[rvalek]

Hi Evgeniy!

I am wondering what is the right way to arrive at the following SQL (the third line in particular):

SELECT id, name 
FROM  table1 
WHERE  id NOT IN (SELECT id FROM table2)

If I do something like: ~table2(id:), it compiles into a general construct for predicate negation with IS NULL, which is a little awkward and doesn't actually work on Trino (as it doesn't have full support for correlated subqueries).

Does this make sense? I will come up with a full code sample for this case if necessary.

Thank you!
-- Robert

[EvgSkv]

Doing it via negation is the canonical way of expressing this logic.
But that's right, unfortunately, not all queries compile on Trino just yet.
For now we can hack it as follows:

NotInTable2(id) :-
  Constraint(SqlExpr("{x} NOT IN (SELECT id FROM table2)",
                     {x: id}));

T(id:, name:) :-
  table1(id:, name:),
  NotInTable2(id);

SQL seems to compile as needed:

$ logica not_in.l print T
SELECT
  table1.id AS id,
  table1.name AS name
FROM
  table1
WHERE
  table1.id NOT IN (SELECT id FROM table2);

If it's a rare situation in your program it may be OK. Of course in the future we do want to make the negation compile properly.

Please let me know if you have further questions.

[rvalek]

As usual, full respect for you, Evgeniy! I think this might do it for us for now.

As a sidenote: my fellows and I are strong believes in Logica, and we would love to contribute as much in our power. If there are particular things you think we can start working on -- do let us know. <3

[rvalek]

although, I'm still have a problem because the path to table2 is parametrised with flags and I can seem to put in the table properly...

NotInTable(item:, column:) :-
  Constraint(SqlExpr("{item} NOT IN (SELECT id FROM ${F1}${F2}.table2)",
                     {item:}));

[rvalek]

I could achieve this with a hack like this (in one of our forks):
vrsn/logica-package@master...tables-in-sqlexpr

Implementing like this:

NotInTable(item:, column:, table:) :-
  Constraint(SqlExpr("{item} NOT IN (SELECT {column} FROM #{table}#)", {item:, column:, table:}));

Using like this:

NotInTable(item:id, column: "id", table: "${F1}${F2}.table2")

Is there a better way?

[EvgSkv]

Thank you for the kind words, Robert!

First to your technical question: I checked it, you're right. Flags in SqlExpr string aren't understood by compiler now. Your ad-hoc solution looks reasonable for your usecase.

I'm thinking it would be good to let SqlExpr accepts say tables named argument, where table names would be passed. So the NotInTable function would look as follows:

NotInTable(item:, table:) :-
  Constraint(SqlExpr("{item} NOT IN (SELECT id FROM {table})", {item:}, tables: {table:}));

To compile it we would compile the first positional argument into a record as we do now, and for the second we'd assert that all of the values are string literals and would use the literals without quote in formatting.
But actually it looks like you want to do the same for the column argument. So we'd need to call the argument something like verbatim.
For the language itself that's all not super pretty...
I wonder if there is a chance that Trino supports correlated subqueries any time soon, it would be easiest for us :-)

Btw, fyi there is another workaround to achieve the result of the original query, which is do the negation via manual aggregation:

NotInTable2(id) :-
  Constraint(SqlExpr("{x} NOT IN (SELECT id FROM table2)",
                     {x: id}));

T1(id:, name:) :-
  table1(id:, name:),
  NotInTable2(id);

# T2 is equivalent to T1, but doesn't use SqlExpr.
InTable2(id) Max= false :- table1(id:);
InTable2(id) Max= true :- table2(id:);

T2(id:, name:) :-
  table1(id:, name:),
  InTable2(id) == false;

Here it should be no problem to replace table2 with a flag. This is what I would do in this situation.

Thank you for offering your help!
At this point, success stories would be very valuable. So if there is any useful and/or interesting problem that you were able to solve with Logica, it would greatly help if you could write a paper or just a blog post about it.
Having more success stories to point to would be helpful for recruiting more users and understanding the most efficient direction of developing the language.

On the technical side I hope that we have a scaffolding for type inference some time soon, there could be some issues that we can fan out to more contributors. I'll post them in the issues, but if you want me to ping you on some messenger or social network when it's ready let me know.

[rvalek]

@EvgSkv thank you so much for this response!

I understand your proposed solution, that makes good sense! Regarding Trino -- they have limited support for correlated queries, and to my understanding, it's not currently priority for them to improve that particular area :(

The aggregator solution is interesting, I'll check whether it makes much difference efficiency-wise; thanks a lot for the suggestion!

Regarding the success stories -- I got you. I will check internally what we can produce.

Type inference sounds exciting! I will keep an eye on the issues and stay in touch.