Thread: 回覆: Re: A strange problem

回覆: Re: A strange problem

From
Tang Tim Hei
Date:
>
> 寄件者: Stephan Szabo <sszabo@megazone.bigpanda.com>
> 日期: 2005/08/27 星期六 下午 11:25:49 HKT
> 收件者: Tang Tim Hei <timheit@netvigator.com>
> 副本: pgsql-general@postgresql.org
> 主旨: Re: [GENERAL] A strange problem
>
> On Sat, 27 Aug 2005, Tang Tim Hei wrote:
>
> > Hi,
>
> >   I'm new to postgresql. Anytime I type the following command to the
> >   database to run, it give me no result record if table 'country' is
> >   empty but can get result if 'country' is not empty. Is this so
> >   strange?
>
> Not really. You're doing a cartesian join between test.currency and
> test.country.  If there are no rows in test.country, there are no rows in
> the output of the from clause.
>
> >   select A.* from test.currency A, test.country B where A.curr_cd='USD'
>
>
>

In the above command, I just add another table reference to it and it gives me two different results.
Even I add columns like B.* to it, it do the same things too. Is it not consistance?
In real world, I don't know the 'country' table is empty or not.
Sometime it is empty and sometime it is not empty, this will confuse me whether my program has bug or not.


Re: 回覆: Re: A strange problem

From
Stephan Szabo
Date:
On Sun, 28 Aug 2005, Tang Tim Hei wrote:

>
> >
> > �H����: Stephan Szabo <sszabo@megazone.bigpanda.com>
> > ����: 2005/08/27 �P���� �U�� 11:25:49 HKT
> > ������: Tang Tim Hei <timheit@netvigator.com>
> > �ƥ�: pgsql-general@postgresql.org
> > �D��: Re: [GENERAL] A strange problem
> >
> > On Sat, 27 Aug 2005, Tang Tim Hei wrote:
> >
> > > Hi,
> >
> > >   I'm new to postgresql. Anytime I type the following command to the
> > >   database to run, it give me no result record if table 'country' is
> > >   empty but can get result if 'country' is not empty. Is this so
> > >   strange?
> >
> > Not really. You're doing a cartesian join between test.currency and
> > test.country.  If there are no rows in test.country, there are no rows in
> > the output of the from clause.
> >
> > >   select A.* from test.currency A, test.country B where A.curr_cd='USD'
> >
> >
> >
>
> In the above command, I just add another table reference to it and it gives me two different results.

I'm not 100% sure what you mean, but if you mean that basically adding the
reference to test.country to a statement that looked like "select
A.* from test.currency A where A.curr_cd='USD'", yes it gives different
results, but it also means something different.

> Even I add columns like B.* to it, it do the same things too. Is it not consistance?
> In real world, I don't know the 'country' table is empty or not.

Well, you have to write your queries to do what you want depending on such
things.  For example, the above doesn't constrain the join from currency
and country and so you get multiple copies of the USD currency info for
each country.  If you want to constrain the currency and country
information (for example, say A.country=B.id if you had that sort of
information) you need to decide what happens if there is no country that
matches the country.

Re: ?^???G Re: A strange problem

From
Bruno Wolff III
Date:
On Sun, Aug 28, 2005 at 08:46:56 +0800,
  Tang Tim Hei <timheit@netvigator.com> wrote:
>
> In the above command, I just add another table reference to it and it gives me two different results.
> Even I add columns like B.* to it, it do the same things too. Is it not consistance?
> In real world, I don't know the 'country' table is empty or not.
> Sometime it is empty and sometime it is not empty, this will confuse me whether my program has bug or not.

You might want some sort of outer join in that case.