Column disappears in query output when sum of a position for all rows is ZERO

[hartzell]

I ran into a problem where some of my queries were not including all of the expected columns in the output.

I think that the diagnosis in the title is accurate.

Using the test case below, using the code from Pypi I get:

$ python3 bug.py
+---------------+
| leaf(account) |
+---------------+
| Bob           |
+---------------+

What I expect (and what is generated with the change in the PR I'm about to submit) is:

$ python3 bug.py
+---------------+---------------------+
| leaf(account) | sum(position) (USD) |
+---------------+---------------------+
| Bob           | 0.00                |
+---------------+---------------------+

Rather than deleting positions that are zero, the updated code just passes them on up the chain. I'm not sure that this change isn't going to screw up something else.

To test, save this ledger as bug.beancount

option "title" "Bug demo"

2025-08-01 * "Bob bought a this expense"
Income:Bob                                   -1.00 USD
Expenses:This:Bob                             1.00 USD

Then run this query (ignore whining about unopened accounts) via
bean-query bug.beancount:

select leaf(account), sum(position) where account ~ "^(Expenses|Income).*" group by leaf(account)

or run this little python program

"""Document this..."""

import sys

from beancount import loader
from beanquery import query, query_render

entries, errors, options_map = loader.load_file("bug.beancount")

fmtopts = dict(
boxed=True, spaced=False, narrow=False, nullvalue="0.00", numberify=False
)

sql = 'select leaf(account), sum(position) where account ~ "^(Income|Expenses).*" group by leaf(account)'
rtypes, rrows = query.run_query(entries, options_map, sql, None, numberify=True)
query_render.render_text(rtypes, rrows, options_map["dcontext"], sys.stdout, **fmtopts)

[dnicolodi]

The fact that there isn't a column for sum(position) is due to the use of numberify and it is consistent on how numberify operates. The column with an empty inventory is there if you do not use numberify. numberify results is a variable number of columns and the processing pipeline must be able to handle it. If you are sure that the resulting inventory contains only one commodity, you can use select leaf(account), number(only('USD', sum(position))) where account ~ "^(Expenses|Income).*" group by leaf(account)

[hartzell]

@dnicolodi -- Thank you.

This change to the bug.py test script does indeed give the columns that I expect.

Thanks for clarifying things.

Unless there's a reason not to, I'll close this and the PR.

diff --git a/bug.py b/bug.py
index 9f1a229..6e6b4dc 100644
--- a/bug.py
+++ b/bug.py
@@ -15,5 +15,5 @@ fmtopts = dict(

 sql = 'select leaf(account), sum(position) where account ~ "^(Income|Expenses).*" group by leaf(account)'
 # sql = 'select leaf(account), sum(position) where account ~ "^(Income|Expenses|Assets).*" group by leaf(account)'
-rtypes, rrows = query.run_query(entries, options_map, sql, None, numberify=True)
+rtypes, rrows = query.run_query(entries, options_map, sql, None, numberify=False)
 query_render.render_text(rtypes, rrows, options_map["dcontext"], sys.stdout, **fmtopts)

[hartzell]

@dnicolodi - Can I ask for a bit more explanation, because I'm still seeing something unexpected.

The test case I supplied up top was a simplification. In the real app, I generate tables for

• all the accounts; then
• on the Asset and Liability accounts; then
• on the Income and Expense accounts; and finally
• some other tables.

The way I have things set up, the sum of the A/L accounts should be 0, and the sum of the I/I accounts should be 0.

I'm back to my real ledger and summarizing tool, and I'm still seeing different behavior depending on what set of accounts I'm looking at.

All of the account names start with Assets, Liabilities, Expenses, or Income. All the account names end with a user name, shortened to G or K. E.g. Assets:Misc:G.

All of the queries are run the same way, and with the same values for entries and options_map, e.g.

rtypes, rrows = query.run_query(entries, options_map, ei_sql, None, numberify=False)

What's confusing to me in the output below is that for the "all" accounts and "A/L" accounts, I get tables with "0.00 USD". But for the I/E case, the column is empty. That empty column is missing when numberify=True but I still get 0.00 values in the other columns.

I'm not sure what makes the E/I table different.

summary_sql = 'select leaf(account) as "User", sum(position) as "Balance" group by leaf(account) order by leaf(account)'

+--------+-----------+
|  User  |  Balance  |
+--------+-----------+
| G      |  0.00 USD |
| K      | -0.00 USD |
+--------+-----------+

al_sql = 'select leaf(account) as "User", sum(position) as "Balance" where account ~ "^(Assets|Liabilities)" group by leaf(account)'
+--------+-----------+
|  User  |  Balance  |
+--------+-----------+
| G      |  0.00 USD |
| K      | -0.00 USD |
+--------+-----------+

ei_sql = 'select leaf(account) as "User", sum(position) as "Balance" where account ~ "^(Expenses|Income)" group by leaf(account)'
+--------+---------+
|  User  | Balance |
+--------+---------+
| G      |         |
| K      |         |
+--------+---------+

[dnicolodi]

The column is not empty, it contains an empty Inventory object, which is displayed as an empty table cell, but it is not the same thing. Inventory objects are used to accumulate the sum of Amount objects and can be thought as a mapping from commodity symbols to quantities held. As you already discovered in your PR, the Inventory object elides entries in the dictionary when the quantity held is identically zero. In your case, the amounts collected in one case sum exactly to zero, in the other they sum to zero within the resolution used to display them in the text output, but are otherwise not zero, and thus are not elided. You can verify this inspecting what the query execution return without using the table formatting code.

I don't know if this behavior of the Inventory object is a good idea or not, but it has been like this for a very long time, thus changing it requires a good motivation and some careful thinking.

[hartzell]

Ah ha! Thank you for the explanation @dnicolodi!

TL;DR: -- For my use case, when the sum of the Income and Expense accounts is exactly 0 (which is pretty common) I'd like to see zero's in the output. Is there a configuration that I can use in my python code that will make this happen?

---

In the case that's printing 0.00 in the tables, the underlying values are:

(Pdb) p rrows
[('G', (0.005 USD)), ('K', (-0.005 USD))]

This is happening because I'm using a split_assets plugin (copy paste from the old split_expenses plugin) and it's dividing an odd number of cents by 2.

In the case that's printing an empty cell in the table, the underlying values are:

(Pdb) p rrows
[('G', ()), ('K', ())]

I'm rendering these query results with this bit of code (like everything else here, "borrowed" from examples and/or other tools):

query_render.render_text(rtypes, rrows, options_map["dcontext"], sys.stdout, **fmtopts)

where fmtops is:

{'boxed': True, 'spaced': False, 'narrow': False, 'nullvalue': '0.00'}

I thought/hoped that the nullvalue option passed into the renderer would result in a "0.00" in cases like this, but I see here in beancount/beanquery/query_render.py that it doesn't (because () is not None):

(Pdb) n
> /Users/hartzell/projects/.../beancount-venv/lib/python3.14/site-packages/beanquery/query_render.py(439)render_rows()
-> cells = [render.format(value) if value is not None else null for render, value in zip(renderers, row)]
(Pdb) p row
('G', ())
(Pdb) n
> /Users/hartzell/projects/.../beancount-venv/lib/python3.14/site-packages/beanquery/query_render.py(441)render_rows()
-> if not any(isinstance(cell, list) for cell in cells):
(Pdb) p cells
['G', '']
(Pdb)

For my use case, when the sum of the Income and Expense accounts is exactly 0 (which is pretty common) I'd like to see zero's in the output. Is there a configuration that I can use in my python code that will make this happen?

[hartzell]

Ah, answering my own question, after wrapping my head further around the input you provided above.... Since I'm only interested in USD, queries like this will work:

ei_sql = 'select leaf(account) as "User", number(only("USD", sum(position))) as "Balance" where account ~ "^(Expenses|Income)" group by leaf(account)'

I end up with 0 instead of 0.00 USD, but that's not the end of the world.

Let me know if there's a better way. Thank you!