Content-Type: multipart/mixed; boundary="------------EhSnST8Hi570i0cSNCsZjdOh"
Message-ID: <29196a1e-ed47-c7ca-9be2-b1c636816183@enterprisedb.com>
Date: Wed, 20 Apr 2022 23:33:19 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.7.0
Content-Language: en-US
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Subject: Assert failure in CTE inlining with view and correlated subquery
Archived-At: 
 <https://www.postgresql.org/message-id/29196a1e-ed47-c7ca-9be2-b1c636816183%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------EhSnST8Hi570i0cSNCsZjdOh
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi,

it seems there's something wrong with CTE inlining when there's a view
containing a correlated subquery referencing the CTE. Consider a simple
example like this:

    create table results (
      id          serial primary key,
      run         text,
      tps         float4
    );

    create view results_agg as
    with base_tps as (
      select run, tps from results
    )
    select
        run,
        count(*) as runs,

        (select tps from base_tps b where b.run = r.run) AS base_tps

    from results r
    group by
        run
    order by
        run;

    explain SELECT run FROM results_agg ORDER BY 1;


This crashes on this assert in inline_cte():

    Assert(context.refcount == 0);

because the refcount value remains 1. There's a backtrace attached.

I don't know why exactly this happens, my knowledge of CTE inlining is
somewhat limited. The counter is clearly out of sync


but a couple more observations:

1) it fails all the way back to PG12, where CTE inlining was added

2) it does not happen if the CTE is defined as MATERIALIZED

                   QUERY PLAN
    -----------------------------------------
     Subquery Scan on results_agg
       ->  Sort
             Sort Key: r.run
             CTE base_tps
               ->  Seq Scan on results
             ->  HashAggregate
                   Group Key: r.run
                   ->  Seq Scan on results r
    (8 rows)

3) without asserts, it seems to work and the query generates this plan

                   QUERY PLAN
    -----------------------------------------
     Subquery Scan on results_agg
       ->  Sort
             Sort Key: r.run
             ->  HashAggregate
                   Group Key: r.run
                   ->  Seq Scan on results r
    (6 rows)

4) it does not seem to happen without the view, i.e. this works

    explain
    with base_tps as (
      select run, tps from results
    )
    select run from (
      select
        run,
        count(*) as runs,

        (select tps from base_tps b where b.run = r.run) AS base_tps

    from results r
    group by
        run
    order by
        run
    ) results_agg order by 1;

The difference between plans in (2) and (3) is interesting, because it
seems the CTE got inlined, so why was the refcount not decremented?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------EhSnST8Hi570i0cSNCsZjdOh
Content-Type: application/sql; name="test.sql"
Content-Disposition: attachment; filename="test.sql"
Content-Transfer-Encoding: base64

Y3JlYXRlIHRhYmxlIHJlc3VsdHMgKAogIGlkICAgICAgICAgIHNlcmlhbCBwcmltYXJ5IGtl
eSwKICBydW4gICAgICAgICB0ZXh0LAogIHRwcyAgICAgICAgIGZsb2F0NAopOwoKLS0gZXhw
bGFpbgotLSB3aXRoIGJhc2VfdHBzIGFzICgKLS0gICBzZWxlY3QgcnVuLCB0cHMgZnJvbSBy
ZXN1bHRzCi0tICkKLS0gc2VsZWN0IHJ1biBmcm9tICgKLS0gICBzZWxlY3QKLS0gICAgIHJ1
biwKLS0gICAgIGNvdW50KCopIGFzIHJ1bnMsCi0tIAotLSAgICAgKHNlbGVjdCB0cHMgZnJv
bSBiYXNlX3RwcyBiIHdoZXJlIGIucnVuID0gci5ydW4pIEFTIGJhc2VfdHBzCi0tIAotLSBm
cm9tIHJlc3VsdHMgcgotLSBncm91cCBieQotLSAgICAgcnVuCi0tIG9yZGVyIGJ5Ci0tICAg
ICBydW4KLS0gKSByZXN1bHRzX2FnZyBvcmRlciBieSAxOwoKCmNyZWF0ZSB2aWV3IHJlc3Vs
dHNfYWdnIGFzCndpdGggYmFzZV90cHMgYXMgKAogIHNlbGVjdCBydW4sIHRwcyBmcm9tIHJl
c3VsdHMKKQpzZWxlY3QKICAgIHJ1biwKICAgIGNvdW50KCopIGFzIHJ1bnMsCgogICAgKHNl
bGVjdCB0cHMgZnJvbSBiYXNlX3RwcyBiIHdoZXJlIGIucnVuID0gci5ydW4pIEFTIGJhc2Vf
dHBzCgpmcm9tIHJlc3VsdHMgcgpncm91cCBieQogICAgcnVuCm9yZGVyIGJ5CiAgICBydW47
CgpleHBsYWluIChjb3N0cyBvZmYpIFNFTEVDVCBydW4gRlJPTSByZXN1bHRzX2FnZyBPUkRF
UiBCWSAxOwo=
--------------EhSnST8Hi570i0cSNCsZjdOh
Content-Type: text/plain; charset=UTF-8; name="backtrace.txt"
Content-Disposition: attachment; filename="backtrace.txt"
Content-Transfer-Encoding: base64

TWlzc2luZyBzZXBhcmF0ZSBkZWJ1Z2luZm9zLCB1c2U6IGRuZiBkZWJ1Z2luZm8taW5zdGFs
bCBsaWJnY2MtMTEuMi4xLTkuZmMzNC54ODZfNjQKKGdkYikgYnQKIzAgIDB4MDAwMDcxNTlk
OWJlODJhMiBpbiByYWlzZSAoKSBmcm9tIC9saWI2NC9saWJjLnNvLjYKIzEgIDB4MDAwMDcx
NTlkOWJkMThhNCBpbiBhYm9ydCAoKSBmcm9tIC9saWI2NC9saWJjLnNvLjYKIzIgIDB4MDAw
MDAwMDAwMDk2ZDNmYSBpbiBFeGNlcHRpb25hbENvbmRpdGlvbiAoY29uZGl0aW9uTmFtZT1j
b25kaXRpb25OYW1lQGVudHJ5PTB4YWU0ZWQzICJjb250ZXh0LnJlZmNvdW50ID09IDAiLCBl
cnJvclR5cGU9ZXJyb3JUeXBlQGVudHJ5PTB4OWMzMDE3ICJGYWlsZWRBc3NlcnRpb24iLCBm
aWxlTmFtZT1maWxlTmFtZUBlbnRyeT0weGFlNGRhNiAic3Vic2VsZWN0LmMiLCBsaW5lTnVt
YmVyPWxpbmVOdW1iZXJAZW50cnk9MTE2NikgYXQgYXNzZXJ0LmM6NjkKIzMgIDB4MDAwMDAw
MDAwMDc4YWNkYSBpbiBpbmxpbmVfY3RlIChjdGU9MHgxMzRlMjQwLCByb290PTB4MTI0MjI5
OCkgYXQgc3Vic2VsZWN0LmM6MTE2NgojNCAgU1NfcHJvY2Vzc19jdGVzIChyb290PXJvb3RA
ZW50cnk9MHgxMjQyMjk4KSBhdCBzdWJzZWxlY3QuYzo5NjMKIzUgIDB4MDAwMDAwMDAwMDc4
MmZiMSBpbiBzdWJxdWVyeV9wbGFubmVyIChnbG9iPTB4MTM0ZGJjMCwgcGFyc2U9cGFyc2VA
ZW50cnk9MHgxMzRkYWE4LCBwYXJlbnRfcm9vdD1wYXJlbnRfcm9vdEBlbnRyeT0weDEzNDkx
ZTgsIGhhc1JlY3Vyc2lvbj1oYXNSZWN1cnNpb25AZW50cnk9ZmFsc2UsIHR1cGxlX2ZyYWN0
aW9uPTApIGF0IHBsYW5uZXIuYzo2NTAKIzYgIDB4MDAwMDAwMDAwMDc1MmFmZSBpbiBzZXRf
c3VicXVlcnlfcGF0aGxpc3QgKHJ0ZT08b3B0aW1pemVkIG91dD4sIHJ0aT08b3B0aW1pemVk
IG91dD4sIHJlbD08b3B0aW1pemVkIG91dD4sIHJvb3Q9PG9wdGltaXplZCBvdXQ+KSBhdCBh
bGxwYXRocy5jOjI1NzIKIzcgIHNldF9yZWxfc2l6ZSAocm9vdD08b3B0aW1pemVkIG91dD4s
IHJlbD08b3B0aW1pemVkIG91dD4sIHJ0aT08b3B0aW1pemVkIG91dD4sIHJ0ZT08b3B0aW1p
emVkIG91dD4pIGF0IGFsbHBhdGhzLmM6NDI0CiM4ICAweDAwMDAwMDAwMDA3NTUwODAgaW4g
c2V0X2Jhc2VfcmVsX3NpemVzIChyb290PTxvcHRpbWl6ZWQgb3V0PikgYXQgYWxscGF0aHMu
YzozMjUKIzkgIG1ha2Vfb25lX3JlbCAocm9vdD1yb290QGVudHJ5PTB4MTM0OTFlOCwgam9p
bmxpc3Q9am9pbmxpc3RAZW50cnk9MHgxMzRlMDc4KSBhdCBhbGxwYXRocy5jOjE4NwojMTAg
MHgwMDAwMDAwMDAwNzdiNGMyIGluIHF1ZXJ5X3BsYW5uZXIgKHJvb3Q9cm9vdEBlbnRyeT0w
eDEzNDkxZTgsIHFwX2NhbGxiYWNrPXFwX2NhbGxiYWNrQGVudHJ5PTB4NzdjNDUwIDxzdGFu
ZGFyZF9xcF9jYWxsYmFjaz4sIHFwX2V4dHJhPXFwX2V4dHJhQGVudHJ5PTB4N2ZmZjVhNTAw
YmEwKSBhdCBwbGFubWFpbi5jOjI3NgojMTEgMHgwMDAwMDAwMDAwNzgxMTIyIGluIGdyb3Vw
aW5nX3BsYW5uZXIgKHJvb3Q9PG9wdGltaXplZCBvdXQ+LCB0dXBsZV9mcmFjdGlvbj08b3B0
aW1pemVkIG91dD4pIGF0IHBsYW5uZXIuYzoxNDY3CiMxMiAweDAwMDAwMDAwMDA3ODM4YTEg
aW4gc3VicXVlcnlfcGxhbm5lciAoZ2xvYj1nbG9iQGVudHJ5PTB4MTM0ZGJjMCwgcGFyc2U9
cGFyc2VAZW50cnk9MHgxMjQxODE4LCBwYXJlbnRfcm9vdD1wYXJlbnRfcm9vdEBlbnRyeT0w
eDAsIGhhc1JlY3Vyc2lvbj1oYXNSZWN1cnNpb25AZW50cnk9ZmFsc2UsIHR1cGxlX2ZyYWN0
aW9uPXR1cGxlX2ZyYWN0aW9uQGVudHJ5PTApIGF0IHBsYW5uZXIuYzoxMDQ0CiMxMyAweDAw
MDAwMDAwMDA3ODNlZTMgaW4gc3RhbmRhcmRfcGxhbm5lciAocGFyc2U9MHgxMjQxODE4LCBx
dWVyeV9zdHJpbmc9PG9wdGltaXplZCBvdXQ+LCBjdXJzb3JPcHRpb25zPTIwNDgsIGJvdW5k
UGFyYW1zPTxvcHRpbWl6ZWQgb3V0PikgYXQgcGxhbm5lci5jOjQwNgojMTQgMHgwMDAwMDAw
MDAwODRlN2E4IGluIHBnX3BsYW5fcXVlcnkgKHF1ZXJ5dHJlZT0weDEyNDE4MTgsIHF1ZXJ5
X3N0cmluZz1xdWVyeV9zdHJpbmdAZW50cnk9MHgxMjQwN2QwICJTRUxFQ1QgcnVuIEZST00g
cmVzdWx0c19hZ2cgT1JERVIgQlkgMTsiLCBjdXJzb3JPcHRpb25zPWN1cnNvck9wdGlvbnNA
ZW50cnk9MjA0OCwgYm91bmRQYXJhbXM9Ym91bmRQYXJhbXNAZW50cnk9MHgwKSBhdCBwb3N0
Z3Jlcy5jOjg4MwojMTUgMHgwMDAwMDAwMDAwODRlOGExIGluIHBnX3BsYW5fcXVlcmllcyAo
cXVlcnl0cmVlcz0weDEzNGJmMDAsIHF1ZXJ5X3N0cmluZz1xdWVyeV9zdHJpbmdAZW50cnk9
MHgxMjQwN2QwICJTRUxFQ1QgcnVuIEZST00gcmVzdWx0c19hZ2cgT1JERVIgQlkgMTsiLCBj
dXJzb3JPcHRpb25zPWN1cnNvck9wdGlvbnNAZW50cnk9MjA0OCwgYm91bmRQYXJhbXM9Ym91
bmRQYXJhbXNAZW50cnk9MHgwKSBhdCBwb3N0Z3Jlcy5jOjk3NQojMTYgMHgwMDAwMDAwMDAw
ODRlYmFiIGluIGV4ZWNfc2ltcGxlX3F1ZXJ5IChxdWVyeV9zdHJpbmc9MHgxMjQwN2QwICJT
RUxFQ1QgcnVuIEZST00gcmVzdWx0c19hZ2cgT1JERVIgQlkgMTsiKSBhdCBwb3N0Z3Jlcy5j
OjExNjkKIzE3IDB4MDAwMDAwMDAwMDg1MDZkZiBpbiBQb3N0Z3Jlc01haW4gKGRibmFtZT08
b3B0aW1pemVkIG91dD4sIHVzZXJuYW1lPTxvcHRpbWl6ZWQgb3V0PikgYXQgcG9zdGdyZXMu
Yzo0NTQyCiMxOCAweDAwMDAwMDAwMDA3YzFkMTIgaW4gQmFja2VuZFJ1biAocG9ydD0weDEy
NjVlOTAsIHBvcnQ9MHgxMjY1ZTkwKSBhdCBwb3N0bWFzdGVyLmM6NDQ5MQojMTkgQmFja2Vu
ZFN0YXJ0dXAgKHBvcnQ9MHgxMjY1ZTkwKSBhdCBwb3N0bWFzdGVyLmM6NDIxOQojMjAgU2Vy
dmVyTG9vcCAoKSBhdCBwb3N0bWFzdGVyLmM6MTc5MwojMjEgMHgwMDAwMDAwMDAwN2MyYzIw
IGluIFBvc3RtYXN0ZXJNYWluIChhcmdjPWFyZ2NAZW50cnk9MywgYXJndj1hcmd2QGVudHJ5
PTB4MTIzYTNiMCkgYXQgcG9zdG1hc3Rlci5jOjE0NjUKIzIyIDB4MDAwMDAwMDAwMDUxMDcy
NSBpbiBtYWluIChhcmdjPTMsIGFyZ3Y9MHgxMjNhM2IwKSBhdCBtYWluLmM6MjAyCg==

--------------EhSnST8Hi570i0cSNCsZjdOh--