Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6c6c53d

Browse files
committed
Fix "cannot accept a set" error when only some arms of a CASE return a set.
In commitc135205, I implemented anoptimization that assumed that a function's argument expressions wouldeither always return a set (ie multiple rows), or always not. This iswrong however: we allow CASE expressions in which some arms return a setof some type and others just return a scalar of that type. There may beother examples as well. To fix, replace the run-time test of whether anargument returned a set with a static precheck (expression_returns_set).This adds a little bit of query startup overhead, but it seems barelymeasurable.Per bug #8228 from David Johnston. This has been broken since 8.0,so patch all supported branches.
1 parent0402f24 commit6c6c53d

File tree

3 files changed

+61
-17
lines changed

3 files changed

+61
-17
lines changed

‎src/backend/executor/execQual.c

Lines changed: 38 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1621,9 +1621,7 @@ tupledesc_match(TupleDesc dst_tupdesc, TupleDesc src_tupdesc)
16211621
* init_fcache is presumed already run on the FuncExprState.
16221622
*
16231623
* This function handles the most general case, wherein the function or
1624-
* one of its arguments might (or might not) return a set.If we find
1625-
* no sets involved, we will change the FuncExprState's function pointer
1626-
* to use a simpler method on subsequent calls.
1624+
* one of its arguments can return a set.
16271625
*/
16281626
staticDatum
16291627
ExecMakeFunctionResult(FuncExprState*fcache,
@@ -1885,13 +1883,12 @@ ExecMakeFunctionResult(FuncExprState *fcache,
18851883
/*
18861884
* Non-set case: much easier.
18871885
*
1888-
* We change the ExprState function pointer to use the simpler
1889-
* ExecMakeFunctionResultNoSets on subsequent calls. This amounts to
1890-
* assuming that no argument can return a set if it didn't do so the
1891-
* first time.
1886+
* In common cases, this code path is unreachable because we'd have
1887+
* selected ExecMakeFunctionResultNoSets instead. However, it's
1888+
* possible to get here if an argument sometimes produces set results
1889+
* and sometimes scalar results. For example, a CASE expression might
1890+
* call a set-returning function in only some of its arms.
18921891
*/
1893-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
1894-
18951892
if (isDone)
18961893
*isDone=ExprSingleResult;
18971894

@@ -2350,10 +2347,22 @@ ExecEvalFunc(FuncExprState *fcache,
23502347
init_fcache(func->funcid,func->inputcollid,fcache,
23512348
econtext->ecxt_per_query_memory, true);
23522349

2353-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2354-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2355-
2356-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2350+
/*
2351+
* We need to invoke ExecMakeFunctionResult if either the function itself
2352+
* or any of its input expressions can return a set. Otherwise, invoke
2353+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2354+
* pointer to go directly there on subsequent uses.
2355+
*/
2356+
if (fcache->func.fn_retset||expression_returns_set((Node*)func->args))
2357+
{
2358+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2359+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2360+
}
2361+
else
2362+
{
2363+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2364+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2365+
}
23572366
}
23582367

23592368
/* ----------------------------------------------------------------
@@ -2373,10 +2382,22 @@ ExecEvalOper(FuncExprState *fcache,
23732382
init_fcache(op->opfuncid,op->inputcollid,fcache,
23742383
econtext->ecxt_per_query_memory, true);
23752384

2376-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2377-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2378-
2379-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2385+
/*
2386+
* We need to invoke ExecMakeFunctionResult if either the function itself
2387+
* or any of its input expressions can return a set. Otherwise, invoke
2388+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2389+
* pointer to go directly there on subsequent uses.
2390+
*/
2391+
if (fcache->func.fn_retset||expression_returns_set((Node*)op->args))
2392+
{
2393+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2394+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2395+
}
2396+
else
2397+
{
2398+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2399+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2400+
}
23802401
}
23812402

23822403
/* ----------------------------------------------------------------

‎src/test/regress/expected/rangefuncs.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -929,3 +929,17 @@ select * from foobar(); -- fail
929929
ERROR: function return row and query-specified return row do not match
930930
DETAIL: Returned row contains 3 attributes, but query expects 2.
931931
drop function foobar();
932+
-- check behavior when a function's input sometimes returns a set (bug #8228)
933+
SELECT *,
934+
lower(CASE WHEN id = 2 THEN (regexp_matches(str, '^0*([1-9]\d+)$'))[1]
935+
ELSE str
936+
END)
937+
FROM
938+
(VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);
939+
id | str | lower
940+
----+------------------+------------------
941+
1 | |
942+
2 | 0000000049404 | 49404
943+
3 | FROM 10000000876 | from 10000000876
944+
(3 rows)
945+

‎src/test/regress/sql/rangefuncs.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -455,3 +455,12 @@ $$ select (1, 2.1, 3) $$ language sql;
455455
select*from foobar();-- fail
456456

457457
dropfunction foobar();
458+
459+
-- check behavior when a function's input sometimes returns a set (bug #8228)
460+
461+
SELECT*,
462+
lower(CASE WHEN id=2 THEN (regexp_matches(str,'^0*([1-9]\d+)$'))[1]
463+
ELSE str
464+
END)
465+
FROM
466+
(VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp