On 04/24/2013 12:44 PM, uwekeim wrote:

> Hello,
>
> i have a table with a field F1 sized VARCHAR(1).
>
> Some values are '' (field is empty).
>
> Astonishing following SQLs produce the same result:
>
> select * from test t where t.f1=''
>
> or
>
> select * from test t where t.f1=' '
>
> or
>
> select * from test t where t.f1=' '
>
> No matter how many spaces i insert into the sql, the result presents all datasets where field is empty.
>
> I testet this with several databases and several Firebird versions: 1.5, 2.1 and 2.5 (all 32 bit). Result is allways the same.
>
> In my oppinion this is a bug, isnt it? Only the first SQL shoud return the datasets, where F1 is empty. Or am i wrong?

From the SQL1992 Standard:

> 3) The comparison of two character strings is determined as fol-
> lows:
>
> a) If the length in characters of X is not equal to the length
> in characters of Y, then the shorter string is effectively
> replaced, for the purposes of comparison, with a copy of
> itself that has been extended to the length of the longer
> string by concatenation on the right of one or more pad char-
> acters, where the pad character is chosen based on CS. If
> CS has the NO PAD attribute, then the pad character is an
> implementation-dependent character different from any char-
> acter in the character set of X and Y that collates less
> than any string under CS. Otherwise, the pad character is a
> <space>.

in other words, before comparison, the shorter string is padded with
"pad-character" (usually a space) to the length of the longer string.

So, it's not a bug, but a SQL-standards feature.

been there too some years ago :-)

fsg

--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling