From dj@research.bell-labs.com  Wed Mar 20 14:55:24 2002
From: dj@research.bell-labs.com (David James)
Date: Wed, 20 Mar 2002 09:55:24 -0500
Subject: [Rd] RMySQL integer range bug (PR#1400)
In-Reply-To: <200203201241.NAA12502@pubhealth.ku.dk>; from matthias.piksa@web.de on Wed, Mar 20, 2002 at 01:41:38PM +0100
References: <200203201241.NAA12502@pubhealth.ku.dk>
Message-ID: <20020320095524.A20541@jessie.research.bell-labs.com>


--82I3+IH0IqGh5yIs
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi,

matthias.piksa@web.de wrote:
> Full_Name: Matthias Piksa
> Version: 1.4.1
> OS: Redhat 7.1 2.96-85
> Submission from: (NULL) (131.220.6.43)
> 
> 
> There is a bug when retrieving values out of MySQL (3.23.36) which are of type
> UNSIGNED INTEGER(10). The values can vary between 0 and 2^32 but R only accepts
> those below 2^31-1. Anything above goes as 2^31-1.

Yes, that causes an overflow.  The attached patch to the file
RS-MySQL.c fixes the problem with unsigned integers, but it may not
be general enough -- I'll have to take a closer look for other cases.
By the way, you may notice that the largest unsigned integer in
MySQL is 4294967295, which may not be large enough for some IP
addresses (your data appears to be IP address, if I'm not mistaken).

Thanks for the report,

--
David

> 
> thats what one gets:
> print(quickSQL(con,"select ip_dst from iphdr where ip_dst>=2212233216 limit
> 5"))
>       ip_dst
> 1 2147483647
> 2 2147483647
> 3 2147483647
> 4 2147483647
> 5 2147483647
> 
> and thats what the numbers really are (conversion to character type first):
> print(quickSQL(con,"select concat(ip_dst) from iphdr where ip_dst>=2212233216
> limit 5"))
>   concat(ip_dst)
> 1     2212234077
> 2     2212234085
> 3     2212234085
> 4     2212234085
> 5     2212234086
> 
> -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
> r-devel mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
> Send "info", "help", or "[un]subscribe"
> (in the "body", not the subject !)  To: r-devel-request@stat.math.ethz.ch
> _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

-- 
David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636

--82I3+IH0IqGh5yIs
Content-Type: text/plain; charset=us-ascii
Content-Description: RMySQL_0.4-3.patch
Content-Disposition: attachment; filename="RMySQL_0.4-3.patch"

494d493
<     case FIELD_TYPE_LONG:            /* 4-byte INTEGER   */
495a495,501
>       break;
>     case FIELD_TYPE_LONG:            /* 4-byte INTEGER   */
>       /* if unsigned, turn into numeric (may be too large for ints/long)*/
>       if(select_dp[j].flags & UNSIGNED_FLAG)
>         flds->Sclass[j] = NUMERIC_TYPE;
>       else
>         flds->Sclass[j] = INTEGER_TYPE;

--82I3+IH0IqGh5yIs--
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-devel mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-devel-request@stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._


[Rd] RMySQL integer range bug (PR#1400)
David James