Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] Oracle buysInnobase)

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(at)wildenhain(dot)de]
>>Sent: Wednesday, October 19, 2005 1:05 PM
>>To: Marc G. Fournier
>>Cc: Dann Corbit; Richard_D_Levine(at)raytheon(dot)com; pgsql-
>>hackers(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)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: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>>>>>owner(at)postgresql(dot)org] On Behalf Of Marc G. Fournier
>>>>>Sent: Wednesday, October 19, 2005 11:41 AM
>>>>>To: Richard_D_Levine(at)raytheon(dot)com
>>>>>Cc: pgsql-general(at)postgresql(dot)org
>>>>>Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
>>>>>
>>>>>On Wed, 19 Oct 2005, Richard_D_Levine(at)raytheon(dot)com 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(at)greatgulfhomes(dot)com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085