EXISTS/NOT EXISTS as sub query

[jonathanstowe]

I've worked around this in code but I needed to add a "NOT EXISTS () to something like:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   ("upload".uploaded)::TIMESTAMP > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

which is basically what the #514 was for, and is implemented as:

my $last = self.^rs.max(*.refresh-time );
 Upload.^all.grep( *.uploaded > $last ).so;

So it becomes:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT EXISTS ( SELECT id FROM stats_view_refresh) OR
   ("upload".uploaded)::TIMESTAMP > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

Which I'd see as being implemented so you'd do something like:

my $last = self.^rs.max(*.refresh-time );
my $exists = self.^rs.map(*.id).exists.not;
 Upload.^all.grep( $exists or *.uploaded > $last ).so;

I thought it could be done like:

my $last = $svr.^rs.max(*.refresh-time );
my $exists = Red::AST::Function.new( args => [$svr.^rs.map(*.id).ast], func => 'NOT EXISTS');

say $upload.^all.grep( -> $v { $exists OR $v.uploaded > $last }).so;

Which is almost there but does something whacky with the generated SQL:

SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT EXISTS(SELECT
      "stats_view_refresh".id as "data_1"
   FROM
      "stats_view_refresh") OR "upload".uploaded IS NOT NULL > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1

Not urgent, I only found this when testing with an empty DB and have dealt with it in some slightly less elegant code.

If you don't get around to it I'll try and take a look next week.

[FCO]

Sorry for not seeing that before. I'll try to take a look at it tomorrow. Thanks for opening the issue.

[FCO]

I've been playing with that and getting No such method 'specialise' for invocant of type ... many times... Still working on that... :(

[FCO]

After long time (sorry for that), I'm back here trying to solve this issue while I have some time waiting... I think I misunderstood the problem the first time I tried (maybe I still misunderstand it now). I tried reproducing the problem with no success, maybe some of the changes on this interval have fixed that, I'm not sure...

❯ raku -Ilib -MRed -e'
model Upload {
    has $.id is serial;
    has DateTime $.uploaded is column;
    has $.refresh-time is column;
}

model StatsViewRefresh {
    has $.id is serial;
    has $.refresh-time is column;
}

red-defaults "SQLite";
schema(Upload, StatsViewRefresh).create;
my $last = StatsViewRefresh.^rs.max(*.refresh-time );
my $exists = Red::AST::Function.new( args => [StatsViewRefresh.^rs.map(*.id).ast], func => "NOT EXISTS");
my $*RED-DEBUG = True;
say Upload.^all.grep( -> $v { $exists OR $v.uploaded > $last }).so;
'
SQL : SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT EXISTS(SELECT
      "stats_view_refresh".id as "data_1"
   FROM
      "stats_view_refresh") OR "upload".uploaded > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1
BIND: []
False

That looks correct, no? Then I decided to add 2 methods to ResultSeq to facilitate that:

❯ raku -Ilib -MRed -e'
model Upload {
    has $.id is serial;
    has DateTime $.uploaded is column;
    has $.refresh-time is column;
}

model StatsViewRefresh {
    has $.id is serial;
    has $.refresh-time is column;
}

red-defaults "SQLite";
schema(Upload, StatsViewRefresh).create;
my $last = StatsViewRefresh.^rs.max(*.refresh-time );

my $*RED-DEBUG = True;
say Upload.^all.grep( -> $v { StatsViewRefresh.^all.exists.not OR $v.uploaded > $last }).so;

'

SQL : SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT (EXISTS(SELECT
      "stats_view_refresh".id , "stats_view_refresh".refresh_time as "refresh-time"
   FROM
      "stats_view_refresh")) OR "upload".uploaded > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1
BIND: []
False

and

❯ raku -Ilib -MRed -e'
model Upload {
    has $.id is serial;
    has DateTime $.uploaded is column;
    has $.refresh-time is column;
}

model StatsViewRefresh {
    has $.id is serial;
    has $.refresh-time is column;
}



red-defaults "SQLite";
schema(Upload, StatsViewRefresh).create;
my $last = StatsViewRefresh.^rs.max(*.refresh-time );

my $*RED-DEBUG = True;
say Upload.^all.grep( -> $v { StatsViewRefresh.^all.is-empty OR $v.uploaded > $last }).so;

'
SQL : SELECT
   count('*') > 0 as "data_1"
FROM
   "upload"
WHERE
   NOT (EXISTS(SELECT
      1 as "data_1"
   FROM
      "stats_view_refresh")) OR "upload".uploaded > ( SELECT
      max("stats_view_refresh".refresh_time) as "data_1"
   FROM
      "stats_view_refresh" )
LIMIT 1
BIND: []
False

Does that look correct?