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:tino@xxxxxxxxxxxxx]
Sent: Wednesday, October 19, 2005 1:05 PM
To: Marc G. Fournier
Cc: Dann Corbit; Richard_D_Levine@xxxxxxxxxxxx; pgsql-
hackers@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] 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: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of Marc G. Fournier
Sent: Wednesday, October 19, 2005 11:41 AM
To: Richard_D_Levine@xxxxxxxxxxxx
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [pgsql-advocacy] Oracle buys Innobase
On Wed, 19 Oct 2005, Richard_D_Levine@xxxxxxxxxxxx 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
terry@xxxxxxxxxxxxxxxxxx
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
