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

Commit90f4c2d

Browse files
committed
Add support for doing FULL JOIN ON FALSE. While this is really a rather
peculiar variant of UNION ALL, and so wouldn't likely get written directlyas-is, it's possible for it to arise as a result of simplification ofless-obviously-silly queries. In particular, now that we can do flatteningof subqueries that have constant outputs and are underneath an outer join,it's possible for the case to result from simplification of queries of thetype exhibited in bug #5263. Back-patch to 8.4 to avoid a functionalityregression for this type of query.
1 parentd86d51a commit90f4c2d

File tree

5 files changed

+116
-13
lines changed

5 files changed

+116
-13
lines changed

‎src/backend/executor/nodeMergejoin.c

Lines changed: 49 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeMergejoin.c,v 1.99 2010/01/02 16:57:44 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeMergejoin.c,v 1.100 2010/01/05 23:25:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -398,8 +398,13 @@ MJCompare(MergeJoinState *mergestate)
398398
* want to report that the tuples are equal. Instead, if result is still
399399
* 0, change it to +1.This will result in advancing the inner side of
400400
* the join.
401+
*
402+
* Likewise, if there was a constant-false joinqual, do not report
403+
* equality. We have to check this as part of the mergequals, else the
404+
* rescan logic will do the wrong thing.
401405
*/
402-
if (nulleqnull&&result==0)
406+
if (result==0&&
407+
(nulleqnull||mergestate->mj_ConstFalseJoin))
403408
result=1;
404409

405410
MemoryContextSwitchTo(oldContext);
@@ -487,6 +492,32 @@ MJFillInner(MergeJoinState *node)
487492
}
488493

489494

495+
/*
496+
* Check that a qual condition is constant true or constant false.
497+
* If it is constant false (or null), set *is_const_false to TRUE.
498+
*
499+
* Constant true would normally be represented by a NIL list, but we allow an
500+
* actual bool Const as well. We do expect that the planner will have thrown
501+
* away any non-constant terms that have been ANDed with a constant false.
502+
*/
503+
staticbool
504+
check_constant_qual(List*qual,bool*is_const_false)
505+
{
506+
ListCell*lc;
507+
508+
foreach(lc,qual)
509+
{
510+
Const*con= (Const*)lfirst(lc);
511+
512+
if (!con|| !IsA(con,Const))
513+
return false;
514+
if (con->constisnull|| !DatumGetBool(con->constvalue))
515+
*is_const_false= true;
516+
}
517+
return true;
518+
}
519+
520+
490521
/* ----------------------------------------------------------------
491522
*ExecMergeTupleDump
492523
*
@@ -1025,9 +1056,13 @@ ExecMergeJoin(MergeJoinState *node)
10251056
* state for the rescanned inner tuples. We know all of
10261057
* them will match this new outer tuple and therefore
10271058
* won't be emitted as fill tuples. This works *only*
1028-
* because we require the extra joinquals to be nil when
1029-
* doing a right or full join --- otherwise some of the
1030-
* rescanned tuples might fail the extra joinquals.
1059+
* because we require the extra joinquals to be constant
1060+
* when doing a right or full join --- otherwise some of
1061+
* the rescanned tuples might fail the extra joinquals.
1062+
* This obviously won't happen for a constant-true extra
1063+
* joinqual, while the constant-false case is handled by
1064+
* forcing the merge clause to never match, so we never
1065+
* get here.
10311066
*/
10321067
ExecRestrPos(innerPlan);
10331068

@@ -1439,6 +1474,7 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
14391474
mergestate->js.joinqual= (List*)
14401475
ExecInitExpr((Expr*)node->join.joinqual,
14411476
(PlanState*)mergestate);
1477+
mergestate->mj_ConstFalseJoin= false;
14421478
/* mergeclauses are handled below */
14431479

14441480
/*
@@ -1498,10 +1534,11 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
14981534
ExecGetResultType(outerPlanState(mergestate)));
14991535

15001536
/*
1501-
* Can't handle right or full join with non-nil extra joinclauses.
1502-
* This should have been caught by planner.
1537+
* Can't handle right or full join with non-constant extra
1538+
*joinclauses.This should have been caught by planner.
15031539
*/
1504-
if (node->join.joinqual!=NIL)
1540+
if (!check_constant_qual(node->join.joinqual,
1541+
&mergestate->mj_ConstFalseJoin))
15051542
ereport(ERROR,
15061543
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
15071544
errmsg("RIGHT JOIN is only supported with merge-joinable join conditions")));
@@ -1517,9 +1554,11 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
15171554
ExecGetResultType(innerPlanState(mergestate)));
15181555

15191556
/*
1520-
* Can't handle right or full join with non-nil extra joinclauses.
1557+
* Can't handle right or full join with non-constant extra
1558+
* joinclauses. This should have been caught by planner.
15211559
*/
1522-
if (node->join.joinqual!=NIL)
1560+
if (!check_constant_qual(node->join.joinqual,
1561+
&mergestate->mj_ConstFalseJoin))
15231562
ereport(ERROR,
15241563
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
15251564
errmsg("FULL JOIN is only supported with merge-joinable join conditions")));

‎src/backend/optimizer/path/joinpath.c

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.128 2010/01/02 16:57:46 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.129 2010/01/05 23:25:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1184,10 +1184,18 @@ select_mergejoin_clauses(PlannerInfo *root,
11841184
if (isouterjoin&&restrictinfo->is_pushed_down)
11851185
continue;
11861186

1187+
/* Check that clause is a mergeable operator clause */
11871188
if (!restrictinfo->can_join||
11881189
restrictinfo->mergeopfamilies==NIL)
11891190
{
1190-
have_nonmergeable_joinclause= true;
1191+
/*
1192+
* The executor can handle extra joinquals that are constants,
1193+
* but not anything else, when doing right/full merge join. (The
1194+
* reason to support constants is so we can do FULL JOIN ON
1195+
* FALSE.)
1196+
*/
1197+
if (!restrictinfo->clause|| !IsA(restrictinfo->clause,Const))
1198+
have_nonmergeable_joinclause= true;
11911199
continue;/* not mergejoinable */
11921200
}
11931201

‎src/include/nodes/execnodes.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.216 2010/01/02 16:58:04 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.217 2010/01/05 23:25:36 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1396,6 +1396,7 @@ typedef struct NestLoopState
13961396
*Clauses info for each mergejoinable clause
13971397
*JoinState current "state" of join. see execdefs.h
13981398
*ExtraMarks true to issue extra Mark operations on inner scan
1399+
*ConstFalseJoin true if we have a constant-false joinqual
13991400
*FillOuter true if should emit unjoined outer tuples anyway
14001401
*FillInner true if should emit unjoined inner tuples anyway
14011402
*MatchedOuter true if found a join match for current outer tuple
@@ -1419,6 +1420,7 @@ typedef struct MergeJoinState
14191420
MergeJoinClausemj_Clauses;/* array of length mj_NumClauses */
14201421
intmj_JoinState;
14211422
boolmj_ExtraMarks;
1423+
boolmj_ConstFalseJoin;
14221424
boolmj_FillOuter;
14231425
boolmj_FillInner;
14241426
boolmj_MatchedOuter;

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

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2443,3 +2443,51 @@ group by t1.q2 order by 1;
24432443
4567890123456789 | 6
24442444
(4 rows)
24452445

2446+
--
2447+
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
2448+
--
2449+
select * from int4_tbl a full join int4_tbl b on true;
2450+
f1 | f1
2451+
-------------+-------------
2452+
0 | 0
2453+
0 | 123456
2454+
0 | -123456
2455+
0 | 2147483647
2456+
0 | -2147483647
2457+
123456 | 0
2458+
123456 | 123456
2459+
123456 | -123456
2460+
123456 | 2147483647
2461+
123456 | -2147483647
2462+
-123456 | 0
2463+
-123456 | 123456
2464+
-123456 | -123456
2465+
-123456 | 2147483647
2466+
-123456 | -2147483647
2467+
2147483647 | 0
2468+
2147483647 | 123456
2469+
2147483647 | -123456
2470+
2147483647 | 2147483647
2471+
2147483647 | -2147483647
2472+
-2147483647 | 0
2473+
-2147483647 | 123456
2474+
-2147483647 | -123456
2475+
-2147483647 | 2147483647
2476+
-2147483647 | -2147483647
2477+
(25 rows)
2478+
2479+
select * from int4_tbl a full join int4_tbl b on false;
2480+
f1 | f1
2481+
-------------+-------------
2482+
| 0
2483+
| 123456
2484+
| -123456
2485+
| 2147483647
2486+
| -2147483647
2487+
0 |
2488+
123456 |
2489+
-123456 |
2490+
2147483647 |
2491+
-2147483647 |
2492+
(10 rows)
2493+

‎src/test/regress/sql/join.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -561,3 +561,9 @@ from int8_tbl t1 left join
561561
(select q1, case when q2=1 then1 else q2 endas q2from int8_tbl) t2
562562
on (t1.q2=t2.q1)
563563
group byt1.q2order by1;
564+
565+
--
566+
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
567+
--
568+
select*from int4_tbl a fulljoin int4_tbl bon true;
569+
select*from int4_tbl a fulljoin int4_tbl bon false;

0 commit comments

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