diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 18 | ||||
| -rw-r--r-- | src/backend/parser/gram.y | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/select_distinct.out | 25 | ||||
| -rw-r--r-- | src/test/regress/sql/select_distinct.sql | 6 |
4 files changed, 57 insertions, 6 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f3df6181798..8e60ba63c81 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.297 2005/12/03 16:45:05 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.298 2005/12/11 10:54:27 neilc Exp $ PostgreSQL documentation --> @@ -345,16 +345,24 @@ PostgreSQL documentation <indexterm> <primary>IS DISTINCT FROM</primary> </indexterm> + <indexterm> + <primary>IS NOT DISTINCT FROM</primary> + </indexterm> The ordinary comparison operators yield null (signifying <quote>unknown</>) when either input is null. Another way to do comparisons is with the <literal>IS DISTINCT FROM</literal> construct: <synopsis> <replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable> +<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable> </synopsis> - For non-null inputs this is the same as the <literal><></> operator. - However, when both inputs are null it will return false, and when just - one input is null it will return true. Thus it effectively acts as though - null were a normal data value, rather than <quote>unknown</>. + For non-null inputs, <literal>IS DISTINCT FROM</literal> this is + the same as the <literal><></> operator. However, when both + inputs are null it will return false, and when just one input is + null it will return true. Similarly, <literal>IS NOT DISTINCT + FROM</literal> is identical to <literal>=</literal> for non-null + inputs, returns true when both inputs are null, and false + otherwise. Thus, these constructs effectively act as though null + were a normal data value, rather than <quote>unknown</>. </para> <para> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 876e8b53ff5..16fdde7b9bc 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.516 2005/11/28 04:35:31 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.517 2005/12/11 10:54:27 neilc Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -6715,6 +6715,13 @@ a_expr: c_expr { $$ = $1; } { $$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, "=", $1, $5); } + | a_expr IS NOT DISTINCT FROM a_expr %prec IS + { + $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL, + (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, + "=", $1, $6)); + + } | a_expr IS OF '(' type_list ')' %prec IS { $$ = (Node *) makeSimpleA_Expr(AEXPR_OF, "=", $1, (Node *) $5); @@ -6880,6 +6887,11 @@ b_expr: c_expr { $$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, "=", $1, $5); } + | b_expr IS NOT DISTINCT FROM b_expr %prec IS + { + $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, + NULL, (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, "=", $1, $6)); + } | b_expr IS OF '(' type_list ')' %prec IS { $$ = (Node *) makeSimpleA_Expr(AEXPR_OF, "=", $1, (Node *) $5); diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out index 019638ed176..abe34ae7ae5 100644 --- a/src/test/regress/expected/select_distinct.out +++ b/src/test/regress/expected/select_distinct.out @@ -195,3 +195,28 @@ SELECT null IS DISTINCT FROM null as "no"; f (1 row) +-- ANSI SQL 2003 form +SELECT 1 IS NOT DISTINCT FROM 2 as "no"; + no +---- + f +(1 row) + +SELECT 2 IS NOT DISTINCT FROM 2 as "yes"; + yes +----- + t +(1 row) + +SELECT 2 IS NOT DISTINCT FROM null as "no"; + no +---- + f +(1 row) + +SELECT null IS NOT DISTINCT FROM null as "yes"; + yes +----- + t +(1 row) + diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql index 94032c312ee..c4a63aaf16f 100644 --- a/src/test/regress/sql/select_distinct.sql +++ b/src/test/regress/sql/select_distinct.sql @@ -56,3 +56,9 @@ SELECT 1 IS DISTINCT FROM 2 as "yes"; SELECT 2 IS DISTINCT FROM 2 as "no"; SELECT 2 IS DISTINCT FROM null as "yes"; SELECT null IS DISTINCT FROM null as "no"; + +-- ANSI SQL 2003 form +SELECT 1 IS NOT DISTINCT FROM 2 as "no"; +SELECT 2 IS NOT DISTINCT FROM 2 as "yes"; +SELECT 2 IS NOT DISTINCT FROM null as "no"; +SELECT null IS NOT DISTINCT FROM null as "yes"; |