roachtest: schemachange/random-load failed [return type mismatch in function declared to return table_w16_201]

[cockroach-teamcity]

roachtest.schemachange/random-load failed with artifacts on release-24.1 @ 627d811b9a7e72e47cff241db14ba89297388552:

(schemachange_random_load.go:123).runSchemaChangeRandomLoad: full command output in run_121340.487818004_n1_workload-run-schemac.log: COMMAND_PROBLEM: exit status 1
test artifacts and logs in: /artifacts/schemachange/random-load/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=true
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0
  Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

Same failure on other branches

- #131405 roachtest: schemachange/random-load failed [C-test-failure O-roachtest O-robot T-sql-foundations branch-release-23.2 release-blocker] - #131345 roachtest: schemachange/random-load failed [C-test-failure O-roachtest O-robot T-sql-foundations branch-master release-blocker] - #129857 roachtest: schemachange/random-load failed [relation "[1285]" does not exist] [C-test-failure O-roachtest O-robot P-2 T-sql-foundations branch-release-24.2.1-rc]

/cc @cockroachdb/sql-foundations

This test on roachdash | Improve this report!

Jira issue: CRDB-42578

[cockroach-teamcity]

roachtest.schemachange/random-load failed with artifacts on release-24.1 @ 71820e0480fb8dc1d27f69390b4cbe865db24e6a:

(schemachange_random_load.go:123).runSchemaChangeRandomLoad: full command output in run_113053.929828888_n1_workload-run-schemac.log: COMMAND_PROBLEM: exit status 1
test artifacts and logs in: /artifacts/schemachange/random-load/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=true
• ROACHTEST_runtimeAssertionsBuild=false
• ROACHTEST_ssd=0
  Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

Same failure on other branches

- #131405 roachtest: schemachange/random-load failed [DROP VIEW is stuck in reverting state] [C-test-failure O-roachtest O-robot P-2 T-sql-foundations branch-release-23.2] - #131345 roachtest: schemachange/random-load failed [NOT NULL could not be validated due to concurrent INSERT] [C-test-failure O-roachtest O-robot P-2 T-sql-foundations branch-master] - #129857 roachtest: schemachange/random-load failed [relation "[1285]" does not exist] [C-test-failure O-roachtest O-robot P-2 T-sql-foundations branch-release-24.2.1-rc]

This test on roachdash | Improve this report!

[rafiss]

{
 "workerId": 13,
 "clientTimestamp": "11:34:40.162699",
 "ops": [
  "BEGIN",
  {
   "sql": "SELECT t0.col😖201_w16_202 AS col0, t0.col201_w16_207 AS col1, t0.col201_w16_207 AS col2, t0.\"col2 01_w16_203\" AS col3, t0.col😖201_w16_202 AS col4, t0.\"c\\\\u6A88ol 20 1_w16_206\" AS col5, t0.col201_w16_208 AS col6, t0.col201_w16_208 AS col7 FROM schema_w9_62.table_w16_201 AS t0 FETCH FIRST 1 ROWS ONLY",
   "potentialExecErr": "53100,53200,57014"
  },
  {
   "sql": "CREATE FUNCTION schema_w1_102.udf_w13_159()\n\tRETURNS SETOF schema_w9_62.table_w16_201\n\tLANGUAGE SQL\n\tAS $$ SELECT NULL WHERE ('fsa'::public.enum_w17_4 IS NULL) AND ('sdfsf'::public.enum_w17_4 IS NULL) AND ((SELECT count(*) FROM schema_w1_102.table_w18_161 LIMIT 0) = 0) AND ((SELECT count(*) FROM schema_w9_62.table_w16_201 LIMIT 0) = 0) AND (SELECT schema_w10_8.udf_w9_238(NULL,NULL) IS NOT NULL) AND (SELECT schema_w2_11.udf_w16_209(NULL,NULL) IS NOT NULL) $$"
  }
 ],
 "expectedExecErrors": "",
 "expectedCommitErrors": "",
 "message": "***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: return type mismatch in function declared to return table_w16_201 (SQLSTATE 42P13)",

[fqazi]

This happens because the definition of a table is modified after a UDF is created via ADD / DROP COLUMN, so the invocation is no longer valid. Let me compare this behaviour vs Postgres, but if that also has UDFs break then the right answer here is to make this a potential error.

[fqazi]

Postgres also has similar behaviour, when the select list is limited:

postgres=# select * from udf();
ERROR:  return type mismatch in function declared to return t1
DETAIL:  Final statement returns too few columns.
CONTEXT:  SQL function "udf" during startup