473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

About PostgreSQL's limit on arithmetic operations

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,

We were performing some tests on PostgreSQL and found that it fails on the
following query:

SELECT 512*18014398509481984 AS result;

and the result is:

result
- ----------------------
-9223372036854775808

It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora
Core 2.

We tested the same query on some other database servers:

Oracle 9 ias r2 : 9,22337203685478E18
Oracle 9i WinXP, 32 bit : 9223372036854775808
Oracle 9.2.0.3 on Redhat 9 : 9223372036854775808
Oracle 9.2.0.3 on Redhat Advanced Server 2.1 : 9223372036854775808
MS-SQL Server 2000 : 9223372036854775808
MySQL on Sun Solaris 6 : -9223372036854775808
MySQL on Red Hat Enterprise Linux 3.0 : -9223372036854775808

It seems that MySQL and PostgreSQL fails on this query. Is this a bug, or
a lack of something?

.... or is there a limit?

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBWmc4tl86P3SPfQ4RAliCAJ9JJA3111mxrcBnnWptI8 cTQzdpoQCgsBQV
HMKeNGowkd35BhMwW6kWwbs=
=BWS3
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
6 2370
On Wednesday 29 Sep 2004 1:11 pm, Devrim GUNDUZ wrote:
Hi,

We were performing some tests on PostgreSQL and found that it fails on the
following query:

SELECT 512*18014398509481984 AS result;

and the result is:

result
----------------------
-9223372036854775808

It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora
Core 2.

shridhar@ps0499:~$ psql template1
Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=# SELECT 512*18014398509481984 AS result;
result
----------------------
-9223372036854775808
(1 row)

template1=# SELECT 512*18014398509481984::bigint AS result;
result
----------------------
-9223372036854775808
(1 row)

template1=# SELECT 512*18014398509481984::numeric(20) AS result;
result
---------------------
9223372036854775808
(1 row)

template1=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 8.0.0beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4
(1 row)

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,

On Wed, 29 Sep 2004, Shridhar Daithankar wrote:

SELECT 512*18014398509481984 AS result;

and the result is:

result
----------------------
-9223372036854775808

It should be 9223372036854775808... This is PostgreSQL 7.4.5 on Fedora
Core 2.

shridhar@ps0499:~$ psql template1


<snip>
template1=# SELECT 512*18014398509481984::numeric(20) AS result;
result
---------------------
9223372036854775808
(1 row)


Ok, I got the same result in 7.4.5... But... Why do we have to cast it
into numeric? The results from other databases shows that they can perform
it without casting...

Anyway, thanks for the tip.

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBWniTtl86P3SPfQ4RAt9/AJ0dG8bXaDVgqAvtgIDiEAX7MwU4YACfeR+l
c4nhYwft3NvJWhOdHJ90Lm8=
=4uVc
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
On Wednesday 29 Sep 2004 2:25 pm, Devrim GUNDUZ wrote:
template1=# SELECT 512*18014398509481984::numeric(20) AS result;
result
---------------------
9223372036854775808
(1 row)


Ok, I got the same result in 7.4.5... But... Why do we have to cast it
into numeric? The results from other databases shows that they can perform
it without casting...


Probably because the normal integer is 4 bytes long and bigint is 8 bytes
long. The value above is exactly 2^63 at which a 8 bytes long signed bigint
should flip sign/overflow. I am still puzzled with correct value and negative
sign..

For arbitrary precision integer, you have to use numeric. It is not same as
oracle.

Furthermore if your number fit in range, then numbers like precision(4,0) in
oracle to smallint in postgresql would buy you huge speed
improvement(compared to postgresql numeric I mean)

Please correct me if I am wrong..

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4
On Wed, Sep 29, 2004 at 04:51:30PM +0530, Shridhar Daithankar wrote:
Probably because the normal integer is 4 bytes long and bigint is 8 bytes
long. The value above is exactly 2^63 at which a 8 bytes long signed bigint
should flip sign/overflow. I am still puzzled with correct value and negative
sign..
Because in signed integer arithmatic the negative numbers go one
further than the positive numbers. So the largest positive number is
2^63-1 and the largest negative number is -2^63.

Or put it another way: -2^63 = +2^63. It's the far end of the loop,
exactly opposite the zero.
For arbitrary precision integer, you have to use numeric. It is not same as
oracle.
I would say that it should produce an overflow error rather than just
returning the wrong answer, but that's not my call...

