BUG #14394: No error raised in IN-clause when commas are missing
The following bug has been logged on the website:
Bug reference: 14394
Logged by: Andreas Imboden
Email address: andreas.imboden@bl.ch
PostgreSQL version: 9.6.0
Operating system: Red Hat Entreprise Linux
Description:
/*
pg-version:
"PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 201506
(...)""
bug description:
in list with new line instead of comma produces incorrect result.
should instead report a syntax error message
*/
set search_path = public;
select version();
drop table if exists abug;
create table abug (cname text, cvalue integer);
insert into abug values ('one', 1);
insert into abug values ('two', 1);
insert into abug values ('three', 1);
insert into abug values ('four', 1);
insert into abug values ('five', 1);
-- correct
select sum(cvalue) from abug
where cname in ('one', 'two', 'three', 'four', 'five');
-- correct, no comma after 'two', error is raised
select sum(cvalue) from abug
where cname in ('one', 'two' 'three', 'four', 'five');
-- correct
select sum(cvalue) from abug
where cname in (
'one',
'two',
'three',
'four',
'five');
-- not correct, result = 3
-- no comma after 'two', no error message, incorrect result
select sum(cvalue) from abug
where cname in (
'one',
'two'
'three',
'four',
'five');
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Oct 24, 2016 at 02:28:41PM +0000, andreas.imboden@bl.ch wrote:
bug description:
in list with new line instead of comma produces incorrect result.
should instead report a syntax error message
This is not a bug.
Please check:
https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
Specifically part: 4.1.2.1. String Constants.
depesz
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
andreas.imboden@bl.ch writes:
-- no comma after 'two', no error message, incorrect result
select sum(cvalue) from abug
where cname in (
'one',
'two'
'three',
'four',
'five');
This is not a bug, it's required by the SQL standard's syntax for
string literals. Per the manual:
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.
https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Oct 24, 2016 at 7:28 AM, <andreas.imboden@bl.ch> wrote:
The following bug has been logged on the website:
Bug reference: 14394
Logged by: Andreas Imboden
Email address: andreas.imboden@bl.ch
PostgreSQL version: 9.6.0
Operating system: Red Hat Entreprise Linux
Description:/*
pg-version:
"PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
201506
(...)""bug description:
in list with new line instead of comma produces incorrect result.
should instead report a syntax error message*/
[...]'two'
'three',
Working as designed
and mandated by the SQL standard.
The above resolve
s
into
a single string
"two
three
"
.
https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
David J.
On 2016-10-24 16:28, andreas.imboden@bl.ch wrote:
The following bug has been logged on the website:
Bug reference: 14394
-- not correct, result = 3
-- no comma after 'two', no error message, incorrect result
select sum(cvalue) from abug
where cname in (
'one',
'two'
'three',
'four',
'five');
This is actually the correct result because
'two'
'three'
will be concatenated to 'twothree', which is isn't present
(leaving 'one', 'four', and 'five' as the 3 that SUM counted).
See also:
select
'two'
'three' ;
?column?
----------
twothree
(1 row)
which is, I believe, as described in the SQL standard.
Erik Rijkers
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Oct 24, 2016 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
andreas.imboden@bl.ch writes:
-- no comma after 'two', no error message, incorrect result
select sum(cvalue) from abug
where cname in (
'one',
'two'
'three',
'four',
'five');This is not a bug, it's required by the SQL standard's syntax for
string literals. Per the manual: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.https://www.postgresql.org/docs/9.6/static/sql-syntax-
lexical.html#SQL-SYNTAX-CONSTANTSregards, tom lane
I agree but shouldn't it run without errors when there is no newline (only
spaces or comments) as well?
Which version of the standard has this "at least one newline"?
x=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit
(1 row)
x=# select 'two' -- comment
x-# 'x' as a ;
a
------
twox
(1 row)
x=# select 'two' /* comment */ 'x' as a ;
ERROR: syntax error at or near "'x'"
LINE 1: select 'two' /* comment */ 'x' as a ;
^
x=# select 'two' 'x' as a ;
ERROR: syntax error at or near "'x'"
LINE 1: select 'two' 'x' as a ;
^
x=#
Pantelis Theodosiou <ypercube@gmail.com> writes:
On Mon, Oct 24, 2016 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.
I agree but shouldn't it run without errors when there is no newline (only
spaces or comments) as well?
No, because then the syntax rule that causes the literals to be merged
into a single literal doesn't apply, so you get a syntax error.
Which version of the standard has this "at least one newline"?
All of them. SQL92 for instance says (see 5.2 <token> and <separator>
and 5.3 <literal>):
<separator> ::= { <comment> | <space> | <newline> }...
<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>... ] <quote> }... ]
1) In a <character string literal> or <national character string
literal>, the sequence:
<quote> <character representation>... <quote>
<separator>... <quote> <character representation>... <quote>
is equivalent to the sequence
<quote> <character representation>... <character representa-
tion>... <quote>
4) In a <character string literal>, <national character string
literal>, <bit string literal>, or <hex string literal>, a <sep-
arator> shall contain a <newline>.
The intent of allowing separators at all is evidently to allow very long
literals to be split across lines. Which is fine, but I wish they'd
used some explicit syntax to specify continuation. The existing
definition is pretty error-prone, as you found out.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Oct 24, 2016 at 5:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pantelis Theodosiou <ypercube@gmail.com> writes:
On Mon, Oct 24, 2016 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.I agree but shouldn't it run without errors when there is no newline
(only
spaces or comments) as well?
No, because then the syntax rule that causes the literals to be merged
into a single literal doesn't apply, so you get a syntax error.Which version of the standard has this "at least one newline"?
All of them. SQL92 for instance says (see 5.2 <token> and <separator>
and 5.3 <literal>):<separator> ::= { <comment> | <space> | <newline> }...
<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>...
] <quote> }... ]1) In a <character string literal> or <national character string
literal>, the sequence:<quote> <character representation>... <quote>
<separator>... <quote> <character representation>... <quote>is equivalent to the sequence
<quote> <character representation>... <character representa-
tion>... <quote>4) In a <character string literal>, <national character string
literal>, <bit string literal>, or <hex string literal>, a
<sep-
arator> shall contain a <newline>.
Thank you, I missed that rule.
It's not consistent with this rule:
SQL text containing one or more instances of <comment> is equivalent to the
same SQL text with the
<comment> replaced with <newline>.
and I certainly agree on the rest, about the explicit syntax:
Show quoted text
The intent of allowing separators at all is evidently to allow very long
literals to be split across lines. Which is fine, but I wish they'd
used some explicit syntax to specify continuation. The existing
definition is pretty error-prone, as you found out.regards, tom lane
On Mon, Oct 24, 2016 at 10:14 AM, Pantelis Theodosiou <ypercube@gmail.com>
wrote:
On Mon, Oct 24, 2016 at 5:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pantelis Theodosiou <ypercube@gmail.com> writes:
On Mon, Oct 24, 2016 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.I agree but shouldn't it run without errors when there is no newline
(only
spaces or comments) as well?
No, because then the syntax rule that causes the literals to be merged
into a single literal doesn't apply, so you get a syntax error.Which version of the standard has this "at least one newline"?
All of them. SQL92 for instance says (see 5.2 <token> and <separator>
and 5.3 <literal>):<separator> ::= { <comment> | <space> | <newline> }...
<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character
representation>... ] <quote> }... ]1) In a <character string literal> or <national character string
literal>, the sequence:<quote> <character representation>... <quote>
<separator>... <quote> <character representation>... <quote>is equivalent to the sequence
<quote> <character representation>... <character representa-
tion>... <quote>4) In a <character string literal>, <national character string
literal>, <bit string literal>, or <hex string literal>, a
<sep-
arator> shall contain a <newline>.Thank you, I missed that rule.
To restate part of the above: <separator> can be a single newline;
otherwise any multi-character sequence must contain (end with?) a new
line. <'pre' /* comment */ 'post'> doesn't qualify for #1 since the
comment does not include a newline.
It's not consistent with this rule:
SQL text containing one or more instances of <comment> is equivalent to
the same SQL text with the
<comment> replaced with <newline>.
Our docs state we (effectively...) replace comments with a single
space...is this (or can it cause) an incompatibility?
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS
David J.
David G. Johnston wrote:
Our docs state we (effectively...) replace comments with a single
space...is this (or can it cause) an incompatibility?https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS
No, because that space is outside the string literal.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs