[YSQL] Updating existing table from temp table fails

[ijungmann]

Jira Link: DB-1936
Creating a temporary table from an existing table and then attempting to update the existing table with data from the new temporary table fails due to variable not found in subplan target lists. This issue does not occur when referencing another permanent table, it only seems to occur with the temporary table. Below is a reduced example of this setup.

CREATE TABLE test1 (x int, y int, z int);
CREATE TABLE

CREATE TEMP TABLE test2 as table test1;
SELECT 0

UPDATE test1 SET z = 2 FROM test2 WHERE test1.x = test2.x;
ERROR:  variable not found in subplan target lists

[jaki]

On debug build, postgres crashes.

ysqlsh output:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Postgres logs:

TRAP: FailedAssertion("!(attno >= rel->min_attr && attno <= rel->max_attr)", File: "../../../../../../../src/postgres/src/backend/optimizer/plan/initsplan.c", Line: 217)

Backtrace on the core file:

(gdb) bt
#0  0x00007fa6b9d4c0a7 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
#1  0x00007fa6b9d4d4aa in __GI_abort () at abort.c:89
#2  0x0000000000b63f7f in ExceptionalCondition (conditionName=0xd767e0 "!(attno >= rel->min_attr && attno <= rel->max_attr)", errorType=0xd767a9 "FailedAssertion", fileName=0xd76760 "../../../../../../../src/postgres/src/backend/optimizer/plan/initsplan
.c", lineNumber=217) at ../../../../../../../src/postgres/src/backend/utils/error/assert.c:54
#3  0x00000000008883f4 in add_vars_to_targetlist (root=0x34a37e8, vars=0x34a4980, where_needed=0x34a4a30, create_new_ph=true) at ../../../../../../../src/postgres/src/backend/optimizer/plan/initsplan.c:217
#4  0x000000000088827b in build_base_rel_tlists (root=0x34a37e8, final_tlist=0x34a4130) at ../../../../../../../src/postgres/src/backend/optimizer/plan/initsplan.c:160
#5  0x000000000088cd23 in query_planner (root=0x34a37e8, tlist=0x34a4130, qp_callback=0x892c0c <standard_qp_callback>, qp_extra=0x7ffe068a77f0) at ../../../../../../../src/postgres/src/backend/optimizer/plan/planmain.c:156
#6  0x0000000000890072 in grouping_planner (root=0x34a37e8, inheritance_update=false, tuple_fraction=0) at ../../../../../../../src/postgres/src/backend/optimizer/plan/planner.c:1908
#7  0x000000000088e68b in subquery_planner (glob=0x34a2098, parse=0x2962040, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at ../../../../../../../src/postgres/src/backend/optimizer/plan/planner.c:966
#8  0x000000000088d318 in standard_planner (parse=0x2962040, cursorOptions=256, boundParams=0x0) at ../../../../../../../src/postgres/src/backend/optimizer/plan/planner.c:405
#9  0x00007fa6aa590f0c in pg_hint_plan_planner (parse=0x2962040, cursorOptions=256, boundParams=0x0) at ../../../../../src/postgres/third-party-extensions/pg_hint_plan/pg_hint_plan.c:3221
#10 0x000000000088d076 in planner (parse=0x2962040, cursorOptions=256, boundParams=0x0) at ../../../../../../../src/postgres/src/backend/optimizer/plan/planner.c:261
#11 0x00000000009ae6f0 in pg_plan_query (querytree=0x2962040, cursorOptions=256, boundParams=0x0) at ../../../../../../src/postgres/src/backend/tcop/postgres.c:848
#12 0x00000000009ae837 in pg_plan_queries (querytrees=0x34a37b0, cursorOptions=256, boundParams=0x0) at ../../../../../../src/postgres/src/backend/tcop/postgres.c:914
#13 0x00000000009aebf4 in exec_simple_query (query_string=0x27e4120 "UPDATE test1 SET z = 2 FROM test2 WHERE test1.x = test2.x;") at ../../../../../../src/postgres/src/backend/tcop/postgres.c:1089
#14 0x00000000009b56db in yb_exec_simple_query (query_string=0x27e4120 "UPDATE test1 SET z = 2 FROM test2 WHERE test1.x = test2.x;", exec_context=0x27e4000) at ../../../../../../src/postgres/src/backend/tcop/postgres.c:4330
#15 0x00000000009b6451 in PostgresMain (argc=1, argv=0x27e1528, dbname=0x27e1388 "yugabyte", username=0x27e1360 "yugabyte") at ../../../../../../src/postgres/src/backend/tcop/postgres.c:4942
#16 0x00000000008f5dad in BackendRun (port=0x267c960) at ../../../../../../src/postgres/src/backend/postmaster/postmaster.c:4429
#17 0x00000000008f51f8 in BackendStartup (port=0x267c960) at ../../../../../../src/postgres/src/backend/postmaster/postmaster.c:4095
#18 0x00000000008f0227 in ServerLoop () at ../../../../../../src/postgres/src/backend/postmaster/postmaster.c:1737
#19 0x00000000008ef931 in PostmasterMain (argc=25, argv=0x27de000) at ../../../../../../src/postgres/src/backend/postmaster/postmaster.c:1400
#20 0x0000000000806d57 in PostgresServerProcessMain (argc=25, argv=0x27de000) at ../../../../../../src/postgres/src/backend/main/main.c:234
#21 0x00000000008070e7 in main ()