Have a nice day,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBWp/PY5Twig3Ge+YRAifNAKCJtc4JzQJVZUSbDAt/BNy2eOIl8QCcD3mw
Alh4n5aFI86azoejWegmhaU=
=qLuU
-----END PGP SIGNATURE-----

Nov 23 '05 #5

On Wed, 29 Sep 2004, Devrim GUNDUZ wrote:
We were performing some tests on PostgreSQL and found that it fails on the
following query:

SELECT 512*18014398509481984 AS result;

and the result is:

result
- ----------------------
-9223372036854775808


This query should either return the "correct" answer or error. The
constants are probably getting typed as int4 and int8 but the result
overflows int8. If we say that the result of an int8 and int4 must be
within the int8 precision (which appears to be allowed by the spec,
although may not be what was inteneded) then it should error.

We don't currently check the overflow cases for int<n> to generate the
error, and we don't upgrade smaller integer types into numerics if it
would have overflowed. At some point we probably need to do one of those,
but AFAIK noone's been particularly motivated to do it.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6
Signed integers use a number system known as "two's compliment" and in
order to
make room for negative numbers the most significant bit is used to
indicate a negative
value and the compliment of the rest of the bits minus 1 {there is no
such number as -0}
evaluate to the negative numbers. This method is used because it makes
addition and
subtraction simpler for computers to perform on integers.

I am not sure how to declare an unsigned bigint, but if you can it would
produce
the expected result.

Only Oracle developers could tell you why they would generate an invalid
result when
doing math using 64 bit signed integers {9223372036854775808 is not a
valid value
for a 64bit signed integer.} Just because Oracle gives you the number
you want it
in no way makes the result correct. Since the result is outside the
scope of a 64 bit
signed integer an overflow error could be a valid result, or the value
postgresql
returns could also be a valid result. If you are multiplying by 512 in
order to perform
a binary right shift of 9 bits on the integer you don't want scope
validation, because if
were enforced a right shift would not work, and the result would produce
an error.
Please read the documentation on data types, it details the scope
{range} for all data
types. If you use the proper data type for your data set you can reduce
the amount of
storage required for small numbers and be fairly certain that the math
will work as
expected using large numbers.

Shridhar Daithankar wrote:
On Wednesday 29 Sep 2004 2:25 pm, Devrim GUNDUZ wrote:

template1=# SELECT 512*18014398509481984::numeric(20) AS result;
result
---------------------
9223372036854775808
(1 row)

Ok, I got the same result in 7.4.5... But... Why do we have to cast it
into numeric? The results from other databases shows that they can perform
it without casting...


Probably because the normal integer is 4 bytes long and bigint is 8 bytes
long. The value above is exactly 2^63 at which a 8 bytes long signed bigint
should flip sign/overflow. I am still puzzled with correct value and negative
sign..

For arbitrary precision integer, you have to use numeric. It is not same as
oracle.

Furthermore if your number fit in range, then numbers like precision(4,0) in
oracle to smallint in postgresql would buy you huge speed
improvement(compared to postgresql numeric I mean)

Please correct me if I am wrong..

Shridhar


--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
8
by: Rade | last post by:
Following a discussion on another thread here... I have tried to understand what is actually standardized in C++ regarding the representing of integers (signed and unsigned) and their conversions....
22
by: James H. | last post by:
Greetings! I'm new to Python and am struggling a little with "and" and "or" logic in Python. Since Python always ends up returning a value and this is a little different from C, the language I...
10
by: Massimiliano Alberti | last post by:
Are there rules on how should the C behave with the arithmetic operations? Are the overflows always ignored? And are they simply truncated? (if I have a 16-bits unsigned short ints unsigned short...
81
by: Matt | last post by:
I have 2 questions: 1. strlen returns an unsigned (size_t) quantity. Why is an unsigned value more approprate than a signed value? Why is unsighned value less appropriate? 2. Would there...
3
by: Madan | last post by:
Hi all, I had problem regarding float/double arithmetic only with + and - operations, which gives inaccurate precisions. I would like to know how the arithmetic operations are internally handled...
1
by: Satpreet | last post by:
I'm looking to simulate the behavior of a digital hardware arithmetic block in a C/C++ program. I was just wondering if there are any libraries (with datatypes and overloaded operators etc.)...
90
by: John Salerno | last post by:
I'm a little confused. Why doesn't s evaluate to True in the first part, but it does in the second? Is the first statement something different? False print 'hi' hi Thanks.
27
by: jacob navia | last post by:
As Richard Bos rightly pointed out, I had left in my classification of types the C99 types Complex and boolean. Here is a new classification. Those are not mentioned in the classification of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.