[YSQL] ERROR: invalid attnum ... on query with array_agg(json_build_object())

[karthik-ramanathan-3006]

Jira Link: DB-11462

Description

Setup

CREATE TABLE site (id uuid PRIMARY KEY);
CREATE TABLE address (id uuid PRIMARY KEY, site_id uuid, recorded_at timestamp without time zone);
CREATE INDEX idx_address_problem ON address (site_id HASH) INCLUDE (recorded_at);

Query

yugabyte=# EXPLAIN SELECT
        s.id,
        aa.addresses
FROM site s
JOIN (
    SELECT
        array_agg(json_build_object(
        'id', a.id,
        'site_id', a.site_id
        )) AS addresses
    FROM address a
    WHERE a.site_id = '01663b9e-0d6b-4954-b53c-470069c84f9c'
    AND a.recorded_at IS NULL
    ) aa ON true;

ERROR:  XX000: invalid attnum 3 for relation "s"
LOCATION:  get_variable, ruleutils.c:6903
Time: 340.877 ms

Notes A combination of factors seem to cause this:

• A covering index that "covers" all the columns used in the WHERE clause. (address.site_id, address.recorded_at)
• A combination of array_agg() and json_build_object() (I haven't tested if it is those two specific functions or any two functions)
• Some of the columns in the inner SELECT go to the main table (ie. address.id in the above example)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[kmuthukk]

Potential duplicate of #13117 ?

[tanujnay112]

Issue goes away when yb_enable_expression_pushdown is disabled. It appears that the issue occurs when we try to deparse the Storage Index Filter on idx_address_problem. On the offending line, the variable a.recorded_at is said to have varno = 1 and varattno = 3. In this context, however, varno = 1 seems to be interpreted as table s. Any vars referring to table a such as in the index qual seem to have varno = 4.

@andrei-mart It seems like Postgres when deparsing the index qual, it deparses the indexqualorig field instead of indexqual. Do you think something similar needs to be done for the yb_idx_pushdown field?

[andrei-mart]

Problem is only with EXPLAIN, the query is executed correctly. For issue to occur there should be IndexScan with a Storage Index Filter in a subquery.