From: | José Soares <jose(at)sferacarta(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] SELECT BUG |
Date: | 1999-09-03 10:57:00 |
Message-ID: | 37CFA97C.4E4A45E@sferacarta.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane ha scritto:
> =?iso-8859-1?Q?Jos=E9?= Soares <jose(at)sferacarta(dot)com> writes:
> > Here an example...
> > create table master(mcode char(11), mcode1 char(16));
> > create table detail(dcode char(16));
> > insert into master values ('a','a');
> > insert into master values ('a1','a1');
> > insert into master values ('a13','a13');
> > insert into detail values ('a13');
> > insert into detail values ('a1');
> > insert into detail values ('a13');
>
> > --in the following example mcode is long 11 and mcode1 is long 16
> > --but mcode=mcode1 is true:
>
> > select * from master where mcode=mcode1;
> > mcode |mcode1
> > -----------+----------------
> > a |a
> > a1 |a1
> > a13 |a13
> > (3 rows)
>
> On looking at the bpchar (ie, fixed-length char) comparison functions,
> I see that they *do* strip trailing blanks before comparing. varchar
> and text do not do this --- they assume trailing blanks are real data.
>
> This inconsistency bothers me: I've always thought that char(),
> varchar(), and text() are functionally interchangeable, but it seems
> that's not so. Is this behavior mandated by SQL92?
>
> > --in the following example mcode is long 11 and dcode1 is long 16
> > --but mcode=dcode1 is false:
>
> > select mcode, dcode from master m, detail d where mcode=dcode;
> > mcode|dcode
> > -----+-----
> > (0 rows)
>
> Oh my, that's interesting. Executing your query with current sources
> gives me:
>
> regression=> select mcode, dcode from master m, detail d where mcode=dcode;
> mcode |dcode
> -----------+----------------
> a1 |a1
> a13 |a13
> a13 |a13
> (3 rows)
>
> When I "explain" this, I see that I am getting a mergejoin plan.
> Are you getting a hash join, perhaps?
Yes.
> prova=> explain select mcode, dcode from master m, detail d where
> mcode=dcode;
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=156.00 rows=1001 width=24)
> -> Seq Scan on detail d (cost=43.00 rows=1000 width=12)
> -> Hash (cost=43.00 rows=1000 width=12)
> -> Seq Scan on master m (cost=43.00 rows=1000 width=12)
>
> EXPLAIN
>
José
>
>
> bpchareq is marked hashjoinable in pg_operator, but if its behavior
> includes blank-stripping then that is WRONG. Hashjoin is only safe
> for operators that represent bitwise equality...
>
> regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1999-09-03 11:19:07 | Re: [HACKERS] Re: University Masters Project |
Previous Message | Andreas Zeugswetter | 1999-09-03 08:00:37 | AW: [HACKERS] SELECT BUG |