Re: IN clause behaving badly with missing comma and line break

> On Jan 18, 2023, at 2:51 AM, Roman Cervenak <roman@cervenak.info> wrote:
>
> Hello,
> if you use IN() clause (I've tested in WHERE and CASE) and you forget comma between values, it is syntax error
(correctbehaviour). But if there is newline between those two values, suddenly it is not syntax error (query will run
successfully),and all values are simply ignored.  
>
> Examples:
>
> WITH sample AS (SELECT 'c' AS t) SELECT CASE WHEN t IN ('a','b') THEN 1 WHEN t IN ('c'
>   'd') THEN 2 END FROM sample;
>
> WITH sample AS (SELECT 'c' AS t)
> SELECT * FROM sample WHERE t IN ('a'
> 'c’);


As David said, this is expected behavior, although it’s subtle and easy to overlook. We’ve adopted a formatting
standardthat helps us to avoid surprises due to missing commas. We put one string literal on each line and place the
commasall in the same column with a significant amount of white space to the left of the commas. With this safeguard in
place,it’s very easy to spot a missing comma. 

WHERE t IN ('a'      ,
            'foo'    ,
            'bar'    ,
            )


Cheers
Philip