Thread: IN clause behaving badly with missing comma and line break
Hello,
if you use IN() clause (I've tested in WHERE and CASE) and you forget comma between values, it is syntax error (correct behaviour). 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;
'd') THEN 2 END FROM sample;
WITH sample AS (SELECT 'c' AS t)
SELECT * FROM sample WHERE t IN ('a'
'c');
SELECT * FROM sample WHERE t IN ('a'
'c');
On Wed, 18 Jan 2023 at 21:03, Roman Cervenak <roman@cervenak.info> wrote:
> 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;
This is not a bug. See 4.1.2.1. String Constants in [1].
David
[1] https://www.postgresql.org/docs/current/sql-syntax-lexical.html
> 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
On Wed, 18 Jan 2023 at 16:21, Philip Semanchuk
<philip@americanefficient.com> wrote:
> .... We’ve adopted a formatting standard that helps us to avoid surprises due to missing commas. We put one string
literalon each line and place the commas all in the same column with a significant amount of white space to the left of
thecommas. With this safeguard in place, it’s very easy to spot a missing comma.
>
> WHERE t IN ('a' ,
> 'foo' ,
> 'bar' ,
> )
I believe that one is a syntax error ( last comma ).
I use a slightly different one, I put commas before the second and
subsequent elements.
WHERE t IN (
'a'
, 'foo'
, 'bar'
)
Both in SQL and in other languages with list constructs. I switched to
that a couple decades ago as many languages do not allow a trailing
comma in literal lists, and ..
- Adding/deleting a first value is rarer ( in my experience ) than
adding/deleting a last one. With the comma first you only edit two
lines when adding/deleting the first one ( commas last means you have
to do it when touching the last ). Same can be said when moving lines
around using cut & paste, you only have to touch the lines contents
when moving the first one.
- Commas line up without worrying about padding when constants have
different lenghts. Also I do not have to worry if a line goes of the
edge due to narrow editor windows.
- I look more to the beginning of the lines than the end, so spotting
missing commas is easier ( for me ).
- Looks pretty to me :-p
I uses it a lot in SQL, specially for field lists in DML, as I tend to
put them in several lines, and found it better ( for me ) then commas
last.
Francisco Olarte.
> On Jan 18, 2023, at 11:06 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
>
> On Wed, 18 Jan 2023 at 16:21, Philip Semanchuk
> <philip@americanefficient.com> wrote:
>
>> .... We’ve adopted a formatting standard that helps us to avoid surprises due to missing commas. We put one string
literalon each line and place the commas all in the same column with a significant amount of white space to the left of
thecommas. With this safeguard in place, it’s very easy to spot a missing comma.
>>
>> WHERE t IN ('a' ,
>> 'foo' ,
>> 'bar' ,
>> )
>
> I believe that one is a syntax error ( last comma ).
Ooops, yes, thanks for catching that. I spend about half of my time in Python where that trailing comma is acceptable
andidiomatic. Not so much in SQL. :-)