[YSQL] WARNING: 0A000: WITH clause not supported yet

[bllewell]

Using YB-2.3.2.0 on macOS Big Sur Version 11.0.1.

Create and populate a test table:

\set VERBOSITY verbose
drop table if exists t cascade;
create table t(k int primary key, v int not null);
insert into t(k, v)
select g.v, g.v*2 from generate_series(1, 5) as g(v);

Postitive tests

with a as (
  select 10 as k, 17 as v)
insert into t(k, v)
select k, v from a;

with a as (
  select 1 as k, 42 as v)
update t set v = (select v from a)
where k in (select k from a);

select k, v from t order by k;

This is the result, exactly as expected:

 k  | v  
----+----
  1 | 42
  2 |  4
  3 |  6
  4 |  8
  5 | 10
 10 | 17

Negative test

with a as (
  select 1 as k)
delete from t
where k in (select k from a);

It cause this outcome:

WARNING:  0A000: WITH clause not supported yet
LINE 1: with a as (
        ^
HINT:  Please report the issue on https://github.com/YugaByte/yugabyte-db/issues
LOCATION:  raise_feature_not_supported_signal, gram.y:17327

The wording, WARNING: 0A000: WITH clause not supported yet is nonsense. The positive tests show that the WITH clause can be used without error when the final main substatement is an INSERT or UPDATE. And the YSQL doc here:

https://docs.yugabyte.com/latest/api/ysql/the-sql-language/with-clause/

has countless other examples where the final main substatement is a SELECT.

Notice that this query:

select k, v from t order by k;

produces this new result:

 k  | v  
----+----
  2 |  4
  3 |  6
  4 |  8
  5 | 10
 10 | 17

So in fact the DELETE did produce the intented effect.

Notice that, of course, the testcase runs without error on vanilla PostgreSQL. And the observer SELECT statements produce the same results.

[bllewell]

The following is a reminder to update the YSQL doc for the "Bacon Distance" use case in the "WITH clause" section when this issue is closed. Here is the ideal code:

with a as (
  select
    min(k)                  as min_k,
    path[cardinality(path)] as terminal
  from temp_paths
  group by path[cardinality(path)])
delete from temp_paths
where
(
  k not in (select min_k from a)
  and path[cardinality(path)] in (select terminal from a)
)
or
(
  path[cardinality(path)] in
  (
    select path[cardinality(path)]
    from paths
  )
);

But the first-published version will have this workaround:

delete from temp_paths
where
(
  k not in                    (select min(k)                  from temp_paths group by path[cardinality(path)]) and
  path[cardinality(path)] in  (select path[cardinality(path)] from temp_paths group by path[cardinality(path)])
)
or
(
  path[cardinality(path)] in
  (
    select path[cardinality(path)]
    from paths
  )
);

[deeps1991]

Fixed with the above commit.

[deeps1991]

@bllewell Just pasting this from our slack conversation for future reference: The fix is present in v2.5.2.0 which is the current latest release as of now, can see this issue [6511] here - https://github.com/yugabyte/yugabyte-db/releases/tag/v2.5.2

[bllewell]

Between when I filed this bug and when I published my “Recursive CTEs” doc, I had found a better, more compact way to formulate the required pruning SQL. So I decided, after all, not to change that code.

I’m glad that I was able to characterize a bug with a small testcase and that the bug has now been fixed.