Ole,

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

I was thinking the same thing.  It would certainly be more efficient and
allow us to do simple length-checking (minus spaces) after comparisons.

<< 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). >>

Performance is always crucial, irregardless of whether we would like it to
be or not.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael,

<< Happy birthday from me too! >>

Thanks.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


If this results in the trailing spaces effectively no longer existing please make sure you tell me and I'll get rid of the triggers

Roy Lambert

Roy,

<< If this results in the trailing spaces effectively no longer existing
please make sure you tell me and I'll get rid of the triggers >>

The trailing spaces are effectively ignored in comparison operations. What
this means is that if you search for the value 'Roy' in a column, you'll get
the rows having 'Roy', 'Roy ', 'Roy  ' etc. It also means that you'd get a
key violation if you assign the value 'Roy' to a unique column that already
has a row with e.g. the value 'Roy '.

The trailing spaces exist in the stored values though, so you'll still see
the trailing spaces when e.g. retrieving values into visual controls.

Ole Willy Tuv

Tim,

<< Performance is always crucial, irregardless of whether we would like it
to be or not. >>

I think we all agree that performance is important but *not* on the
expence of producing incorrect results.

Consider the following table and query:

create table test (col1 char(10));
insert into test values ('Tim');

select *
from test
where col1 = 'Tim'

ElevateDB currently returns an empty result.

I assume you'd agree that this is an incorrect and unexpected result.

Ole Willy Tuv

Ole

>The trailing spaces exist in the stored values though, so you'll still see
>the trailing spaces when e.g. retrieving values into visual controls.

Oh no I won't <vbg>

Roy Lambert

Roy,

> Oh no I won't <vbg>

Not if you keep your triggers, no

Ole Willy Tuv

Ole,

<< Not if you keep your triggers, no >>

I think he means that he/the customer couldn't visually see the spaces to
begin with, which was his beef with not removing the trailing spaces.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< ElevateDB currently returns an empty result.

I assume you'd agree that this is an incorrect and unexpected result. >>

Well, not considering how CHAR columns work in terms of padding with spaces.
They are a fixed-width column type.  Now, if you used a VARCHAR instead,
then I would say that it would be unexpected......

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Well, not considering how CHAR columns work in terms of padding with
spaces. They are a fixed-width column type. >>

Sorry, this is not how querying fixed-length character string columns is
supposed to work.

The result is incorrect in terms of general comparison rules. I think you
can check and verify this with any other database engine.

<< Now, if you used a VARCHAR instead, then I would say that it would be
unexpected...... >>

If the VARCHAR column contained any values with trailing spaces, ElevateDB
would return an unexpected result.

Anyhow, effectively ignoring trailing spaces in comparisons, as discussed
earlier in this thread, will take care of these problems.

Ole Willy Tuv