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

Commit25549ed

Browse files
committed
Fix equivclass.c's not-quite-right strategy for handling X=X clauses.
The original coding correctly noted that these aren't just redundancies(they're effectively X IS NOT NULL, assuming = is strict). However, theygot treated that way if X happened to be in a single-member EquivalenceClassalready, which could happen if there was an ORDER BY X clause, for instance.The simplest and most reliable solution seems to be to not try to processsuch clauses through the EquivalenceClass machinery; just throw them backfor traditional processing. The amount of work that'd be needed to besmarter than that seems out of proportion to the benefit.Per bug #5084 from Bernt Marius Johnsen, and analysis by Andrew Gierth.
1 parent176c3c8 commit25549ed

File tree

4 files changed

+44
-10
lines changed

4 files changed

+44
-10
lines changed

‎src/backend/optimizer/README

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
$PostgreSQL: pgsql/src/backend/optimizer/README,v 1.51 2009/09/1720:49:28 tgl Exp $
1+
$PostgreSQL: pgsql/src/backend/optimizer/README,v 1.52 2009/09/29 01:20:34 tgl Exp $
22

33
Optimizer
44
=========
@@ -481,7 +481,10 @@ operator is mergejoinable, so there is no way for a volatile expression to
481481
get into EquivalenceClasses otherwise. Aggregates are disallowed in WHERE
482482
altogether, so will never be found in a mergejoinable clause.) This is just
483483
a convenience to maintain a uniform PathKey representation: such an
484-
EquivalenceClass will never be merged with any other.
484+
EquivalenceClass will never be merged with any other. Note in particular
485+
that a single-item EquivalenceClass {a.x} is *not* meant to imply an
486+
assertion that a.x = a.x; the practical effect of this is that a.x could
487+
be NULL.
485488

486489
An EquivalenceClass also contains a list of btree opfamily OIDs, which
487490
determines what the equalities it represents actually "mean". All the

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

Lines changed: 18 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/optimizer/path/equivclass.c,v 1.20 2009/09/12 00:04:58 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/optimizer/path/equivclass.c,v 1.21 2009/09/29 01:20:34 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -114,6 +114,19 @@ process_equivalence(PlannerInfo *root, RestrictInfo *restrictinfo,
114114
item1_relids=restrictinfo->left_relids;
115115
item2_relids=restrictinfo->right_relids;
116116

117+
/*
118+
* Reject clauses of the form X=X. These are not as redundant as they
119+
* might seem at first glance: assuming the operator is strict, this is
120+
* really an expensive way to write X IS NOT NULL. So we must not risk
121+
* just losing the clause, which would be possible if there is already
122+
* a single-element EquivalenceClass containing X. The case is not
123+
* common enough to be worth contorting the EC machinery for, so just
124+
* reject the clause and let it be processed as a normal restriction
125+
* clause.
126+
*/
127+
if (equal(item1,item2))
128+
return false;/* X=X is not a useful equivalence */
129+
117130
/*
118131
* If below outer join, check for strictness, else reject.
119132
*/
@@ -152,13 +165,10 @@ process_equivalence(PlannerInfo *root, RestrictInfo *restrictinfo,
152165
*
153166
* 4. We find neither.Make a new, two-entry EC.
154167
*
155-
* Note: since all ECs are built through this process, it's impossible
156-
* that we'd match an item in more than one existing EC. It is possible
157-
* to match more than once within an EC, if someone fed us something silly
158-
* like "WHERE X=X". (However, we can't simply discard such clauses,
159-
* since they should fail when X is null; so we will build a 2-member EC
160-
* to ensure the correct restriction clause gets generated. Hence there
161-
* is no shortcut here for item1 and item2 equal.)
168+
* Note: since all ECs are built through this process or the similar
169+
* search in get_eclass_for_sort_expr(), it's impossible that we'd match
170+
* an item in more than one existing nonvolatile EC. So it's okay to stop
171+
* at the first match.
162172
*/
163173
ec1=ec2=NULL;
164174
em1=em2=NULL;

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -768,3 +768,19 @@ select sillysrf(-1) order by 1;
768768
(4 rows)
769769

770770
drop function sillysrf(int);
771+
-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
772+
-- (see bug #5084)
773+
select * from (values (2),(null),(1)) v(k) where k = k order by k;
774+
k
775+
---
776+
1
777+
2
778+
(2 rows)
779+
780+
select * from (values (2),(null),(1)) v(k) where k = k;
781+
k
782+
---
783+
2
784+
1
785+
(2 rows)
786+

‎src/test/regress/sql/select.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -202,3 +202,8 @@ select sillysrf(42);
202202
select sillysrf(-1)order by1;
203203

204204
dropfunction sillysrf(int);
205+
206+
-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
207+
-- (see bug #5084)
208+
select*from (values (2),(null),(1)) v(k)where k= korder by k;
209+
select*from (values (2),(null),(1)) v(k)where k= k;

0 commit comments

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