Thread: Re: ： Re: A strange problem

>
> 寄件者: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
> 日期: 2005/08/28 星期日 上午 09:03:20 HKT
> 收件者: "Tang Tim Hei" <timheit@netvigator.com>
> 副本: <pgsql-general@postgresql.org>
> 主旨: Re: 回覆： Re: [GENERAL] A strange problem
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

Let me explain my point in more detail below:

The following commands are little different from the previous one.
(1) select A.* from test.currency A, test.price_list B where A.curr_cd=B.curr_cd and A.curr_cd='USD'
(2) select A.* from test.currency A, test.price_list B, test.country C where A.curr_cd=B.curr_cd and A.curr_cd='USD'

For command (1), it is ok. The result is what I expect.
However, for command (2), it has problem. I added the "test.country C" to it, here I actually just write a table name
toit and no more other purpose. However, the result maybe totally different. If the table "country" is not empty, the
resultis just the same as in command (1) but if "country" is empty, there are no result row. 

The point is that: even though I add a constraint to a command, if an additional empty table is mentioned in the
command,the result may be different. 

I think using outer join is a solution but the above old-style command seems to be a problem.
Or it maybe a problem that hasn't be considered by our postgresql dbms developer

On Sun, 2005-08-28 at 16:22 +0800, Tang Tim Hei wrote:

> The following commands are little different from the previous one.
> (1) select A.* from test.currency A, test.price_list B where A.curr_cd=B.curr_cd and A.curr_cd='USD'
> (2) select A.* from test.currency A, test.price_list B, test.country C where A.curr_cd=B.curr_cd and A.curr_cd='USD'
>
> For command (1), it is ok. The result is what I expect.
> However, for command (2), it has problem. I added the "test.country C" to it,
> here I actually just write a table name to it and no more other purpose.

I do not understand what you mean by that.
the added table name means an additional cartesian join

> However, the result maybe totally different.

that is because of the added cartesian join

> If the table "country" is not empty, the result is just the same as in command (1)

it will only be the same if the table contains EXACTLY 1 row

>  but if "country" is empty, there are no result row.

a cartesian join to 0 rows results in 0 rows

if you are talking about something else, please
show us a concrete simple example.

gnari

On Sun, 28 Aug 2005, Tang Tim Hei wrote:

> > 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.
>
> Let me explain my point in more detail below:
>
> The following commands are little different from the previous one.
> (1) select A.* from test.currency A, test.price_list B where
> A.curr_cd=B.curr_cd and A.curr_cd='USD'
> (2) select A.* from test.currency A, test.price_list B, test.country C
> where A.curr_cd=B.curr_cd and A.curr_cd='USD'
>
> For command (1), it is ok. The result is what I expect.
> However, for command (2), it has problem. I added the "test.country C"
> to it, here I actually just write a table name to it and no more other
> purpose. However, the result maybe totally different. If the table
> "country" is not empty, the result is just the same as in command (1)
> but if "country" is empty, there are no result row.

Theoretically, it's not the same if country has multiple rows given the
query above.  The second query is simply incorrect if you want one row for
each currency that has a price list and is USD.

> The point is that: even though I add a constraint to a command, if an
> additional empty table is mentioned in the command, the result may be
> different.

Right, the result is different because it's a query with a different
semantic meaning in SQL.

The first query means something like:
 Return currency data for any currency with curr_cd='USD' which has a
price list.

The second query means something like:
 Return a copy of the currency data for any currency with curr_cd='USD'
which has a price list once for each row in country (which implicitly
means no times if there are no rows in country).

If you write the second when you are trying to get the first meaning,
that's an error in whatever is generating the query.