sql: query decorrelation error under recursive CTE in asyncpg

[gordthompson]

tl;dr: #71908 (comment)

---

Describe the problem

I've seen a number of issues regarding "could not decorrelate subquery" (e.g., #68086 , #67951 ) but this one might be a little different in that it seems to be related more specifically to asyncpg.

Reflection for PostgreSQL in SQLAlchemy uses queries that include ANY(). For example, to retrieve primary key columns it emits a query similar to this:

SELECT
cons.conname as name,
cons.conkey as key,
a.attnum as col_num,
a.attname as col_name
FROM
pg_catalog.pg_constraint cons
join pg_attribute a
  on cons.conrelid = a.attrelid AND
    a.attnum = ANY(cons.conkey)
WHERE
cons.contype = 'p'

If I create a table in an empty CockroachDB database

CREATE TABLE tbl1 (id int primary key, txt varchar(50))

then this code (using psycopg2) works fine:

import psycopg2

reflect_query = """\
SELECT
cons.conname as name,
cons.conkey as key,
a.attnum as col_num,
a.attname as col_name
FROM
pg_catalog.pg_constraint cons
join pg_attribute a
  on cons.conrelid = a.attrelid AND
    a.attnum = ANY(cons.conkey)
WHERE
cons.contype = 'p'
"""

# connect to CockroachDB
conn = psycopg2.connect(host="localhost", port=26257, user="root", dbname="defaultdb")
cur = conn.cursor()
cur.execute(reflect_query)
print(cur.fetchall())
# [('primary', [1], 1, 'id')]

However, if I connect to CockroachDB using asyncpg I get the "could not decorrelate subquery" error

import asyncio
import asyncpg

reflect_query = """\
SELECT
cons.conname as name,
cons.conkey as key,
a.attnum as col_num,
a.attname as col_name
FROM
pg_catalog.pg_constraint cons
join pg_attribute a
  on cons.conrelid = a.attrelid AND
    a.attnum = ANY(cons.conkey)
WHERE
cons.contype = 'p'
"""

async def run():
    cr = {"host": "localhost", "port": 26257, "user": "root", "database": "defaultdb"}
    conn = await asyncpg.connect(**cr)
    values = await conn.fetch(reflect_query)  # Error: "could not decorrelate subquery"
    print(values)
    await conn.close()


loop = asyncio.get_event_loop()
loop.run_until_complete(run())

However, if I use asyncpg to connect to PostgreSQL then the same query does not fail:

async def run():
    pg = {"host": "192.168.0.199", "user": "scott", "password": "tiger", "database": "test"}
    conn = await asyncpg.connect(**pg)
    values = await conn.fetch(reflect_query)
    print(values)
    # [<Record name='tbl1_pkey' key=[1] col_num=1 col_name='id'>]
    await conn.close()

It also seems to be directly related to ANY() because if I remove the AND a.attnum = ANY(cons.conkey) then the error does not occur for cockroachdb+asyncpg.

Environment:

• CockroachDB CCL v21.2.0-rc.1 (x86_64-unknown-linux-gnu, built 2021/10/18 14:45:39, go1.16.6)
• Server OS: Xubuntu 20.04
• asyncpg 0.24.0 (compared with psycopg2 2.9.1)

Additional context

One possible workaround might be to have the sqlalchemy-cockroachdb dialect override SQLAlchemy's code for PostgreSQL reflection with code that does not emit queries that use ANY().

