create table test (col1 char(10), col2 varchar(10));
insert into test values ('A  ','B  ');

select
 col1,
 length(col1) length_col1,
 col2,
 length(col2) length_col2
from test
where col1 = 'A' and col2 = 'B'

The query returns an empty result, meaning that the comparisons in the WHERE
clause failed.

This seems to be a bug. In a comparison of two character string values,
trailing spaces in either operand should not affect the comparison, hence
the comparison:

'Value  ' = 'Value'

is true.

The query should return 1 row with the following result:

col1: A character string containing the character 'A' plus 9 trailing spaces
length_col1: 10
col2: A character string containing the character 'B' plus 2 trailing spaces
length_col1: 3

Ole Willy Tuv

Ole


If you're right then what's the point of having CHAR and VARCHAR? And what's the point of specifying that trailing blanks will be stored in a VARCHAR?

Roy Lambert feeling very puzzled.

Roy,

<< If you're right then what's the point of having CHAR and VARCHAR? And
what's the point of specifying that trailing blanks will be stored in a
VARCHAR? >>

Store assignment and comparisons are two different things.

I think that you'll find that the expression:

'Value  ' = 'Value'

evaluates to true in most all RDBMS.

I've checked with SQL Server, Oracle, Interbase, Firebird and NexusDB, and
they all do.

Ole Willy Tuv

Ole


Well, if Tim does that I don't care about removing trailing blanks and I might as well use all CHAR's rather than VARCHAR's cos from the front of the office its the same as trimming everything and I don't care what happens under the hood

Roy Lambert

Roy,

<< Well, if Tim does that I don't care about removing trailing blanks and I
might as well use all CHAR's rather than VARCHAR's cos from the front of the
office its the same as trimming everything and I don't care what happens
under the hood >>

Right. It also means that assigning two values that are only different in
terms of trailing spaces into a unique column, would correctly fail with a
key violation error.

Example:

create table test (col1 varchar(10), primary key (col1));
insert into test values ('A');
insert into test values ('A ');

The second insert should cause a key violation error.

Ole Willy Tuv

Ole,

<< This seems to be a bug. In a comparison of two character string values,
trailing spaces in either operand should not affect the comparison, hence
the comparison: >>

What collation are you using for the columns ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Happy birthday!

<< What collation are you using for the columns ? >>

As you'll se from the table definition in my original post, I didn't specify
a collation explicitly. I guess this means that the columns are implicitly
defined with the ANSI collation ?

I retried the test with explicit ENU collations defined and got the same
result - an empty result.

Also a condition like:

where 'Value  ' = 'Value'

evaluates to false.

Ole Willy Tuv

Ole,

<< Happy birthday!>>

Thanks.

<< As you'll se from the table definition in my original post, I didn't
specify a collation explicitly. I guess this means that the columns are
implicitly defined with the ANSI collation ? >>

Yes.

<< I retried the test with explicit ENU collations defined and got the same
result - an empty result.

Also a condition like:

where 'Value  ' = 'Value'

evaluates to false. >>

I'll look into this, but it may end up staying put for now.  I'm not quite
sure what kind of performance impact it will have if we pad out the strings
with spaces to make the lengths match.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I'll look into this, but it may end up staying put for now.  I'm not
quite sure what kind of performance impact it will have if we pad out the
strings with spaces to make the lengths match. >>

I guess trimming trailing spaces on both operands, which might be more
optimized, will effectively be the same as padding the shorter string to
match the length of the longer string.

Anyhow, I think correct results should take precedence over performance. As
it is now, the incorrect comparison result leads to incorrect/unexpected
query results etc. - compared to what you get with other database engines
(SQL Server, Oracle, Sybase ASA, Mimer, Interbase, Firebird, NexusDB).

Ole Willy Tuv

Hello Tim,

> << Happy birthday!>>

Happy birthday from me too!

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr