Minor buglet in update...from (I think)

I can confirm this is still a bug.

A minor nasty error I got when trying to improve the query used to disable
triggers:

create table t1(f1 int4, f2 int4);
create table t2(f1 int4, f2 int4);

insert into t1 values(1, 0);
insert into t1 values(2, 0);

insert into t2 values(1, 0);

update t1 set f2=count(*) from t2 where t1.f1=1 and t2.f1=t1.f1 ;
UPDATE 1

update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
ERROR: ExecutePlan: (junk) `ctid' is NULL!

I would have expected no update to occur since no rows match.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

This still throws a strange error. Someone want to fix it?

A minor nasty error I got when trying to improve the query used to disable
triggers:

create table t1(f1 int4, f2 int4);
create table t2(f1 int4, f2 int4);

insert into t1 values(1, 0);
insert into t1 values(2, 0);

insert into t2 values(1, 0);

update t1 set f2=count(*) from t2 where t1.f1=1 and t2.f1=t1.f1 ;
UPDATE 1

update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
ERROR: ExecutePlan: (junk) `ctid' is NULL!

I would have expected no update to occur since no rows match.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Can anyone explain this failure? It still exists in CVS.

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

A minor nasty error I got when trying to improve the query used to disable
triggers:

create table t1(f1 int4, f2 int4);
create table t2(f1 int4, f2 int4);

insert into t1 values(1, 0);
insert into t1 values(2, 0);

insert into t2 values(1, 0);

update t1 set f2=count(*) from t2 where t1.f1=1 and t2.f1=t1.f1 ;
UPDATE 1

update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
ERROR: ExecutePlan: (junk) `ctid' is NULL!

I would have expected no update to occur since no rows match.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Can anyone explain this failure? It still exists in CVS.

update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
ERROR: ExecutePlan: (junk) `ctid' is NULL!

As I recall, discussion about fixing that problem trailed off because
no one could explain what an aggregate means in UPDATE. My thought
is we should probably forbid the construct entirely (SQL does).
See previous discussion around 7/7/00.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Can anyone explain this failure? It still exists in CVS.

update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ;
ERROR: ExecutePlan: (junk) `ctid' is NULL!

As I recall, discussion about fixing that problem trailed off because
no one could explain what an aggregate means in UPDATE. My thought
is we should probably forbid the construct entirely (SQL does).
See previous discussion around 7/7/00.

Oh, so it is the aggregate. What threw me off is that both parts of the
WHERE clause are required to cause the failure, so I thought it was
something else.

I don't see a problem with aggregates in UPDATE, except when the updated
field is part of the WHERE clause, but even then, transaction semantics
should make it matter. I see the mailist thread now.

I will try and get it added to the TODO list so it is documented.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Oh, so it is the aggregate. What threw me off is that both parts of the
WHERE clause are required to cause the failure,

Not necessarily; I think it's got more to do with a null aggregate
result:

regression=# create table t1 (f1 datetime);
CREATE
regression=# create table t2 (f2 datetime);
CREATE
regression=# update t2 set f2 = min(f1) from t1;
ERROR: ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t1 values ('now');
INSERT 400577 1
regression=# update t2 set f2 = min(f1) from t1;
ERROR: ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t2 values ('now');
INSERT 400578 1
regression=# update t2 set f2 = min(f1) from t1;
UPDATE 1
regression=#

However the ERROR is only one symptom. The real problem is that the
calculation that's being done is useless/nonsensical.

I don't see a problem with aggregates in UPDATE,

Think harder ... what is the aggregate being taken over, and how do you
associate the aggregate's single result row with any particular row in
the UPDATE's target table?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Oh, so it is the aggregate. What threw me off is that both parts of the
WHERE clause are required to cause the failure,

Not necessarily; I think it's got more to do with a null aggregate
result:

regression=# create table t1 (f1 datetime);
CREATE
regression=# create table t2 (f2 datetime);
CREATE
regression=# update t2 set f2 = min(f1) from t1;
ERROR: ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t1 values ('now');
INSERT 400577 1
regression=# update t2 set f2 = min(f1) from t1;
ERROR: ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t2 values ('now');
INSERT 400578 1
regression=# update t2 set f2 = min(f1) from t1;
UPDATE 1
regression=#

However the ERROR is only one symptom. The real problem is that the
calculation that's being done is useless/nonsensical.

I don't see a problem with aggregates in UPDATE,

Think harder ... what is the aggregate being taken over, and how do you
associate the aggregate's single result row with any particular row in
the UPDATE's target table?

I thought the aggregate would be generated on all rows in the table in
the pre-transaction version of the table, so in this example:

regression=# update t2 set f2 = min(f1) from t1;

It places the minimum value of t1.f1 in all t2.f2 rows. Is there
another way to look at it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I thought the aggregate would be generated on all rows in the table in
the pre-transaction version of the table, so in this example:
regression=# update t2 set f2 = min(f1) from t1;
It places the minimum value of t1.f1 in all t2.f2 rows.

This actually is not the most interesting example, because the aggregate
doesn't depend at all on t2. Try this instead:

regression=# create table t1(f1 int);
CREATE
regression=# create table t2(f1 int);
CREATE
regression=# insert into t1 values(-1);
INSERT 400599 1
regression=# insert into t1 values(-2);
INSERT 400600 1
regression=# insert into t1 values(-3);
INSERT 400601 1
regression=# insert into t2 values(-1);
INSERT 400602 1
regression=# insert into t2 values(-2);
INSERT 400603 1
regression=# insert into t2 values(-3);
INSERT 400604 1
regression=# update t2 set f1 = count(*) from t1;
UPDATE 1
regression=# select * from t2;
f1
----
-2
-3
9
(3 rows)

regression=#

This is certainly broken, but what's the correct behavior?
Or how about this, which doesn't even use an aggregate:

regression=# update t2 set f1 = t1.f1 from t1;
UPDATE 3
regression=# select * from t2;
f1
----
-1
-1
-1
(3 rows)

regression=#

That's surprising too, perhaps, but what would you have expected
and why?

There's a reason why SQL99 forbids joins and aggregates in UPDATE ...
they're not always well-defined.

I had a proposal (GROUP BY ctid) in the older thread for fixing the
aggregate misbehavior, but it doesn't solve the more general problem
of a join that produces multiple matches for the same target row.
Seems like that probably ought to draw an error.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I thought the aggregate would be generated on all rows in the table in
the pre-transaction version of the table, so in this example:
regression=# update t2 set f2 = min(f1) from t1;
It places the minimum value of t1.f1 in all t2.f2 rows.

This actually is not the most interesting example, because the aggregate
doesn't depend at all on t2. Try this instead:

regression=# create table t1(f1 int);
CREATE
regression=# create table t2(f1 int);
CREATE
regression=# insert into t1 values(-1);
INSERT 400599 1
regression=# insert into t1 values(-2);
INSERT 400600 1
regression=# insert into t1 values(-3);
INSERT 400601 1
regression=# insert into t2 values(-1);
INSERT 400602 1
regression=# insert into t2 values(-2);
INSERT 400603 1
regression=# insert into t2 values(-3);
INSERT 400604 1
regression=# update t2 set f1 = count(*) from t1;
UPDATE 1
regression=# select * from t2;
f1
----
-2
-3
9
(3 rows)

regression=#

This is certainly broken, but what's the correct behavior?

Shouldn't it be 9 because there is no join of t1 and t2?
I can also see 3 as a valid answer.

Or how about this, which doesn't even use an aggregate:

regression=# update t2 set f1 = t1.f1 from t1;
UPDATE 3
regression=# select * from t2;
f1
----
-1
-1
-1
(3 rows)

regression=#

That's surprising too, perhaps, but what would you have expected
and why?

So it grabs the first match. Seems reasonable because t1 returns more
than one row.

There's a reason why SQL99 forbids joins and aggregates in UPDATE ...
they're not always well-defined.

Yes, I see that now.

I had a proposal (GROUP BY ctid) in the older thread for fixing the
aggregate misbehavior, but it doesn't solve the more general problem
of a join that produces multiple matches for the same target row.
Seems like that probably ought to draw an error.

Or a NOTICE stating a random row was chosen.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thread added to TODO.detail.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I thought the aggregate would be generated on all rows in the table in
the pre-transaction version of the table, so in this example:
regression=# update t2 set f2 = min(f1) from t1;
It places the minimum value of t1.f1 in all t2.f2 rows.

This actually is not the most interesting example, because the aggregate
doesn't depend at all on t2. Try this instead:

regression=# create table t1(f1 int);
CREATE
regression=# create table t2(f1 int);
CREATE
regression=# insert into t1 values(-1);
INSERT 400599 1
regression=# insert into t1 values(-2);
INSERT 400600 1
regression=# insert into t1 values(-3);
INSERT 400601 1
regression=# insert into t2 values(-1);
INSERT 400602 1
regression=# insert into t2 values(-2);
INSERT 400603 1
regression=# insert into t2 values(-3);
INSERT 400604 1
regression=# update t2 set f1 = count(*) from t1;
UPDATE 1
regression=# select * from t2;
f1
----
-2
-3
9
(3 rows)

regression=#

This is certainly broken, but what's the correct behavior?
Or how about this, which doesn't even use an aggregate:

regression=# update t2 set f1 = t1.f1 from t1;
UPDATE 3
regression=# select * from t2;
f1
----
-1
-1
-1
(3 rows)

regression=#

That's surprising too, perhaps, but what would you have expected
and why?

There's a reason why SQL99 forbids joins and aggregates in UPDATE ...
they're not always well-defined.

I had a proposal (GROUP BY ctid) in the older thread for fixing the
aggregate misbehavior, but it doesn't solve the more general problem
of a join that produces multiple matches for the same target row.
Seems like that probably ought to draw an error.

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026