Thread: implied FROM

Hello, all.  New to this list.  If this isn't the right place for this
issue, please let me know.

Here is a condensed version of the situation.  I have two tables (STORE and
APPLIANCE).  They share a field name STOREID, where the field is a primary
key in STORE and a foreign key in APPLIANCE.

1) Here is what I *should* have typed in:
select appliance.storeid from appliance where appliance.applianceid = 32;

2) Here is what I accidentally typed in:
select store.storeid from appliance where appliance.applianceid = 32;

#1 gives me a single int4 column in a single-row resultset.
#2 gives me a single int4 column in a resultset that has a row for every row
in STORE.

I finally figured out my error, but I would have found it much faster if the
problem query had thrown an exception.

So, why doesn't asking for a field from a table that IS NOT in the from
clause throw an exception?  Isn't it violating the SQL language?  Why is PG
implying FROM tables?

Thanks!

--
Matt Mello

Dnia 2003-05-02 04:06, Uz.ytkownik Matt Mello napisa?:
<cut>
> So, why doesn't asking for a field from a table that IS NOT in the from
> clause throw an exception?  Isn't it violating the SQL language?  Why is PG
> implying FROM tables?
>
> Thanks!
Your second case is translated into:
select store.storeid from appliance,store where appliance.applianceid = 32;

There was a discussion several months ago about "missing from clause". I
was voting for changing this warning into exception. Some people said,
that it is very useful inside update queries. The conclusion was this
behaviour should stay as it is. Anyway it isn't such a big problem for
me, because the only problem is to catch "missing from clause" warning.

Regards,
Tomasz Myrta

Tomasz Myrta said:
> <cut>
> Some people
> said,  that it is very useful inside update queries. The conclusion was
> this  behaviour should stay as it is.

I see.  I would have to disagree with those people.

It seems that if you mistype an SQL statement, PG should except rather than
proceeding with what could potentially be a very long / very wrong
transaction.  If table "store" has 2 billion entries, then my resultset is
going to be 2 billion rows, even though I was expecting ONE, and since I am
using JDBC, every single row will have to be sent and stored in ram before I
can EVEN KNOW that there was a problem.  With a sufficiently large data set,
this will probably cause an out of memory exception, which will eventually
let me know there was a problem, but since I might get the same error on
other threads/processes before this one, I won't know WHY I ran out of ram
suddenly.  I'm sure there are many other bad things that could happen via
this "feature", but this is just the first one I could think of.

This "feature" violates the law of least surprise (or whatever you call it):
No first-time user of PG who knows SQL would expect this to happen.  And
furthermore, you never want a program to second-guess and you go off and
spend CPU cycles and valuable ram doing something.  That is why so many
people hate Microsoft products.  It is an MS-ism to try to make "computers
for dummys", however PG shouldn't be for dummys, but should be for people
who know SQL.  I am very worried that some people find this feature "very
useful inside update queries".

We should at least have the ability to change a PG setting to say that we DO
NOT WANT PG to try to second guess us ('set FORDUMMYS = false', or
whatever). In my mind, this "feature" is a violation of the spirit of SQL.
I wish I was on this list for the "vote" everyone took on this.

Please, great elephant gurus, please, please, please do not add any more
features like this one without giving me the power to turn it off.  I don't
want my mistakes to be turned into opportunities for my system to completely
crash.

--
Matt Mello

On Fri, May 02, 2003 at 08:44:53 -0500, Matt Mello <alien@spaceship.com> wrote:
> Tomasz Myrta said:
> > <cut>
> > Some people
> > said,  that it is very useful inside update queries. The conclusion was
> > this  behaviour should stay as it is.
> 
> I see.  I would have to disagree with those people.

The problem is that there is currently no way to do this on delete
statements without having the tables joined automagically.

There was some talk going on in another thread in the last couple of days
of adding a GUC setting to force an error instead of a warning in this
case.

Tomasz,

> There was a discussion several months ago about "missing from clause". I
> was voting for changing this warning into exception. Some people said,
> that it is very useful inside update queries. The conclusion was this
> behaviour should stay as it is. Anyway it isn't such a big problem for
> me, because the only problem is to catch "missing from clause" warning.

You'll be happy to know that Nigel Andrews just this week submitted a patch to
the 7.4 source which makes "missing from clause" a GUC option that defaults
to true.   So in future versions you will be able to set it to false, and
Postgres will throw you a fatal exception instead of assuming which data you
want.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Fri, 2 May 2003, Matt Mello wrote:

> Tomasz Myrta said:
> > <cut>
> > Some people
> > said,  that it is very useful inside update queries. The conclusion was
> > this  behaviour should stay as it is.
>
> We should at least have the ability to change a PG setting to say that we DO
> NOT WANT PG to try to second guess us ('set FORDUMMYS = false', or
> whatever). In my mind, this "feature" is a violation of the spirit of SQL.
> I wish I was on this list for the "vote" everyone took on this.

Actually 7.4 should have an GUC option for this (enable_implicit_from),
see recent posts to -patches.