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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This work is licensed under a Creative Commons License.
The views and opinions expressed herein are those of the authors and do not necessarily reflect those of any other person or legal entity.
Kdb+ is the registered trademark of Kx Systems, Inc.