At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:
"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.
And every SQL implementation already has many differences from every
other one, even with fundamentals, making them all generally
incompatible and proprietary, and this example is right in line with
the kinds of differences they have. Other implementations have
distinct var-char and fixed-char types, while SQLite only has the
former.
Part of the problem here is that the ANSI standard is itself flawed.
(Maybe this particular case of trailing spaces is an exception, but
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of
SQL is, and they say that the semantics, even of some fundamental
operations, is left up to the implementation to decide for itself.
So what good does it do you if SQL of the same syntax will compile on
different DBMSs if it behaves differently in each one? And the
standard considers this valid.)
How many other programming langauges besides ANSI SQL treat trailing
spaces as insignificant.
Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.
And rightly so, you should not trim spaces, because spaces are significant.
Herein lies a wider part of the problem. The ANSI SQL is
inconsistent in how it treats trailing spaces in strings. On one
hand it wants to preserve them, but on the other hand it wants to
ignore them in its most fundamental operation other than preserving.
(With my "it should not be possible" sentence, I was not saying that
spaces should not be trimmed in the fictional scenario where a
character string does by definition not contain trailing spaces, but
that code specifying them should produce an error rather than
succeed. The matter is analagous to what would happen if you write
code that tries to treat the character string literal 'foo' as a
number.)
But - whether ANSI compliance is considered "bloat" is not really my
place to comment upon. I guess it's really is up to the SQLite team.
Yes it is up to the developers. And they have already demonstrated
willingness to do some things differently than ANSI SQL because they
considered the differences to be improvements, or alternately
reasonable feature cutting.
Purely from a business usability standpoint (not a programming one), I
would say there's no question that it's far more useful to do
comparisons the ANSI way.
And why is it more useful to ignore trailing spaces than respect
them. And if ignoring them is more useful, why do most programming
languages (AFAIK) respect them?
If for some reason I truly want to compare &
respect trailing spaces, I can still do that using a function such as
HEX(A) = HEX(B) or something better.
I would argue that it is rediculous to do such ugly things in order
to do something that should be fundamental, and is simple and
fundamental in any other language. Better for basic '=' comparison
to test that the values are the same, and have some other operator or
function like 'equal_when_trimmed( v1, v2 )' when you want various
exceptional comparisons.
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
