Bugs in bean-query

[Dan Andersson]

Hi

This might related to an earlier post I made, but this time I have reproduceable examples

I'm on Beancount 2.3.6.

Here is my test.beancount (valid Beancount file with no errors):
----------
option "inferred_tolerance_default" "USD:0.000001"

2025-01-01 open Assets:Cash:USD USD
2025-01-01 open Assets:Cash:JPY JPY
2025-01-01 open Income:Consultation USD
2025-01-01 open Expenses:Salary USD

2025-01-01 * "income"
  Assets:Cash:USD                      1000 USD
  Income:Consultation                 -1000 USD

2025-01-01 * "income"
  Assets:Cash:JPY                    147440 JPY { 0.006782419967 USD }
  Income:Consultation                 -1000 USD

2025-01-01 * "expense"
  Assets:Cash:JPY      -147439.999999999999 JPY { 0.006782419967 USD }
  Expenses:Salary                      1000 USD
----------

Running this command:
----------
bean-query test.beancount \
  "SELECT root(account, 5), sum(position) \
  FROM OPEN ON 2025-01-01 CLOSE ON 2026-01-01 CLEAR \
  WHERE not account ~ 'Income|Expenses' \
  GROUP BY 1 ORDER BY 1;"
----------

Gives this output:
----------
     root_account_c5                sum_position        
-------------------------- ------------------------------
Assets:Cash:JPY             1E-12             JPY {0 USD}
Assets:Cash:USD             1000              USD        
Equity:Conversions:Current    -6.782419967E-1 USD        
Equity:Earnings:Current    -1000              USD
----------

Bug 1: Users are lead to believe the conversions are not negligible (because important digits after "E" getting truncated).


Running this command (same command as above, but with cost() added):
----------
bean-query test.beancount \
  "SELECT root(account, 5), cost(sum(position)) \
  FROM OPEN ON 2025-01-01 CLOSE ON 2026-01-01 CLEAR \
  WHERE not account ~ 'Income|Expenses' \
  GROUP BY 1 ORDER BY 1;"
----------
Gives this output:
----------
     root_account_c5       cost_sum_
-------------------------- ---------
Assets:Cash:JPY                6 USD
Assets:Cash:USD             1000 USD
Equity:Conversions:Current    -6 USD
Equity:Earnings:Current    -1000 USD
----------

Bug 2: Users are lead to believe they own 6 USD worth of JPY, but in reality they own almost nothing.
Bug 3: Users are now lead to believe the conversions are -6 USD.

Thanks
DA

[Dan Andersson]

By the way, I'm just normal Beancount user.
I'm not a Python programmer, so I can't write scripts myself (and I clearly didn't have the skills to debug wrong things output by ChatGPT/Grok).

Please forgive my ignorance. This time I hope it's ok with you because now at least I have reproduceable examples.

[Daniele Nicolodi]

On 14/07/25 08:22, Dan Andersson wrote:
> Hi
>
> This might related to an earlier post I made, but this time I have
> reproduceable examples

Thanks for reporting these issues. However, issues are best tracked on
the benquery issue tracker https://github.com/beancount/beanquery/issues

> I'm on Beancount 2.3.6.

Why? There should be no drawbacks in updating to Beancount 3.1.0.

> Here is my test.beancount (valid Beancount file with no errors):

[...]

> Running this command:
> ----------
> bean-query test.beancount \
>   "SELECT root(account, 5), sum(position) \
>   FROM OPEN ON 2025-01-01 CLOSE ON 2026-01-01 CLEAR \
>   WHERE not account ~ 'Income|Expenses' \
>   GROUP BY 1 ORDER BY 1;"
> ----------
>
> Gives this output:
> ----------
>      root_account_c5                sum_position
> -------------------------- ------------------------------
> Assets:Cash:JPY             1E-12             JPY {0 USD}
> Assets:Cash:USD             1000              USD
> Equity:Conversions:Current    -6.782419967E-1 USD
> Equity:Earnings:Current    -1000              USD
> ----------

Please note that this is only an issue with the display of the query
results. The computations are correct, but the code that formats the
amounts for display is confused by the unusually large number of digits.

I can reproduce this with Beancount 2.3.6. However, as of Beancount
3.0.0, bean-query has been split off into its own package, named
beanquery, and the issue is already fixed there. Because of the limited
development resources, I don't think the bug will ever be fixed in
Beancount 2.3. Beanquery is compatible with Beancount 2.3.6, thus I
suggest you to simply install beanquery:

> pip install beanquery

> Running this command (same command as above, but with cost() added):
> ----------
> bean-query test.beancount \
>   "SELECT root(account, 5), cost(sum(position)) \
>   FROM OPEN ON 2025-01-01 CLOSE ON 2026-01-01 CLEAR \
>   WHERE not account ~ 'Income|Expenses' \
>   GROUP BY 1 ORDER BY 1;"
> ----------
> Gives this output:
> ----------
>      root_account_c5       cost_sum_
> -------------------------- ---------
> Assets:Cash:JPY                6 USD
> Assets:Cash:USD             1000 USD
> Equity:Conversions:Current    -6 USD
> Equity:Earnings:Current    -1000 USD
> ----------

This is pretty much the same bug and it also is fixed in beanquery.

Cheers,
Dan

[Dan Andersson]

Thanks, upgrading fixed it.

> Why? There should be no drawbacks in updating to Beancount 3.1.0.

I just remember seeing a clear "use this" on version 2 last year, and I wasn't aware this had changed.

Nice to see how Beancount is progressing.

Thanks for all your work,

DA