Re: BUG #11103: to_json() does not convert correctly DOMAINs type since 9.3.5 (int expected instead of string) ?

The following bug has been logged on the website:

Bug reference: 11103
Logged by: David GRELAUD
Email address: dgrelaud(at)ideolys(dot)com
PostgreSQL version: 9.3.5
Operating system: Ubuntu 12.04 or Mac OS X 10.9
Description:

Hi,

The function to_json() does not convert correctly (= as before) DOMAINs
since 9.3.5. It was OK in 9.3.4.

Here is a minimal test case :

CREATE DOMAIN UNSIGNED_INT4 AS INT4 CHECK (VALUE >= 0);
SELECT to_json(2::UNSIGNED_INT4);

With 9.3.5, it returns a JSON string : '2' (with quotes)
With 9.3.4, it returns a JSON int : 2 (without quotes)

The same behavior happens with array_to_json() and maybe others related
functions...

If we do not use domains, SELECT to_json(2::INT4) returns always a JSON int
(even with 9.3.5).

Is it related to the change made by Tom Lane?
commit 0ca6bda8e7501947c05f30c127f6d12ff90b5a64 and the release note 9.3.5
"Fix identification of input type category in to_json() and friends (Tom
Lane)"?

Is it volontary? If so, don't worry, I will find a workaround.

Thank you very much for maintaining this wonderfull Open Source database.

David.

On Fri, Aug 1, 2014 at 12:24 AM, <dgrelaud(at)ideolys(dot)com> wrote:
> With 9.3.5, it returns a JSON string : '2' (with quotes)
> With 9.3.4, it returns a JSON int : 2 (without quotes)

Double quotes actually, to make it valid JSON:
=# CREATE DOMAIN UNSIGNED_INT4 AS INT4 CHECK (VALUE >= 0);
CREATE DOMAIN
=# SELECT to_json(2::UNSIGNED_INT4);
to_json
---------
"2"
(1 row)
You could still recast it back to int4 to enforce the constraint
check, except that unsigned int and int do not have the same range of
values normally (smth that your domain breaks as it cannot take values
higher than 2^31 btw):
=# SELECT to_json(2::UNSIGNED_INT4::int4);
to_json
---------
2
(1 row)

> If we do not use domains, SELECT to_json(2::INT4) returns always a JSON int
> (even with 9.3.5).
>
> Is it related to the change made by Tom Lane?
> commit 0ca6bda8e7501947c05f30c127f6d12ff90b5a64 and the release note 9.3.5
> "Fix identification of input type category in to_json() and friends (Tom
> Lane)"?
To be picky, this commit is on 9.4 stable branch, on 9.3 it is 13c6799
:) And yes the commit you are referring to is the origin of this
modification of behavior.

> Is it volontary? If so, don't worry, I will find a workaround.

By looking at the documentation here about json functions
(http://www.postgresql.org/docs/current/static/functions-json.html)
and looking at to_json, there is the following quote:
"If the data type is not built in, and there is a cast from the type
to json, the cast function will be used to perform the conversion.
Otherwise, for any value other than a number, a Boolean, or a null
value, the text representation will be used."
So in your case as there is no cast function to json for
unsigned_int4, text representation is used. New behavior seems more
consistent with the documentation.

Regards,
--
Michael

Thank you for your response.

> except that unsigned int and int do not have the same range of values
normally

Yes, of course, it was just for the example ;).

In fact, I showed you a basic test case to simplify the description of my
problem. But my real problem is a little bit more complex (custom domain
used in an array of custom types converted by array_to_json so it is not
easy to cast...).

But, you are right, now it is consistent with the documentation. And I have
noticed the code was a lot simplified in the commit of Tom Lane and it is
always better for maintenance... Sometimes, it is better to have a simple
code than doing to much "magic" for end users.

Ok, I will find other solutions (create a new cast function or change a
little bit my model).

Kind regards,

*David Grelaud*

2014-08-01 9:01 GMT+02:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

> On Fri, Aug 1, 2014 at 12:24 AM, <dgrelaud(at)ideolys(dot)com> wrote:
> > With 9.3.5, it returns a JSON string : '2' (with quotes)
> > With 9.3.4, it returns a JSON int : 2 (without quotes)
>
> Double quotes actually, to make it valid JSON:
> =# CREATE DOMAIN UNSIGNED_INT4 AS INT4 CHECK (VALUE >= 0);
> CREATE DOMAIN
> =# SELECT to_json(2::UNSIGNED_INT4);
> to_json
> ---------
> "2"
> (1 row)
> You could still recast it back to int4 to enforce the constraint
> check, except that unsigned int and int do not have the same range of
> values normally (smth that your domain breaks as it cannot take values
> higher than 2^31 btw):
> =# SELECT to_json(2::UNSIGNED_INT4::int4);
> to_json
> ---------
> 2
> (1 row)
>
> > If we do not use domains, SELECT to_json(2::INT4) returns always a JSON
> int
> > (even with 9.3.5).
> >
> > Is it related to the change made by Tom Lane?
> > commit 0ca6bda8e7501947c05f30c127f6d12ff90b5a64 and the release note
> 9.3.5
> > "Fix identification of input type category in to_json() and friends (Tom
> > Lane)"?
> To be picky, this commit is on 9.4 stable branch, on 9.3 it is 13c6799
> :) And yes the commit you are referring to is the origin of this
> modification of behavior.
>
> > Is it volontary? If so, don't worry, I will find a workaround.
>
> By looking at the documentation here about json functions
> (http://www.postgresql.org/docs/current/static/functions-json.html)
> and looking at to_json, there is the following quote:
> "If the data type is not built in, and there is a cast from the type
> to json, the cast function will be used to perform the conversion.
> Otherwise, for any value other than a number, a Boolean, or a null
> value, the text representation will be used."
> So in your case as there is no cast function to json for
> unsigned_int4, text representation is used. New behavior seems more
> consistent with the documentation.
>
> Regards,
> --
> Michael
>

Michael Paquier wrote
> On Fri, Aug 1, 2014 at 12:24 AM, &lt;

> dgrelaud@

> &gt; wrote:
>> With 9.3.5, it returns a JSON string : '2' (with quotes)
>> With 9.3.4, it returns a JSON int : 2 (without quotes)
>
> Double quotes actually, to make it valid JSON:
> =# CREATE DOMAIN UNSIGNED_INT4 AS INT4 CHECK (VALUE >= 0);
> CREATE DOMAIN
> =# SELECT to_json(2::UNSIGNED_INT4);
> to_json
> ---------
> "2"
> (1 row)
> You could still recast it back to int4 to enforce the constraint
> check, except that unsigned int and int do not have the same range of
> values normally (smth that your domain breaks as it cannot take values
> higher than 2^31 btw):
> =# SELECT to_json(2::UNSIGNED_INT4::int4);
> to_json
> ---------
> 2
> (1 row)
>
>> If we do not use domains, SELECT to_json(2::INT4) returns always a JSON
>> int
>> (even with 9.3.5).
>>
>> Is it related to the change made by Tom Lane?
>> commit 0ca6bda8e7501947c05f30c127f6d12ff90b5a64 and the release note
>> 9.3.5
>> "Fix identification of input type category in to_json() and friends (Tom
>> Lane)"?
> To be picky, this commit is on 9.4 stable branch, on 9.3 it is 13c6799
> :) And yes the commit you are referring to is the origin of this
> modification of behavior.
>
>> Is it volontary? If so, don't worry, I will find a workaround.
>
> By looking at the documentation here about json functions
> (http://www.postgresql.org/docs/current/static/functions-json.html)
> and looking at to_json, there is the following quote:
> "If the data type is not built in, and there is a cast from the type
> to json, the cast function will be used to perform the conversion.
> Otherwise, for any value other than a number, a Boolean, or a null
> value, the text representation will be used."
> So in your case as there is no cast function to json for
> unsigned_int4, text representation is used. New behavior seems more
> consistent with the documentation.

Since the unsigned_int4 is a domain now, in head, it would appear without
the quotes. Before the patch, in 9.3.4, it also appeared without the
quotes. After the patch, in 9.3.5, it now has quotes so the patch has
already changed user-visible behavior which is the reason Tom didn't
back-patch the part that pertained to domains.

The work-around should be to simply CREATE CAST from the domain to json.

Seems testing on other types should be done and, depending on the results,
the domain part of the commit could be back-patched as well.

Also, a comment on domains should be considered though if they do
pass-through to the base type that would be expected and thus superfluous to
the informed reader. If this new behavior remains in 9.3 it should probably
be documented.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11103-to-json-does-not-convert-correctly-DOMAINs-type-since-9-3-5-int-expected-instead-of-string-tp5813459p5813509.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Michael Paquier wrote
>> On Fri, Aug 1, 2014 at 12:24 AM, <dgrelaud@> wrote:
>>> With 9.3.5, it returns a JSON string : '2' (with quotes)
>>> With 9.3.4, it returns a JSON int : 2 (without quotes)

> Since the unsigned_int4 is a domain now, in head, it would appear without
> the quotes. Before the patch, in 9.3.4, it also appeared without the
> quotes. After the patch, in 9.3.5, it now has quotes so the patch has
> already changed user-visible behavior which is the reason Tom didn't
> back-patch the part that pertained to domains.

Hm ... actually the intent of that was to *not* cause a behavior change in
pre-9.4. But it looks like things are more complicated than I thought.

A domain inherits the typcategory of its base type, which means that in
the old coding that relied on typcategory to decide what to do, domains
*were* treated the same as their base types, in some cases anyway.
Specifically it looks like the old behavior was:

* If there was a cast function to json, you got the behavior of that
cast function. The cast had to be from exactly the given type (ie,
in this case, the domain not its base type).

* If the type was JSON (or JSONB, though that's not relevant to pre-9.4),
you got the JSON-appropriate behavior. Again, it had to be exactly JSON
and not a domain over same.

* Otherwise you got classification according to typcategory, which *would*
effectively look through domains. Specifically, domains over boolean
and domains over numeric types would be converted like their base types.

So I was thinking about not changing the first two cases and failed to
realize the side-effects for the last case.

I'm inclined to think that the best thing to do is add the getBaseType
call in the older branches too. That will restore the previous behavior
for domains over booleans and numerics. It will change the behavior
for the other two cases, which would affect domains over json and hstore
in particular; but arguably that's a bug fix. The old behavior was
just plain inconsistent, because it effectively looked through domains
over some types and not domains over others.

Thoughts, objections?

regards, tom lane