Thread

1. IN clause behaving badly with missing comma and line break

   Roman Cervenak <roman@cervenak.info> — 2023-01-18T07:51:30Z

   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;
   
   WITH sample AS (SELECT 'c' AS t)
   SELECT * FROM sample WHERE t IN ('a'
   'c');
   

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

   David Rowley <dgrowleyml@gmail.com> — 2023-01-18T08:06:57Z

   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 (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;
   
   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
   
   
   
   

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

   Philip Semanchuk <philip@americanefficient.com> — 2023-01-18T15:20:57Z

   
   > 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 (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;
   > 
   > 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 standard that helps us to avoid surprises due to missing commas. We put one string literal on each line and place the commas all 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
   
   
   

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

   Francisco Olarte <folarte@peoplecall.com> — 2023-01-18T16:06:21Z

   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 literal on each line and place the commas all 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'    ,
   >             )
   
   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.
   
   
   
   

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

   Philip Semanchuk <philip@americanefficient.com> — 2023-01-18T16:09:38Z

   
   > 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 literal on each line and place the commas all 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'    ,
   >>            )
   > 
   > 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 and idiomatic. Not so much in SQL. :-)