Postgres Pro
Downloads
Home   >   mailing lists

Thread: Odd query behavior...

Odd query behavior...

From
Gregory Brauer
Date:
I have a question about the behavior of SELECT with multiple
tables.  Take this example... 4 tables, 2 related by 1 many-to-many
table, and 1 unrelated table:



CREATE TABLE TGroup (
     id SERIAL PRIMARY KEY,
     name VARCHAR(16) UNIQUE
);

CREATE TABLE TUser (
     id SERIAL PRIMARY KEY,
     username VARCHAR(16) UNIQUE,
     password VARCHAR(16)
);

CREATE TABLE TUnrelated (
     id SERIAL PRIMARY KEY,
     something VARCHAR(16) UNIQUE
);

CREATE TABLE TGroupTUserLink (
     tgroup_id int4 NOT NULL,
     tuser_id int4 NOT NULL,
     UNIQUE (tgroup_id, tuser_id),
     UNIQUE (tuser_id, tgroup_id),
     FOREIGN KEY (tgroup_id) REFERENCES TGroup(id) ON DELETE CASCADE,
     FOREIGN KEY (tuser_id) REFERENCES TUser(id) ON DELETE CASCADE
);

INSERT INTO TUser (username, password) VALUES ('bob', 'god');
INSERT INTO TUser (username, password) VALUES ('fred', 'sex');
INSERT INTO TGroup (name) VALUES ('user');
INSERT INTO TGroup (name) VALUES ('luser');


If I do a select from the TUser table, I get what is expected:

test=# select TUser.username from TUser;
  username
----------
  bob
  fred
(2 rows)



However if I do a select from both the TUser and TGroup tables,
I get a product, even though I have expressed no joins:

test=# select TUser.username from TUser, TGroup;
  username
----------
  bob
  bob
  fred
  fred
(4 rows)



And finally, if I do a select from the TUser and TUnrelated tables,
I get nothing, even though I have specified no joins:

test=# select TUser.username from TUser, TUnrelated;
  username
----------
(0 rows)



What is going on here?  Why am I getting "products"?  It appears
to have something to do with how tables are related, but I don't
understand the full reasoning behind it.  I have done futher tests
with a table that is related to a table that is related to yet
another table that I am querying, and I no longer get a product,
just the results I expect.

Any help would be appreciated...

Thanks.

Greg Brauer
greg@wildbrain.com

Re: Odd query behavior...

From
Jarmo Paavilainen
Date:
On Tue, 2001-11-20 at 23:51, Gregory Brauer wrote:

> I have a question about the behavior of SELECT with multiple
> tables.  Take this example... 4 tables, 2 related by 1 many-to-many
> table, and 1 unrelated table:
...
> CREATE TABLE TGroup (
>      id SERIAL PRIMARY KEY,
>      name VARCHAR(16) UNIQUE );
>
> CREATE TABLE TUser (
>      id SERIAL PRIMARY KEY,
>      username VARCHAR(16) UNIQUE,
>      password VARCHAR(16) );
>
> CREATE TABLE TUnrelated (
>      id SERIAL PRIMARY KEY,
>      something VARCHAR(16) UNIQUE );
...
> INSERT INTO TUser (username, password) VALUES ('bob', 'god');
> INSERT INTO TUser (username, password) VALUES ('fred', 'sex');
> INSERT INTO TGroup (name) VALUES ('user');
> INSERT INTO TGroup (name) VALUES ('luser');
...
> test=# select TUser.username from TUser, TGroup;
>   username
> ----------
>   bob
>   bob
>   fred
>   fred
> (4 rows)

The select is a cross join (or whatever the correct term is), so this
result is (almost) expected. But should not two of them be NULL?

...
> test=# select TUser.username from TUser, TUnrelated;

*I think* that should have resulted in two rows (its still is a cross
join).

// Jarmo

Re: Odd query behavior...

From
Antonio Fiol Bonnín
Date:
Hi,

To see what you are doing, try

SELECT * FROM table1, table2;

You are SELECTing from the CROSS PRODUCT of both tables.

You inserted:
table       rows
TUser       2
TGroup      2
TUnrelated 0

So the cross product for the 2 first tables gives 2x2=4 rows.
The cross product of TUnrelated with any other will be 0x?=0.

Good luck.

Antonio


Gregory Brauer wrote:

> I have a question about the behavior of SELECT with multiple
> tables.  Take this example... 4 tables, 2 related by 1 many-to-many
> table, and 1 unrelated table:
>
> CREATE TABLE TGroup (
>      id SERIAL PRIMARY KEY,
>      name VARCHAR(16) UNIQUE
> );
>
> CREATE TABLE TUser (
>      id SERIAL PRIMARY KEY,
>      username VARCHAR(16) UNIQUE,
>      password VARCHAR(16)
> );
>
> CREATE TABLE TUnrelated (
>      id SERIAL PRIMARY KEY,
>      something VARCHAR(16) UNIQUE
> );
>
> CREATE TABLE TGroupTUserLink (
>      tgroup_id int4 NOT NULL,
>      tuser_id int4 NOT NULL,
>      UNIQUE (tgroup_id, tuser_id),
>      UNIQUE (tuser_id, tgroup_id),
>      FOREIGN KEY (tgroup_id) REFERENCES TGroup(id) ON DELETE CASCADE,
>      FOREIGN KEY (tuser_id) REFERENCES TUser(id) ON DELETE CASCADE
> );
>
> INSERT INTO TUser (username, password) VALUES ('bob', 'god');
> INSERT INTO TUser (username, password) VALUES ('fred', 'sex');
> INSERT INTO TGroup (name) VALUES ('user');
> INSERT INTO TGroup (name) VALUES ('luser');
>
> If I do a select from the TUser table, I get what is expected:
>
> test=# select TUser.username from TUser;
>   username
> ----------
>   bob
>   fred
> (2 rows)
>
> However if I do a select from both the TUser and TGroup tables,
> I get a product, even though I have expressed no joins:
>
> test=# select TUser.username from TUser, TGroup;
>   username
> ----------
>   bob
>   bob
>   fred
>   fred
> (4 rows)
>
> And finally, if I do a select from the TUser and TUnrelated tables,
> I get nothing, even though I have specified no joins:
>
> test=# select TUser.username from TUser, TUnrelated;
>   username
> ----------
> (0 rows)
>
> What is going on here?  Why am I getting "products"?  It appears
> to have something to do with how tables are related, but I don't
> understand the full reasoning behind it.  I have done futher tests
> with a table that is related to a table that is related to yet
> another table that I am querying, and I no longer get a product,
> just the results I expect.
>
> Any help would be appreciated...
>
> Thanks.
>
> Greg Brauer
> greg@wildbrain.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster