Re: [HACKERS] Handling NULL records in plpgsql
On Sat, Oct 25, 2008 at 11:59 AM, Robert Haas <[EMAIL PROTECTED]> wrote: > > I really think we should have a way of telling if a array/row/record > > variable is actually set to something, and I'm pretty sure that should be > > unrelated to whether all the elements in it happen to be null. > > +1. Also, can whatever syntax we introduce by something relatively > concise? Code that looks nice when you write "=" does not look as > nice when you write "IS NOT DISTINCT FROM". I'm almost inclined to > suggest using functional notation rather than dreaming up a new "IS > WHATEVER" syntax. I've always thought that IS should be used for this. That is: x IS y is equivalent to x=y OR (x IS NULL AND y IS NULL) and x IS NOT y is equivalent to x <> y AND (x IS NOT NULL OR y IS NOT NULL) If you define the IS operator like this then IS NULL is no longer an operator. It is just the IS operator with the NULL literal as the second operand.
Re: [HACKERS] Handling NULL records in plpgsql
> I really think we should have a way of telling if a array/row/record > variable is actually set to something, and I'm pretty sure that should be > unrelated to whether all the elements in it happen to be null. +1. Also, can whatever syntax we introduce by something relatively concise? Code that looks nice when you write "=" does not look as nice when you write "IS NOT DISTINCT FROM". I'm almost inclined to suggest using functional notation rather than dreaming up a new "IS WHATEVER" syntax. > And the IS > NOT NULL case seems exceptionally broken. I think it's extremely confusing > to have it behave differently than NOT blah IS NULL. -1. This is obviously a trap for the unwary but I think we're stuck with it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Handling NULL records in plpgsql
Decibel! <[EMAIL PROTECTED]> writes: > ... I would argue that rowvar_b IS NOT NULL should be false and rowvar_a > IS NOT NULL should be true. While I don't necessarily disagree with you about what is sane, what is required by the SQL spec seems pretty clear here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Handling NULL records in plpgsql
On Oct 24, 2008, at 7:19 PM, Tom Lane wrote: Decibel! <[EMAIL PROTECTED]> writes: Was anything ever done with http://archives.postgresql.org/pgsql- hackers/2008-09/msg01758.php ? No, we got stalled on what the behavior really ought to be: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01772.php I have a different issue I'm seeing, but I think it's related, and ISTM it's a bug (on 8.2.10): It's entirely possible for a row variable to be in a state where neither IS NULL nor IS NOT NULL is true. RTFM (under Comparison Operators) or see the SQL spec. Ahh, I finally saw the bit that talks about it. I really think we should have a way of telling if a array/row/record variable is actually set to something, and I'm pretty sure that should be unrelated to whether all the elements in it happen to be null. And the IS NOT NULL case seems exceptionally broken. I think it's extremely confusing to have it behave differently than NOT blah IS NULL. This puts us in an ugly position. Do we break with spec? Or should we come up with a different construct (IS [NOT] DEFINED?)? I'm disinclined to just leave it as-is, because I think it's pretty common for people to want to see if a variable is set or not. I'm inclined towards DEFINED, as ugly as it is, so that we're not breaking the spec. To answer the questions in that thread, I would say that a record containing all nulls is still a distinct record. It was set to something, it just happens that that something contained all nulls. That's definitely not the same as it being set to nothing. Consider: CREATE TABLE moo(a int, b int, c int); CREATE TABLE cow(LIKE moo); INSERT INTO moo SELECT NULL, NULL, NULL FROM generate_series(1,10) i; SELECT count(*) FROM moo; SELECT count(*) FROM cow; SELECT INTO rowvar_a * FROM moo LIMIT 1; SELECT INTO rowvar_b * FROM cow LIMIT 1; I would argue that rowvar_b IS NOT NULL should be false and rowvar_a IS NOT NULL should be true. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Handling NULL records in plpgsql
Decibel! <[EMAIL PROTECTED]> writes: > Was anything ever done with > http://archives.postgresql.org/pgsql-hackers/2008-09/msg01758.php ? No, we got stalled on what the behavior really ought to be: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01772.php > I have a different issue I'm seeing, but I think it's related, and > ISTM it's a bug (on 8.2.10): It's entirely possible for a row variable to be in a state where neither IS NULL nor IS NOT NULL is true. RTFM (under Comparison Operators) or see the SQL spec. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Handling NULL records in plpgsql
Was anything ever done with http://archives.postgresql.org/pgsql- hackers/2008-09/msg01758.php ? I have a different issue I'm seeing, but I think it's related, and ISTM it's a bug (on 8.2.10): SELECT INTO v_prev * FROM installments_static WHERE id = (SELECT id FROM installments_static i WHERE i.loan_id = NEW.loan_id AND i.installment_number < NEW.installment_number ORDER BY installment_number DESC LIMIT 1 ) ; ... RAISE DEBUG $$Previous installment: id = %, due_date = % Current installment: id = %, number = %, loan_id = %, installment_date = %, due_date = % Next installment: id = %, installment_date = % v_prev IS NOT NULL = %, v_prev IS NULL = % v_next IS NOT NULL = %, v_next IS NULL = %$$ , v_prev.id, v_prev.due_date , NEW.id, NEW.installment_number, NEW.loan_id, NEW.installment_date, NEW.due_date , v_next.id, v_next.installment_date , v_prev IS NOT NULL, v_prev IS NULL , v_next IS NOT NULL, v_next IS NULL ; psql:sql/installments_static.sql:XX: DEBUG: Previous installment: id = 5, due_date = -XX-XX Current installment: id = 8, number = 2, loan_id = 3, installment_date = -XX-XX, due_date = -XX-XX Next installment: id = , installment_date = v_prev IS NOT NULL = f, v_prev IS NULL = f -- v_prev is actually set! v_next IS NOT NULL = f, v_next IS NULL = t -- v_next is unset, ie: NULL If I change v_* IS NOT NULL to NOT v_* IS NULL everything's ok: psql:sql/installments_static.sql:XX: DEBUG: Previous installment: id = 5, due_date = -XX-XX Current installment: id = 8, number = 2, loan_id = 3, installment_date = -XX-XX, due_date = -XX-XX Next installment: id = , installment_date = NOT v_prev IS NULL = t, v_prev IS NULL = f NOT v_next IS NULL = f, v_next IS NULL = t -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature