pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16636: Upper case issue in JSONB type
5+ messages / 3 participants
[nested] [flat]

* BUG #16636: Upper case issue in JSONB type
@ 2020-09-25 13:36  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2020-09-25 13:36 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: osaimar19@gmail.com

The following bug has been logged on the website:

Bug reference:      16636
Logged by:          ChandraKumar Ovanan
Email address:      osaimar19@gmail.com
PostgreSQL version: 11.0
Operating system:   Windows 10
Description:        

Hi All,
Below example which I was execute query in postgres.
The problem statement here, when I use upper case function by converting
JSONB object has Null value,
Then I getting below error.
Please could fix it defect or why this behavior on postures,required
clarification 



Error message:
SQL Error [22P02]: ERROR: invalid input syntax for type json
  Detail: Token "NULL" is invalid.
  Where: JSON data, line 1: ...E": "2018-01-10", "NAME": "TEST3"}, {"DATE":
NULL...




CREATE TABLE logs(id serial, data JSONB); 
INSERT INTO logs VALUES
 (1,
'[{"name":"test1","date":"2020-11-11"},{"name":"test2","date":"2018-01-10"}]'),
 (2,
'[{"name":"test3","date":"2020-05-18"},{"name":"test4","date":"null"}]');

 SELECT * FROM logs WHERE data @> '[{"date":null}]';
 SELECT * FROM logs WHERE data @> '[{"date":"2018-01-10"}]';
 SELECT * FROM logs WHERE data @> '[{"date":"2018-01-11"}]';
 SELECT * FROM logs WHERE data @> '[{"name":"test4"}]';
 SELECT * FROM logs WHERE lower(data::text)::jsonb @>
lower('[{"date":"2018-01-10"}]')::jsonb ; --working
 SELECT * FROM logs WHERE upper(data::text)::jsonb @>
upper('[{"date":"2018-01-10"}]')::jsonb ; --Not working



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16636: Upper case issue in JSONB type
@ 2020-09-25 15:38  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: David G. Johnston @ 2020-09-25 15:38 UTC (permalink / raw)
  To: osaimar19@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

On Fri, Sep 25, 2020 at 8:13 AM PG Bug reporting form <
noreply@postgresql.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16636
> Logged by:          ChandraKumar Ovanan
> Email address:      osaimar19@gmail.com
> PostgreSQL version: 11.0
> Operating system:   Windows 10
> Description:
>
> Hi All,
> Below example which I was execute query in postgres.
> The problem statement here, when I use upper case function by converting
> JSONB object has Null value,
> Then I getting below error.
> Please could fix it defect or why this behavior on postures,required
> clarification
>
> Error message:
> SQL Error [22P02]: ERROR: invalid input syntax for type json
>   Detail: Token "NULL" is invalid.
>   Where: JSON data, line 1: ...E": "2018-01-10", "NAME": "TEST3"}, {"DATE":
> NULL...
>
> CREATE TABLE logs(id serial, data JSONB);
> INSERT INTO logs VALUES
>  (1,
>
> '[{"name":"test1","date":"2020-11-11"},{"name":"test2","date":"2018-01-10"}]'),
>  (2,
> '[{"name":"test3","date":"2020-05-18"},{"name":"test4","date":"null"}]');
>
>  SELECT * FROM logs WHERE data @> '[{"date":null}]';
>  SELECT * FROM logs WHERE upper(data::text)::jsonb @>
> upper('[{"date":"2018-01-10"}]')::jsonb ; --Not working
>
>
The error and the example queries don't seem to match...

When you upper('{"date":null}'::text) you get the literal text:
{"date":NULL}, which is not valid json; a JSON null "value" must be written
in lowercase.  You would find the same problem had you chosen a boolean
field and written true/false as documented on the json data type page [1],
Table 8.23, and the json standard [2]

David J.

1. https://www.postgresql.org/docs/13/datatype-json.html
2. https://www.json.org/json-en.html

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16636: Upper case issue in JSONB type
@ 2020-09-26 06:25  ChandraKumar Ovanan <osaimar19@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: ChandraKumar Ovanan @ 2020-09-26 06:25 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Hello David,

Thanks a lot, you cleared my question.and it seems fine to me.

You mention boolean type, also the same problem but the document is more
clearled. 🙂

*Just a suggestion: *
The JSON primitive type is null, The document is not cleared such as
lowercase or uppercase.

Please update the document

[image: pql table.PNG]

Regards
Chandrakumar





On Fri, Sep 25, 2020 at 9:08 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
> On Fri, Sep 25, 2020 at 8:13 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      16636
>> Logged by:          ChandraKumar Ovanan
>> Email address:      osaimar19@gmail.com
>> PostgreSQL version: 11.0
>> Operating system:   Windows 10
>> Description:
>>
>> Hi All,
>> Below example which I was execute query in postgres.
>> The problem statement here, when I use upper case function by converting
>> JSONB object has Null value,
>> Then I getting below error.
>> Please could fix it defect or why this behavior on postures,required
>> clarification
>>
>> Error message:
>> SQL Error [22P02]: ERROR: invalid input syntax for type json
>>   Detail: Token "NULL" is invalid.
>>   Where: JSON data, line 1: ...E": "2018-01-10", "NAME": "TEST3"},
{"DATE":
>> NULL...
>>
>> CREATE TABLE logs(id serial, data JSONB);
>> INSERT INTO logs VALUES
>>  (1,
>>
'[{"name":"test1","date":"2020-11-11"},{"name":"test2","date":"2018-01-10"}]'),
>>  (2,
>> '[{"name":"test3","date":"2020-05-18"},{"name":"test4","date":"null"}]');
>>
>>  SELECT * FROM logs WHERE data @> '[{"date":null}]';
>>  SELECT * FROM logs WHERE upper(data::text)::jsonb @>
>> upper('[{"date":"2018-01-10"}]')::jsonb ; --Not working
>>
>
> The error and the example queries don't seem to match...
>
> When you upper('{"date":null}'::text) you get the literal text:
{"date":NULL}, which is not valid json; a JSON null "value" must be written
in lowercase.  You would find the same problem had you chosen a boolean
field and written true/false as documented on the json data type page [1],
Table 8.23, and the json standard [2]
>
> David J.
>
> 1. https://www.postgresql.org/docs/13/datatype-json.html
> 2. https://www.json.org/json-en.html
>


-- 
Thanks & Regards,
Chandra Kumar O
07639078641

Attachments:

pql table.PNG (image/png, 38.7K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #16636: Upper case issue in JSONB type
@ 2020-09-30 02:57  David G. Johnston <david.g.johnston@gmail.com>
  parent: ChandraKumar Ovanan <osaimar19@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: David G. Johnston @ 2020-09-30 02:57 UTC (permalink / raw)
  To: ChandraKumar Ovanan <osaimar19@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

On Fri, Sep 25, 2020 at 11:25 PM ChandraKumar Ovanan <osaimar19@gmail.com>
wrote:

> Hello David,
>
> Thanks a lot, you cleared my question.and it seems fine to me.
>
> You mention boolean type, also the same problem but the document is more
> clearled. 🙂
>
> *Just a suggestion: *
> The JSON primitive type is null, The document is not cleared such as
> lowercase or uppercase.
>
> Please update the document
>
> [image: pql table.PNG]
>

FWIW, I've proposed a patch [1] but I'm 70-30 whether to cover this
material as our documentation doesn't intend to document what is already
covered in the RFC and the idea that the value of the null type is spelled
(null) is part of the standard.  We document the boolean part because in
PostgreSQL inputs for booleans are considerably more liberal.  That all
said it doesn't eat up much space and was easy enough to do without being
annoying (IMO) so I did it.

David J.

[1]
https://www.postgresql.org/message-id/CAHyU1EYRYCV8gELrBYm6V8E%2BToqf%3DKQe370bPt4yrE_Y1yDZ9w%40mail...

Attachments:

pql table.PNG (image/png, 38.7K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #16636: Upper case issue in JSONB type
@ 2020-10-01 06:23  ChandraKumar Ovanan <osaimar19@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: ChandraKumar Ovanan @ 2020-10-01 06:23 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Ok, thanks.

On Wed 30 Sep, 2020, 8:27 AM David G. Johnston, <david.g.johnston@gmail.com>
wrote:

> On Fri, Sep 25, 2020 at 11:25 PM ChandraKumar Ovanan <osaimar19@gmail.com>
> wrote:
>
>> Hello David,
>>
>> Thanks a lot, you cleared my question.and it seems fine to me.
>>
>> You mention boolean type, also the same problem but the document is more
>> clearled. 🙂
>>
>> *Just a suggestion: *
>> The JSON primitive type is null, The document is not cleared such as
>> lowercase or uppercase.
>>
>> Please update the document
>>
>> [image: pql table.PNG]
>>
>
> FWIW, I've proposed a patch [1] but I'm 70-30 whether to cover this
> material as our documentation doesn't intend to document what is already
> covered in the RFC and the idea that the value of the null type is spelled
> (null) is part of the standard.  We document the boolean part because in
> PostgreSQL inputs for booleans are considerably more liberal.  That all
> said it doesn't eat up much space and was easy enough to do without being
> annoying (IMO) so I did it.
>
> David J.
>
> [1]
> https://www.postgresql.org/message-id/CAHyU1EYRYCV8gELrBYm6V8E%2BToqf%3DKQe370bPt4yrE_Y1yDZ9w%40mail...
>
>

Attachments:

pql table.PNG (image/png, 38.7K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread


end of thread, other threads:[~2020-10-01 06:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-25 13:36 BUG #16636: Upper case issue in JSONB type PG Bug reporting form <noreply@postgresql.org>
2020-09-25 15:38 ` David G. Johnston <david.g.johnston@gmail.com>
2020-09-26 06:25   ` ChandraKumar Ovanan <osaimar19@gmail.com>
2020-09-30 02:57     ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-01 06:23       ` ChandraKumar Ovanan <osaimar19@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox