Query failing with strange error. 
Author Message
 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     |

| http://www.***.com/                              |   are precisely opposite.  |
=========================================================GLO================

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 03 Jul 2005 23:04:48 GMT
 Query failing with strange error.

Quote:

> 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

---------------------------(end of broadcast)---------------------------



Mon, 04 Jul 2005 00:16:59 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. DBA2192 Database connection failed - Strange error when connecting using the Command Center

2. CDaoRecordset : Strange SQL-Query Error

3. QUERY HELP: Strange Error Mssg.

4. Strange URL Query Error

5. ConnectionCheckForData (strange query error)

6. OI 1.2/01 strange query error?

7. Strange error in query

8. strange error /distributed queries

9. Strange error executing INSERT query on Oracle

10. strange error message from a query

11. Strange error executing INSERT query on Oracle

12. Strange WebForms error, strange Oracle answer - baffled!


 
Powered by phpBB® Forum Software