Thread: Add Missing From?

Add Missing From?

From
Josh Berkus
Date:
Tom, Et Al:

Going over the .conf, I just noticed that add_missing_from is still set to 
True in postgresql.conf.sample.    By my memory of our discussion, this 
option was introduced in 7.4 and was to be set to False by default in 8.0.   
Can we switch it to False?

BTW, the reason for setting it to false is that it's not SQL-standard and 
often leads to "silent failures"; that is, users getting results they don't 
expect because they left out a table name.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Add Missing From?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Going over the .conf, I just noticed that add_missing_from is still set to 
> True in postgresql.conf.sample.    By my memory of our discussion, this 
> option was introduced in 7.4 and was to be set to False by default in 8.0.   
> Can we switch it to False?

I don't see anything in the current docs warning that such a change is
afoot.  We have insisted on one release cycle's warning for smaller
things than this ...

Personally I don't mind (but bear in mind that the regression tests will
likely need fixes).
        regards, tom lane


Re: Add Missing From?

From
Josh Berkus
Date:
Tom,

> I don't see anything in the current docs warning that such a change is
> afoot.  We have insisted on one release cycle's warning for smaller
> things than this ...

Ok.  Can we put a warning in, then?  Where should we put it?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Add Missing From?

From
Robert Treat
Date:
On Sunday 08 August 2004 21:44, Josh Berkus wrote:
> Tom,
>
> > I don't see anything in the current docs warning that such a change is
> > afoot.  We have insisted on one release cycle's warning for smaller
> > things than this ...
>
> Ok.  Can we put a warning in, then?  Where should we put it?

