On Tue, Sep 10, 2013 at 10:50:32AM -0400, Bruce Momjian wrote:
> > have to hit all the targets. If not, I'd either A: leave things alone
> > or B: remove the special case logic in IS NULL (so that it behaves as
> > coalesce() does) and document our divergence from the standard. Point
> > being: B might actually be the best choice, but it should be
> > understood that we are not going in that direction before pushing
> > patches that go in the other direction.
>
> I see. So going one-level deep in the ROW NULL inspection is something
> we do for IS NULL in queries (actually double-deep inspection)q, but it
> was never consistently implemented across all NULL tests.
Using your examples and others I have collected, I have created an SQL
script which shows our inconsistent behavior, attached, and its output.
If we agree that a single-level NULL inspection of ROWS is the right
approach, it would seem we need my patch, and we need to fix coalesce()
and NOT NULL constraint testing? Is that accurate? Is there more
areas?
Nested RECORDS seem to collapse to a single level, so I don't think we
have to change the recursion there:
SELECT RECORD(RECORD(RECORD(NULL)));
record
--------
(null)
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
SELECT ROW(NULL) IS NULL;
SELECT ROW(ROW(NULL)) IS NULL;
SELECT ROW(ROW(ROW(NULL))) IS NULL;
SELECT RECORD(NULL) IS NULL;
SELECT RECORD(RECORD(NULL)) IS NULL;
SELECT RECORD(RECORD(RECORD(NULL))) IS NULL;
DROP TABLE IF EXISTS tt;
CREATE TABLE tt (x INT);
INSERT INTO tt VALUES(NULL);
SELECT ROW(x) IS NULL FROM tt;
SELECT ROW(ROW(x)) IS NULL FROM tt;
SELECT ROW(ROW(ROW(x))) IS NULL FROM tt;
SELECT coalesce(ROW(NULL,NULL), ROW('no', 'bueno'));
SELECT CASE WHEN ROW(NULL,NULL) IS NULL THEN ROW('no', 'bueno') END;
DROP TABLE IF EXISTS test3;
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (x INT);
CREATE TABLE test3(x test2 NOT NULL);
INSERT INTO test3 VALUES (null);
INSERT INTO test3 VALUES (row(null));
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN
DROP TABLE IF EXISTS test;
CREATE TABLE test (x INT, y INT);
INSERT INTO test VALUES (1, NULL), (NULL, 1), (NULL, NULL);
FOR r IN SELECT * FROM test
LOOP
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END LOOP;
END;
$$;
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN
SELECT NULL INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
SELECT ROW(NULL) INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END;
$$;
SELECT ROW(NULL) IS NULL;
?column?
----------
t
(1 row)
SELECT ROW(ROW(NULL)) IS NULL;
?column?
----------
t
(1 row)
SELECT ROW(ROW(ROW(NULL))) IS NULL;
?column?
----------
f
(1 row)
SELECT RECORD(NULL) IS NULL;
?column?
----------
t
(1 row)
SELECT RECORD(RECORD(NULL)) IS NULL;
?column?
----------
t
(1 row)
SELECT RECORD(RECORD(RECORD(NULL))) IS NULL;
?column?
----------
t
(1 row)
DROP TABLE IF EXISTS tt;
DROP TABLE
CREATE TABLE tt (x INT);
CREATE TABLE
INSERT INTO tt VALUES(NULL);
INSERT 0 1
SELECT ROW(x) IS NULL FROM tt;
?column?
----------
t
(1 row)
SELECT ROW(ROW(x)) IS NULL FROM tt;
?column?
----------
t
(1 row)
SELECT ROW(ROW(ROW(x))) IS NULL FROM tt;
?column?
----------
f
(1 row)
SELECT coalesce(ROW(NULL,NULL), ROW('no', 'bueno'));
coalesce
----------
(,)
(1 row)
SELECT CASE WHEN ROW(NULL,NULL) IS NULL THEN ROW('no', 'bueno') END;
case
------------
(no,bueno)
(1 row)
DROP TABLE IF EXISTS test3;
DROP TABLE
DROP TABLE IF EXISTS test2;
DROP TABLE
CREATE TABLE test2 (x INT);
CREATE TABLE
CREATE TABLE test3(x test2 NOT NULL);
CREATE TABLE
INSERT INTO test3 VALUES (null);
ERROR: null value in column "x" violates not-null constraint
DETAIL: Failing row contains (null).
INSERT INTO test3 VALUES (row(null));
INSERT 0 1
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN
DROP TABLE IF EXISTS test;
CREATE TABLE test (x INT, y INT);
INSERT INTO test VALUES (1, NULL), (NULL, 1), (NULL, NULL);
FOR r IN SELECT * FROM test
LOOP
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END LOOP;
END;
$$;
NOTICE: false
NOTICE: false
NOTICE: true
DO
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN
SELECT NULL INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
SELECT ROW(NULL) INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END;
$$;
NOTICE: true
NOTICE: false
DO
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
