From: | Anders Granlund <anders(dot)granlund(at)trustly(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)trustly(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Idea on how to simplify comparing two sets |
Date: | 2017-02-07 15:40:39 |
Message-ID: | CACA-HOGXcLav4OxuGRB5Dgf3APJtebZMWD_6+edZQ1UOWR40iA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
What about this ambiguity?
SELECT TRUE
WHERE FALSE
IS NOT DISTINCT FROM
(SELECT TRUE)
On Tue, Feb 7, 2017 at 4:13 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> Hi hackers,
>
> Currently there is no simple way to check if two sets are equal.
>
> Looks like no RDBMS in the world has a simple command for it.
>
> You have to do something like:
>
> WITH
> T1 AS (SELECT * FROM Foo WHERE FooID BETWEEN 1 AND 10000),
> T2 AS (SELECT * FROM Bar WHERE BarID BETWEEN 1 AND 10000)
> SELECT
> GREATEST(
> (SELECT COUNT(*) FROM T1),
> (SELECT COUNT(*) FROM T2)
> )
> =
> (SELECT COUNT(*) FROM (
> SELECT * FROM T1
> INTERSECT ALL
> SELECT * FROM T2
> ) AS X)
> INTO _Identical;
>
> or,
>
> SELECT 'Missmatch!' WHERE EXISTS (
> SELECT * FROM Foo
> FULL JOIN Bar ON (Foo.FooID = Bar.BarID AND
> Foo IS NOT DISTINCT FROM Bar)
> WHERE TRUE
> AND ( Foo.FooID BETWEEN 1 AND 10000 AND
> Bar.BarID BETWEEN 1 AND 10000 )
> AND ( Foo.FooID IS NULL OR
> Bar.BarID IS NULL);
>
> Introducing new SQL keywords is of course not an option,
> since it would possibly break backwards compatibility.
>
> So here is an idea that doesn't break backwards compatibility:
>
> Let's give a meaning for the existing IS DISTINCT and IS NOT DISTINCT,
> that is currently a syntax error when used between two sets.
>
> SELECT 1 IS DISTINCT FROM SELECT 1;
> ERROR: syntax error at or near "SELECT"
> LINE 1: SELECT 1 IS DISTINCT FROM SELECT 1;
>
> The example above could be written as:
>
> _Identical := (
> SELECT * FROM Foo WHERE FooID BETWEEN 1 AND 10000
> IS NOT DISTINCT FROM
> SELECT * FROM Bar WHERE BarID BETWEEN 1 AND 10000
> );
>
> Which would set _Identical to TRUE if the two sets are equal,
> and FALSE otherwise.
>
> Since it's currently a syntax error, there is no risk for changed
> behaviour for any existing executable queries.
>
> Thoughts?
>
> /Joel
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Jacobson | 2017-02-07 15:43:16 | Re: Idea on how to simplify comparing two sets |
Previous Message | Jonathan S. Katz | 2017-02-07 15:37:36 | Press Release Draft - 2016-02-09 Cumulative Update |