Thread: bug: evil autoConcat when each string is on new line

On 24/04/2019 13:35, Jan Skydánek wrote:
> Does not work (as expected):
> SELECT 'aaa' 'bbb' 'ccc';
> 
> Works (definitely NOT expected):
> SELECT
> 'aaa'
> 'bbb'
> 'ccc'
> ; -- result is: 'aaabbbccc'
> 
> 
> I really hope this behavior is not intentional.
Hi.

Yes, this behavior is intentional as mandated by the SQL spec.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On 2019-Apr-24, Jan Skydánek wrote:

> Works (definitely NOT expected):
> SELECT
> 'aaa'
> 'bbb'
> 'ccc'
> ; -- result is: 'aaabbbccc'
> 
> I really hope this behavior is not intentional.

Actually it's the SQL standard that defines that it must work in exactly
that way.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Apr-24, Jan Skydánek wrote:
>> Works (definitely NOT expected):
>> SELECT
>> 'aaa'
>> 'bbb'
>> 'ccc'
>> ; -- result is: 'aaabbbccc'
>>
>> I really hope this behavior is not intentional.

> Actually it's the SQL standard that defines that it must work in exactly
> that way.

Yeah.  Quoting from SQL99:

         <separator> ::= { <comment> | <white space> }...

         <character string literal> ::=
              [ <introducer><character set specification> ]
              <quote> [ <character representation>... ] <quote>
                [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

         Syntax Rules

         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
              representation>... <quote>

            NOTE 47 - The <character representation>s in the equivalent
            sequence are in the same sequence and relative sequence as in
            the original <character string literal>.


         5) In a <character string literal>, <national character string
            literal>, <bit string literal>, <binary string literal>, or <hex
            string literal>, a <separator> shall contain a <newline>.


Don't ask us, we just implement it.

            regards, tom lane

Alvaro Herrera wrote:

> On 2019-Apr-24, Jan Skydánek wrote:
> 
> > Works (definitely NOT expected):
> > SELECT
> > 'aaa'
> > 'bbb'
> > 'ccc'
> > ; -- result is: 'aaabbbccc'
> > 
> > I really hope this behavior is not intentional.
> 
> Actually it's the SQL standard that defines that it must work in exactly
> that way.
> 
> -- 
> Álvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Perhaps what Jan meant to do was:

  select
  'aaa',
  'bbb',
  'ccc'
  ;

Many languages automatically concatenate consecutive
string literals, not just SQL. Even C does it. Probably
because there's no other useful interpretation of what
multiple consecutive string literals means. It's either
concatenate them or treat it as a syntax error.
Concatenating can be useful so it wins.

Actually, I just tried that command on a single line
and that was a syntax error. So what do I know?
Presumably the SQL standard says to do that as well. :-)

It does seem like an inconsistency. I wouldn't have
expected SQL to be so whitespace-dependant but there'll
be a good reason for it somewhere. And, again, it's not
the only language where newlines matter. :-)

cheers,
raf

On Thu, Apr 25, 2019 at 1:13 AM <raf@raf.org> wrote:

> Many languages automatically concatenate consecutive
> string literals, not just SQL. Even C does it. Probably
> because there's no other useful interpretation of what
> multiple consecutive string literals means. It's either
> concatenate them or treat it as a syntax error.
> Concatenating can be useful so it wins.

In C, IIRC, it is also useful when combined with the stringification
construct of the preprocessor ( #xxx => "xxx" ) to construct things
like long messages ( evaluated at compile time, like #define
PRINT_INT(v) printf( #v "=%d\n",(v))  ).

> Actually, I just tried that command on a single line
> and that was a syntax error. So what do I know?
> Presumably the SQL standard says to do that as well. :-)

Actually when I first saw this it made me curious and I searched for
"string constants" in the doc, the second paragraph in "4.1.2.1.
String Constants" says "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.
For example:", but I suppose reading TFM before reporting a bug is
getting too old fashioned ( and one of my top reasons for using
postgres is doc quality ).

> It does seem like an inconsistency. I wouldn't have
> expected SQL to be so whitespace-dependant but there'll
> be a good reason for it somewhere. And, again, it's not
> the only language where newlines matter. :-)

SQL std is really puzzling and irregular. My usual comment when
reading some of it is "what do these guys smoke, I want some of it".
For me is the type of language which makes for great looking examples
in the manual but when you use it is so irregular to learn and when
you implement is difficult to parse. I mean, substring( "hello" from 1
for 2) may look great, but substr("hello", 1, 2) is, for me, much
regular and easier to remember ( learning the parameters is hard
enough without having to learn the special delimiters too ). String
constant concatenation requiring at least one newline among the
whitespace is one of these things. Although it MAY have a good reason
for it.

Francisco Olarte.

Pavel:

On Thu, Apr 25, 2019 at 11:43 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> SQL std is really puzzling and irregular. ....
.....Rest of rant snipped.
> There is a impact of some older traditions. Originally SQL was a language for ending users, it was not designed for
developers.

Yes, I know where it comes from. It reminds me a lot of when I as
learning COBOL ( on punched cards). Seeing the evolution I think
people assumed that adding a little verbosity and words instead of
plain commas here and there would make it easier, but IMO it makes for
great presentation material but harder to use, both for end users and
for profesional developers. And it's not the only one, IE, I've always
thought that the format of HTTP headers is great for a casual reading
and/or presenting examples in RFCs, but needlesly difficult to parse
or generate, I group that with glossy screens, better for shop
display, worse for everyday use in my experience.

Regards.
    Francisco Olarte.