Thread: BUG #5755: Query Optimzer Bug

The following bug has been logged online:

Bug reference:      5755
Logged by:          Elliott Groszek
Email address:      elliott.groszek@navy.mil
PostgreSQL version: 8.4.4
Operating system:   RedHat
Description:        Query Optimzer Bug
Details:

The following SQL statements produce different query plans even though
"phone" is not reference in the SQL!

explain update customer set login_name =
(select "NESSOuserName" from person,phone
where person.cac_cert=customer.cac_cert);


explain update customer set login_name =
(select "NESSOuserName" from person
where person.cac_cert=customer.cac_cert);

Please advise,  Elliott

"Elliott Groszek" <elliott.groszek@navy.mil> writes:
> The following SQL statements produce different query plans even though
> "phone" is not reference in the SQL!

> explain update customer set login_name =
> (select "NESSOuserName" from person,phone
> where person.cac_cert=customer.cac_cert);

> explain update customer set login_name =
> (select "NESSOuserName" from person
> where person.cac_cert=customer.cac_cert);

That's not a bug.

            regards, tom lane

On 17.11.2010 18:05, Tom Lane wrote:
> "Elliott Groszek"<elliott.groszek@navy.mil>  writes:
>> The following SQL statements produce different query plans even though
>> "phone" is not reference in the SQL!
>
>> explain update customer set login_name =
>> (select "NESSOuserName" from person,phone
>> where person.cac_cert=customer.cac_cert);
>
>> explain update customer set login_name =
>> (select "NESSOuserName" from person
>> where person.cac_cert=customer.cac_cert);
>
> That's not a bug.

Particularly, note that they produce different results if "phone" table
is empty.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com