create table fooa (col1 varchar(30))
go
create table bara (col1 varchar(300))
go
insert into fooa values ('Danniel ')
go
insert into bara values ('Danniel ')
go
select * from fooa,bara where fooa.col1=bara.col1
go
Returns:
Danniel Danniel
I think that the issue is:
Does PostgreSQL use something other than <space> as the pad character?
If so, what character is that (and why choose it over <space>)?
If not, then PostgreSQL is clearly returning the wrong results.
> -----Original Message-----
> From: Terry Fielder [mailto:terry@xxxxxxxxxxxxxxxxxxxx]
> Sent: Wednesday, October 19, 2005 2:02 PM
> To: Dann Corbit
> Cc: Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql-
> general@xxxxxxxxxxxxxx
> Subject: Re: [pgsql-advocacy] Oracle buys Innobase
>
> Hi Dann
>
> Without looking at the internals to see if the 1 column or the other
is
> being converted to the other columns type before the compare, it
really
> demonstrates nothing.
>
> It could perhaps be used to help demonstrate that when comparing a
> datatype of CHAR to VARCHAR that
> MS-SQL converts the VARCHAR to CHAR and then does the compare
> Postgres converts the CHAR to VARCHAR and then does the compare
>
> But there isn't even enough evidence here to support that.
>
> Terry
>
> Dann Corbit wrote:
> > create table foo (col1 varchar(30))
> > go
> > create table bar (col1 char(30))
> > go
> > insert into foo values ('Danniel ')
> > go
> > insert into bar values ('Danniel ')
> > go
> > select * from foo,bar where foo.col1=bar.col1
> > go
> >
> > Result set:
> > Danniel Danniel
> >
> >
> >>-----Original Message-----
> >>From: Terry Fielder [mailto:terry@xxxxxxxxxxxxxxxxxxxx]
> >>Sent: Wednesday, October 19, 2005 1:39 PM
> >>To: Dann Corbit
> >>Cc: Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql-
> >>general@xxxxxxxxxxxxxx
> >>Subject: Re: [pgsql-advocacy] Oracle buys Innobase
> >>
> >>I agree with you, but...
> >>
> >>Actually that's not how the compare works usually.
> >>
> >>Generally one of the operands is converted to the same datatype as
the
> >>other, and THEN the compare is performed.
> >>
> >>I expect MS SQL is converting a 'sdas' typeless string to be assumed
> >>CHAR and Postgresql is converting a 'sdas' typeless string to be
> >
> > assumed
> >
> >>VARCHAR.
> >>
> >>Hence, the different behaviour.
> >>
> >>Terry
> >>
> >>Dann Corbit wrote:
> >>
> >>>Would you want varchar(30) 'Dann Corbit' to compare equal to
> >
> > bpchar(30)
> >
> >>>'Dann Corbit'?
> >>>
> >>>I would.
> >>>
> >>>If both are considered character types by the language, then they
> >
> > must
> >
> >>>compare that way.
> >>>
> >>>Perhaps there are some nuances that I am not aware of. But that is
> >
> > how
> >
> >>>things ought to behave, if I were king of the forest.
> >>>
> >>>
> >>>
> >>>>-----Original Message-----
> >>>>From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
> >>>>owner@xxxxxxxxxxxxxx] On Behalf Of Terry Fielder
> >>>>Sent: Wednesday, October 19, 2005 12:37 PM
> >>>>To: Marc G. Fournier
> >>>>Cc: Richard_D_Levine@xxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
> >>>>Subject: Re: [pgsql-advocacy] Oracle buys Innobase
> >>>>
> >>>>OK, I am not an expert on the SQL standard, but I thought the
> >>>
> >>>definition
> >>>
> >>>
> >>>>varied by data type e.g. varchar <> bpchar
> >>>>
> >>>>Terry
> >>>>
> >>>>Marc G. Fournier wrote:
> >>>>
> >>>>
> >>>>>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)
> >>>>>
> >>>>>----
> >>>>>Marc G. Fournier Hub.Org Networking Services
> >>>>
> >>>>(http://www.hub.org)
> >>>>
> >>>>
> >>>>>Email: scrappy@xxxxxxx Yahoo!: yscrappy
ICQ:
> >>>>
> >>>>7615664
> >>>>
> >>>>
> >>>>>---------------------------(end of
> >>>
> >>>broadcast)---------------------------
> >>>
> >>>
> >>>>>TIP 4: Have you searched our list archives?
> >>>>>
> >>>>> http://archives.postgresql.org
> >>>>>
> >>>>
> >>>>--
> >>>>Terry Fielder
> >>>>terry@xxxxxxxxxxxxxxxxxx
> >>>>Associate Director Software Development and Deployment
> >>>>Great Gulf Homes / Ashton Woods Homes
> >>>>Fax: (416) 441-9085
> >>>>
> >>>>---------------------------(end of
> >>>
> >>>broadcast)---------------------------
> >>>
> >>>
> >>>>TIP 6: explain analyze is your friend
> >>>
> >>>
> >>--
> >>Terry Fielder
> >>terry@xxxxxxxxxxxxxxxxxx
> >>Associate Director Software Development and Deployment
> >>Great Gulf Homes / Ashton Woods Homes
> >>Fax: (416) 441-9085
> >
> >
>
> --
> Terry Fielder
> terry@xxxxxxxxxxxxxxxxxx
> Associate Director Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> Fax: (416) 441-9085
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
