SEO

The following bug has been logged online:

Bug reference: 2503
Logged by: Russell Francis
Email address: rfrancis@ev.net
PostgreSQL version: 8.1.4
Operating system: FreeBSD 6.1
Description: Query results differ based on record count of unrelated
table.
Details:

Summary:

If a SELECT statement contains a table in it's FROM clause which has 0
records in it, the query will not return any results. If a record is added
to the table even though it shouldn't modify the results of query, the query
will return the expected result.

The SQL statements below are a trimmed down test case which duplicates this
issue. The identical SELECT statements return different results depending
on the record count of the unrelated table_b.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

CREATE TABLE table_a
(
ta_col_a INTEGER
);

CREATE TABLE table_b
(
tb_col_a INTEGER
);

INSERT INTO table_a ( ta_col_a ) VALUES ( 50 );

SELECT table_a.* FROM table_a, table_b WHERE table_a.ta_col_a > 0;

INSERT INTO table_b ( tb_col_a ) VALUES ( 50 );

SELECT table_a.* FROM table_a, table_b WHERE table_a.ta_col_a > 0;

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Russell Francis wrote:

> If a SELECT statement contains a table in it's FROM clause which has 0
> records in it, the query will not return any results. If a record is added
> to the table even though it shouldn't modify the results of query, the query
> will return the expected result.
>
> The SQL statements below are a trimmed down test case which duplicates this
> issue. The identical SELECT statements return different results depending
> on the record count of the unrelated table_b.
>
> SELECT table_a.* FROM table_a, table_b WHERE table_a.ta_col_a > 0;

table_b is certainly not unrelated, as it appears in the FROM clause.
So the query is correct to not show any result, per definition of
cartesian product.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Alvaro Herrera wrote:
> Russell Francis wrote:
>
>> If a SELECT statement contains a table in it's FROM clause which has 0
>> records in it, the query will not return any results. If a record is added
>> to the table even though it shouldn't modify the results of query, thequery
>> will return the expected result.
>>
>> The SQL statements below are a trimmed down test case which duplicatesthis
>> issue. The identical SELECT statements return different results depending
>> on the record count of the unrelated table_b.
>>
>> SELECT table_a.* FROM table_a, table_b WHERE table_a.ta_col_a > 0;
>
> table_b is certainly not unrelated, as it appears in the FROM clause.
> So the query is correct to not show any result, per definition of
> cartesian product.
>

Alvaro,

Yes, you are correct. Sorry.

Thanks,
Russ


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEpCP6oA1beFbIX88RAsWEAKCZ/zJlvPaLRWsrm6sUyVD4IJ3haACgjQCi
rva8mvHC43OuL8+/YlkB8pc=
=oZZ7
-----END PGP SIGNATURE-----