Thread: 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
From
hubert depesz lubaczewski
Date:
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