SELECT for UPDATE and outer join?

Started by Josh Berkusover 20 years ago2 messages
#1Josh Berkus
josh@agliodbs.com

Folks,

------------------------------
SQL statement<select t0.po_number, t0.po_site_id, t0.po_supp_id,
t1.pol_number,
t1.pol_po_id, t1.pol_balance, t1.pol_qty, t1.pol_p_id, t1.pol_message,
t1.pol_de
ldate from  s_purchase_order t0  left outer join s_purchase_orderline
t1  on t0.
po_number = t1.pol_po_id   where t0.po_number = ? for update > with
input values
:java.lang.Integer:30239.
Please examine the SQLException for more information.
NestedException: java.sql.SQLException: ERROR: SELECT FOR UPDATE cannot
be applied to the nullable side of an outer join
-----------------------------

So, my question is: why can't we apply FOR UPDATE to an outer join? Is this
defined in the SQL Spec?

--
Josh Berkus
Aglio Database Solutions
San Francisco

#2William ZHANG
uniware@zedware.org
In reply to: Josh Berkus (#1)
Re: SELECT for UPDATE and outer join?

I find the comments in initsplan.c as following.

/*
* Presently the executor cannot support FOR UPDATE/SHARE marking of
* rels appearing on the nullable side of an outer join. (It's
* somewhat unclear what that would mean, anyway: what should we
* mark when a result row is generated from no element of the
* nullable relation?) So, complain if target rel is FOR UPDATE/SHARE.
* It's sufficient to make this check once per rel, so do it only
* if rel wasn't already known nullable.
*/

IIRC, SQL:2003 also says the nullable side is not updatable, since it is not
key-preserved. See 7.12 <query specification> of ISO/IEC 9075-2:2003.
And I also got the idea from Oracle's SQL manual.

"Josh Berkus" <josh@agliodbs.com> wrote
news:200508110843.12819.josh@agliodbs.com...

Folks,

------------------------------
SQL statement<select t0.po_number, t0.po_site_id, t0.po_supp_id,
t1.pol_number,
t1.pol_po_id, t1.pol_balance, t1.pol_qty, t1.pol_p_id, t1.pol_message,
t1.pol_de
ldate from s_purchase_order t0 left outer join s_purchase_orderline
t1 on t0.
po_number = t1.pol_po_id where t0.po_number = ? for update > with
input values
:java.lang.Integer:30239.
Please examine the SQLException for more information.
NestedException: java.sql.SQLException: ERROR: SELECT FOR UPDATE cannot
be applied to the nullable side of an outer join
-----------------------------

So, my question is: why can't we apply FOR UPDATE to an outer join? Is

this

Show quoted text

defined in the SQL Spec?

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly