Re: Is this a bug in pg_current_logfile() on Windows?

[Thomas Kellerer]

Tom Lane schrieb am 08.07.2020 um 18:41:

Somehow, the reading file is being left in binary mode and thus it's
failing to convert \r\n back to plain \n.

Now the weird thing about that is I'd have expected "r" and "w" modes
to imply Windows text mode already, so that I'd have figured that
_setmode call to be a useless no-op.  Apparently on some Windows libc
implementations, it's not.  How was your installation built exactly?


That's the build from EnterpriseDB

https://www.enterprisedb.com/download-postgresql-binaries

Re: Is this a bug in pg_current_logfile() on Windows?

[Tom Lane]

Thomas Kellerer  writes:
> I noticed the following strage output when running Postgres 12.3 (not psql) 
> on Windows

> postgres=# select pg_current_logfile();
>  pg_current_logfile
> 
>  pg_log/postgresql-2020-07-08.log\r
> (1 row)

> Note the "\r" at the end of the file name.

Yeah, that seems like a bug.  I think the reason is that syslogger.c
does this when writing the log metafile:

fh = fopen(LOG_METAINFO_DATAFILE_TMP, "w");
...
#ifdef WIN32
/* use CRLF line endings on Windows */
_setmode(_fileno(fh), _O_TEXT);
#endif

while misc.c only does this when reading the file:

fd = AllocateFile(LOG_METAINFO_DATAFILE, "r");

Somehow, the reading file is being left in binary mode and thus it's
failing to convert \r\n back to plain \n.

Now the weird thing about that is I'd have expected "r" and "w" modes
to imply Windows text mode already, so that I'd have figured that
_setmode call to be a useless no-op.  Apparently on some Windows libc
implementations, it's not.  How was your installation built exactly?

regards, tom lane

Re: Is this a bug in pg_current_logfile() on Windows?

[Adrian Klaver]

On 7/8/20 6:45 AM, Adrian Klaver wrote:

On 7/8/20 6:05 AM, Thomas Kellerer wrote:

Hello,

I noticed the following strage output when running Postgres 12.3 (not 
psql) on Windows


 postgres=# select pg_current_logfile();
  pg_current_logfile
 
  pg_log/postgresql-2020-07-08.log\r
 (1 row)

Note the "\r" at the end of the file name.

This does not happen when running Postgres on Linux.

Is this intended for some strange reason?
Or a bug or a technical limitation?


I'm guessing the difference between Unix line ending:

\n

and Windows:

\r\n



From source(backend/utils/adt/misc.c):

nlpos = strchr(log_filepath, '\n');
if (nlpos == NULL)
{
/* Uh oh.  No newline found, so file content is corrupted. */
elog(ERROR,
"missing newline character in \"%s\"", 
LOG_METAINFO_DATAFILE);

break;
}
*nlpos = '\0';

if (logfmt == NULL || strcmp(logfmt, log_format) == 0)
{
FreeFile(fd);
PG_RETURN_TEXT_P(cstring_to_text(log_filepath));
}



Regards
Thomas








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

Re: Is this a bug in pg_current_logfile() on Windows?

[Adrian Klaver]

On 7/8/20 6:05 AM, Thomas Kellerer wrote:

Hello,

I noticed the following strage output when running Postgres 12.3 (not psql) on 
Windows

 postgres=# select pg_current_logfile();
  pg_current_logfile
 
  pg_log/postgresql-2020-07-08.log\r
 (1 row)

Note the "\r" at the end of the file name.

This does not happen when running Postgres on Linux.

Is this intended for some strange reason?
Or a bug or a technical limitation?


I'm guessing the difference between Unix line ending:

\n

and Windows:

\r\n



Regards
Thomas





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

Re: Is this a bug ?

[George Neuner]

On Wed, 23 Oct 2019 11:27:12 -0500, Ron 
wrote:

>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.

It WAS an advantage querying interactively on 80 character text mode
screens, and when SQL was embedded (directly) into programs written in
other languages.

Regardless of how recent the latest standard - SQL still has many ...
warts really, but I will be charitable and call them "vestiges" ... of
its roots as a 1970s language.

YMMV,
George

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.

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: 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 ?

[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 ?

[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: 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: 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 ?

[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: 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: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: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: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: 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 ?

[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: 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 ?

[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 ?

[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 ?

[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 ?

[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 ?

[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 ?

[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 ?

[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.