Re: SQL/JSON: functions

On 1/6/22 06:24, Himanshu Upadhyaya wrote:
> I have one general question on the below scenario.
> CREATE TABLE T (Id INTEGER PRIMARY KEY,Jcol CHARACTER VARYING ( 5000
> )CHECK ( Jcol IS JSON ) );
> insert into T values (1,323);
> ORACLE is giving an error(check constraint...violated ORA-06512) for
> the above insert but Postgres is allowing it, however is not related
> to this patch but just thinking if this is expected.
>
> ‘postgres[22198]=#’SELECT * FROM T WHERE Jcol IS JSON;
> id | jcol
> ----+------
> 1 | 323
> How come number 323 is the valid json?

If you look at the JSON grammar at <https://www.json.org/json-en.html>
or
<https://www.ecma-international.org/wp-content/uploads/ECMA-404_2nd_edition_december_2017.pdf>
it's clear that a bare number is valid json. Our parser implements that
grammar pretty faithfully, in fact rather more faithfully than many
implementations (e.g. we allow huge number strings). So as far as I'm
concerned, we are right and Oracle is wrong. It would hardly be the
first time such a thing has happened.

Oracle is not the definer of the JSON standard. ECMA is.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com