BQL: filtering out on missing metadata values

[Stefano Zacchiroli]

Heya,
using the following BQL query:

SELECT ANY_META('trip') AS trip, SUM(position) AS amount
WHERE account ~ '^Assets:Reimbursable'
GROUP BY trip

I find in its output rows like:

trip amount
--------------------------- -----------
2014-03-21-somewhere1-somereason1 42.00 EUR
2014-03-24-somewhere2-somereason2
48.37 EUR
2014-03-28-somewhere3-somereason3
2014-03-29-somewhere4-somereason4 47.00 EUR

where lines 1 and 5 have non-empty values everywhere and lines 2 and 4
have empty amounts. Line 3 has empty trip, due to the fact that not all
postings have a "trip" metadata, so that is the grouped total of all
such postings.

As that line doesn't make sense for my report, I'm trying to filter it
out. The following (weird) solution works:

SELECT ANY_META('trip') AS trip, SUM(position) AS amount
WHERE account ~ '^Assets:Reimbursable'
AND STR(ANY_META('trip')) != 'None'
GROUP BY trip

What's happening is that ANY_META is returning a value of None, and I
can filter on its (Python) string representation. Which is awful :-)

Ideally, I'd like the following to work:

SELECT ANY_META('trip') AS trip, SUM(position) AS amount
WHERE account ~ '^Assets:Reimbursable'
AND ANY_META('trip')
GROUP BY trip

but it fails with the following traceback instead:

File "/home/zack/.local/lib/python3.6/site-packages/beancount/query/query_compile.py", line 121, in __call__
return self.operator(self.left(context), self.right(context))
TypeError: unsupported operand type(s) for &: 'bool' and 'str'

Alternatively, the following would be a little bit less weird that the
solution above:

SELECT ANY_META('trip') AS trip, SUM(position) AS amount
WHERE account ~ '^Assets:Reimbursable'
AND STR(ANY_META('trip')) != ''
GROUP BY trip

Martin: what solution would you like best?

The patch for the last solution should be trivial (make ANY_META return
'' instead of None), but it's IMO not the best solution. Making None be
interpreted as False in WHERE context would be better, but I haven't yet
looked at how hard it is to make that work.

TIA,
Cheers
--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack . . o . . . o . o
Computer Science Professor . CTO Software Heritage . . . . . o . . . o o
Former Debian Project Leader & OSI Board Director . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »