search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
29.99k stars 3.79k forks source link

sql: don't type-check subqueries in routine argument defaults #122085

Closed cockroach-teamcity closed 6 months ago

cockroach-teamcity commented 6 months ago

roachtest.sqlsmith/setup=empty/setting=default failed with artifacts on release-24.1 @ 87740bd080658e93b40637556c9dfa27a91f8cf5:

(sqlsmith.go:272).func3: error: pq: internal error: building declarative schema change targets for CREATE FUNCTION: ReturnType called on TypedExpr with empty typeAnnotation. Was the underlying Expr type-checked before asserting a type of TypedExpr?
stmt:
CREATE FUNCTION func_8(IN p0 TSVECTOR, IN p1 STRING DEFAULT (SELECT e'hV\x1b2s':::STRING AS col_533 FROM (VALUES (NULL), (e'-Fq\x07b\x03Z\x03':::STRING), (NULL), (e'_\x181N;':::STRING), ('G4':::STRING), (e'm\x1bT':::STRING)) AS tab_241 (col_532) LIMIT 1:::INT8))
    RETURNS SETOF RECORD
    LANGUAGE SQL
    CALLED ON NULL INPUT
    NOT LEAKPROOF
    AS $funcbody$
SELECT p0 AS col_540 FROM (VALUES ('BOX(0.5527705014068854 -0.4125820046210852,1.1246751322232384 -0.36645002950394595)':::BOX2D), ('BOX(0.4144616526942638 -0.8596527803576048,1.4342473302964112 -0.2551390511967643)':::BOX2D), ('BOX(0.14592681405399263 -1.6126179700403744,0.5217514108553464 -0.8241399292138221)':::BOX2D), ('BOX(-1.0470714577906404 -0.8749756427980282,1.196943035378553 0.2333336840564736)':::BOX2D)) AS tab_246 (col_538) INNER JOIN (VALUES (COALESCE(3511584523:::OID, 4275404981:::OID)), (1263041575:::OID), (714575715:::OID), (1079959690:::OID)) AS tab_247 (col_539) ON true LIMIT 74:::INT8;
SELECT '0102000020E610000000000000':::GEOGRAPHY AS col_542, p1 AS col_543, '''IlfRsB''':::TSVECTOR AS col_544 FROM (VALUES (NULL), ('0101000060E6100000906C6E6C4422214024D5FA78119F31400C823A7CAA0AF241':::GEOGRAPHY), ('0106000060E610000007000000010300004001000000090000005ECF1583350954C040C565394DAA07407437B3884FAAFBC19C7695D677DD5AC040CB697C6DFA31C0C40A44DB5899FBC1B4EDEE31E9BE6040C0C23CA9661D18C0D060522B8471FC41FCB60BFC189D56406CA944D74D2E33409BF652BF489B02C2A6776D2B6355624060B4F12552AD3F40EE9708CD7888F841F2779C81B82362404A4A2F59B0B84040F4F11D14C913EFC1A45ADF81512B5640CC3C6605A98C3E40A4143A9FC0F4FE4163E566FAB2B557C010B5D5D766BD44403E9859770E2702425ECF1583350954C040C565394DAA07407437B3884FAAFBC10103000040010000000C0000004C42B654F9B843C080A0E36606202740A41FD743733FE941586601A38D6F3BC0005817956D6D1940B62C3B71679DF8C1D48E499357E95CC0C9D5F28939ED47C042D4D4AF4709FEC1762B28D736BF4FC032486EFAD0D54EC09C051525A3D2E141A80F8348901A3CC080291DF3E3C439C0A06144F0FA24D4C1B8C09D3A981E5C40698DAEF4108A4CC0300A48484E07D94164E56755FD2B63409803A4074EFE4C408AD51F2C3333EBC1B4B09EBCACB65D40CC5707C5C3C24A4080708D77E9CFE841841AE7330CED5A4024618A8DC91F53404894C3EEDB0BFC41E067104D466550C080C909DAD79A4640FE279AC27118F4417AA3FEF8DC1E66C03A7D8FAA9D3F554016E85D5BA30DF2414C42B654F9B843C080A0E36606202740A41FD743733FE94101030000400100000009000000930E1CFF573564C08D3ACC3A2DEF4CC09BE3AFE01D4801C2A8B32BAB3AA748C0C6F7E882EE8348C0B1C9E27B2DF6F2C158C11BB947EA6440C06DA146999807C07CC43477DA1FFC41CA7F8F70EC0C6240C814D72E701950401232D56DA09E0142A46C1F65455B5C40A232C56CE2BA5240F8CCC993E73700C268586AF0E601354094862863E2394A4000FA1871510FCEC190B240EEF1E22AC078966EC0205B50403854FAC5AB12FEC194C7F1B54AFE59C0F419FD12B53652400E052C1155EA01C2930E1CFF573564C08D3ACC3A2DEF4CC09BE3AFE01D4801C201030000400100000005000000881D65D77ABD40C0D0FE78C2407651C05EC74187D2B4F8C1C005C65203DA444080D34D4048CAE8BF98853791E7BEF241745CC97F5A5F4640D07D0DDA5A284B40E0D0999E3E4ED141009219D6A16D27406A18B4FC45B04240181B242C282EFAC1881D65D77ABD40C0D0FE78C2407651C05EC74187D2B4F8C10103000040010000000800000093DAF30CB47251C07CD25A93FA913BC0C1D125B613FDF8C13DCCB115965150C0DE1B6A3E499541C0FA5752B52097EDC170611BDA87093B4025BAE7C4DB7555C0B439E5EBE18FEAC100229CB1E1966340B4D60C8E4C3B37C070A54F982E61D7C1FE4F509DDCDC62407CEE2C26F6623A40446F0FC4492CE041F472CAA6D5B5584026922E2E912255401C429A39FB3BF641A0BFCE2AB53A44C0B0E84E3F92874D40BC8686707B1BF6C193DAF30CB47251C07CD25A93FA913BC0C1D125B613FDF8C10103000040010000000500000018156B3EB9F73BC02814C56753E539C0E9A56FD14FAC00C2F02ED65085033C4086B08D12885F48C02F47F1FAB2C7FCC1C255A62B9BF0624030469E238D6346C0AA7016F17DE6E6C1E07D2565614766C07CB0D91749C04640DFF8B8F86A11FAC118156B3EB9F73BC02814C56753E539C0E9A56FD14FAC00C201030000400100000006000000783925C2A41237C096E33E10989D4DC0584F344B681200428FED91C4368051C0218E3DEFD2B255C0E82C8884AB72D441C2DF2471A434654060B57346A02652C060B98C5D6BB301422418DF3145355D4040623FCD5D6D274080280671977FF5416864E32F0BA33CC02EFEAA3A19484140081FE721B05ED341783925C2A41237C096E33E10989D4DC0584F344B68120042':::GEOGRAPHY), ('01010000A0E6100000941467247D2C4B4028E58966498950C05CDC78A46C03E9C1':::GEOGRAPHY)) AS tab_248 (col_541) WHERE false ORDER BY tab_248.col_541 ASC NULLS FIRST, tab_248.col_541 NULLS FIRST, tab_248.col_541 ASC, tab_248.col_541 ASC NULLS LAST;
SELECT tab_249.col_546 AS col_548, p1 AS col_549, '''ItOacSAr'' ''PVQHfyMA'' ''f''':::TSVECTOR AS col_550, tab_250.col_547 AS col_551 FROM (VALUES ('19 years 1 mon 466 days 01:26:50.420501':::INTERVAL), ('2 years 1 mon 87 days 20:03:03.047971':::INTERVAL), (NULL), (NULL), ('-56 years -8 mons -679 days -15:24:56.356236':::INTERVAL), ((SELECT '-68 years -5 mons -714 days -17:20:25.955384':::INTERVAL AS col_545 LIMIT 1:::INT8))) AS tab_249 (col_546) CROSS JOIN (VALUES (1566:::INT8), (10177:::INT8)) AS tab_250 (col_547);
SELECT NULL AS col_553, p1 AS col_554, tab_251.col_552 AS col_555 FROM (VALUES (3860627533:::OID), (0:::OID)) AS tab_251 (col_552) WHERE true LIMIT 72:::INT8;
SELECT p1 AS col_557, p0 AS col_558 FROM (VALUES (parse_interval(p1::STRING)::INTERVAL), ('-19 years -10 mons -499 days -06:00:03.698937':::INTERVAL), ('-66 years -1 mons -668 days -11:51:09.316566':::INTERVAL)) AS tab_252 (col_556) WHERE false LIMIT 17:::INT8;
SELECT tab_273.col_595 AS col_597, p1 AS col_598, tab_273.col_595 AS col_599 FROM (VALUES ('!''sXt'' | ''sCKbIdobmU'' <-> ''NIt'' <-> ''eSJHLOgyf''':::TSQUERY, '1990-08-07':::DATE), ('!''pO'' <-> ''KPkdHKAyao'' | ''QKlD''':::TSQUERY, '1992-04-12':::DATE), ('''eIEdDN'' & ''sQCgVurUJ'' <-> ''gepwgHLV''':::TSQUERY, '4714-11-24 BC':::DATE), ('''vz'' & !''zRMPhs''':::TSQUERY, '1980-10-16':::DATE), ('''PrRbuhl'' | !''S'' & ''m''':::TSQUERY, '-infinity':::DATE)) AS tab_253 (col_559, col_560), (SELECT tab_272.col_585 AS col_587, tab_272.col_584 AS col_588, tab_272.col_586 AS col_589, p0 AS col_590, tab_272.col_585 AS col_591 FROM (VALUES (refcursor(e'\x197\x0bqe':::STRING::STRING)::REFCURSOR, e'\x074A':::STRING, NULL), ('Q':::REFCURSOR, p1, NULL)) AS tab_272 (col_584, col_585, col_586) ORDER BY tab_272.col_586 DESC NULLS FIRST, tab_272.col_584 DESC NULLS FIRST LIMIT 95:::INT8) AS tab_273 (col_592, col_593, col_594, col_595, col_596) LIMIT 50:::INT8;
SELECT '''omola'' ''sirHIKH'':598A,789B':::TSVECTOR AS col_605, tab_277.col_604 AS col_606, 'F797C142/A163C3F4':::PG_LSN AS col_607 FROM (VALUES ('393853DC/EE17D209':::PG_LSN), ('69D227D3/5380BE69':::PG_LSN)) AS tab_274 (col_600), (VALUES (0:::OID), (2835421920:::OID), (regproc(e'D\x1c':::STRING::STRING)::REGPROC), (regproc((SELECT 2075348435:::OID AS col_603 FROM (VALUES ('04:58:32.587786':::TIME)) AS tab_275 (col_601), (VALUES (date_trunc(e'|En}\x18X':::STRING::STRING, '-20 years -2 mons -90 days -00:31:34.173627':::INTERVAL::INTERVAL)::INTERVAL), ('62 years 742 days 24:16:59.123949':::INTERVAL), ('-55 years -10 mons -107 days -05:06:43.04165':::INTERVAL), ('1 day':::INTERVAL), ('29 years 5 mons 227 days 13:11:00.902566':::INTERVAL)) AS tab_276 (col_602) WHERE false LIMIT 1:::INT8)::OID)::REGPROC)) AS tab_277 (col_604) WHERE true ORDER BY tab_277.col_604 DESC NULLS LAST, tab_277.col_604 ASC NULLS LAST;
SELECT to_uuid(p1::STRING)::BYTES AS col_610 FROM (VALUES (p1, p0), (p1, '''FCMcu'' ''gQ'' ''qI'':491A,736B ''vmqgT'':634C,875B':::TSVECTOR), (e'q\x14mi<\x18\x02':::STRING:::NAME, p0), (p1, p0), (name(';':::REFCURSOR::REFCURSOR)::NAME, p0), (e'8\x0e':::STRING:::NAME, '''PQMae'' ''UaFbq'' ''pQd'':463B,637C,690C ''te''':::TSVECTOR)) AS tab_278 (col_608, col_609) WHERE true ORDER BY tab_278.col_608 LIMIT 61:::INT8
$funcbody$;
test artifacts and logs in: /artifacts/sqlsmith/setup=empty/setting=default/run_1

Parameters:

See: roachtest README

See: How To Investigate (internal)

See: Grafana

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-37693

DrewKimball commented 6 months ago

Smaller repro:

create procedure p(x int default (select 100 from values (null))) language sql as $$ select x; $$;
DrewKimball commented 6 months ago

Similar to https://github.com/cockroachdb/cockroach/issues/105259, fixed by https://github.com/cockroachdb/cockroach/pull/106868.

mgartner commented 6 months ago

Postgres doesn't allow subqueries in DEFAULT expressions, so we can disallow this too:

marcus=# CREATE PROCEDURE p(i INT DEFAULT (SELECT 1 FROM (VALUES (NULL)) AS v)) AS $$
marcus$# SELECT i;
marcus$# $$ LANGUAGE SQL;
ERROR:  0A000: cannot use subquery in DEFAULT expression
LINE 1: CREATE PROCEDURE p(i INT DEFAULT (SELECT 1 FROM (VALUES (NUL...
                                         ^
LOCATION:  transformSubLink, parse_expr.c:1813