Re: [HACKERS] Idea on how to simplify comparing two sets

 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers