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

Commit6ca712f

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 parent5301c83 commit6ca712f

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
@@ -1607,9 +1607,7 @@ tupledesc_match(TupleDesc dst_tupdesc, TupleDesc src_tupdesc)
16071607
* init_fcache is presumed already run on the FuncExprState.
16081608
*
16091609
* This function handles the most general case, wherein the function or
1610-
* one of its arguments might (or might not) return a set.If we find
1611-
* no sets involved, we will change the FuncExprState's function pointer
1612-
* to use a simpler method on subsequent calls.
1610+
* one of its arguments can return a set.
16131611
*/
16141612
staticDatum
16151613
ExecMakeFunctionResult(FuncExprState*fcache,
@@ -1888,13 +1886,12 @@ ExecMakeFunctionResult(FuncExprState *fcache,
18881886
/*
18891887
* Non-set case: much easier.
18901888
*
1891-
* We change the ExprState function pointer to use the simpler
1892-
* ExecMakeFunctionResultNoSets on subsequent calls. This amounts to
1893-
* assuming that no argument can return a set if it didn't do so the
1894-
* first time.
1889+
* In common cases, this code path is unreachable because we'd have
1890+
* selected ExecMakeFunctionResultNoSets instead. However, it's
1891+
* possible to get here if an argument sometimes produces set results
1892+
* and sometimes scalar results. For example, a CASE expression might
1893+
* call a set-returning function in only some of its arms.
18951894
*/
1896-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
1897-
18981895
if (isDone)
18991896
*isDone=ExprSingleResult;
19001897

@@ -2350,10 +2347,22 @@ ExecEvalFunc(FuncExprState *fcache,
23502347
/* Initialize function lookup info */
23512348
init_fcache(func->funcid,fcache,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
/* ----------------------------------------------------------------
@@ -2372,10 +2381,22 @@ ExecEvalOper(FuncExprState *fcache,
23722381
/* Initialize function lookup info */
23732382
init_fcache(op->opfuncid,fcache,econtext->ecxt_per_query_memory, true);
23742383

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

23812402
/* ----------------------------------------------------------------

‎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, E'^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, E'^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