[BUGS] BUG #2503: Query results differ based on record count of unrelated table.

[Russell Francis]

The following bug has been logged online:

Bug reference:  2503
Logged by:  Russell Francis
Email address:  [EMAIL PROTECTED]
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_aINTEGER
);

CREATE TABLE table_b
(
tb_col_aINTEGER
);

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 [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [BUGS] BUG #2503: Query results differ based on record count

[Russell Francis]

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, 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,

Yes, you are correct.  Sorry.

Thanks,
Russ



signature.asc
Description: OpenPGP digital signature