Hello All, Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, and in fact SQLite's implementation of character comparison (respecting trailing spaces) is superior to ANSI's specs. Keep in mind this is not some obscure issue that can be subject to different interpretations by different vendors; it's very clearly stated: "The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them." Does anyone know of another RDBMS (ANSI or no) that respects trailing spaces when comparing character data? We have both Oracle 10g and SQL Server 2005 in house and they both work "correctly" according to the specification. Has anyone tried it with DB2 or Informix? What about PostGres and MySQL? Although I haven't asked him, I'm guessing Zbigniew's suggestion a while back for an auto-trim feature stemmed from this issue. Other than saving space, would there be a need to trim data if WHERE, HAVING clauses and joins followed the spec? Also, other than performance (which seems to be the primary concern), would anyone would be negatively impacted if the current behavior were changed? Our app creates SQLite tables dynamically based on the output from user-defined queries that run against data warehouses (of practically any "flavor") we have no control over, and we insert the results into SQLite. Sure - we can handle this situation by writing more code looking for spaces everywhere they might occur. But to me (and maybe only to me?), it makes sense for SQLite -- where reasonably possible -- to attempt to follow clear ANSI guidelines, allowing developers to override it only in cases where this adherence produces undesirable results. I can't see where this is undesirable from an applications standpoint, although I can see where performance may be a concern. Regards, Jeff Fowler ________________________________
From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Fri 1/18/2008 4:47 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQLite character comparisons 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] -----------------------------------------------------------------------------
