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

Commit6e07709

Browse files
committed
Implement SQL-compliant treatment of row comparisons for < <= > >= cases
(previously we only did = and <> correctly). Also, allow row comparisonswith any operators that are in btree opclasses, not only those with thesespecific names. This gets rid of a whole lot of indefensible assumptionsabout the behavior of particular operators based on their names ... thoughit's still true that IN and NOT IN expand to "= ANY". The patch adds aRowCompareExpr expression node type, and makes some changes in therepresentation of ANY/ALL/ROWCOMPARE SubLinks so that they can share codewith RowCompareExpr.I have not yet done anything about making RowCompareExpr an indexableoperator, but will look at that soon.initdb forced due to changes in stored rules.
1 parenta37422e commit6e07709

File tree

26 files changed

+1442
-649
lines changed

26 files changed

+1442
-649
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 93 additions & 65 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.300 2005/12/21 23:22:55 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.301 2005/12/28 01:29:58 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -350,18 +350,18 @@ PostgreSQL documentation
350350
</indexterm>
351351
The ordinary comparison operators yield null (signifying <quote>unknown</>)
352352
when either input is null. Another way to do comparisons is with the
353-
<literal>IS DISTINCT FROM</literal> construct:
353+
<literal>IS<optional> NOT </>DISTINCT FROM</literal> construct:
354354
<synopsis>
355355
<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>
356356
<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable>
357357
</synopsis>
358-
For non-null inputs, <literal>IS DISTINCT FROM</literal>thisis
358+
For non-null inputs, <literal>IS DISTINCT FROM</literal> is
359359
the same as the <literal>&lt;&gt;</> operator. However, when both
360360
inputs are null it will return false, and when just one input is
361361
null it will return true. Similarly, <literal>IS NOT DISTINCT
362362
FROM</literal> is identical to <literal>=</literal> for non-null
363-
inputs, returns true when both inputs are null, and false
364-
otherwise. Thus, these constructs effectively act as though null
363+
inputs,but itreturns true when both inputs are null, and false when only
364+
one input is null. Thus, these constructs effectively act as though null
365365
were a normal data value, rather than <quote>unknown</>.
366366
</para>
367367

@@ -7999,8 +7999,8 @@ SELECT col1 FROM tab1
79997999
equal if all their corresponding members are non-null and equal; the rows
80008000
are unequal if any corresponding members are non-null and unequal;
80018001
otherwise the result of that row comparison is unknown (null).
8002-
If all the row results are either unequal or null, with at least one null,
8003-
then the result of <token>IN</token> is null.
8002+
If all theper-row results are either unequal or null, with at least one
8003+
null,then the result of <token>IN</token> is null.
80048004
</para>
80058005
</sect2>
80068006

@@ -8055,8 +8055,8 @@ SELECT col1 FROM tab1
80558055
equal if all their corresponding members are non-null and equal; the rows
80568056
are unequal if any corresponding members are non-null and unequal;
80578057
otherwise the result of that row comparison is unknown (null).
8058-
If all the row results are either unequal or null, with at least one null,
8059-
then the result of <token>NOT IN</token> is null.
8058+
If all theper-row results are either unequal or null, with at least one
8059+
null,then the result of <token>NOT IN</token> is null.
80608060
</para>
80618061
</sect2>
80628062

@@ -8109,23 +8109,19 @@ SELECT col1 FROM tab1
81098109
subquery, which must return exactly as many columns as there are
81108110
expressions in the left-hand row. The left-hand expressions are
81118111
evaluated and compared row-wise to each row of the subquery result,
8112-
using the given <replaceable>operator</replaceable>. Presently,
8113-
only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
8114-
in row-wise <token>ANY</token> constructs.
8115-
The result of <token>ANY</token> is <quote>true</> if any equal or unequal row is
8116-
found, respectively.
8117-
The result is <quote>false</> if no such row is found (including the special
8118-
case where the subquery returns no rows).
8112+
using the given <replaceable>operator</replaceable>.
8113+
The result of <token>ANY</token> is <quote>true</> if the comparison
8114+
returns true for any subquery row.
8115+
The result is <quote>false</> if the comparison returns false for every
8116+
subquery row (including the special case where the subquery returns no
8117+
rows).
8118+
The result is NULL if the comparison does not return true for any row,
8119+
and it returns NULL for at least one row.
81198120
</para>
81208121

81218122
<para>
8122-
As usual, null values in the rows are combined per
8123-
the normal rules of SQL Boolean expressions. Two rows are considered
8124-
equal if all their corresponding members are non-null and equal; the rows
8125-
are unequal if any corresponding members are non-null and unequal;
8126-
otherwise the result of that row comparison is unknown (null).
8127-
If there is at least one null row result, then the result of <token>ANY</token>
8128-
cannot be false; it will be true or null.
8123+
See <xref linkend="row-wise-comparison"> for details about the meaning
8124+
of a row-wise comparison.
81298125
</para>
81308126
</sect2>
81318127

@@ -8145,20 +8141,14 @@ SELECT col1 FROM tab1
81458141
The result of <token>ALL</token> is <quote>true</> if all rows yield true
81468142
(including the special case where the subquery returns no rows).
81478143
The result is <quote>false</> if any false result is found.
8144+
The result is NULL if the comparison does not return false for any row,
8145+
and it returns NULL for at least one row.
81488146
</para>
81498147

81508148
<para>
81518149
<token>NOT IN</token> is equivalent to <literal>&lt;&gt; ALL</literal>.
81528150
</para>
81538151

8154-
<para>
8155-
Note that if there are no failures but at least one right-hand row yields
8156-
null for the operator's result, the result of the <token>ALL</token> construct
8157-
will be null, not true.
8158-
This is in accordance with SQL's normal rules for Boolean combinations
8159-
of null values.
8160-
</para>
8161-
81628152
<para>
81638153
As with <token>EXISTS</token>, it's unwise to assume that the subquery will
81648154
be evaluated completely.
@@ -8175,24 +8165,19 @@ SELECT col1 FROM tab1
81758165
subquery, which must return exactly as many columns as there are
81768166
expressions in the left-hand row. The left-hand expressions are
81778167
evaluated and compared row-wise to each row of the subquery result,
8178-
using the given <replaceable>operator</replaceable>. Presently,
8179-
only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
8180-
in row-wise <token>ALL</token> queries.
8181-
The result of <token>ALL</token> is <quote>true</> if all subquery rows are equal
8182-
or unequal, respectively (including the special
8168+
using the given <replaceable>operator</replaceable>.
8169+
The result of <token>ALL</token> is <quote>true</> if the comparison
8170+
returns true for all subquery rows (including the special
81838171
case where the subquery returns no rows).
8184-
The result is <quote>false</> if any row is found to be unequal or equal,
8185-
respectively.
8172+
The result is <quote>false</> if the comparison returns false for any
8173+
subquery row.
8174+
The result is NULL if the comparison does not return false for any
8175+
subquery row, and it returns NULL for at least one row.
81868176
</para>
81878177

81888178
<para>
8189-
As usual, null values in the rows are combined per
8190-
the normal rules of SQL Boolean expressions. Two rows are considered
8191-
equal if all their corresponding members are non-null and equal; the rows
8192-
are unequal if any corresponding members are non-null and unequal;
8193-
otherwise the result of that row comparison is unknown (null).
8194-
If there is at least one null row result, then the result of <token>ALL</token>
8195-
cannot be true; it will be false or null.
8179+
See <xref linkend="row-wise-comparison"> for details about the meaning
8180+
of a row-wise comparison.
81968181
</para>
81978182
</sect2>
81988183

@@ -8216,17 +8201,11 @@ SELECT col1 FROM tab1
82168201
the subquery cannot return more than one row. (If it returns zero rows,
82178202
the result is taken to be null.) The left-hand side is evaluated and
82188203
compared row-wise to the single subquery result row.
8219-
Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
8220-
in row-wise comparisons.
8221-
The result is <quote>true</> if the two rows are equal or unequal, respectively.
82228204
</para>
82238205

82248206
<para>
8225-
As usual, null values in the rows are combined per
8226-
the normal rules of SQL Boolean expressions. Two rows are considered
8227-
equal if all their corresponding members are non-null and equal; the rows
8228-
are unequal if any corresponding members are non-null and unequal;
8229-
otherwise the result of the row comparison is unknown (null).
8207+
See <xref linkend="row-wise-comparison"> for details about the meaning
8208+
of a row-wise comparison.
82308209
</para>
82318210
</sect2>
82328211
</sect1>
@@ -8255,6 +8234,10 @@ SELECT col1 FROM tab1
82558234
<primary>SOME</primary>
82568235
</indexterm>
82578236

8237+
<indexterm>
8238+
<primary>row-wise comparison</primary>
8239+
</indexterm>
8240+
82588241
<indexterm>
82598242
<primary>comparison</primary>
82608243
<secondary>row-wise</secondary>
@@ -8264,6 +8247,10 @@ SELECT col1 FROM tab1
82648247
<primary>IS DISTINCT FROM</primary>
82658248
</indexterm>
82668249

8250+
<indexterm>
8251+
<primary>IS NOT DISTINCT FROM</primary>
8252+
</indexterm>
8253+
82678254
<indexterm>
82688255
<primary>IS NULL</primary>
82698256
</indexterm>
@@ -8288,7 +8275,7 @@ SELECT col1 FROM tab1
82888275
<title><literal>IN</literal></title>
82898276

82908277
<synopsis>
8291-
<replaceable>expression</replaceable> IN (<replaceable>value</replaceable><optional>, ...</optional>)
8278+
<replaceable>expression</replaceable> IN (<replaceable>value</replaceable><optional>, ...</optional>)
82928279
</synopsis>
82938280

82948281
<para>
@@ -8319,7 +8306,7 @@ OR
83198306
<title><literal>NOT IN</literal></title>
83208307

83218308
<synopsis>
8322-
<replaceable>expression</replaceable> NOT IN (<replaceable>value</replaceable><optional>, ...</optional>)
8309+
<replaceable>expression</replaceable> NOT IN (<replaceable>value</replaceable><optional>, ...</optional>)
83238310
</synopsis>
83248311

83258312
<para>
@@ -8425,7 +8412,7 @@ AND
84258412
</para>
84268413
</sect2>
84278414

8428-
<sect2>
8415+
<sect2 id="row-wise-comparison">
84298416
<title>Row-wise Comparison</title>
84308417

84318418
<synopsis>
@@ -8436,23 +8423,52 @@ AND
84368423
Each side is a row constructor,
84378424
as described in <xref linkend="sql-syntax-row-constructors">.
84388425
The two row values must have the same number of fields.
8439-
Each side is evaluated and they are compared row-wise.
8440-
Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
8441-
in row-wise comparisons.
8442-
The result is <quote>true</> if the two rows are equal or unequal, respectively.
8426+
Each side is evaluated and they are compared row-wise. Row comparisons
8427+
are allowed when the <replaceable>operator</replaceable> is
8428+
<literal>=</>,
8429+
<literal>&lt;&gt;</>,
8430+
<literal>&lt;</>,
8431+
<literal>&lt;=</>,
8432+
<literal>&gt;</> or
8433+
<literal>&gt;=</>,
8434+
or has semantics similar to one of these. (To be specific, an operator
8435+
can be a row comparison operator if it is a member of a btree operator
8436+
class, or is the negator of the <literal>=</> member of a btree operator
8437+
class.)
84438438
</para>
84448439

84458440
<para>
8446-
As usual, null values in the rows are combined per
8447-
thenormal rules of SQL Boolean expressions. Two rows are considered
8441+
The <literal>=</> and <literal>&lt;&gt;</> cases work slightly differently
8442+
fromtheothers. Two rows are considered
84488443
equal if all their corresponding members are non-null and equal; the rows
84498444
are unequal if any corresponding members are non-null and unequal;
84508445
otherwise the result of the row comparison is unknown (null).
84518446
</para>
84528447

8453-
<indexterm>
8454-
<primary>IS DISTINCT FROM</primary>
8455-
</indexterm>
8448+
<para>
8449+
For the <literal>&lt;</>, <literal>&lt;=</>, <literal>&gt;</> and
8450+
<literal>&gt;=</> cases, the row elements are compared left-to-right,
8451+
stopping as soon as an unequal or null pair of elements is found.
8452+
If either of this pair of elements is null, the result of the
8453+
row comparison is unknown (null); otherwise comparison of this pair
8454+
of elements determines the result. For example,
8455+
<literal>ROW(1,2,NULL) &lt; ROW(1,3,0)</>
8456+
yields true, not null, because the third pair of elements are not
8457+
considered.
8458+
</para>
8459+
8460+
<note>
8461+
<para>
8462+
Prior to <productname>PostgreSQL</productname> 8.2, the
8463+
<literal>&lt;</>, <literal>&lt;=</>, <literal>&gt;</> and <literal>&gt;=</>
8464+
cases were not handled per SQL specification. A comparison like
8465+
<literal>ROW(a,b) &lt; ROW(c,d)</>
8466+
was implemented as
8467+
<literal>a &lt; c AND b &lt; d</>
8468+
whereas the correct behavior is equivalent to
8469+
<literal>a &lt; c OR (a = c AND b &lt; d)</>.
8470+
</para>
8471+
</note>
84568472

84578473
<synopsis>
84588474
<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable>
@@ -8466,6 +8482,18 @@ AND
84668482
be either true or false, never null.
84678483
</para>
84688484

8485+
<synopsis>
8486+
<replaceable>row_constructor</replaceable> IS NOT DISTINCT FROM <replaceable>row_constructor</replaceable>
8487+
</synopsis>
8488+
8489+
<para>
8490+
This construct is similar to a <literal>=</literal> row comparison,
8491+
but it does not yield null for null inputs. Instead, any null value is
8492+
considered unequal to (distinct from) any non-null value, and any two
8493+
nulls are considered equal (not distinct). Thus the result will always
8494+
be either true or false, never null.
8495+
</para>
8496+
84698497
<synopsis>
84708498
<replaceable>row_constructor</replaceable> IS NULL
84718499
<replaceable>row_constructor</replaceable> IS NOT NULL

‎src/backend/catalog/dependency.c‎

Lines changed: 16 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.48 2005/11/22 18:17:07 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.49 2005/12/28 01:29:59 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1129,23 +1129,28 @@ find_expr_references_walker(Node *node,
11291129
&context->addrs);
11301130
/* fall through to examine arguments */
11311131
}
1132-
if (IsA(node,SubLink))
1132+
if (is_subplan(node))
11331133
{
1134-
SubLink*sublink= (SubLink*)node;
1135-
ListCell*opid;
1134+
/* Extra work needed here if we ever need this case */
1135+
elog(ERROR,"already-planned subqueries not supported");
1136+
}
1137+
if (IsA(node,RowCompareExpr))
1138+
{
1139+
RowCompareExpr*rcexpr= (RowCompareExpr*)node;
1140+
ListCell*l;
11361141

1137-
foreach(opid,sublink->operOids)
1142+
foreach(l,rcexpr->opnos)
11381143
{
1139-
add_object_address(OCLASS_OPERATOR,lfirst_oid(opid),0,
1144+
add_object_address(OCLASS_OPERATOR,lfirst_oid(l),0,
1145+
&context->addrs);
1146+
}
1147+
foreach(l,rcexpr->opclasses)
1148+
{
1149+
add_object_address(OCLASS_OPCLASS,lfirst_oid(l),0,
11401150
&context->addrs);
11411151
}
11421152
/* fall through to examine arguments */
11431153
}
1144-
if (is_subplan(node))
1145-
{
1146-
/* Extra work needed here if we ever need this case */
1147-
elog(ERROR,"already-planned subqueries not supported");
1148-
}
11491154
if (IsA(node,Query))
11501155
{
11511156
/* Recurse into RTE subquery or not-yet-planned sublink subquery */

0 commit comments

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