Re: Outer joins and NULLs (old subject "ERROR: failed to find conversion function from key_vals_nn to record[]")

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

Allowing domains to be defined as not null at this point is simply something that we don't support but don't error out upon in the interest of backward compatibility. (IMO, the documentation is not this strongly worded.) It, as you note, has some corner-case bugs. You can avoid those bugs by simply not using a non-null constraint as suggested.

...At most we should probably go from saying "Best practice therefore..." to "We no longer support setting a not null constraint on a domain but will not error in the interest of not breaking existing uses that are careful to avoid the problematic corner-cases”.

Thank you all for helping me see the problem here. I’d certainly welcome strengthening the doc’s admonition to use wording like "unsupported", "unpredictable results", and "just don’t do this".

I was simply confused. There's no other way to say it. Anyway, my foolish use of domains with "not null" constraints hadn't left the privacy of my own laptop—and I've expunged all those uses now.

Here’s what I now take from that "create domain" note:

When a datum (either an actual value or a NULL) is copied from a "container" whose data type is the domain "d" to another container with that same data type, the constraints that might have been defined for "d" are not re-checked.

I'm using "container" here as an ad hoc, and somewhat loose, umbrella term of art for any of these:

—row-column intersection in a table (or view)

—an attribute of a composite type instance

—a local variable in a PL/pgSQL subprogram or anonymous block 

—a PL/pgSQL subprogram's formal parameter

—a PL/pgSQL subprogram's return datum

—and so on

I quite like this compact illustration of the paradox. (I expect that everybody has their own favorite.)

create domain text_nn as text not null;

create view null_having_null_not_constraint(t_nn, dt) as
with
  c1(t_nn) as (
    values('dog'::text_nn)),

  c2(t_nn) as (
    select (select t_nn from c1 where null))

select
  t_nn, pg_typeof(t_nn)
from c2;

\pset null '~~~~'
select t_nn, dt from null_having_null_not_constraint;

This is the result:

 t_nn |   dt    
------+---------
 ~~~~ | text_nn

And I quite like this demo of the fact that copying a datum between containers with the same constrained domain data type doesn't re-check the constraints:

do $body$
declare
  t_var_nn text_nn := '';
begin
  t_var_nn := (select t_nn from null_having_null_not_constraint);
  assert (t_var_nn is null), 'Expected "t_var_nn" (paradoxically) to be NULL here';
end;

$body$;

I'll use « the "paradoxical" pattern », below, to denote the fact that you can find a NULL in a container whose datatype has a "not null" constraint.

 Here's the summary of my tests:

 ————————————————————————————————————————————————|—————————————————————————————————————
|                                                |                                     |
|  (1) select t_nn, dt from                      |  Follows the "paradoxical" pattern  |
|      null_having_null_not_constraint;          |                                     |
|                                                |                                     |
—————————————————————————————————————————————————|——————————————————————————————————————
|                                                |                                     |
|  (2) select t_nn... into                       |                                     |
|      text_nn local variable                    |  Follows the "paradoxical" pattern  |
|                                                |                                     |
—————————————————————————————————————————————————|——————————————————————————————————————
|                                                |                                     |
|  (3) select t_nn... into                       |                                     |
|      text_nn field in composite type           |  Follows the "paradoxical" pattern  |
|                                                |                                     |
—————————————————————————————————————————————————|——————————————————————————————————————
|                                                |                                     |
|  (4) assign (select t_nn...) to                |                                     |
|      text_nn IN formal                         |  Follows the "paradoxical" pattern  |
|                                                |                                     |
|————————————————————————————————————————————————|——————————————————————————————————————
|                                                |                                     |
|  (5) returning t_nn in function that           |                                     |
|      returns "text_nn"                         |  Follows the "paradoxical" pattern  |
|                                                |                                     |
|————————————————————————————————————————————————|——————————————————————————————————————
|                                                |                                     |
|  (6) select t_nn ... into                      |  UNSURPRISING                       |
|      unconstrained text local variable         |  "domain text_nn does not           |
|      returning text_nn                         |     allow null values"              |
|                                                |                                     |

|————————————————————————————————————————————————|——————————————————————————————————————

I expect that there's yet more tests that I might do. But there's no point because, as I hope That I've understood properly, the "paradoxical" pattern _can_ happen. So it's unimportant to discover every case where it _does_ happen.

Finally, nothing in these discussions has convinced me that there are two kinds of NULL. I continue to believe that "there is no information available" has no nuances. But I do see that there are (at least) two ways that NULL, with this meaning, can be produced:

Either, an explicit assignment says "I have no information"; or (as the "scalar subquery where false" and the "outer join" SQLs have shown, the evaluation of a datum that the query produces concludes "I have no information".