> david.g.johns...@gmail.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>> I’ve copied a self-contained testcase below. Is the error that the "as
>> intended" test causes due to a known limitation—or even a semantic dilemma
>> that I'm failing to spot? Or might it be due to a bug?
>
> I read the note in create domain as basically “don’t do this” (the not null
> part) but the issue you are pointing out seems unrelated to that.
>
>> /*
>> This one cases the error, thus:
>>
>> ERROR: failed to find conversion function from key_vals_nn to record[]
>> CONTEXT: SQL expression "(kv1_nn = any(kvs_nn))"
>> */;
>> select f('as intended');
>
> The fact that a domain over an array isn’t being seen as an array here seems
> like a bug. POLA violation at least, and I don’t recall any notes regarding
> this dynamic in the docs.
>
> However, a more trivial case does work, at least in HEAD:
>
> create domain mytext as text[] not null;
> select '1' = any(array['1','2']::mytext);
> ?column?
> ----------
> t
>
> However, as you show:
>
> create type kv AS ( key text, val text );
> create domain kvarr as kv[];
> select ('1','one')::kv = any (array[('1','one')::kv]);
> ?column?
> ----------
> t
> (1 row)
>
> select ('1','one')::kv = any ((array[('1','one')::kv])::kvarr);
> ERROR: failed to find conversion function from kvarr to record[]
>
> So the interaction of a composite type and the domain over array seems to be
> the scope of the issue - which makes me thing bug even more.
Thanks for the quick response, David. I'll use my workaround for now. And I'll
look out for news about a possible fix.
About this from the "create domain" doc:
«
Best practice therefore is to design a domain's constraints so that a null
value is allowed, and then to apply column NOT NULL constraints to columns of
the domain type as needed, rather than directly to the domain type.
»
What an enormous disappointment. It defeats a large part of what I wanted to
adopt as a practice. As I understand that “don’t do this” caution, and the
discussion that surrounds it, the advice applies only to the case that a domain
with a not null constraint is used as the data type of a column in a table. I
tried this variant on what the doc has:
create domain text_nn as text not null;
create table t1(k int primary key, v text not null);
insert into t1(k, v) values(1, 'x');
-- Causes:
-- null value in column "v" of relation "t1" violates not-null constraint
insert into t1(k, v) values(2, (select 'y'::text_nn where false));
Right, the subquery evaluates to "null". Then failure comes, as is intended,
when the attempt is made to assign "null" to "t.v" in the to-be-inserted row.
Then I repeated the test like this:
create table t2(k int primary key, v text_nn);
insert into t2(k, v) values(1, 'x');
insert into t2(k, v) values(2, (select 'y'::text_nn where false));
\pset null '<null>'
select
k,
v,
pg_typeof(v) as "pg_typeof(v)"
from t2;
No error—and this result:
k | v | pg_typeof(v)
---+--------+--------------
1 | x | text_nn
2 | <null> | text_nn
This is what the doc promises. But how can you see it as anything but a bug?
The subquery evaluates to "null", and only then is the attempt made to create a
new row which self-evidently violates the domain's constraint. How is it any
different from this:
insert into t2(k, v) values(1, null);
This obligingly causes "domain text_nn does not allow null values".
