Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cartesian product not correct

Cartesian product not correct

From
Angus Miller
Date:
Hi

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

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
);

PostgreSQL 8.4.0 on windows

Cheers
Angus

Re: Cartesian product not correct

From
Tom Lane
Date:
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

Re: Cartesian product not correct

From
"A. Kretschmer"
Date:
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)

Re: Cartesian product not correct

From
Andreas Kretschmer
Date:
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°

Re: Cartesian product not correct

From
Angus Miller
Date:
Thanks Tom I can confirm this passed in 8.4.1

Tom Lane wrote:
> 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
>