cross join oddity?

[Klint Gore]

I've hit a situation where I'm getting an unexpected set of results
from a cross
join that I've narrowed down to a table alias.

If I do (a has 4 rows, b has 1 row)
select a.field1, b.*
from a as maintable
cross join b

I get 16 rows returned. Is this expected behaviour or should an error
have been raised with the a. reference in the field list? postgres
7.2.3 on linux. (mssql and ingres both raise an error)

klint.

[rstp]

Klint Gore wrote:

I've hit a situation where I'm getting an unexpected set of results
from a cross
join that I've narrowed down to a table alias.

If I do (a has 4 rows, b has 1 row)
select a.field1, b.*
from a as maintable
cross join b

I get 16 rows returned. Is this expected behaviour or should an error
have been raised with the a. reference in the field list? postgres
7.2.3 on linux. (mssql and ingres both raise an error)

klint.

It looks like each element in a is being matched up with each in
maintable. If you want to use the alias maintable, perhaps you should
write the query as
select maintable.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

Ron

[Klint Gore]

rstp <rs**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.com>...

It looks like each element in a is being matched up with each in
maintable. If you want to use the alias maintable, perhaps you should
write the query as
select maintable.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

I did it that way when I got the incorrect result set.

In other DBMS that we use here (mssql,ingres,sybase), they all return
an error if you use the table name instead of the alias. I would have
expected postgres to do the same. So my question is more should it
raise an error rather than how to fix it?

klint.

[rstp]

Klint Gore wrote:

rstp <rs**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.com>...

It looks like each element in a is being matched up with each in
maintable. If you want to use the alias maintable, perhaps you should
write the query as
select maintable.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

I did it that way when I got the incorrect result set.

In other DBMS that we use here (mssql,ingres,sybase), they all return
an error if you use the table name instead of the alias. I would have
expected postgres to do the same. So my question is more should it
raise an error rather than how to fix it?

klint.

I don't think that it should raise an error as there are some instances
where you would want to access a table twice within one query, with an
alias and without. Eg to get information from tblUsers (fName, lName,
inputBy) where inputBy is another user in the table.

Ron