Обсуждение: BUG #14394: No error raised in IN-clause when commas are missing
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5NApMb2dnZWQgYnk6ICAg ICAgICAgIEFuZHJlYXMgSW1ib2RlbgpFbWFpbCBhZGRyZXNzOiAgICAgIGFu ZHJlYXMuaW1ib2RlbkBibC5jaApQb3N0Z3JlU1FMIHZlcnNpb246IDkuNi4w Ck9wZXJhdGluZyBzeXN0ZW06ICAgUmVkIEhhdCBFbnRyZXByaXNlIExpbnV4 IApEZXNjcmlwdGlvbjogICAgICAgIAoKLyoNCnBnLXZlcnNpb246IA0KIlBv c3RncmVTUUwgOS42LjAgb24geDg2XzY0LXBjLWxpbnV4LWdudSwgY29tcGls ZWQgYnkgZ2NjIChHQ0MpIDQuOC41IDIwMTUwNgooLi4uKSIiDQoNCmJ1ZyBk ZXNjcmlwdGlvbjogDQppbiBsaXN0IHdpdGggbmV3IGxpbmUgaW5zdGVhZCBv ZiBjb21tYSBwcm9kdWNlcyBpbmNvcnJlY3QgcmVzdWx0Lg0Kc2hvdWxkIGlu c3RlYWQgcmVwb3J0IGEgc3ludGF4IGVycm9yIG1lc3NhZ2UNCg0KKi8NCg0K c2V0IHNlYXJjaF9wYXRoID0gcHVibGljOw0KDQpzZWxlY3QgdmVyc2lvbigp Ow0KDQpkcm9wIHRhYmxlIGlmIGV4aXN0cyBhYnVnOw0KY3JlYXRlIHRhYmxl IGFidWcgKGNuYW1lIHRleHQsIGN2YWx1ZSBpbnRlZ2VyKTsNCg0KaW5zZXJ0 IGludG8gYWJ1ZyB2YWx1ZXMgKCdvbmUnLCAxKTsNCmluc2VydCBpbnRvIGFi dWcgdmFsdWVzICgndHdvJywgMSk7DQppbnNlcnQgaW50byBhYnVnIHZhbHVl cyAoJ3RocmVlJywgMSk7DQppbnNlcnQgaW50byBhYnVnIHZhbHVlcyAoJ2Zv dXInLCAxKTsNCmluc2VydCBpbnRvIGFidWcgdmFsdWVzICgnZml2ZScsIDEp Ow0KDQotLSBjb3JyZWN0IA0Kc2VsZWN0IHN1bShjdmFsdWUpIGZyb20gYWJ1 ZyANCiB3aGVyZSBjbmFtZSBpbiAoJ29uZScsICd0d28nLCAndGhyZWUnLCAn Zm91cicsICdmaXZlJyk7DQoNCi0tIGNvcnJlY3QsIG5vIGNvbW1hIGFmdGVy ICd0d28nLCBlcnJvciBpcyByYWlzZWQgDQpzZWxlY3Qgc3VtKGN2YWx1ZSkg ZnJvbSBhYnVnIA0KIHdoZXJlIGNuYW1lIGluICgnb25lJywgJ3R3bycgJ3Ro cmVlJywgJ2ZvdXInLCAnZml2ZScpOw0KDQotLSBjb3JyZWN0DQpzZWxlY3Qg c3VtKGN2YWx1ZSkgZnJvbSBhYnVnIA0KIHdoZXJlIGNuYW1lIGluICgNCiAg ICAnb25lJywgDQogICAgJ3R3bycsIA0KICAgICd0aHJlZScsIA0KICAgICdm b3VyJywgDQogICAgJ2ZpdmUnKTsgDQoNCi0tIG5vdCBjb3JyZWN0LCByZXN1 bHQgPSAzDQotLSBubyBjb21tYSBhZnRlciAndHdvJywgbm8gZXJyb3IgbWVz c2FnZSwgaW5jb3JyZWN0IHJlc3VsdA0Kc2VsZWN0IHN1bShjdmFsdWUpIGZy b20gYWJ1ZyANCiB3aGVyZSBjbmFtZSBpbiAoDQogICAgJ29uZScsIA0KICAg ICd0d28nIA0KICAgICd0aHJlZScsIA0KICAgICdmb3VyJywgDQogICAgJ2Zp dmUnKTsgDQoKCg==
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-SYN=
TAX-CONSTANTS
regards, tom lane
Re: BUG #14394: No error raised in IN-clause when commas are missing
От
hubert depesz lubaczewski
Дата:
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
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
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 > > */ > =E2=80=8B[...]=E2=80=8B > > 'two' > 'three', > =E2=80=8BWorking as designed =E2=80=8B and mandated by the SQL standard.=E2=80=8B The above resolve =E2=80=8Bs=E2=80=8B into =E2=80=8Ba single string =E2=80=8B "two =E2=80=8Bthree=E2=80=8B " =E2=80=8B. https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNT= AX-CONSTANTS David J. =E2=80=8B =E2=80=8B
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-CONSTANTS > > regards, 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 (on=
ly
> 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> ::=3D { <comment> | <space> | <newline> }...
<character string literal> ::=3D
[ <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 <se=
p-
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
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:
>
> 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> ::=3D { <comment> | <space> | <newline> }...
>
>
>> <character string literal> ::=3D
>> [ <introducer><character set specification> ]
>> <quote> [ <character representation>... ] <quote>
>> [ { <separator>... <quote> [ <character
>> representation>... ] <quote> }... ]
>>
>> 1) In a <character string literal> or <national character strin=
g
>> literal>, the sequence:
>>
>> <quote> <character representation>... <quote>
>> <separator>... <quote> <character representation>... <quot=
e>
>>
>> is equivalent to the sequence
>>
>> <quote> <character representation>... <character represent=
a-
>> 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.
>
=E2=80=8BTo restate part of the above: <separator> can be a single newline;
otherwise any multi-character=E2=80=8B 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>.
>
=E2=80=8B=E2=80=8BOur 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