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
'84066472837652480
q)count select from large_table where id = 84066472837652480j
1
q)