Thread: ERROR: XX000: variable not found in subplan target list

Ryan Kelly <rpkelly22@gmail.com> writes:
> After upgrading to 11.15 we have queries failing as follows:
> (postgres@[local]:5432 02:52:54) [capitalrx_test_adjudication_master]>
> EXPLAIN SELECT count(*) FROM adjudication.claims;
> ERROR:  XX000: variable not found in subplan target list

It's impossible to comment on this with this amount of detail.
Please try to extract a self-contained test case.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

            regards, tom lane

Ryan Kelly <rpkelly22@gmail.com> writes:
> Sorry about that. Couldn't seem to get a simple reproduction. I have now
> attached a straightforward one. I am not sure why so many columns are
> required to cause the error, so it'd be nice to understand that too.

Got it, thanks for the test case.

(For the archives' sake: this example doesn't fail for me with default
planner settings.  I got it to fail after adding
    set enable_seqscan to 0;
    set enable_bitmapscan to 0;
Probably some less heavy-handed manipulation of the planner's cost
constants would do the trick too, but I didn't try.)

The problem occurs when the planner decides that an index-only scan
on the idx_claims_first_name_upper_trgm index is the thing to do,
because it generates an invalid plan in that case.  Said plan used to
accidentally work before we tightened up what setrefs.c would allow,
which is how come you didn't see the failure before.  The normal
case is probably to do an IOS on the claims_pkey index instead,
which'll work fine --- it's the fact that the gist_trgm_ops opclass
doesn't support returning column values that creates the issue.
But with an empty or near-empty table, the two index plans have
exactly the same estimated cost, so it's luck of the draw which
one you get.  (I think it'll be the index with highest OID that gets
chosen in such cases, but that's an implementation artifact not
something to rely on.)  I think the apparent dependency on number
of columns is a red herring.  Possibly, depending on what cost
settings you are using, that could affect the relative cost
estimates for seqscan vs. index scan.

Anyway, the attached seems to be enough to fix it in HEAD.
It should apply more or less easily to v11 as well.

            regards, tom lane

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index cd6d72c763..fa069a217c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -914,6 +914,22 @@ use_physical_tlist(PlannerInfo *root, Path *path, int flags)
             return false;
     }

+    /*
+     * For an index-only scan, the "physical tlist" is the index's indextlist.
+     * We can only return that without a projection if all the index's columns
+     * are returnable.
+     */
+    if (path->pathtype == T_IndexOnlyScan)
+    {
+        IndexOptInfo *indexinfo = ((IndexPath *) path)->indexinfo;
+
+        for (i = 0; i < indexinfo->ncolumns; i++)
+        {
+            if (!indexinfo->canreturn[i])
+                return false;
+        }
+    }
+
     /*
      * Also, can't do it if CP_LABEL_TLIST is specified and path is requested
      * to emit any sort/group columns that are not simple Vars.  (If they are

> 12 февр. 2022 г., в 00:28, Tom Lane <tgl@sss.pgh.pa.us> написал(а):
>
> Anyway, the attached seems to be enough to fix it in HEAD.
> It should apply more or less easily to v11 as well.

FWIW I've just observed reproduction on 10.10. Applied the patch and tested that query works correctly afterwards.

Do we need to check something else for this fix to be in next minor release?

Thanks!

Best regards, Andrey Borodin.

14 апр. 2022 г., в 15:24, Andrey Borodin <x4mmm@yandex-team.ru> написал(а):
>
> FWIW I've just observed reproduction on 10.10.

Oh, sorry for the noise. Surely it was 10.20.

Best regards, Andrey Borodin.

Andrey Borodin <x4mmm@yandex-team.ru> writes:
> Do we need to check something else for this fix to be in next minor release?

Are you referring to commit e5691cc91?

            regards, tom lane

> On 14 Apr 2022, at 19:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrey Borodin <x4mmm@yandex-team.ru> writes:
>> Do we need to check something else for this fix to be in next minor release?
>
> Are you referring to commit e5691cc91?

Uhm, yes, it's already there. I've skimmed log of REL_10_STABLE, but somehow haven't found it. And

> It should apply more or less easily to v11 as well.

ensured me to ask.
Checking again I see it's there.

Many thanks!

Best regards, Andrey Borodin.