Re: jsonb_set() strictness considered harmful to data

[Laurenz Albe]

David G. Johnston wrote:
> Now if only the vast majority of users could have and keep this level of 
> understanding
> in mind while writing complex queries so that they remember to always add 
> protections
> to compensate for the unique design decision that SQL has taken here...

You can only say that if you don't understand NULL (you wouldn't be alone).
If I modify a JSON with an unknown value, the result is unknown.
This seems very intuitive to me.

One could argue that whoever uses SQL should understand SQL.

But I believe that it is reasonable to suppose that many people who
use JSON in the database are more savvy with JSON than with SQL
(they might not have chosen JSON otherwise), so I agree that it makes
sense to change this particular behavior.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

date function bug

[Abraham, Danny]

Hi,

The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?


ctrlmdb=> select to_date('2018100X','MMDD');
  to_date

 2018-10-01
(1 row)

Re: date function bug

[Ron]

On 10/23/19 9:22 AM, Abraham, Danny wrote:

Hi,

The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?


ctrlmdb=> select to_date('2018100X','MMDD');
   to_date

  2018-10-01
(1 row)


psql (9.6.15)
Type "help" for help.

postgres=# select to_date('2018100X','MMDD');
  to_date

 2018-10-01
(1 row)

postgres=#
postgres=# select to_date('2018150X','MMDD');
  to_date

 2019-03-03
(1 row)

postgres=# select to_date('20181501','MMDD');
  to_date

 2019-03-03
(1 row)



--
Angular momentum makes the world go 'round.

Re: date function bug

[Ravi Krishna]

> ctrlmdb=> select to_date('2018100X','MMDD');
> to_date

>2018-10-01
>(1 row)

I am able to reproduce this in 11.5 It seems PG can take a single digit for Day 
too.
select to_date('2018109','MMDD') produces 2018-10-09.

Re: date function bug

[Ravi Krishna]

> postgres=# select to_date('2018150X','MMDD');
  > to_date

> 2019-03-03


> postgres=# select to_date('20181501','MMDD');
>  to_date
> 
  > 2019-03-03
is this a cut-n-paste mistake?

Re: date function bug

[Ron]

On 10/23/19 9:32 AM, Ravi Krishna wrote:


> postgres=# select to_date('2018150X','MMDD');
  > to_date

> 2019-03-03


> postgres=# select to_date('20181501','MMDD');
>  to_date
> 
  > 2019-03-03

is this a cut-n-paste mistake?


Nope.

Here's the screen print: http://i.imgur.com/f0UXfZh.png

--
Angular momentum makes the world go 'round.

Re: date function bug

[Adrian Klaver]

On 10/23/19 7:22 AM, Abraham, Danny wrote:

Hi,

The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?


ctrlmdb=> select to_date('2018100X','MMDD');
   to_date

  2018-10-01
(1 row)




At:

https://www.postgresql.org/docs/11/functions-formatting.html

I would read the section starting :

"Usage notes for date/time formatting: ..."

several times. There a lot of 'if and or buts' in there.

--
Adrian Klaver
adrian.kla...@aklaver.com

RE: Re: date function bug

[Abraham, Danny]

20181501 is illegal. Working OK.
ctrlmdb=> select to_date('20181501','MMDD')
ctrlmdb-> \g
ERROR:  date/time field value out of range: "20181501"

From: Ravi Krishna 
Sent: Wednesday, October 23, 2019 5:28 PM
To: Abraham, Danny ; pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: date function bug

> ctrlmdb=> select to_date('2018100X','MMDD');
> to_date

>2018-10-01
>(1 row)

I am able to reproduce this in 11.5 It seems PG can take a single digit for Day 
too.

select to_date('2018109','MMDD') produces 2018-10-09.

RE: date function bug

[Kevin Brannen]

From: Ravi Krishna 

> postgres=# select to_date('2018150X','MMDD');
  > to_date

> 2019-03-03


> postgres=# select to_date('20181501','MMDD');
>  to_date
> 
  > 2019-03-03

is this a cut-n-paste mistake?


Surprisingly (to me), no….
db=# select to_date('20181501','MMDD');
  to_date

2019-03-03
(1 row)

Time: 0.497 ms
nms=# select version();
 version
--
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 
(Red Hat 4.4.7-16), 64-bit
(1 row)

Time: 0.247 ms
db=#



This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: date function bug

[Tom Lane]

"Abraham, Danny"  writes:
> The function "to_date" does not fail illegal values.
> Is this a known bug?

No, it's a feature, because the point of to_date() is to parse strings
that would be rejected or misinterpreted by the regular date input
function.  If you want tighter error checking and your input is supposed
to follow a common format, just cast the string to date.

regression=# select '2018100X'::date;
ERROR:  invalid input syntax for type date: "2018100X"
LINE 1: select '2018100X'::date;
   ^
regression=# select '20181501'::date;
ERROR:  date/time field value out of range: "20181501"
LINE 1: select '20181501'::date;
   ^
HINT:  Perhaps you need a different "datestyle" setting.
regression=# select '20181001'::date;
date

 2018-10-01
(1 row)

regards, tom lane

Re: date function bug

[Ravi Krishna]

> 
> 
> 
> Surprisingly (to me), no….
> 
> db=# select to_date('20181501','MMDD');
>  to_date
> 
> 2019-03-03

The above fails in 11.5 SQL Error [22008]: ERROR: date/time field value out 
of range: "20181501"

Re: Calling jsonb_array_elements 4 times in the same query

[Alexander Farber]

Thank you -

On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver 
wrote:

> As Thomas pointed there is a difference between -> and ->>:
>
> test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
> -> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
>   pg_typeof | ?column?
> ---+--
>   jsonb | 1
> (1 row)
>
> test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
> ->> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
>   pg_typeof | ?column?
> ---+--
>   text  | 1
>

I have ended up with the stored function using ->> and casting:

 CREATE OR REPLACE FUNCTION words_get_move(
in_mid integer
) RETURNS TABLE (
out_bidinteger,
out_midbigint,
out_hand   text,
out_colinteger,
out_rowinteger,
out_letter text,
out_value  integer
) AS
$func$
SELECT
g.bid,
m.mid,
m.hand,
(j.tile->>'col')::int   AS col,
(j.tile->>'row')::int   AS row,
 j.tile->>'letter'  AS letter,
(j.tile->>'value')::int AS value
FROM words_moves m
CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS j(tile)
LEFT JOIN words_games g USING(gid)
LEFT JOIN LATERAL (SELECT gid, played FROM words_moves WHERE mid =
in_mid) AS m2 ON TRUE
WHERE m.action = 'play'
AND m.gid = m2.gid
AND m.played <= m2.played
ORDER BY m.played ASC;
$func$ LANGUAGE sql;

It gives me the desired output:

 out_bid | out_mid | out_hand | out_col | out_row | out_letter | out_value
-+-+--+-+-++---
   1 |  385934 | РТМРЕКО  |   7 |   7 | О  | 1
   1 |  385934 | РТМРЕКО  |   7 |   3 | М  | 2
   1 |  385934 | РТМРЕКО  |   7 |   4 | Е  | 1
   1 |  385934 | РТМРЕКО  |   7 |   5 | Т  | 2
   1 |  385934 | РТМРЕКО  |   7 |   6 | Р  | 2
   1 |  386610 | МИЛСЯРО  |   5 |   6 | Л  | 2
   1 |  386610 | МИЛСЯРО  |   6 |   6 | Я  | 3
   1 |  386610 | МИЛСЯРО  |   4 |   6 | О  | 1
   1 |  386610 | МИЛСЯРО  |   3 |   6 | М  | 2
   1 |  391416 | РКП*АДЕ  |   4 |   9 | Л  | 0
   1 |  391416 | РКП*АДЕ  |   4 |  10 | К  | 2
   1 |  391416 | РКП*АДЕ  |   4 |   5 | Р  | 2
   1 |  391416 | РКП*АДЕ  |   4 |   7 | Д  | 2
   1 |  391416 | РКП*АДЕ  |   4 |   4 | П  | 2
   1 |  391416 | РКП*АДЕ  |   4 |   8 | Е  | 1
   1 |  391416 | РКП*АДЕ  |   4 |  11 | А  | 1
(16 rows)

Regards
Alex

Is this a bug ?

[Ravi Krishna]

We noticed this bug in Redshift. It then occurred to me that this may 
very well be a PG bug since Redshift is based on PG.

Reproduced it in Version 11.5

create table bugtest (fld1 char(1)) ;

insert into bugtest values('a');
insert into bugtest values('b');
insert into bugtest values('c');
insert into bugtest values('d');
insert into bugtest values('e');

select count(*) from bugtest where fld1 in ('a','b','c',
'd','e');  -> produces 5 as output.
So far so good.

Now check this:

select count(*) from bugtest where fld1 in ('a','b','c'
'd','e');
  
Note the missing comma after 'c'. 

PG takes it a syntactically right SQL and gives 3 as output. 

In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax near 'd'.

Can't believe this bug was never found before.  We ended up wasting lot of time 
to figure this out.

Re: Is this a bug ?

[Ron]

On 10/23/19 10:42 AM, Ravi Krishna wrote:

We noticed this bug in Redshift. It then occurred to me that this may
very well be a PG bug since Redshift is based on PG.

Reproduced it in Version 11.5

create table bugtest (fld1 char(1)) ;

insert into bugtest values('a');
insert into bugtest values('b');
insert into bugtest values('c');
insert into bugtest values('d');
insert into bugtest values('e');

select count(*) from bugtest where fld1 in ('a','b','c',
 'd','e');  -> produces 5 as output.
So far so good.

Now check this:

select count(*) from bugtest where fld1 in ('a','b','c'
 'd','e');
   
Note the missing comma after 'c'.


PG takes it a syntactically right SQL and gives 3 as output.

In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax near 'd'.

Can't believe this bug was never found before.  We ended up wasting lot of time 
to figure this out.


Confirmed in 9.6.

postgres@haggis:~$ psql
psql (9.6.15)
Type "help" for help.

postgres=# create table bugtest (fld1 char(1)) ;
CREATE TABLE
postgres=#
postgres=# insert into bugtest values('a');
INSERT 0 1
postgres=# insert into bugtest values('b');
INSERT 0 1
postgres=# insert into bugtest values('c');
INSERT 0 1
postgres=# insert into bugtest values('d');
INSERT 0 1
postgres=# insert into bugtest values('e');
INSERT 0 1
postgres=# select count(*) from bugtest where fld1 in ('a','b','c',
postgres(# 'd','e');
 count
---
 5
(1 row)

postgres=# select count(*) from bugtest where fld1 in ('a','b','c'
postgres(# 'd','e');
 count
---
 3
(1 row)



--
Angular momentum makes the world go 'round.

Re: Lookup Primary Key of Foreign Server's Table

[Chris Morris]

The foreign table has a primary key. Ruby on Rails uses a system query to
lookup what the primary key on the table is, but it's querying the local
database, and not finding anything. In a schema dump of the local database,
I don't see a primary key defined, so I'm presuming I need to issue an ADD
CONSTRAINT command against the foreign table to reflect what is actually
true in the foreign table? Is that correct?

Re: Is this a bug ?

[Gianni Ceccarelli]

Weird, but apparently not a bug. From
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

> Two string constants that are only separated by whitespace with at
> least one newline are concatenated and effectively treated as if the
> string had been written as one constant. For example:
>
>   SELECT 'foo'
>   'bar';
>
> is equivalent to:
>
>   SELECT 'foobar';
>
> but:
>
>   SELECT 'foo'  'bar';
>
> is not valid syntax. (This slightly bizarre behavior is specified by
> SQL; PostgreSQL is following the standard.)

-- 
Dakkar - 
GPG public key fingerprint = A071 E618 DD2C 5901 9574
 6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

Re: Is this a bug ?

[Geoff Winkless]

On Wed, 23 Oct 2019 at 16:42, Ravi Krishna  wrote:

> select count(*) from bugtest where fld1 in ('a','b','c'
> 'd','e');
>
> Note the missing comma after 'c'.
>
> PG takes it a syntactically right SQL and gives 3 as output.
>
> In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax near 'd'.
>
> Can't believe this bug was never found before.  We ended up wasting lot of 
> time to figure this out.

Simplify:

select 'a'
db-# 'b';
 ?column?
--
 ab
(1 row)

This is not a bug.

https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html

Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written as one constant.

Geoff

Re: Is this a bug ?

[Ron]

On 10/23/19 10:51 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 16:42, Ravi Krishna  wrote:


select count(*) from bugtest where fld1 in ('a','b','c'
 'd','e');

Note the missing comma after 'c'.

PG takes it a syntactically right SQL and gives 3 as output.

In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax near 'd'.

Can't believe this bug was never found before.  We ended up wasting lot of time 
to figure this out.

Simplify:

select 'a'
db-# 'b';
  ?column?
--
  ab
(1 row)

This is not a bug.

https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html

Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written as one constant.


Then -- since the 'e' is separated from 'd' by a comma, the result should be 
"4", not "3".


No doubt: it's a bug, no matter what the Pg devs say.

--
Angular momentum makes the world go 'round.

Re: Is this a bug ?

[Ravi Krishna]

> 
> Simplify:
> 
> select 'a'
> db-# 'b';
> ?column?
> --
> ab
> (1 row)
> 
> This is not a bug.
> 
> https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html
> 
> Two string constants that are only separated by whitespace with at
> least one newline are concatenated and effectively treated as if the
> string had been written as one constant.
> 
> Geoff

Shouldn't the output be 4 in that case since it is looking 
for('a','b','cd','e') and it should find all but cd.
  

Re: Is this a bug ?

[Fabio Ugo Venchiarutti]

On 23/10/2019 16:55, Ron wrote:

On 10/23/19 10:51 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 16:42, Ravi Krishna  wrote:


select count(*) from bugtest where fld1 in ('a','b','c'
 'd','e');

Note the missing comma after 'c'.

PG takes it a syntactically right SQL and gives 3 as output.

In SQLServer it errors out SQL Error [102] [S0001]: Incorrect syntax 
near 'd'.


Can't believe this bug was never found before.  We ended up wasting 
lot of time to figure this out.

Simplify:

select 'a'
db-# 'b';
  ?column?
--
  ab
(1 row)

This is not a bug.

https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html

Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written as one constant.


Then -- since the 'e' is separated from 'd' by a comma, the result 
should be "4", not "3".


No doubt: it's a bug, no matter what the Pg devs say.




Does any record in your test table contain the concatenated 'cd' in 
"fld1"? If not, it's working as per specification & documentation.



I can agree that this sounds like one of those idiosyncratic parts of 
the ANSI specification that exist only for compatibility with some 
ancient practice, but this behaviour is documented.



BTW, can parser behavior be affected by settings this early?


A GUC setting disabling this madness would make sense IMHO...




--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice: 
This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group.


If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses.


References to the 
"Ocado Group" are to Ocado Group plc (registered in England and Wales with 
number 7098618) and its subsidiary undertakings (as that expression is 
defined in the Companies Act 2006) from time to time. The registered office 
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, 
Hatfield, Hertfordshire, AL10 9UL.

Re: Is this a bug ?

[Tom Lane]

Ron  writes:
> On 10/23/19 10:51 AM, Geoff Winkless wrote:
>> Two string constants that are only separated by whitespace with at
>> least one newline are concatenated and effectively treated as if the
>> string had been written as one constant.

> Then -- since the 'e' is separated from 'd' by a comma, the result should be 
> "4", not "3".

No, because your command is equivalent to

select count(*) from bugtest where fld1 in ('a','b','cd','e');

'cd' does not match any of the table rows, so 3 is the correct answer.

> No doubt: it's a bug, no matter what the Pg devs say.

Complain to the SQL standards committee, not us.

regards, tom lane

Re: Is this a bug ?

[John W Higgins]

On Wed, Oct 23, 2019 at 8:56 AM Ravi Krishna  wrote:

> >
> > Simplify:
> >
> > select 'a'
> > db-# 'b';
> > ?column?
> > --
> > ab
> > (1 row)
> >
> > This is not a bug.
> >
> > https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html
> >
> > Two string constants that are only separated by whitespace with at
> > least one newline are concatenated and effectively treated as if the
> > string had been written as one constant.
> >
> > Geoff
>
> Shouldn't the output be 4 in that case since it is looking
> for('a','b','cd','e') and it should find all but cd.
>
>
And that means there are 3 options on the table = a, b, and e - where is
the 4th item?

cd is not in the table so it cannot be found.

John

Re: Is this a bug ?

[Geoff Winkless]

On Wed, 23 Oct 2019 at 16:55, Ron  wrote:
> Then -- since the 'e' is separated from 'd' by a comma, the result should be
> "4", not "3".
>
> No doubt: it's a bug, no matter what the Pg devs say.

I'm confused why you consider that to be true. The count is checking
for columns containing the strings 'a', 'b', 'cd' or 'e'. There is not
one with 'cd', so the correct result is 3.

Geoff

Re: jsonb_set() strictness considered harmful to data

[David G. Johnston]

On Wed, Oct 23, 2019 at 4:42 AM Laurenz Albe 
wrote:

> David G. Johnston wrote:
> > Now if only the vast majority of users could have and keep this level of
> understanding
> > in mind while writing complex queries so that they remember to always
> add protections
> > to compensate for the unique design decision that SQL has taken here...
>
> You can only say that if you don't understand NULL (you wouldn't be alone).
> If I modify a JSON with an unknown value, the result is unknown.
> This seems very intuitive to me.
>
> One could argue that whoever uses SQL should understand SQL.
>
> But I believe that it is reasonable to suppose that many people who
> use JSON in the database are more savvy with JSON than with SQL
> (they might not have chosen JSON otherwise), so I agree that it makes
> sense to change this particular behavior.
>

I can and do understand SQL quite well and still likely would end up being
tripped up by this (though not surprised when it happened) because I can't
and don't want to think about what will happen if NULL appears in every
expression I write when a typical SQL query can contain tens of them.  I'd
much rather assume that NULL inputs aren't going to happen and have the
system tell me when that assumption is wrong.  Having to change my
expressions to: COALESCE(original_input, function(original_input,
something_that_could_be_null_in_future_but_cannot_right_now)) just adds
undesirable mental and typing overhead.

David J.

Re: Is this a bug ?

[Ron]

On 10/23/19 11:03 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 16:55, Ron  wrote:

Then -- since the 'e' is separated from 'd' by a comma, the result should be
"4", not "3".

No doubt: it's a bug, no matter what the Pg devs say.

I'm confused why you consider that to be true. The count is checking
for columns containing the strings 'a', 'b', 'cd' or 'e'. There is not
one with 'cd', so the correct result is 3.


That's my mistake.

--
Angular momentum makes the world go 'round.

Re: Is this a bug ?

[Ron]

On 10/23/19 11:00 AM, Tom Lane wrote:

Ron  writes:

On 10/23/19 10:51 AM, Geoff Winkless wrote:

Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written as one constant.

Then -- since the 'e' is separated from 'd' by a comma, the result should be
"4", not "3".

No, because your command is equivalent to

 select count(*) from bugtest where fld1 in ('a','b','cd','e');

'cd' does not match any of the table rows, so 3 is the correct answer.


Yes, that's my mistake.


No doubt: it's a bug, no matter what the Pg devs say.

Complain to the SQL standards committee, not us.


As much as I hate to say it, MSFT was right to ignore this bug in the standard.


--
Angular momentum makes the world go 'round.

Re: Lookup Primary Key of Foreign Server's Table

[David G. Johnston]

On Wed, Oct 23, 2019 at 8:47 AM Chris Morris 
wrote:

> The foreign table has a primary key. Ruby on Rails uses a system query to
> lookup what the primary key on the table is, but it's querying the local
> database, and not finding anything. In a schema dump of the local database,
> I don't see a primary key defined, so I'm presuming I need to issue an ADD
> CONSTRAINT command against the foreign table to reflect what is actually
> true in the foreign table? Is that correct?
>

The documentation says doing what you suggest won't work...

David J.

Re: EXPLAIN BUFFERS and I/O timing accounting questions

[Maciek Sakrejda]

Also, I noticed that in this plan, the root (again, an Aggregate) has 0
Temp Read Blocks, but two of its children (two of the ModifyTable nodes)
have non-zero Temp Read Blocks. Again, this contradicts the documentation,
as these costs are stated to be cumulative. Any ideas?

Thanks,
Maciek

Re: Is this a bug ?

[Geoff Winkless]

On Wed, 23 Oct 2019 at 17:09, Ron  wrote:

> As much as I hate to say it, MSFT was right to ignore this bug in the 
> standard.

Standards are standards for a reason. It is almost never correct to
deliberately ignore them. If you don't like them, then revise the
standard.

Historically Microsoft ignored standards either because they
misunderstood them or because they wanted to lock in their customers,
not for any reasons of altruism.

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
   'which I've joined together '
   'across multiple lines';

although the advantage of it vs using a concat operator is slim.

Geoff

Re: Is this a bug ?

[Ron]

On 10/23/19 11:20 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 17:09, Ron  wrote:


As much as I hate to say it, MSFT was right to ignore this bug in the standard.

Standards are standards for a reason. It is almost never correct to
deliberately ignore them. If you don't like them, then revise the
standard.

Historically Microsoft ignored standards either because they
misunderstood them or because they wanted to lock in their customers,
not for any reasons of altruism.

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
'which I've joined together '
'across multiple lines';

although the advantage of it vs using a concat operator is slim.


There is no advantage to using it vs using a concat operator, and all 
disadvantage.


--
Angular momentum makes the world go 'round.

Re: Is this a bug ?

[Geoff Winkless]

On Wed, 23 Oct 2019 at 17:20, Geoff Winkless  wrote:
> For what it's worth, I can see a value to having
>
> SELECT 'this is quite a long string'
>'which I've joined together '
>'across multiple lines';
>
> although the advantage of it vs using a concat operator is slim.

As an aside, Postgres isn't the only DB to follow the standard here.

mysql> select 'hello'
-> ' there';
+-+
| hello   |
+-+
| hello there |
+-+

Geoff

Re: Is this a bug ?

[Ron]

On 10/23/19 11:27 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 17:20, Geoff Winkless  wrote:

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
'which I've joined together '
'across multiple lines';

although the advantage of it vs using a concat operator is slim.

As an aside, Postgres isn't the only DB to follow the standard here.

mysql> select 'hello'
 -> ' there';
+-+
| hello   |
+-+
| hello there |
+-+


This is the kind of weird stuff that we mocked mysql for.

This too would have been roundly mocked if discovered in mysql:

postgres=# select to_date('2018150X','MMDD');
  to_date

 2019-03-03
(1 row)


--
Angular momentum makes the world go 'round.

Re: date function bug

[Adrian Klaver]

On 10/23/19 7:55 AM, Ravi Krishna wrote:




Surprisingly (to me), no….

db=# select to_date('20181501','MMDD');
  to_date

2019-03-03


The above fails in 11.5 SQL Error [22008]: ERROR: date/time field value out of range: 
"20181501"



Behavior changed in v10:

https://www.postgresql.org/docs/10/release-10.html
"

Make to_timestamp() and to_date() reject out-of-range input fields 
(Artur Zakirov)


For example, previously to_date('2009-06-40','-MM-DD') was accepted 
and returned 2009-07-10. It will now generate an error.

"

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a bug ?

[Adrian Klaver]

On 10/23/19 9:30 AM, Ron wrote:

On 10/23/19 11:27 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 17:20, Geoff Winkless  wrote:

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
    'which I've joined together '
    'across multiple lines';

although the advantage of it vs using a concat operator is slim.

As an aside, Postgres isn't the only DB to follow the standard here.

mysql> select 'hello'
 -> ' there';
+-+
| hello   |
+-+
| hello there |
+-+


This is the kind of weird stuff that we mocked mysql for.

This too would have been roundly mocked if discovered in mysql:

postgres=# select to_date('2018150X','MMDD');
   to_date

  2019-03-03
(1 row)




As of v10 it does not work in Postgres either:

test_(postgres)# select version(); 




  version 




 



 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
7.4.1 20190424 [gcc-7-branch revision 270538], 64-bit 



(1 row)

test_(postgres)# select to_date('2018150X','MMDD');
ERROR:  date/time field value out of range: "2018150X"



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a bug ?

[Fabio Ugo Venchiarutti]

On 23/10/2019 17:30, Ron wrote:

On 10/23/19 11:27 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 17:20, Geoff Winkless  wrote:

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
    'which I've joined together '
    'across multiple lines';

although the advantage of it vs using a concat operator is slim.

As an aside, Postgres isn't the only DB to follow the standard here.

mysql> select 'hello'
 -> ' there';
+-+
| hello   |
+-+
| hello there |
+-+


This is the kind of weird stuff that we mocked mysql for.

This too would have been roundly mocked if discovered in mysql:

postgres=# select to_date('2018150X','MMDD');
   to_date

  2019-03-03
(1 row)





The main difference being that we mocked MySQL because it BROKE the 
standard requirements in bespoke, arguably lazy/stupid/dangerous ways 
(and often undocumented as a result).



You ran into a gotcha of the SQL specification; in your shoes I'd 
instead appreciate the fact that the PG maintainers went out of their 
way and documented this spec weirdness as such in their own project when 
they could have just went "look into the specification for oddities".



MySQL silently truncated your overflowing string for years without as 
much as a warning, by default. In my book that broke specification, rule 
of least surprise, data safety and a lot more.




--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice: 
This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group.


If you are not the intended recipient, please notify us 
immediately and delete all copies of this message. Please note that it is 
your responsibility to scan this message for viruses.


References to the 
"Ocado Group" are to Ocado Group plc (registered in England and Wales with 
number 7098618) and its subsidiary undertakings (as that expression is 
defined in the Companies Act 2006) from time to time. The registered office 
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, 
Hatfield, Hertfordshire, AL10 9UL.

Re: jsonb_set() strictness considered harmful to data

[Peter J. Holzer]

On 2019-10-22 18:06:39 -0700, David G. Johnston wrote:
> On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer  wrote:
> On 2019-10-20 13:20:23 -0700, Steven Pousty wrote:
> > I would think though that raising an exception is better than a
> > default behavior which deletes data.
> > As an app dev I am quite used to all sorts of "APIs" throwing
> > exceptions and have learned to deal with them.
> >
> > This is my way of saying that raising an exception is an
> > improvement over the current situation. May not be the "best"
> > solution but definitely an improvement.
> 
> I somewhat disagree. SQL isn't in general a language which uses
> exceptions a lot. It does have the value NULL to mean "unknown", and
> generally unknown combined with something else results in an unknown
> value again:
> 
> [...] 
> 
> 
> Throwing an exception for a pure function seems "un-SQLy" to me. In
> particular, jsonb_set does something similar for json values as replace
> does for strings, so it should behave similarly.
> 
> 
> Now if only the vast majority of users could have and keep this level of
> understanding in mind while writing complex queries so that they remember to
> always add protections to compensate for the unique design decision that SQL
> has taken here...

I grant that SQL NULL takes a bit to get used to. However, it is a core
part of the SQL language and everyone who uses SQL must understand it (I
don't remember when I first stumbled across "select * from t where c =
NULL" returning 0 rows, but it was probably within the first few days of
using a database). And personally I find it much easier to deal with
concept which are applied consistently across the whole language than
those which sometimes apply and sometimes don't seemingly at random,
just because a developer thought it would be convenient for the specific
use-case they had in mind.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Is this a bug ?

[Ron]

On 10/23/19 11:20 AM, Geoff Winkless wrote:

On Wed, 23 Oct 2019 at 17:09, Ron  wrote:


As much as I hate to say it, MSFT was right to ignore this bug in the standard.

Standards are standards for a reason. It is almost never correct to
deliberately ignore them. If you don't like them, then revise the
standard.

Historically Microsoft ignored standards either because they
misunderstood them or because they wanted to lock in their customers,
not for any reasons of altruism.

For what it's worth, I can see a value to having

SELECT 'this is quite a long string'
'which I've joined together '
'across multiple lines';

although the advantage of it vs using a concat operator is slim.


It just occurred to me that this is probably something that COBOL does (it's 
been 30 years, so I might be mistaken) and so that's how it slipped into the 
SQL standard.


--
Angular momentum makes the world go 'round.

Re: jsonb_set() strictness considered harmful to data

[Stuart McGraw]

On 10/23/19 5:42 AM, Laurenz Albe wrote:

David G. Johnston wrote:

Now if only the vast majority of users could have and keep this level of 
understanding
in mind while writing complex queries so that they remember to always add 
protections
to compensate for the unique design decision that SQL has taken here...


You can only say that if you don't understand NULL (you wouldn't be alone).
If I modify a JSON with an unknown value, the result is unknown.
This seems very intuitive to me.


Would you expect modifying an array value with an unknown would result
in the entire array being unknown?


One could argue that whoever uses SQL should understand SQL.

But I believe that it is reasonable to suppose that many people who
use JSON in the database are more savvy with JSON than with SQL
(they might not have chosen JSON otherwise), so I agree that it makes
sense to change this particular behavior.

Yours,
Laurenz Albe


That (generally) SQL NULL results in NULL for any operation has been
brought up multiple times in this thread, including above, as a rationale
for the current jsonb behavior.  I don't think it is a valid argument.

When examples are given, they typically are with scalar values where
such behavior makes sense: the resulting scalar value has to be NULL
or non-NULL, it can't be both.

It is less sensible with compound values where the rule can apply to
individual scalar components.  And indeed that is what Postgresql does
for another compound type:

  # select array_replace(array[1,2,3],2,NULL);
   array_replace
  ---
   {1,NULL,3}

The returned value is not NULL.  Why the inconsistency between the array
type and json type?  Are there any cases other than json where the entire
compound value is set to NULL as a result of one of its components being
NULL?

Re: Is this a bug ?

[Peter J. Holzer]

On 2019-10-23 17:20:07 +0100, Geoff Winkless wrote:
> For what it's worth, I can see a value to having
> 
> SELECT 'this is quite a long string'
>'which I've joined together '
>'across multiple lines';
> 
> although the advantage of it vs using a concat operator is slim.

True. However, concatenation of string literals by juxtaposition isn't
specific to SQL. Two other well known languages where this works (even
without a newline) are C and Python.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Is this a bug ?

[Gaetano Mendola]

On Wed, Oct 23, 2019 at 5:55 PM Ron  wrote:
> No doubt: it's a bug, no matter what the Pg devs say.

select 'a'
'b'  = 'ab'

this is what SQL mandates

You should submit a complain to SQL guys

-- 
cpp-today.blogspot.com

Re: Is this a bug ?

[Gaetano Mendola]

On Wed, Oct 23, 2019 at 9:03 PM Peter J. Holzer  wrote:
>
> On 2019-10-23 17:20:07 +0100, Geoff Winkless wrote:
> > For what it's worth, I can see a value to having
> >
> > SELECT 'this is quite a long string'
> >'which I've joined together '
> >'across multiple lines';
> >
> > although the advantage of it vs using a concat operator is slim.
>
> True. However, concatenation of string literals by juxtaposition isn't
> specific to SQL. Two other well known languages where this works (even
> without a newline) are C and Python.

And C++ as well

Re: Automatically parsing in-line composite types

[Mitar]

Hi!

Bump my previous question. I find it surprising that it seems this
information is not possible to be reconstructed by the client, when
the server has to have it internally. Is this a new feature request or
am I missing something?

> I am trying to understand how could I automatically parse an in-line
> composite type. By in-line composite type I mean a type corresponding
> to ROW. For example, in the following query:
>
> SELECT _id, body, (SELECT array_agg(ROW(comments._id, comments.body))
> FROM comments WHERE comments.post_id=posts._id) AS comments FROM posts
>
> It looks like I can figure out that "comments" is an array of records.
> But then there is no way really to understand how to parse those
> records? So what are types of fields in the record?
>
> I start the parsing process by looking at types returned in
> RowDescription message and then reading descriptions in pg_type table.
>
> Is there some other way to get full typing information of the result I
> am assuming is available to PostreSQL internally?


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Is this a bug ?

[Ravi Krishna]

So reading responses from all, here is a SQL test I did on few RDBMS:


select 'John'
   'Doe' as rrr
from information_schema.tables
limit 1;

PG output

rrr
---
JohnDoe

Oracle and mysql gave same output as PG with no error

SQLServer: Syntax error near 'as'.
DB2 gave same error as SQLServer.

Composite type storage overhead

[Laiszner Tamás]

Hey there,

I am currently exploring the options to utilize 128-bit numeric primary keys. 
One of the options I am looking at is to store them as composites of two 64-bit 
integers.

The documentation page on composite types does not tell too much about the 
internal storage, so I've made my own experiment:

CREATE TYPE entity_id AS
(​
high bigint,​
low bigint​
);

CREATE TABLE composite_test
(​
entity_id entity_id NOT NULL,​
CONSTRAINT composite_test_pkey PRIMARY KEY (entity_id)​
)

INSERT INTO composite_test (entity_id) VALUES (ROW(0, 0));

Now, as I am really interested in keeping the indexes compact, tried 
pageinspect to find out what's going on internally:

SELECT * FROM  bt_page_items(get_raw_page('composite_test_pkey', 1));

It seems wrapping the values into a composite type has a pretty significant 
storage overhead, as the index entry has a total size of 48 bytes, end the data 
look like this:

4b ff ff ff ff fa 40 00 00 ff ff ff ff 00 00 02 00 00 00 18 00 00 00 00 00 00 
00 00 00 00 00 00 00 00 00 00 00 00 00 00

For comparison, when simply using a composite primary key of two columns, each 
index entry has a length of only 24 bytes - a 100% overhead from wrapping the 
values in a composite type.

Now, I understand there might be valid reasons to store a structure header 
alongside the plain data - e. g. to store version information so when the type 
is altered there is no need to rebuild the whole table.

However, I also think this should be highlighted in the documentation. (If it 
already is I apologise.)

Also, I would like ask if there is a way to instruct the storage engine to omit 
the housekeeping information and simply store the plain data, even if it comes 
with drawbacks.

I would highly appreciate any comments or additional information on this topic.

Best regards,
Tamas

Re: Composite type storage overhead

[Rob Sargent]

> On Oct 23, 2019, at 1:32 PM, Laiszner Tamás  wrote:
> 
> Hey there,
> 
> I am currently exploring the options to utilize 128-bit numeric primary keys. 
> One of the options I am looking at is to store them as composites of two 
> 64-bit integers.
> 
> The documentation page on composite types does not tell too much about the 
> internal storage, so I've made my own experiment:
> 
> CREATE TYPE entity_id AS
> (​
> high bigint,​
> low bigint​
> );
> 
> CREATE TABLE composite_test
> (​
> entity_id entity_id NOT NULL,​
> CONSTRAINT composite_test_pkey PRIMARY KEY (entity_id)​
> )
> 
> INSERT INTO composite_test (entity_id) VALUES (ROW(0, 0));
> 
> Now, as I am really interested in keeping the indexes compact, tried 
> pageinspect to find out what's going on internally:
> 
> SELECT * FROM  bt_page_items(get_raw_page('composite_test_pkey', 1));
> 
> It seems wrapping the values into a composite type has a pretty significant 
> storage overhead, as the index entry has a total size of 48 bytes, end the 
> data look like this:
> 
> 4b ff ff ff ff fa 40 00 00 ff ff ff ff 00 00 02 00 00 00 18 00 00 00 00 00 00 
> 00 00 00 00 00 00 00 00 00 00 00 00 00 00
> 
> For comparison, when simply using a composite primary key of two columns, 
> each index entry has a length of only 24 bytes - a 100% overhead from 
> wrapping the values in a composite type.
> 
> Now, I understand there might be valid reasons to store a structure header 
> alongside the plain data - e. g. to store version information so when the 
> type is altered there is no need to rebuild the whole table.
> 
> However, I also think this should be highlighted in the documentation. (If it 
> already is I apologise.)
> 
> Also, I would like ask if there is a way to instruct the storage engine to 
> omit the housekeeping information and simply store the plain data, even if it 
> comes with drawbacks.
> 
> I would highly appreciate any comments or additional information on this 
> topic.
> 
> Best regards,
> Tamas
Why not use UUID type?

Re: Composite type storage overhead

[Rob Sargent]

On 10/23/19 3:24 PM, Laiszner Tamás wrote:
That's an absolutely reasonable suggestion. I am still in the 
exploration phase so while this solution is not completely ruled out, 
I have some concerns about it:


1.
Although it does not enforce, but the UUID type kind of suggests a
specific interpretation of the data. Of course the documentation
says you are free to use any algorithm to generate the values, but
there are quite a few standard UUID types and we are not planning
to use any of them.
2.
The serialization format is different than needed by the
application and, while once again this is not a hard technical
barrier, that might cause slight additional complexity and confusion.
3.
The value is logically defined as a 128-bit integer, that is in
itself a compound value split into a few "bit groups". Extracting
these parts can be done by simple (and supposedly efficient)
bitwise operators when stored as integer, but becomes much more
cumbersome with UUID, I guess.


*Feladó:* Rob Sargent 
*Elküldve:* 2019. október 23., szerda 22:58
*Címzett:* Laiszner Tamás 
*Másolatot kap:* pgsql-gene...@postgresql.org 


*Tárgy:* Re: Composite type storage overhead


On Oct 23, 2019, at 1:32 PM, Laiszner Tamás > wrote:


Hey there,

I am currently exploring the options to utilize 128-bit numeric 
primary keys. One of the options I am looking at is to store them as 
composites of two 64-bit integers.


I would highly appreciate any comments or additional information on 
this topic.


Best regards,
Tamas

Why not use UUID type?



Putting logic and meaning into primary keys?  To what end, I wonder.

Re: Composite type storage overhead

[Laiszner Tamás]

That's an absolutely reasonable suggestion. I am still in the exploration phase 
so while this solution is not completely ruled out, I have some concerns about 
it:

  1.
Although it does not enforce, but the UUID type kind of suggests a specific 
interpretation of the data. Of course the documentation says you are free to 
use any algorithm to generate the values, but there are quite a few standard 
UUID types and we are not planning to use any of them.
  2.
The serialization format is different than needed by the application and, while 
once again this is not a hard technical barrier, that might cause slight 
additional complexity and confusion.
  3.
The value is logically defined as a 128-bit integer, that is in itself a 
compound value split into a few "bit groups". Extracting these parts can be 
done by simple (and supposedly efficient) bitwise operators when stored as 
integer, but becomes much more cumbersome with UUID, I guess.


Feladó: Rob Sargent 
Elküldve: 2019. október 23., szerda 22:58
Címzett: Laiszner Tamás 
Másolatot kap: pgsql-gene...@postgresql.org 
Tárgy: Re: Composite type storage overhead



On Oct 23, 2019, at 1:32 PM, Laiszner Tamás 
mailto:t.laisz...@outlook.com>> wrote:

Hey there,

I am currently exploring the options to utilize 128-bit numeric primary keys. 
One of the options I am looking at is to store them as composites of two 64-bit 
integers.

The documentation page on composite types does not tell too much about the 
internal storage, so I've made my own experiment:

CREATE TYPE entity_id AS
(​
high bigint,​
low bigint​
);

CREATE TABLE composite_test
(​
entity_id entity_id NOT NULL,​
CONSTRAINT composite_test_pkey PRIMARY KEY (entity_id)​
)

INSERT INTO composite_test (entity_id) VALUES (ROW(0, 0));

Now, as I am really interested in keeping the indexes compact, tried 
pageinspect to find out what's going on internally:

SELECT * FROM  bt_page_items(get_raw_page('composite_test_pkey', 1));

It seems wrapping the values into a composite type has a pretty significant 
storage overhead, as the index entry has a total size of 48 bytes, end the data 
look like this:

4b ff ff ff ff fa 40 00 00 ff ff ff ff 00 00 02 00 00 00 18 00 00 00 00 00 00 
00 00 00 00 00 00 00 00 00 00 00 00 00 00

For comparison, when simply using a composite primary key of two columns, each 
index entry has a length of only 24 bytes - a 100% overhead from wrapping the 
values in a composite type.

Now, I understand there might be valid reasons to store a structure header 
alongside the plain data - e. g. to store version information so when the type 
is altered there is no need to rebuild the whole table.

However, I also think this should be highlighted in the documentation. (If it 
already is I apologise.)

Also, I would like ask if there is a way to instruct the storage engine to omit 
the housekeeping information and simply store the plain data, even if it comes 
with drawbacks.

I would highly appreciate any comments or additional information on this topic.

Best regards,
Tamas
Why not use UUID type?

Re: jsonb_set() strictness considered harmful to data

[Maciek Sakrejda]

On Wed, Oct 23, 2019 at 12:01 PM Stuart McGraw  wrote:
> Why the inconsistency between the array
> type and json type?  Are there any cases other than json where the entire
> compound value is set to NULL as a result of one of its components being
> NULL?

That's a great point. It does look like hstore's delete / minus
operator behaves like that, though:

=# select 'a=>1,b=>2'::hstore - null;
 ?column?
--

(1 row)

Re: jsonb_set() strictness considered harmful to data

[rob stone]

Hello,

On Wed, 2019-10-23 at 20:33 +0200, Peter J. Holzer wrote:
> 
> I grant that SQL NULL takes a bit to get used to. However, it is a
> core
> part of the SQL language and everyone who uses SQL must understand it
> (I
> don't remember when I first stumbled across "select * from t where c
> =
> NULL" returning 0 rows, but it was probably within the first few days
> of
> using a database). And personally I find it much easier to deal with
> concept which are applied consistently across the whole language than
> those which sometimes apply and sometimes don't seemingly at random,
> just because a developer thought it would be convenient for the
> specific
> use-case they had in mind.
> 
> hp
> 

>From the JSON spec:-

3.  Values

   A JSON value MUST be an object, array, number, or string, or one of
   the following three literal names:

  false
  null
  true

   The literal names MUST be lowercase.  No other literal names are
   allowed.

So, you can't set a value associated to a key to SQL NULL. If a key
should not have a value then delete that key from the JSON.

If you decide your application is going to use one of those three
literal names, then you need to code accordingly. 

My 2 cents.

Re: jsonb_set() strictness considered harmful to data

[Maurice Aubrey]

On Wed, Oct 23, 2019 at 12:01 PM Stuart McGraw  wrote:

> When examples are given, they typically are with scalar values where
> such behavior makes sense: the resulting scalar value has to be NULL
> or non-NULL, it can't be both.
>
> It is less sensible with compound values where the rule can apply to
> individual scalar components.  And indeed that is what Postgresql does
> for another compound type:
>

I agree completely. Scalar vs compound structure seems like the essential
difference.
You don't expect an operation on an element of a compound structure to be
able to effect the entire structure.
Maurice

Re: Composite type storage overhead

[Thomas Kellerer]

> 3. The value is logically defined as a 128-bit integer, that is in
> itself a compound value split into a few "bit groups". Extracting
> these parts can be done by simple (and supposedly efficient) bitwise
> operators when stored as integer, but becomes much more cumbersome
> with UUID, I guess.

This is usually a bad idea. 

Putting logic into the primary key value and merging different types of 
information in a single column is typically not such a good idea. 
(And it violates first normal form to begin with) 

I would strongly recommend to revisit this idea, and e.g. think about a 
multi-column primary key instead. Where each of these "groups" are stored in a 
separate column where the actual (business) value can be retrieved without any 
bitshifting or similar operations. 

Thomas