Epic CRDB-18886
Jira issue: CRDB-10828

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @RaduBerinde (commented on Could not decorrelate subquery inside of CASE statement #67951)
• @cockroachdb/sql-experience (found keywords: pg_,SQLAlchemy,psycopg2)
• @cockroachdb/bulk-io (found keywords: import)
• @rafiss (commented on opt: could not decorrelate subquery #68086)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[otan]

the "real" issue is that asyncpg is trying to execute

WITH RECURSIVE
	typeinfo_tree
		(
			oid,
			ns,
			name,
			kind,
			basetype,
			elemtype,
			elemdelim,
			range_subtype,
			attrtypoids,
			attrnames,
			depth
		)
		AS (
			SELECT
				ti.oid,
				ti.ns,
				ti.name,
				ti.kind,
				ti.basetype,
				ti.elemtype,
				ti.elemdelim,
				ti.range_subtype,
				ti.attrtypoids,
				ti.attrnames,
				0
			FROM
				(
					SELECT
						t.oid AS oid,
						ns.nspname AS ns,
						t.typname AS name,
						t.typtype AS kind,
						(
							CASE
							WHEN t.typtype = 'd'
							THEN (
								WITH RECURSIVE
									typebases (oid, depth)
										AS (
											SELECT
												t2.typbasetype
													AS oid,
												0 AS depth
											FROM
												pg_type
													AS t2
											WHERE
												t2.oid
												= t.oid
											UNION ALL
												SELECT
													t2.typbasetype
														AS oid,
													tb.depth
													+ 1
														AS depth
												FROM
													pg_type
														AS t2,
													typebases
														AS tb
												WHERE
													(
														tb.oid
														= t2.oid
													)
													AND (
															t2.typbasetype
															!= 0
														)
										)
								SELECT
									oid
								FROM
									typebases
								ORDER BY
									depth DESC
								LIMIT
									1
							)
							ELSE NULL
							END
						)
							AS basetype,
						t.typelem AS elemtype,
						elem_t.typdelim AS elemdelim,
						range_t.rngsubtype AS range_subtype,
						(
							CASE
							WHEN t.typtype = 'c'
							THEN (
								SELECT
									array_agg(
										ia.atttypid ORDER BY
											ia.attnum
									)
								FROM
									pg_attribute AS ia
									INNER JOIN pg_class
											AS c ON
											(
												ia.attrelid
												= c.oid
											)
								WHERE
									(
										(ia.attnum > 0)
										AND (
												NOT
													ia.attisdropped
											)
									)
									AND (c.reltype = t.oid)
							)
							ELSE NULL
							END
						)
							AS attrtypoids,
						(
							CASE
							WHEN t.typtype = 'c'
							THEN (
								SELECT
									array_agg(
										ia.attname::STRING ORDER BY
											ia.attnum
									)
								FROM
									pg_attribute AS ia
									INNER JOIN pg_class
											AS c ON
											(
												ia.attrelid
												= c.oid
											)
								WHERE
									(
										(ia.attnum > 0)
										AND (
												NOT
													ia.attisdropped
											)
									)
									AND (c.reltype = t.oid)
							)
							ELSE NULL
							END
						)
							AS attrnames
					FROM
						pg_catalog.pg_type AS t
						INNER JOIN pg_catalog.pg_namespace
								AS ns ON
								(ns.oid = t.typnamespace)
						LEFT JOIN pg_type AS elem_t ON
								(
									(
										(t.typlen = -1)
										AND (t.typelem != 0)
									)
									AND (
											t.typelem
											= elem_t.oid
										)
								)
						LEFT JOIN pg_range AS range_t ON
								(t.oid = range_t.rngtypid)
				)
					AS ti
			WHERE
				ti.oid = ANY ($1::OID[])
			UNION ALL
				SELECT
					ti.oid,
					ti.ns,
					ti.name,
					ti.kind,
					ti.basetype,
					ti.elemtype,
					ti.elemdelim,
					ti.range_subtype,
					ti.attrtypoids,
					ti.attrnames,
					tt.depth + 1
				FROM
					(
						SELECT
							t.oid AS oid,
							ns.nspname AS ns,
							t.typname AS name,
							t.typtype AS kind,
							(
								CASE
								WHEN t.typtype = 'd'
								THEN (
									WITH RECURSIVE
										typebases
											(oid, depth)
											AS (
												SELECT
													t2.typbasetype
														AS oid,
													0
														AS depth
												FROM
													pg_type
														AS t2
												WHERE
													t2.oid
													= t.oid
												UNION ALL
													SELECT
														t2.typbasetype
															AS oid,
														tb.depth
														+ 1
															AS depth
													FROM
														pg_type
															AS t2,
														typebases
															AS tb
													WHERE
														(
															tb.oid
															= t2.oid
														)
														AND (
																t2.typbasetype
																!= 0
															)
											)
									SELECT
										oid
									FROM
										typebases
									ORDER BY
										depth DESC
									LIMIT
										1
								)
								ELSE NULL
								END
							)
								AS basetype,
							t.typelem AS elemtype,
							elem_t.typdelim AS elemdelim,
							range_t.rngsubtype
								AS range_subtype,
							(
								CASE
								WHEN t.typtype = 'c'
								THEN (
									SELECT
										array_agg(
											ia.atttypid ORDER BY
												ia.attnum
										)
									FROM
										pg_attribute AS ia
										INNER JOIN pg_class
												AS c ON
												(
													ia.attrelid
													= c.oid
												)
									WHERE
										(
											(ia.attnum > 0)
											AND (
													NOT
														ia.attisdropped
												)
										)
										AND (
												c.reltype
												= t.oid
											)
								)
								ELSE NULL
								END
							)
								AS attrtypoids,
							(
								CASE
								WHEN t.typtype = 'c'
								THEN (
									SELECT
										array_agg(
											ia.attname::STRING ORDER BY
												ia.attnum
										)
									FROM
										pg_attribute AS ia
										INNER JOIN pg_class
												AS c ON
												(
													ia.attrelid
													= c.oid
												)
									WHERE
										(
											(ia.attnum > 0)
											AND (
													NOT
														ia.attisdropped
												)
										)
										AND (
												c.reltype
												= t.oid
											)
								)
								ELSE NULL
								END
							)
								AS attrnames
						FROM
							pg_catalog.pg_type AS t
							INNER JOIN pg_catalog.pg_namespace
									AS ns ON
									(
										ns.oid
										= t.typnamespace
									)
							LEFT JOIN pg_type AS elem_t ON
									(
										(
											(t.typlen = -1)
											AND (
													t.typelem
													!= 0
												)
										)
										AND (
												t.typelem
												= elem_t.oid
											)
									)
							LEFT JOIN pg_range AS range_t ON
									(
										t.oid
										= range_t.rngtypid
									)
					)
						AS ti,
					typeinfo_tree AS tt
				WHERE
					(
						(
							(tt.elemtype IS NOT NULL)
							AND (ti.oid = tt.elemtype)
						)
						OR (
								(tt.attrtypoids IS NOT NULL)
								AND (
										ti.oid
										= ANY (
												tt.attrtypoids
											)
									)
							)
					)
					OR (
							(tt.range_subtype IS NOT NULL)
							AND (ti.oid = tt.range_subtype)
						)
		)
SELECT
	DISTINCT
	*,
	basetype::REGTYPE::STRING AS basetype_name,
	elemtype::REGTYPE::STRING AS elemtype_name,
	range_subtype::REGTYPE::STRING AS range_subtype_name
FROM
	typeinfo_tree
ORDER BY
	depth DESC;

Where $1 can be {23}.

Source: https://github.com/MagicStack/asyncpg/blob/383c711eb68bc6a042c121e1fddfde0cdefb8068/asyncpg/introspection.py#L8

[otan]

It also seems to be directly related to ANY() because if I remove the AND a.attnum = ANY(cons.conkey) then the error does not occur for cockroachdb+asyncpg.

I still get it without that line.

[otan]

cc @ZhouXing19 i'm surprised this wasn't caught in the tests here:

cockroach/pkg/cmd/roachtest/tests/asyncpg.go

Line 102 in 041aaff

fmt.Sprintf("cd /mnt/data1/asyncpg && PGPORT=%d python3 setup.py test", cockroachPort),

are we sure we're hitting cockroach with those queries?

we should also fix

cockroach/pkg/cmd/roachtest/tests/asyncpg.go

Line 54 in 041aaff

"master",

to a certain release instead of master

[gnat]

Any updates on this? @knz Currently a blocker for ARRAY support in Piccolo ORM.

Example query where we get asyncpg.exceptions._base.UnknownPostgresError: could not decorrelate subquery:

SELECT "band"."name", 
        ARRAY(
            SELECT
                "inner_genre"."name"
            FROM 
"genre_to_band"
JOIN "band" "inner_band" ON (
    "genre_to_band"."band" = "inner_band"."id"
)
JOIN "genre" "inner_genre" ON (
    "genre_to_band"."genre" = "inner_genre"."id"
)
WHERE "genre_to_band"."band" = "band"."id"

        ) AS "genres"
     FROM band

[knz]

I think the title of this issue (71908) should be modified to refer to the specific kind of correlation mentioned by the OP.

@gnat your specific query is using a different structure, and should be filed as a different issue. Thanks

[rafiss]

We'll be working on subquery decorrelation (including this issue specifically) in the coming months, but it likely won't be in any release until v23.1 or later

[gnat]

That's great. We'll move forward with a Cockroach release for Piccolo ORM relatively soon- if we cannot figure something out ourselves, we'll just flag the affected features as in-progress and keep an eye out here.