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

Commit97a39f2

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 parent3bd8987 commit97a39f2

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
@@ -1631,9 +1631,7 @@ tupledesc_match(TupleDesc dst_tupdesc, TupleDesc src_tupdesc)
16311631
* init_fcache is presumed already run on the FuncExprState.
16321632
*
16331633
* This function handles the most general case, wherein the function or
1634-
* one of its arguments might (or might not) return a set.If we find
1635-
* no sets involved, we will change the FuncExprState's function pointer
1636-
* to use a simpler method on subsequent calls.
1634+
* one of its arguments can return a set.
16371635
*/
16381636
staticDatum
16391637
ExecMakeFunctionResult(FuncExprState*fcache,
@@ -1895,13 +1893,12 @@ ExecMakeFunctionResult(FuncExprState *fcache,
18951893
/*
18961894
* Non-set case: much easier.
18971895
*
1898-
* We change the ExprState function pointer to use the simpler
1899-
* ExecMakeFunctionResultNoSets on subsequent calls. This amounts to
1900-
* assuming that no argument can return a set if it didn't do so the
1901-
* first time.
1896+
* In common cases, this code path is unreachable because we'd have
1897+
* selected ExecMakeFunctionResultNoSets instead. However, it's
1898+
* possible to get here if an argument sometimes produces set results
1899+
* and sometimes scalar results. For example, a CASE expression might
1900+
* call a set-returning function in only some of its arms.
19021901
*/
1903-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
1904-
19051902
if (isDone)
19061903
*isDone=ExprSingleResult;
19071904

@@ -2360,10 +2357,22 @@ ExecEvalFunc(FuncExprState *fcache,
23602357
init_fcache(func->funcid,func->inputcollid,fcache,
23612358
econtext->ecxt_per_query_memory, true);
23622359

2363-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2364-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2365-
2366-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2360+
/*
2361+
* We need to invoke ExecMakeFunctionResult if either the function itself
2362+
* or any of its input expressions can return a set. Otherwise, invoke
2363+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2364+
* pointer to go directly there on subsequent uses.
2365+
*/
2366+
if (fcache->func.fn_retset||expression_returns_set((Node*)func->args))
2367+
{
2368+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2369+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2370+
}
2371+
else
2372+
{
2373+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2374+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2375+
}
23672376
}
23682377

23692378
/* ----------------------------------------------------------------
@@ -2383,10 +2392,22 @@ ExecEvalOper(FuncExprState *fcache,
23832392
init_fcache(op->opfuncid,op->inputcollid,fcache,
23842393
econtext->ecxt_per_query_memory, true);
23852394

2386-
/* Go directly to ExecMakeFunctionResult on subsequent uses */
2387-
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2388-
2389-
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2395+
/*
2396+
* We need to invoke ExecMakeFunctionResult if either the function itself
2397+
* or any of its input expressions can return a set. Otherwise, invoke
2398+
* ExecMakeFunctionResultNoSets. In either case, change the evalfunc
2399+
* pointer to go directly there on subsequent uses.
2400+
*/
2401+
if (fcache->func.fn_retset||expression_returns_set((Node*)op->args))
2402+
{
2403+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResult;
2404+
returnExecMakeFunctionResult(fcache,econtext,isNull,isDone);
2405+
}
2406+
else
2407+
{
2408+
fcache->xprstate.evalfunc= (ExprStateEvalFunc)ExecMakeFunctionResultNoSets;
2409+
returnExecMakeFunctionResultNoSets(fcache,econtext,isNull,isDone);
2410+
}
23902411
}
23912412

23922413
/* ----------------------------------------------------------------

‎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