Cartesian product not correct

Angus Miller <angus@spinnaker-ims.com> writes:

Given the sql below shouldn't I get the 3 records back?

Try 8.4.1 --- I think you got bit by the semijoin ordering bugs in
8.4.0.

regards, tom lane

In response to Angus Miller :

Hi

Given the sql below shouldn't I get the 3 records back?

No, why?

create table t1 (attribute text);
insert into t1 values ('cars');
insert into t1 values ('trucks');
insert into t1 values ('bikes');

create table t2 (id serial, category text, attribute text, val integer);
insert into t2(category, attribute, val) values ('vehicle','cars', 100);

select t2.category, t1.attribute, 0 as val
from t2, t1
where t2.id in (
select max(id)
from t2
group by category
);

The inner select returns:

test=*# select max(id) from t2 group by category;
max
-----
1
(1 row)

The outer query contains:

where t2.id in ( result from the inner select )

You have only one record in t2, with id = 1.

Maybe you want:

test=*# select t2.category, t1.attribute, 0 as val from t2, t1
test-# ;
category | attribute | val
----------+-----------+-----
vehicle | cars | 0
vehicle | trucks | 0
vehicle | bikes | 0
(3 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)

A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

In response to Angus Miller :

Hi

Given the sql below shouldn't I get the 3 records back?

No, why?

Oh,... sorry, right. 8.1 returns 3 rows, see Tom's answer.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Thanks Tom I can confirm this passed in 8.4.1

Tom Lane wrote: