Thread: BUG #2503: Query results differ based on record count of unrelated table.
BUG #2503: Query results differ based on record count of unrelated table.
From
"Russell Francis"
Date:
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;
Re: BUG #2503: Query results differ based on record count of unrelated table.
From
Alvaro Herrera
Date:
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
Alvaro Herrera wrote: > Russell Francis wrote: >=20 >> 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 ad= ded >> to the table even though it shouldn't modify the results of query, the q= uery >> will return the expected result. >> >> The SQL statements below are a trimmed down test case which duplicates t= his >> issue. The identical SELECT statements return different results dependi= ng >> on the record count of the unrelated table_b. >> >> SELECT table_a.* FROM table_a, table_b WHERE table_a.ta_col_a > 0; >=20 > 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. >=20 Alvaro, Yes, you are correct. Sorry. Thanks, Russ