Thread: Adding missing FROM clause - thanks or no thanks?

I recently had a problem where a bunch of postgres backends were taking up
huge amounts of CPU time.  I found a bunch of log messages like this:

NOTICE:  adding missing FROM-clause entry for table "b"

Which I eventually tracked to an improperly constructed query that reduced
to something like this:

select a1, a2 FROM a WHERE a1 = '...' AND b.b1 = '...';

Table b is pretty big and the conditions were such that it was (apparently)
doing a join of every row in a with every possible row of b.  That's
certainly now what I wanted.

Anyway, I fixed the query construction bug, but I have a question: is this
"adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
"feature" of Postgres?  Prior to seeing this, I'd have expected a query like
the above to cause a fatal error in the parsing stage.  Put another way, can
someone give me an example of when this behavior is useful or desirable?

-John

On Tue, Jan 27, 2004 at 13:00:15 -0500,
  John Siracusa <siracusa@mindspring.com> wrote:
>
> Anyway, I fixed the query construction bug, but I have a question: is this
> "adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
> "feature" of Postgres?  Prior to seeing this, I'd have expected a query like
> the above to cause a fatal error in the parsing stage.  Put another way, can
> someone give me an example of when this behavior is useful or desirable?

It is an extension. I think starting with 7.4 you can turn it off.
It is sort of useful for delete where there isn't a syntax to specify
addition tables, though I think you can get the same functionality
even there using subselects in the where clause.

John Siracusa wrote:
> I recently had a problem where a bunch of postgres backends were taking up
> huge amounts of CPU time.  I found a bunch of log messages like this:
>
> NOTICE:  adding missing FROM-clause entry for table "b"
>
> Which I eventually tracked to an improperly constructed query that reduced
> to something like this:
>
> select a1, a2 FROM a WHERE a1 = '...' AND b.b1 = '...';
>
> Table b is pretty big and the conditions were such that it was (apparently)
> doing a join of every row in a with every possible row of b.  That's
> certainly now what I wanted.
>
> Anyway, I fixed the query construction bug, but I have a question: is this
> "adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
> "feature" of Postgres?  Prior to seeing this, I'd have expected a query like
> the above to cause a fatal error in the parsing stage.  Put another way, can
> someone give me an example of when this behavior is useful or desirable?

It is a PostgreSQL extension and can be turned off with a
postgresql.conf variable or via SET:

    add_missing_from = false

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On 1/27/04 1:28 PM, Bruno Wolff III wrote:
> It is an extension. I think starting with 7.4 you can turn it off.
> It is sort of useful for delete where there isn't a syntax to specify
> addition tables, though I think you can get the same functionality
> even there using subselects in the where clause.

On 1/27/04 1:34 PM, Bruce Momjian wrote:
> It is a PostgreSQL extension and can be turned off with a
> postgresql.conf variable or via SET:
>
> add_missing_from = false

Great, thanks to both of you :)

-John

Dear John Siracusa ,
Are you using version

7.4.x if no whats the version of PostgreSQL  ?

AFAIK  this limitation was introduced in 7.4.x  refer the docs to verify.
Some of my application's query developed on 7.3.x  also reported same
errors/ suggestions when same was taken to 7.4.x
I added the from tabel and all went well
but this behaviour is relent for subqueries.

>I recently had a problem where a bunch of postgres backends were taking up
>huge amounts of CPU time.  I found a bunch of log messages like this:
>
>




--
Regards,
Vishal Kashyap

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap@jabber.org to your roster.
                     OR
Seek Me at 264360076
~*~*~*~*~*~*~*~*
I am usually called as Vishal Kashyap
but my Girlfriend calls me as Vishal CASH UP.
This is because others identify me because of my generosity
but my Girlfriend identify me because of my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*