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

Commit47ac447

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 parent3aefff4 commit47ac447

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
@@ -1634,9 +1634,7 @@ tupledesc_match(TupleDesc dst_tupdesc, TupleDesc src_tupdesc)
16341634
* init_fcache is presumed already run on the FuncExprState.
16351635
*
16361636
* This function handles the most general case, wherein the function or
1637-
* one of its arguments might (or might not) return a set.If we find
1638-
* no sets involved, we will change the FuncExprState's function pointer
1639-
* to use a simpler method on subsequent calls.
1637+
* one of its arguments can return a set.
16401638
*/
16411639
staticDatum
16421640
ExecMakeFunctionResult(FuncExprState*fcache,
@@ -1906,13 +1904,12 @@ ExecMakeFunctionResult(FuncExprState *fcache,
19061904
/*
19071905
* Non-set case: much easier.
19081906
*
1909-
* We change the ExprState function pointer to use the simpler
1910-
* ExecMakeFunctionResultNoSets on subsequent calls. This amounts to
1911-
* assuming that no argument can return a set if it didn't do so the
1912-
* first time.
1907+
* In common cases, this code path is unreachable because we'd have
1908+
* selected ExecMakeFunctionResultNoSets instead. However, it's
1909+
* possible to get here if an argument sometimes produces set results
1910+
* and sometimes scalar results. For example, a CASE expression might
1911+
* call a set-returning function in only some of its arms.
19131912
*/
1914-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
1915-
19161913
if (isDone)
19171914
*isDone=ExprSingleResult;
19181915

@@ -2371,10 +2368,22 @@ ExecEvalFunc(FuncExprState *fcache,
23712368
init_fcache(func->funcid,func->inputcollid,fcache,
23722369
econtext->ecxt_per_query_memory, true);
23732370

2374-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2375-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2376-
2377-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2371+
/*
2372+
* We need to invoke ExecMakeFunctionResult if either the function itself
2373+
* or any of its input expressions can return a set. Otherwise, invoke
2374+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2375+
* pointer to go directly there on subsequent uses.
2376+
*/
2377+
if (fcache->func.fn_retset||expression_returns_set((Node*)func->args))
2378+
{
2379+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2380+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2381+
}
2382+
else
2383+
{
2384+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2385+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2386+
}
23782387
}
23792388

23802389
/* ----------------------------------------------------------------
@@ -2394,10 +2403,22 @@ ExecEvalOper(FuncExprState *fcache,
23942403
init_fcache(op->opfuncid,op->inputcollid,fcache,
23952404
econtext->ecxt_per_query_memory, true);
23962405

2397-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2398-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2399-
2400-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2406+
/*
2407+
* We need to invoke ExecMakeFunctionResult if either the function itself
2408+
* or any of its input expressions can return a set. Otherwise, invoke
2409+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2410+
* pointer to go directly there on subsequent uses.
2411+
*/
2412+
if (fcache->func.fn_retset||expression_returns_set((Node*)op->args))
2413+
{
2414+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2415+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2416+
}
2417+
else
2418+
{
2419+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2420+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2421+
}
24012422
}
24022423

24032424
/* ----------------------------------------------------------------

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

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

‎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