" Adding missing FROM-clause entry for table .... " problem.

Hi ,

We find that if we alias a tablename and refer to that tablename in where cluase instead of reffering
to the alias it produces wrond results.

EG:
select to_char(a.generated, 'DD/Mon/YYYY' ) ,userid,email,descr from membership_invoice a join payment_classes using(payment_class)
join users using(userid) join membership_status using(userid) where membership_invoice.status='a' and granted is false and
membership_invoice.last_reminder is null and current_date - date(a.generated) > 4 limit 10 ;
NOTICE: Adding missing FROM-clause entry for table "membership_invoice"
+-------------+--------+--------------------------+---------------+
| to_char | userid | email | descr |
+-------------+--------+--------------------------+---------------+
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
| 23/Nov/2002 | 34886 | pradeepk(at)trade-india(dot)com | IID TradePass |
+-------------+--------+--------------------------+---------------+

Where as merely rewriting the quer to use defined aliases gives the correct results.

select to_char(a.generated, 'DD/Mon/YYYY' ) ,userid,email,descr from membership_invoice a join payment_classes
using(payment_class) join users using(userid) join membership_status using(userid) where a.status='a' and granted is
false and a.last_reminder is null and current_date - date(a.generated) > 4 ;
+-------------+--------+--------------------------------+------------------------+
| to_char | userid | email | descr |
+-------------+--------+--------------------------------+------------------------+
| 12/Feb/2003 | 125182 | blackandwhitetextile(at)yahoo(dot)com | Mini Silver MemberShip |
| 13/Feb/2003 | 117512 | vaishnavipower(at)vsnl(dot)net | Mini Silver MemberShip |
+-------------+--------+--------------------------------+------------------------+
(2 rows)

Can Anyone please explain if its a BUG for problem in my
understanding

Regds
Mallah.

--

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.