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 <sr_krishna@xxxxxxx> 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.
