Thread: BUG #10201: Invalid input accepted with IN expression

The following bug has been logged on the website:

Bug reference:      10201
Logged by:          Daniel Baston
Email address:      dbaston@gmail.com
PostgreSQL version: 9.2.2
Operating system:   Windows Server 2012
Description:

If two items in an IN expression are separated by a newline instead of a
comma, those items will be ignored with no error.

CREATE TABLE testing (id varchar(1));
INSERT INTO testing VALUES ('1'), ('2'), ('3'), ('4'), ('5');

-- Missing comma produces a syntax error
SELECT * FROM testing WHERE id IN ('1'  '2', '3');

-- Unless there is a newline
SELECT * FROM testing WHERE id IN ('1'
'2', '3');

 id
----
 3
(1 row)

Hi,

On 2014-05-02 20:01:27 +0000, dbaston@gmail.com wrote:
> If two items in an IN expression are separated by a newline instead of a
> comma, those items will be ignored with no error.
>
> CREATE TABLE testing (id varchar(1));
> INSERT INTO testing VALUES ('1'), ('2'), ('3'), ('4'), ('5');
>
> -- Missing comma produces a syntax error
> SELECT * FROM testing WHERE id IN ('1'  '2', '3');
>
> -- Unless there is a newline
> SELECT * FROM testing WHERE id IN ('1'
> '2', '3');

Check what
SELECT '1'
'2';
returns. Two string constants separated by a newline are essentially
concatenated. So, what the above means is: id IN ('12', '3')

Check:
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

dbaston@gmail.com writes:
> If two items in an IN expression are separated by a newline instead of a
> comma, those items will be ignored with no error.

> CREATE TABLE testing (id varchar(1));
> INSERT INTO testing VALUES ('1'), ('2'), ('3'), ('4'), ('5');

> -- Missing comma produces a syntax error
> SELECT * FROM testing WHERE id IN ('1'  '2', '3');

> -- Unless there is a newline
> SELECT * FROM testing WHERE id IN ('1'
> '2', '3');

This is not a bug; what you've got there is the SQL-standard way of
breaking a literal across lines.  Compare

select '1'
'2', '3';
 ?column? | ?column?
----------+----------
 12       | 3
(1 row)

            regards, tom lane

Got it.  Apologies for the false positive.


On Fri, May 2, 2014 at 4:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> dbaston@gmail.com writes:
> > If two items in an IN expression are separated by a newline instead of a
> > comma, those items will be ignored with no error.
>
> > CREATE TABLE testing (id varchar(1));
> > INSERT INTO testing VALUES ('1'), ('2'), ('3'), ('4'), ('5');
>
> > -- Missing comma produces a syntax error
> > SELECT * FROM testing WHERE id IN ('1'  '2', '3');
>
> > -- Unless there is a newline
> > SELECT * FROM testing WHERE id IN ('1'
> > '2', '3');
>
> This is not a bug; what you've got there is the SQL-standard way of
> breaking a literal across lines.  Compare
>
> select '1'
> '2', '3';
>  ?column? | ?column?
> ----------+----------
>  12       | 3
> (1 row)
>
>                         regards, tom lane
>