Support for EXISTS constraint

[rvalek]

Hi Evgeniy!

It seems there is no way to create something like this from Logica:
select ... where exists (select 1 from ...)

I understand that similar semantics can be achieved by using ANY_VALUE aggregation together with IS NULL check
select ... where not (select any_value(1) from ...) is null

..., but specifically in PostgreSQL the query plan generated for the latter is vastly more complicated and performance of the latter is dramatically worse (for my complete queries this one change makes the difference between 10 minutes and 5 seconds).

Is there a way to make exists condition work from Logica currently? If not, I might be able to work on adding that, especially if you give me a few pointers as to how you see this implemented properly.

Cheers!

[EvgSkv]

Hi Robert,
the idea makes total sense. Let me look into this. I'll get back to you with where we are and what can we do in a couple days!
Best.

[EvgSkv]

@rvalek I looked at it. There is some type casting that was added for robustness, which prevents exists working as-is. But we can figure that part out.

For now I created exists branch, where I disabled the type casting of aggregates.

There you can do:

Exists(e) :- Constraint(SqlExpr("exists ({e})", {e:}));
# Making it sound more humane :-D
ForReal(x) = Aggr(x);

@Ground(T);
T(1);
T(3);
T(7);
T(20);

@Ground(D);
D(x) :- x in Range(10);

Q(x) :- D(x), Exists(ForReal{x :- T(x)});

Which produces (as expected):

+------+
| col0 |
+------+
| 1    |
| 3    |
| 7    |
+------+

By default SQL that you get looks like this:

SELECT
  D.col0 AS col0
FROM
  logica_home.D AS D
WHERE
  exists ((SELECT
    (CASE WHEN x_42 = 0 THEN D.col0 ELSE NULL END) AS logica_value
  FROM
    logica_home.T AS t_6_T, UNNEST(ARRAY[0]::numeric[]) as x_42
  WHERE
    (t_6_T.col0 = D.col0)));

It still has list unnesting that comes from making sure that aggregation expressions have correct scope.

We can disable it ad-hoc by an override:

dialects.PostgreSQL.DecorateCombineRule = lambda self, rule, var: rule

Then SQL looks like:

SELECT
  D.col0 AS col0
FROM
  logica_home.D AS D
WHERE
  exists ((SELECT
    D.col0 AS logica_value
  FROM
    logica_home.T AS t_6_T
  WHERE
    (t_6_T.col0 = D.col0)));

Which seems to be precisely what you were looking for.

Here and here are colabs with the two versions.

Please try these out and let me know how the two versions work for your querries and data. Let me know if you have any questions! We will go from there.

[rvalek]

Evgeniy, this is awesome. And your support is always superb!

We'll play around with it and keep you posted.

Thank you so much, and have a great weekend! :)

[rvalek]

Dear Evgeniy (or do you prefer Dr. Skvortsov? :),

I am sitting on a somewhat older version of Logica with a bunch of experiemtns and modifications, which doesn't seem to support the {} notation you've used on the last line. I also don't remember seeing before.
Is it some new syntactic sugar that is optional, or is something happening behind the scenes there that's important for this use-case?

Thank you!

[rvalek]

Ok, I think I figured it out -- it's basically syntactic sugar for something like this, right?

Q(x) :- 
  D(x), 
  subq Aggr= (x :- T(x)),
  Exists(subq);

Very neat!

[EvgSkv]

Hello Robert :-)

Yes, you've got it exactly right. A{x :- P(x)} is the same as y A= (x :- P(x)) and then using y. I should indeed update the documentation with this.

I've been thinking of this notation for a while. And recently I've been working on some educational examples, which were the last drop: the A{x :- P(x)} felt substantially easier to read, so I went ahead and added it.

Cheers!

PS:

Dear Evgeniy (or do you prefer Dr. Skvortsov? :),

Evgeniy is fine 😆

[mikko-ahonen]

Hello, I am new to Logica, but really like the approach.

I have programmed a little bit with Prolog before, but I find it a little bit difficult to learn Logica syntax.

Apparently Logica does not have constructs for predicate logic "there exists" and "for all".

I was struggling to implement the latter with count, as the count is not defined for empty query set.

And the example above (select ... where not (select any_value(1) from ...) is null) seems like a way to implement it, but any_value is not supported by SQLite3.

How would you implement it using Logica constructs? For me, the performance is not an issue.

[rvalek]

Hi Mikko!

This is how it would look in Logica:

Test(x:) :-
  A(x:),
  t AnyValue= (1 :- B(x:)),
  !IsNull(t);

This would select x's from A that do not exist in B:

SELECT
  A.x AS x
FROM
  A
WHERE
  NOT ((SELECT
    ANY_VALUE(1) AS logica_value
  FROM
    B
  WHERE
    (B.x = A.x)) IS NULL);

For this use-case other aggregations would work just as well (it doesn't matter what it returned from the subquery as long as it's not NULL:

  t Max= (1 :- B(x:)),

so you can use that instead of AnyValue to the same effect.

Also, you can add Any_Value or other functions to your SQLite when connecting to it programmatically.
For example, when the Logica compiler connects to SQLite, it adds a bunch of functions (implemented in Python) here:

logica/common/sqlite3_logica.py

Line 220 in 5c645ec

def SqliteConnect():

Does this help?

[rvalek]

By the way, I noticed that if you are running your Logica on SQLite through the logica run command, then ANY_VALUE should work, thanks to this:

logica/common/sqlite3_logica.py

Line 226 in 5c645ec

con.create_aggregate('ANY_VALUE', 1, TakeFirst)

[mikko-ahonen]

Thank you! I am testing Logica on SQLite but will be actually running it on Postgres. I tried these functions but documentation does not mention them and it is pretty unclear how to use them. I think I tried small caps versions of the names or something, and then found out that SQLite does not have any_value support built in.

[rvalek]

Yeah, getting started can be a little frustrating. As for the function naming -- the general pattern to map SQL names to Logica is this:
ANY_VALUE -> AnyValue -- dropping underscores and capitalizing all words.
You can see the full list of generally available functions here.
(there are also dialect-specific ones here and here)

[mikko-ahonen]

Yes, I could figure that one out. If you sre writing a new doc, just listing the functions and @tags would be of great help.