Thread

1. pg7.3.4: pg_atoi: zero-length string

   rob <rob@dsvr.net> — 2003-11-04T11:21:35Z

   
   Hi,
   
   We're currently experiencing a problem where SQL statements are failing 
   when entring a '' for not not-null integer columns:
   
   ERROR:  pg_atoi: zero-length string
   
   This was discovered just after a database migration from 7.2 to 7.3.4.
   
   Example:
   
   insert into renewal_cache
                    (dom, expiry, issued, aid) values
                    ('data','2004-03-05','19980305','')
   
   The above example in just one case where 'aid' can accept a null value. 
   The use of quotes around all values was established as IIRC pg7.2 
   wouldn't accept statements without them. The use of this convention is 
   extensive.
   
   Cheers,
   
   Rob Fielding
   Development
   Designer Servers Ltd
   
   
   
   

2. Re: pg7.3.4: pg_atoi: zero-length string

   Andrew Sullivan <andrew@libertyrms.info> — 2003-11-04T11:32:22Z

   On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote:
   > 
   > 
   > Hi,
   > 
   > We're currently experiencing a problem where SQL statements are failing 
   > when entring a '' for not not-null integer columns:
   
   Yes.  This behaviour was made more compliant in 7.3.  It's in the
   release notes.
   
   > The above example in just one case where 'aid' can accept a null value. 
   
   That's not a null.  It's a zero-length string.
   
   > The use of quotes around all values was established as IIRC pg7.2 
   > wouldn't accept statements without them. The use of this convention is 
   > extensive.
   
   You could probably put in a rewrite rule to convert '' to NULL and
   allow nulls on the column.  It's the only suggestion I can think of,
   short of going back to 7.2.
   
   A
   
   -- 
   ----
   Andrew Sullivan                         204-4141 Yonge Street
   Afilias Canada                        Toronto, Ontario Canada
   <andrew@libertyrms.info>                              M2P 2A8
                                            +1 416 646 3304 x110
   
   
   

3. Re: pg7.3.4: pg_atoi: zero-length string

   rob <rob@dsvr.net> — 2003-11-04T12:19:58Z

   
   Andrew Sullivan wrote:
   > On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote:
   > 
   >>
   >>Hi,
   >>
   >>We're currently experiencing a problem where SQL statements are failing 
   >>when entring a '' for not not-null integer columns:
   > 
   > 
   > Yes.  This behaviour was made more compliant in 7.3.  It's in the
   > release notes.
   > 
   > 
   >>The above example in just one case where 'aid' can accept a null value. 
   
   I've found this is a feature of 7.3 to not treat a empty string as a 
   NULL integer type. Silly lazy me ;)
   
   As it turned out it relatively trivial to fix the offending statements 
   on the few occasions where it has been valid to do this.
   
   Consider this a non-issue.
   
   
   Cheers,
   
   -- 
   
   Rob Fielding
   Development
   Designer Servers Ltd
   
   
   

4. Re: pg7.3.4: pg_atoi: zero-length string

   Bruno Wolff III <bruno@wolff.to> — 2003-11-04T13:19:31Z

   On Tue, Nov 04, 2003 at 12:19:58 +0000,
     Rob Fielding <rob@dsvr.net> wrote:
   > 
   > I've found this is a feature of 7.3 to not treat a empty string as a 
   > NULL integer type. Silly lazy me ;)
   
   It didn't even then. It was treated as 0. Oracle is the DB that treats
   empty strings as null values.
   
   

5. Re: pg7.3.4: pg_atoi: zero-length string

   rob <rob@dsvr.net> — 2003-11-04T13:26:47Z

   
   >>The above example in just one case where 'aid' can accept a null value. 
   > 
   > That's not a null.  It's a zero-length string.
   
   I've found this is a feature of 7.3 to not treat a empty string as a 
   NULL integer type. Silly lazy me.
   
   As it turned out it relatively trivial to fix the offending statements 
   on the few occasions where it has been valid to do this.
   
   Consider this a non-issue.
   
   
   Cheers,
   
   -- 
   
   Rob Fielding
   Development
   Designer Servers Ltd
   
   
   

6. Re: pg7.3.4: pg_atoi: zero-length string

   Andrew Sullivan <andrew@libertyrms.info> — 2003-11-04T14:18:23Z

   On Tue, Nov 04, 2003 at 12:19:58PM +0000, Rob Fielding wrote:
   > I've found this is a feature of 7.3 to not treat a empty string as a 
   > NULL integer type. Silly lazy me ;)
   
   For the record, it _never_ treated it as NULL.  It treated it as
   "empty string".  '' != NULL.  In fact, !(NULL=NULL) & !(NULL!=NULL). 
   SQL uses three-valued logic.
   
   A
   
   -- 
   ----
   Andrew Sullivan                         204-4141 Yonge Street
   Afilias Canada                        Toronto, Ontario Canada
   <andrew@libertyrms.info>                              M2P 2A8
                                            +1 416 646 3304 x110
   
   
   

7. Re: pg7.3.4: pg_atoi: zero-length string

   rob <rob@dsvr.net> — 2003-11-04T15:18:01Z

   > For the record, it _never_ treated it as NULL.  It treated it as
   > "empty string".  '' != NULL.  In fact, !(NULL=NULL) & !(NULL!=NULL). 
   > SQL uses three-valued logic.
   
   
   You're absolutely right.  That explains why, when I quickly looked, some 
   are zero's and some are NULLs - the NULLs where NULLs and the zeros 
   where empty strings.
   
   Two different bad-programming examples.  If I actually used these 
   columns for anything whenever they didn't have non zero or null data in 
   them then I'd have probably been alot more careful about what went in them.
   
   I presume that an 32bit integer of zero and a NULL are represented 
   differently in the database ?  I suppose internally you aren't 
   representing a NULL within the context of a 32bit integer field and it 
   would just probably be magic pointer to the next field - some sort of 
   exercise in space squashing?  I don't know anything about the internal 
   stucture of the tuples.
   
   Dependant on the above, it would probably make sense to clean up the 
   database, especially considering these columns are also indexed.
   
   
   Cheers
   
   -- 
   
   Rob Fielding
   Development
   Designer Servers Ltd