Dann Corbit wrote:
Try this query in Oracle, SQL*Server, DB/2, Informix, etc.:

connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a ' as
varchar(30));
 ?column?
----------
(0 rows)

I see how you can interpret the SQL Standard to make the above response
a correct one.  But is it the response that you would like?

When the compared datatypes are VARCHAR: YES
When the compared datatypes are fixed length like CHAR: NO


Suppose, for instance, that you have an employee table in your database.
You have another table called benefits.  Perhaps it is even in another
database.  Conceivably even in a database other than PostgreSQL.

Anyway, you want to match information between the two systems so you
join on some redundant columns like a.First_Name = b.fname AND
a.Last_name = b.lname AND a.Middle_Initial = b.mi AND a.City = b.city
AND a.Street_name = b.street

If the columns are not the same length (or one is fixed length and the
other variable), then 'Danniel' won't match 'Danniel' unless you trim
it.  If you trim it, then the indexes go out the window.  If the indexes
go out the window, then we table scan.

If you want to compare datatypes that are different, and you don't have an index that accomodates, that's the price you pay for comparing different data types.


I don't like that behavior.  Perhaps others who know more than me can
say why not blank padding comparisons is a good idea.

Clearly, one could argue that having redundant data is bad and that
every attribute in a database intended to match should be exactly the
same type.  But there are lots of database systems badly designed.  And
of well designed systems, it is not uncommon to have more than one
database in your organization, and a need to perform federated joins as
well because of it.


-----Original Message-----
From: Tino Wildenhain [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 19, 2005 1:05 PM
To: Marc G. Fournier
Cc: Dann Corbit; [EMAIL PROTECTED]; pgsql-
[EMAIL PROTECTED]; pgsql-general@postgresql.org
Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle
buysInnobase)

Am Mittwoch, den 19.10.2005, 16:29 -0300 schrieb Marc G. Fournier:

I'm CC'ng this over to -hackers ... Tom?  Comments?

On Wed, 19 Oct 2005, Dann Corbit wrote:


Yes, clearly that is the wrong result according to the SQL

standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a  ' AND 'a ' = 'a         '

It returns (correctly): 1


-----Original Message-----
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Marc G. Fournier
Sent: Wednesday, October 19, 2005 11:41 AM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

On Wed, 19 Oct 2005, [EMAIL PROTECTED] wrote:


I was referring to trailing blanks, but did not explicitly say

it,

though showed it in the examples.  I am pretty sure that the SQL
standard says that trailing whitespace is insignificant in

string

comparison.

Then we are broken too :)

# select 'a ' = 'a  ';
 ?column?
----------
 f
(1 row)


experiment=# SELECT 'a '::char = 'a  '::char;
?column?
----------
t




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


--
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to