ASCII_CHAR(13) being first character of EOL is ignored by ascii_val(), char_length() and comparison operator "="

[pavel-zotov]

Consider two .sql scripts from attached .zip file. sql-check-EOL-handling_-_linux-vs-windows.zip

File "check-linux-line-EOL.sql" was created in Linux text editor (one that built-in in mc) and, of couse, has appropriate EOL characters = ascii_char(10):

File "check-windows-line-EOL.sql" was created in Windows and has EOL = concatenated ascii_char(13) and ascii_char(10):

Now run this scriprs in corresponding OS. On Linux output will be expected:

select ascii_val( '
' )
from rdb$database;
       10
-------------------------------------------------------
select char_length( '
' )
from rdb$database;
           1
-------------------------------------------------------
select ascii_char(10) = '
'
from rdb$database;
<true>
-------------------------------------------------------
select '
' = ascii_char(10)
from rdb$database;
<true>
-------------------------------------------------------
select trim('
') = trim(ascii_char(10))
from rdb$database;
<true>

On Windows output shows that ASCII_CHAR() ignores first character of EOL that is used on that OS, i.e. chr(13). And we get wrong results for all statements of this script:

select ascii_val( '
' )
from rdb$database;
       10 ---------------- ?? why not 13 ??
-------------------------------------------------------
select char_length( '
' )
from rdb$database;
           1 --------------------- ?? why not 2 ??
-------------------------------------------------------
select ascii_char(13) || ascii_char(10) = '
'
from rdb$database;
<false>
-------------------------------------------------------
select '
' = ascii_char(13) || ascii_char(10)
from rdb$database;
<false>

PS. Checked on 6.0.0.765

[mrotteveel]

What happens if you use ascii_char (i.e. ascii_char(13) || ascii_char(10) instead of a literal? (My tests show that works as expected.) I rather guess this is some normalization done on the script input by ISQL.

To be honest I wouldn't consider this something that needs or should be fixed.

[pavel-zotov]

@mrotteveel

Did you mean this:

select
    ascii_char(13) || ascii_char(10) = ascii_char(13) || ascii_char(10)
   ,ascii_char(13) || ascii_char(10) = ascii_char(10)
   ,ascii_char(13) || ascii_char(10) = ascii_char(13)
from rdb$database;

-- ?

Ouput will be:

======= ======= =======
<true>  <false> <false>

guess this is some normalization done on the script input by ISQL.

Why this normalization affects on EOL ?

[aafemt]

Why this normalization affects on EOL ?

SQL script should produce the same results being run on Windows and Linux.

[mrotteveel]

Why this normalization affects on EOL ?

Probably it performs line oriented reading (which generally removes the line ending), and then normalizes on \n (0x10) as the new line.

And to be clear, this is my guess, and if my guess is correct, I see no point in changing the behaviour. I find relying on non-printable characters in string literals rather questionable, even if the standard and Firebird allows it.

And in tests it's even worse if you consider that line-endings can depend on your Git configuration (if not controlled with .gitattributes), potentially making your tests unstable.

[AlexPeshkoff]

And to be clear, this is my guess, and if my guess is correct,

99.99% it is correct

I see no point in changing the behaviour. I find relying on non-printable characters in string literals rather questionable, even if the standard and Firebird allows it.

+1