« How do I get the length of a list? | Main | Does the order of field constraints matter in the performance of a select query? »

select sum size from a large table unexpectedly returns a negative number.  Why?

Short answer: cast the column to long before applying sum:

select sum `long $ size from large_table

In many programming languages, including q, anytime you add integers (unless you somehow know for sure that the sum will fit in - roughly - 31 bits) you risk integer overflow. If you're lucky, the error will be obvious (e.g., you'll get a negative number when you expected a positive one). Casting the arguments to sum to long will give you (almost) 63 bits of breathing room.

If your sum won't fit in 63 bits, you'll need to explore other options:

  • Switch to floats. Although overflow is still possible, it's rare. However, you lose some precision.
  • Use a bignum library such as gmp.
  • Use a language (e.g., haskell or clojure) that has built-in support for arbitrary-precision arithmetic.

Lastly, keep in mind that literal values that cannot fit into an int must be suffixed with j - even if the context suggests that a long is expected:

q)select from meta large_table where c = `id
c | t f a
--| -----
id| j
q)count select from large_table where id = 84066472837652480
q)count select from large_table where id = 84066472837652480j

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
All HTML will be escaped. Textile formatting is allowed.