Thread: Query failing with strange error.

In the middle of a script, inside a transaction, I run the following
query:

update customer_contact
 set next_billed=min(customer_services.eff_date)
 where customer_contact.conn_num=7698
  and customer_services.conn_num=7698
  and customer_services.inv_num=0

I have also tried changing this to

update customer_contact
 set next_billed=min(customer_services.eff_date)
 where customer_contact.conn_num=7698
  and customer_services.conn_num=customer_contact.conn_num
  and customer_services.inv_num=0

... which changes the query plan, but both give the following error:

ERROR:  ExecutePlan: (junk) `ctid' is NULL!

Now... I tried dumping and reloading the database.  Then I upgraded
from 7.2.1 to 7.2.3 (and reloaded the database from scratch).  Neither
worked.

The odd part about the error is that this query gets executed 100's of
times a day with different conn_num values.  Only this conn_num gives
the error.  This conn_num exists in both tables.  eff_date has a
senable value (it's a 'timestamp with timezone').  In fact:

sales=# select min(next_billed),min(customer_services.eff_date)
     from customer_contact, customer_services
     where customer_contact.conn_num=7698
      and customer_services.conn_num=7698
      and customer_services.inv_num=0;

    min     |          min
------------+------------------------
 2003-01-23 | 2003-01-23 00:00:00-05
(1 row)

Anyone got any ideas?

Dave.

--
============================================================================
|David Gilbert, Velocet Communications.       | Two things can only be     |
|Mail:       dgilbert@velocet.net             |  equal if and only if they |
|http://daveg.ca                              |   are precisely opposite.  |
=========================================================GLO================

David Gilbert <dgilbert@velocet.ca> writes:
> update customer_contact
>  set next_billed=min(customer_services.eff_date)
>  where customer_contact.conn_num=7698
>   and customer_services.conn_num=7698
>   and customer_services.inv_num=0

This is not a well-defined query --- exactly what do you think the
semantics should be?  Over what set of rows is the MIN() taken, for
any particular target row to be updated?  With only one WHERE clause,
you've got no way to control the set of rows the MIN() scans separately
from the set of rows the UPDATE targets.

SQL92 forbids such things outright:

         <update statement: searched> ::=
              UPDATE <table name>
                SET <set clause list>
                [ WHERE <search condition> ]

         Syntax Rules

         2) A <value expression> in a <set clause> shall not directly con-
            tain a <set function specification>.

Postgres doesn't presently forbid it, but we probably should, because
the executor tends to get confused --- unsurprisingly considering that
there's no well-defined behavior for this case.

What I think you mean is

update customer_contact
 set next_billed =
  (SELECT min(customer_services.eff_date) FROM customer_services
   where customer_services.conn_num=7698
   and customer_services.inv_num=0)
 where conn_num=7698

but that's just a guess about the intended behavior.

            regards, tom lane