AFAIR this is still the only way to do updates on joined tables, a feature 
that IIRC is in the sql spec (and certianly in other rdbms') that we do not 
support.  Until we come up with a solution for that, I think our current 
behavior of defaulting on and emiting a warning upon occurance is the right 
way to go. 

-- 
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL


Re: Add Missing From?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
>> I don't see anything in the current docs warning that such a change is
>> afoot.  We have insisted on one release cycle's warning for smaller
>> things than this ...

> Ok.  Can we put a warning in, then?  Where should we put it?

In the description of add_missing_from, I think.
        regards, tom lane


Re: Add Missing From?

From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:
> AFAIR this is still the only way to do updates on joined tables, a feature 
> that IIRC is in the sql spec (and certianly in other rdbms') that we do not 
> support.

I think you're wrong on both counts --- we do support UPDATE FROM, and
it's not in the spec.

What we don't have is an equivalent syntax for DELETE, and you're right
that we probably ought to offer that before we deprecate add_missing_from.
        regards, tom lane


Re: Add Missing From?

From
Josh Berkus
Date:
Robert, Tom,

> I think you're wrong on both counts --- we do support UPDATE FROM, and
> it's not in the spec.

I can verify that it's not in SQL92.   Unless you've got a place they added a 
different syntax in 99 or 2003, Robert?

> What we don't have is an equivalent syntax for DELETE, and you're right
> that we probably ought to offer that before we deprecate add_missing_from.

Hmmm.  What would that look like?

DELETE FROM table
{FROM | WITH | USING | ?? }
WHERE ...

I think we don't have this mainly because, what word do we use?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Add Missing From?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Hmmm.  What would that look like?

> DELETE FROM table
> {FROM | WITH | USING | ?? }
> WHERE ...

> I think we don't have this mainly because, what word do we use?

Yup, eggzackle.  The implementation would really be trivial, but
previous discussion hung up on the problem of finding a syntax
people liked ...
        regards, tom lane


Re: Add Missing From?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Hmmm.  What would that look like?
> 
> > DELETE FROM table
> > {FROM | WITH | USING | ?? }
> > WHERE ...
> 
> > I think we don't have this mainly because, what word do we use?
> 
> Yup, eggzackle.  The implementation would really be trivial, but
> previous discussion hung up on the problem of finding a syntax
> people liked ...

Agreed, but are we ever going to find any better solution than just
picking one of the suggestions?

I vote for the second FROM.

--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Robert Treat
Date:
On Sunday 08 August 2004 23:16, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Hmmm.  What would that look like?
> >
> > DELETE FROM table
> > {FROM | WITH | USING | ?? }
> > WHERE ...
> >
> > I think we don't have this mainly because, what word do we use?
>
> Yup, eggzackle.  The implementation would really be trivial, but
> previous discussion hung up on the problem of finding a syntax
> people liked ...
>

Well, as yall have pointed out, the feature is not sql spec (for some reason I 
thought it had been put in) so since the update syntax seems quite similar to 
oracles, perhaps they can provide a pointer on delete syntax as well?  I 
can't seem to find my oracle syntax book, anyone have one handy ?

-- 
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL


Re: Add Missing From?

From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:
> Well, as yall have pointed out, the feature is not sql spec (for some
> reason I thought it had been put in) so since the update syntax seems
> quite similar to oracles, perhaps they can provide a pointer on delete
> syntax as well?  I can't seem to find my oracle syntax book, anyone
> have one handy ?

Didn't get any Oracle hits in a quick google, but I did find out that
MySQL spells it USING:
DELETE FROM target_tbl USING other_tbls WHERE ...

This isn't a particularly compelling precedent seeing that (a) MySQL
doesn't use our flavor of UPDATE syntax and (b) they only adopted the
above in 4.0.2.  But it's better than no precedent.  And frankly I
was having a big problem with "DELETE FROM target FROM others ..."
If that's not a recipe for confusion I don't know what is.
        regards, tom lane


Re: Add Missing From?

From
Christopher Kings-Lynne
Date:
> Didn't get any Oracle hits in a quick google, but I did find out that
> MySQL spells it USING:

You guys can go to otn.oracle.com and register for free to get access to 
all the documentation they've ever written.  I've got an account there.  I do get the odd oracle magazine sent to me
though...

This is the DELETE syntax:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_8005.htm#sthref6065

This is the text version:

DELETE [ hint ]   [ FROM ]   { dml_table_expression_clause   | ONLY (dml_table_expression_clause)   }   [ t_alias ]   [
where_clause]   [ returning_clause ] ;
 

Where dml_table_expression_clause is:

{ [ schema. ]  { table    [ { PARTITION (partition)      | SUBPARTITION (subpartition)      }    | @ dblink    ]  | {
view| materialized view } [ @ dblink ]  }
 
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}


I can paste more but the way it works is that I can only view each 
sub-part of the gammar separately so it's a pain in the butt to copy.

Chris





Re: Add Missing From?

From
Harald Fuchs
Date:
In article <5431.1092025741@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Robert Treat <xzilla@users.sourceforge.net> writes:
>> Well, as yall have pointed out, the feature is not sql spec (for some
>> reason I thought it had been put in) so since the update syntax seems
>> quite similar to oracles, perhaps they can provide a pointer on delete
>> syntax as well?  I can't seem to find my oracle syntax book, anyone
>> have one handy ?

> Didn't get any Oracle hits in a quick google, but I did find out that
> MySQL spells it USING:

>     DELETE FROM target_tbl USING other_tbls WHERE ...

> This isn't a particularly compelling precedent seeing that (a) MySQL
> doesn't use our flavor of UPDATE syntax and (b) they only adopted the
> above in 4.0.2.

Actually, MySQL supports two different syntaxes for multi-table DELETEs:

1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;  (introduced in MySQL 4.0.0)

2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;  (introduced in MySQL 4.0.2)



Re: Add Missing From?

From
Jan Wieck
Date:
On 8/9/2004 12:29 AM, Tom Lane wrote:

> Robert Treat <xzilla@users.sourceforge.net> writes:
>> Well, as yall have pointed out, the feature is not sql spec (for some
>> reason I thought it had been put in) so since the update syntax seems
>> quite similar to oracles, perhaps they can provide a pointer on delete
>> syntax as well?  I can't seem to find my oracle syntax book, anyone
>> have one handy ?
> 
> Didn't get any Oracle hits in a quick google, but I did find out that
> MySQL spells it USING:
> 
>     DELETE FROM target_tbl USING other_tbls WHERE ...

Feels much more understandable. The second FROM looks like a hickup.


Jan

> 
> This isn't a particularly compelling precedent seeing that (a) MySQL
> doesn't use our flavor of UPDATE syntax and (b) they only adopted the
> above in 4.0.2.  But it's better than no precedent.  And frankly I
> was having a big problem with "DELETE FROM target FROM others ..."
> If that's not a recipe for confusion I don't know what is.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: Add Missing From?

From
Tom Lane
Date:
Harald Fuchs <hf0722x@protecting.net> writes:
> Actually, MySQL supports two different syntaxes for multi-table DELETEs:

> 1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
>    (introduced in MySQL 4.0.0)

> 2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
>    (introduced in MySQL 4.0.2)

Yeah.  I ignored the first, as being so stupid that even the MySQL guys
soon realized what a bad idea it was ;-)
        regards, tom lane


Re: Add Missing From?

From
Josh Berkus
Date:
People,

> >       DELETE FROM target_tbl USING other_tbls WHERE ...
>
> Feels much more understandable. The second FROM looks like a hickup.

Yes, although imagine:

DELETE FROM staff USING users JOIN logons USING (user_id)
WHERE last_logon < ( now() - '6 months');

Not as bad as FROM, but still a bit baffling to look at.   Still, I can't 
think of anything else that wouldn't require inventing a new reserved word.

Oh, and MySQL's "multi-table deletes":  PLEASE tell me that's not 
SQL-standard.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Add Missing From?

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
> What about
> DELETE FROM staff JOIN users ...
> then?

I don't much care for that, mainly because in my mind "x JOIN y" should
always be semantically equivalent to "y JOIN x".  I think we want a real
clear syntactical separation between the deletion target table and the
other tables.

Also we do have the precedent of the way that UPDATE does things.  We
don't want to use the keyword FROM because of confusion, but I think
we want to keep it basically the same as UPDATE.
        regards, tom lane


Re: Add Missing From?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Oh, and MySQL's "multi-table deletes":  PLEASE tell me that's not 
> SQL-standard.

It's not.
        regards, tom lane


Re: Add Missing From?

From
Jan Wieck
Date:
On 8/9/2004 12:53 PM, Josh Berkus wrote:

> People,
> 
>> >       DELETE FROM target_tbl USING other_tbls WHERE ...
>>
>> Feels much more understandable. The second FROM looks like a hickup.
> 
> Yes, although imagine:
> 
> DELETE FROM staff USING users JOIN logons USING (user_id)
> WHERE last_logon < ( now() - '6 months');
> 
> Not as bad as FROM, but still a bit baffling to look at.   Still, I can't 
> think of anything else that wouldn't require inventing a new reserved word.

What about

DELETE FROM staff JOIN users ...

then?

> 
> Oh, and MySQL's "multi-table deletes":  PLEASE tell me that's not 
> SQL-standard.
> 

Yes, not standard.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: Add Missing From?

From
Harald Fuchs
Date:
In article <16580.1092071243@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Jan Wieck <JanWieck@Yahoo.com> writes:
>> What about
>> DELETE FROM staff JOIN users ...
>> then?

> I don't much care for that, mainly because in my mind "x JOIN y" should
> always be semantically equivalent to "y JOIN x".  I think we want a real
> clear syntactical separation between the deletion target table and the
> other tables.

Just one minor nit:

You're talking about "the deletion target table".  Sorry to mention
the M word again, but MySQL allows deleting from more than one table
at the same time.  Should we support that?



Re: Add Missing From?

From
Stephan Szabo
Date:
On Tue, 10 Aug 2004, Harald Fuchs wrote:

> In article <16580.1092071243@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > Jan Wieck <JanWieck@Yahoo.com> writes:
> >> What about
> >> DELETE FROM staff JOIN users ...
> >> then?
>
> > I don't much care for that, mainly because in my mind "x JOIN y" should
> > always be semantically equivalent to "y JOIN x".  I think we want a real
> > clear syntactical separation between the deletion target table and the
> > other tables.
>
> Just one minor nit:
>
> You're talking about "the deletion target table".  Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time.  Should we support that?

I don't think we should unless we can give a good definition to its
behavior.  The message (on -sql?) on UPDATE...FROM doing not what the user
expected when the join wasn't 1-1 gives a good example of some of the
issues that come from these sorts of extensions.

I don't see anything in the MySQL delete syntax docs that talks about what
happens in that case.  If you do:

delete from foo, bar where bar.val=3;

Is that an error, is it only an error if one of foo or bar has more than
1 row? In the case both have more than 1 row, does it delete all rows in
foo or none or something else?


Re: Add Missing From?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>   There is no way to specify a table alias for the deleted table in
>   the DELETE WHERE clause because there is no FROM clause.

This description confuses two quite separate issues.
        regards, tom lane


Re: Add Missing From?

From
Christopher Kings-Lynne
Date:
> You're talking about "the deletion target table".  Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time.  Should we support that?

No, because it makes no logical sense at all...

Chris



Re: Add Missing From?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >   There is no way to specify a table alias for the deleted table in
> >   the DELETE WHERE clause because there is no FROM clause.
> 
> This description confuses two quite separate issues.

Yea, it does.

How is this text:

* Allow DELETE to handle table aliases for self-joins
 There is no way to create a table alias for the deleted table for use in the DELETE WHERE clause.  The agreed approach
isto allow a USING clause to specify additional tables.  UPDATE already has an optional FROM clause for this purpose.
 

--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Bruce Momjian
Date:
I have added the USING mention to the TODO list description for the
item.

* Allow DELETE to handle table aliases for self-joins [delete]
 There is no way to specify a table alias for the deleted table in the DELETE WHERE clause because there is no FROM
clause.The agreed approach is to allow a USING clause to specify additional tables with aliases.  UPDATE already has
suchan optional FROM clause.
 


---------------------------------------------------------------------------

=Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > What about
> > DELETE FROM staff JOIN users ...
> > then?
> 
> I don't much care for that, mainly because in my mind "x JOIN y" should
> always be semantically equivalent to "y JOIN x".  I think we want a real
> clear syntactical separation between the deletion target table and the
> other tables.
> 
> Also we do have the precedent of the way that UPDATE does things.  We
> don't want to use the keyword FROM because of confusion, but I think
> we want to keep it basically the same as UPDATE.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Tom Lane
Date:
Harald Fuchs <hf0722x@protecting.net> writes:
> You're talking about "the deletion target table".  Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time.  Should we support that?

There is zero interest in that around here, AFAIK.  I don't think it's
even very well-defined...
        regards, tom lane


Re: Add Missing From?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >>> * Allow an alias to be provided for the target table in UPDATE/DELETE
> 
> > Yea, I guess for a long table that also needed an alias you would have
> > to specify the long name every time you reference the table.  However,
> > we haven't had anyone ask for that capability,
> 
> Yes we have.  Do I need to trawl the archives for you?  It's been asked
> for quite a few times.

OK, TODO updated:

* Allow an alias to be provided for the target table in UPDATE/DELETE
 This is not SQL-spec but many DBMSs allow it.

* Allow additional tables to be specified in DELETE for joins
 UPDATE already allows this (UPDATE...FROM) but we need similar functionality in DELETE.  It's been agreed that the
keywordshould be USING, to avoid anything as confusing as DELETE FROM a FROM b.
 



--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Josh Berkus
Date:
Harald,

> You're talking about "the deletion target table".  Sorry to mention
> the M word again, but MySQL allows deleting from more than one table
> at the same time.  Should we support that?

Nope.   In fact, I'd argue pretty strongly against any move to do so.

MySQL supports multi-table delete for 2 reasons:
1) MySQL does not support CASCADE Foriegn Keys, and as a result this is the
only way they can "clean out" all related records.
2) To be blunt: MySQL doesn't care about your data.  Just look at their
implementation of constraints.

Since we do support CASCADE FKs, there is no reason for us to support this
syntax; just set your FKs up correctly and it's taken care of.   Were we to
consider implementing this, the implementors would have to answer the
following questions:

A) In what order are the rows deleted, from which table first?
B) If no join criteria are supplied, is it OK to delete all rows from one of
the tables?  If not, how do you deliberately clean out a table?
C) If one of the tables had FKs or triggers that affect one of the other
tables, when do these get evaluated/fired?

Overall, I consider it a very, very bad idea.

--
-Josh Berkus"A developer of Very Little Brain"Aglio Database SolutionsSan Francisco



Re: Add Missing From?

From
Josh Berkus
Date:
Bruce,

> Yea, I guess for a long table that also needed an alias you would have
> to specify the long name every time you reference the table.  However,
> we haven't had anyone ask for that capability, even for UPDATE which
> does already have that limitation.  Seems like a new TODO item but I am
> not sure anyone wants it.

Oh, I think many people want this (I'd use it) but it's not in anybody's "top 
10" list which is why you don't hear about it.   I'd say throw it in the TODO 
in case some university student studying query parsers wants to implement it 
for us at some undefined date in the future.    Also, it will spare you 
questions about it later ... "It's in the TODO!"

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Add Missing From?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> * Allow an alias to be provided for the target table in UPDATE/DELETE

> Yea, I guess for a long table that also needed an alias you would have
> to specify the long name every time you reference the table.  However,
> we haven't had anyone ask for that capability,

Yes we have.  Do I need to trawl the archives for you?  It's been asked
for quite a few times.
        regards, tom lane


Re: Add Missing From?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> * Allow an alias to be provided for the target table in UPDATE/DELETE
> >> 
> >> This is not SQL-spec but many DBMSs allow it.
> 
> > I don't think we would ever do the above item.
> 
> Why not?  You can hardly argue that "it's not SQL spec" while
> simultaneously putting in a much larger extension to the spec.
> 
> > I see no purpose to
> > allowing the UPDATE/DELETE table to have an alias
> 
> Brevity.  If you are really updating AVeryLongSchemaName.AVeryLongTableName
> then you would reasonably want to have an alias for that.  In the pure
> SQL-spec syntax it's not so important because there's seldom a need to
> name the table again within the query.  But in a self-join situation you
> would be forced to name the target table within the query, and then a
> shorter alias would be both easier to type and less prone to confusion.
> (Even in pure SQL, you may need to name the table again for references
> within subqueries that have duplicate column names.)

Yea, I guess for a long table that also needed an alias you would have
to specify the long name every time you reference the table.  However,
we haven't had anyone ask for that capability, even for UPDATE which
does already have that limitation.  Seems like a new TODO item but I am
not sure anyone wants it.

--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> * Allow an alias to be provided for the target table in UPDATE/DELETE
>> 
>> This is not SQL-spec but many DBMSs allow it.

> I don't think we would ever do the above item.

Why not?  You can hardly argue that "it's not SQL spec" while
simultaneously putting in a much larger extension to the spec.

> I see no purpose to
> allowing the UPDATE/DELETE table to have an alias

Brevity.  If you are really updating AVeryLongSchemaName.AVeryLongTableName
then you would reasonably want to have an alias for that.  In the pure
SQL-spec syntax it's not so important because there's seldom a need to
name the table again within the query.  But in a self-join situation you
would be forced to name the target table within the query, and then a
shorter alias would be both easier to type and less prone to confusion.
(Even in pure SQL, you may need to name the table again for references
within subqueries that have duplicate column names.)
        regards, tom lane


Re: Add Missing From?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> This description confuses two quite separate issues.
> 
> > Yea, it does.
> 
> > How is this text:
> 
> > * Allow DELETE to handle table aliases for self-joins
> 
> >   There is no way to create a table alias for the deleted table for use
> >   in the DELETE WHERE clause.  The agreed approach is to allow a USING
> >   clause to specify additional tables.  UPDATE already has an optional
> >   FROM clause for this purpose.
> 
> Not a lot better.  They really should be two separate issues, because we
> could in theory do either without the other.
> 
>     * Allow an alias to be provided for the target table in UPDATE/DELETE
> 
>     This is not SQL-spec but many DBMSs allow it.

I don't think we would ever do the above item.  I see no purpose to
allowing the UPDATE/DELETE table to have an alias as long as you can
create another reference to the table that does have an alias.  In fact,
having a alias for the deleted item seems too prone to confusion.

--  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,
Pennsylvania19073
 


Re: Add Missing From?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> This description confuses two quite separate issues.

> Yea, it does.

> How is this text:

> * Allow DELETE to handle table aliases for self-joins

>   There is no way to create a table alias for the deleted table for use
>   in the DELETE WHERE clause.  The agreed approach is to allow a USING
>   clause to specify additional tables.  UPDATE already has an optional
>   FROM clause for this purpose.

Not a lot better.  They really should be two separate issues, because we
could in theory do either without the other.
* Allow an alias to be provided for the target table in UPDATE/DELETE
This is not SQL-spec but many DBMSs allow it.
* Allow additional tables to be specified in DELETE for joining
UPDATE already allows this (UPDATE...FROM) and we need a similarability in DELETE.  It's been agreed that the keyword
shouldbeUSING, to avoid anything so confusing as DELETE FROM a FROM b.
 

I have not looked to see whether or not there are already entries
similar to these.
        regards, tom lane


Re: Add Missing From?

From
Tom Lane
Date:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> The USING would be in place of the mandatory FROM in MSSQL. And why use 
> a different keyword for the same thing in DELETE and UPDATE?

Please read the earlier part of the thread... this ground was covered
already.

As for emulating MSSQL's syntax, we are *not* about to emulate their
semantics, so it seems a good idea not to use the same syntax.
        regards, tom lane


Re: Add Missing From?

From
Josh Berkus
Date:
Andreas,

> The USING would be in place of the mandatory FROM in MSSQL. And why use 
> a different keyword for the same thing in DELETE and UPDATE?

Um, because for us the 1st FROM isn't optional (per SQL spec)?

-- 
-Josh Berkus"A developer of Very Little Brain"Aglio Database SolutionsSan Francisco



Re: Add Missing From?

From
Andreas Pflug
Date:
Bruce Momjian wrote:
It's been agreed that the keyword should
>   be USING, to avoid anything as confusing as DELETE FROM a FROM b.

FYI,

MSSQL says

DELETE [FROM]    {tablename} [FROM {<tablesource> [, ...] ] [WHERE <condition>]


Note that the first FROM is optional (as in Oracle), we can have

DELETE a FROM b

The USING would be in place of the mandatory FROM in MSSQL. And why use 
a different keyword for the same thing in DELETE and UPDATE?

DELETE a FROM b
UPDATE a FROM b
DELETE FROM a USING b


Regards,
Andreas


Re: Add Missing From?

From
Andreas Pflug
Date:
Josh Berkus wrote:
> Andreas,
> 
> 
>>The USING would be in place of the mandatory FROM in MSSQL. And why use 
>>a different keyword for the same thing in DELETE and UPDATE?
> 
> 
> Um, because for us the 1st FROM isn't optional (per SQL spec)?
> 

Seems that many RDBMS regard that FROM as a noise word. Making it 
optional would probably enhance portability, I can't see drawbacks.

Regards,
Andreas




Re: Add Missing From?

From
Harald Fuchs
Date:
In article <200408101156.19796.josh@agliodbs.com>,
Josh Berkus <josh@agliodbs.com> writes:

> Harald,
>> You're talking about "the deletion target table".  Sorry to mention
>> the M word again, but MySQL allows deleting from more than one table
>> at the same time.  Should we support that?

> Nope.   In fact, I'd argue pretty strongly against any move to do so.

> MySQL supports multi-table delete for 2 reasons:
> 1) MySQL does not support CASCADE Foriegn Keys, and as a result this is the
> only way they can "clean out" all related records.

Not quite - MySQL implemented ON DELETE CASCADE for foreign keys
_before_ multi-table DELETEs.

> 2) To be blunt: MySQL doesn't care about your data.

I know - what do you think why I'm lurking here? ;-)

> Since we do support CASCADE FKs, there is no reason for us to support this
> syntax; just set your FKs up correctly and it's taken care of.   Were we to
> consider implementing this, the implementors would have to answer the
> following questions:

> A) In what order are the rows deleted, from which table first?

In exactly the same order as for single-table DELETEs -
implementation-defined.

> B) If no join criteria are supplied, is it OK to delete all rows from one of
> the tables?

Yes - people creating Cartesian products deserve punishment :-)

> C) If one of the tables had FKs or triggers that affect one of the other
> tables, when do these get evaluated/fired?

Implementation-defined.

> Overall, I consider it a very, very bad idea.

My main concern was not multi-table DELETEs per se, but a way to do
deletions based on results of arbitrary queries.  Multi-table DELETEs
would just be a logical extension to that.

I thought about something like that:
 DELETE [tbl [,tbl]...] FROM fromexp

"fromexp" could be anything which is legal after a "SELECT ... FROM",
including outer joins and LIMIT clauses.
"tbl" could be names or aliases of tables used in fromexp.  If none
supplied, this would default to all tables used there, thus making
"DELETE FROM t1 WHERE whatever" just a degenerate case.

The semantics of that would be:
1. Do a "SELECT * FROM fromexp"
2. For every tbl, delete everything covered by the result set of the  SELECT, in some arbitrary order (unless
restrictedby an ORDER BY) 



Re: Add Missing From?

From
Stephan Szabo
Date:
On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <200408101156.19796.josh@agliodbs.com>,
> Josh Berkus <josh@agliodbs.com> writes:

> > A) In what order are the rows deleted, from which table first?
>
> In exactly the same order as for single-table DELETEs -
> implementation-defined.

I think you probably meant in an unspecified order.
Implementation-defined really doesn't mean anything when you're trying to
define what it means for a particular implementation. ;)

Unless it's unspecified, changing the order later may be hard because
people will be using it, and changes will almost certainly break things
for some of those people. IMHO, this means we should choose a behavior
even if it's explicitly unspecified, rather than just letting it go.

> > B) If no join criteria are supplied, is it OK to delete all rows from one of
> > the tables?
>
> Yes - people creating Cartesian products deserve punishment :-)

What we do here should be related to what we would want to happen on
a view with a join if we were to make automatically updatable views.

> > C) If one of the tables had FKs or triggers that affect one of the other
> > tables, when do these get evaluated/fired?
>
> Implementation-defined.

See A.

> > Overall, I consider it a very, very bad idea.
>
> My main concern was not multi-table DELETEs per se, but a way to do
> deletions based on results of arbitrary queries.  Multi-table DELETEs
> would just be a logical extension to that.
>
> I thought about something like that:
>
>   DELETE [tbl [,tbl]...]
>   FROM fromexp
>
> "fromexp" could be anything which is legal after a "SELECT ... FROM",
> including outer joins and LIMIT clauses.

Including subselects in FROM? That's a big barrel of fish.  How does this
interact with subselects with union or distinct or group by.


Re: Add Missing From?

From
Harald Fuchs
Date:
In article <20040811074837.Q17280@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

>> In exactly the same order as for single-table DELETEs -
>> implementation-defined.

> I think you probably meant in an unspecified order.
> Implementation-defined really doesn't mean anything when you're trying to
> define what it means for a particular implementation. ;)

You're right - what I meant was something like "the same order in
which SELECT without an ORDER BY returns its rows" - something you
can't rely upon.

>> > B) If no join criteria are supplied, is it OK to delete all rows from one of
>> > the tables?
>> 
>> Yes - people creating Cartesian products deserve punishment :-)

> What we do here should be related to what we would want to happen on
> a view with a join if we were to make automatically updatable views.

I think automatically updatable views are something like views with
automatically generated INSERT/UPDATE/DELETE rules, aren't they?

Well, my hypothetical multi-table-DELETE would then call the DELETE
rule.  Where do you see a problem?

>> I thought about something like that:
>> 
>> DELETE [tbl [,tbl]...]
>> FROM fromexp
>> 
>> "fromexp" could be anything which is legal after a "SELECT ... FROM",
>> including outer joins and LIMIT clauses.

> Including subselects in FROM? That's a big barrel of fish.  How does this
> interact with subselects with union or distinct or group by.

Ouch, didn't think about that.

The general problem seems to be that a table can occur at many places
within one query, and at each place different rows are matched.  On
the top level this should not be a problem: just specify the correct
table alias between DELETE and FROM.

Perhaps we could disallow deleting from tables/aliases in deeper
subselect levels?



Re: Add Missing From?

From
Stephan Szabo
Date:
On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <20040811074837.Q17280@megazone.bigpanda.com>,
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
> >> In exactly the same order as for single-table DELETEs -
> >> implementation-defined.
>
> > I think you probably meant in an unspecified order.
> > Implementation-defined really doesn't mean anything when you're trying to
> > define what it means for a particular implementation. ;)
>
> You're right - what I meant was something like "the same order in
> which SELECT without an ORDER BY returns its rows" - something you
> can't rely upon.

Right, the reason it's important is that there are some things now that
are potentially tied together. If you have table A with rows A1,...,An and
table B with rows B1,...,Bm and the delete join condition gives the two
outputs (A1,B1) and (A2,B1) does a before trigger returning NULL for B1
prevent A1 and A2 from being deleted?  Do row triggers for B1 get run
twice? If the row trigger for B1 is run twice and say returns NULL on the
first but not the second, do A1 and B1 get deleted but not A2?  I'm also
afraid that running the triggers multiple times could actually break
people's current triggers.  Also, if we do allow multiple table at a time
deletes, should we be considering multiple table at a time updates, and if
so, what works for those.

I'm also not sure how rules apply to these multi-table delete statements.

> >> > B) If no join criteria are supplied, is it OK to delete all rows from one of
> >> > the tables?
> >>
> >> Yes - people creating Cartesian products deserve punishment :-)
>
> > What we do here should be related to what we would want to happen on
> > a view with a join if we were to make automatically updatable views.
>
> I think automatically updatable views are something like views with
> automatically generated INSERT/UPDATE/DELETE rules, aren't they?
>
> Well, my hypothetical multi-table-DELETE would then call the DELETE
> rule.  Where do you see a problem?

It's not a problem, but I'd think that:

delete from foo, bar where foo.val=3;
should probably act similarly to:
create view v as select * from foo,bar where foo.val=3;
delete from v;
or probably even:
create view v as select * from foo,bar;
delete from v where val=3;

If that's true, we're not merely defining behavior for the first, but
defining what we expect to make the behavior for the latter two as well so
we should think about that as well.

> >> I thought about something like that:
> >>
> >> DELETE [tbl [,tbl]...]
> >> FROM fromexp
> >>
> >> "fromexp" could be anything which is legal after a "SELECT ... FROM",
> >> including outer joins and LIMIT clauses.
>
> > Including subselects in FROM? That's a big barrel of fish.  How does this
> > interact with subselects with union or distinct or group by.
>
> Ouch, didn't think about that.
>
> The general problem seems to be that a table can occur at many places
> within one query, and at each place different rows are matched.  On
> the top level this should not be a problem: just specify the correct
> table alias between DELETE and FROM.

That's not quite the issue I was thinking of. I was thinking of cases
where the output rows are not directly/easily connected to base table
rows, likedelete foo from (select sum(a) from tab group by b having sum(a)>10) foo;

> Perhaps we could disallow deleting from tables/aliases in deeper
> subselect levels?

That's probably a good thing for any first implementation. It'd probably
still be good to think about those cases to not box out possible future
enhancements.


Re: Add Missing From?

From
Harald Fuchs
Date:
In article <20040811110840.D23732@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

> Right, the reason it's important is that there are some things now that
> are potentially tied together. If you have table A with rows A1,...,An and
> table B with rows B1,...,Bm and the delete join condition gives the two
> outputs (A1,B1) and (A2,B1) does a before trigger returning NULL for B1
> prevent A1 and A2 from being deleted?  Do row triggers for B1 get run
> twice?

Me thinks that at the time of deletion the join between A and B should
not matter any more.  The DELETE code would see A1 and A2, and it
would see B1.  Thus a B1 trigger would get called only once.

By the way, this would be an issue also for singe-table DELETEs.

> Also, if we do allow multiple table at a time
> deletes, should we be considering multiple table at a time updates, and if
> so, what works for those.

IMHO multi-table UPDATEs would be much harder because in this case the
join conditions would matter at update time: which row in A would get
updated with values from which row in B?

> I'm also not sure how rules apply to these multi-table delete statements.

See above.  If we break the join relationships before deletion we can
issue DELETE requests "logically sequentially", and these requests
would do the right thing: either fire a trigger or get changed by a
rule.

>> Well, my hypothetical multi-table-DELETE would then call the DELETE
>> rule.  Where do you see a problem?

> It's not a problem, but I'd think that:

> delete from foo, bar where foo.val=3;

>  should probably act similarly to:
> create view v as select * from foo,bar where foo.val=3;
> delete from v;

>  or probably even:
> create view v as select * from foo,bar;
> delete from v where val=3;

> If that's true, we're not merely defining behavior for the first, but
> defining what we expect to make the behavior for the latter two as well so
> we should think about that as well.

That's right, but I think it would be well-defined if we say "build
the result set and then for each table seperately delete all distinct
rows covered by the result set".  In either case we would end up with
deleteing some rows in foo and all rows in bar.

>> The general problem seems to be that a table can occur at many places
>> within one query, and at each place different rows are matched.  On
>> the top level this should not be a problem: just specify the correct
>> table alias between DELETE and FROM.

> That's not quite the issue I was thinking of. I was thinking of cases
> where the output rows are not directly/easily connected to base table
> rows, like
>  delete foo from (select sum(a) from tab group by b having sum(a)>10) foo;

>> Perhaps we could disallow deleting from tables/aliases in deeper
>> subselect levels?

> That's probably a good thing for any first implementation. It'd probably
> still be good to think about those cases to not box out possible future
> enhancements.

As you correctly pointed out above, this is a similar problem to
updatable views.  Does anyone know what the SQL standard says about
those beasts?