Issue #335: BQL: None should be considered False in boolean context (blais/beancount)

[Stefano Zacchiroli]

New issue 335: BQL: None should be considered False in boolean context
https://bitbucket.org/blais/beancount/issues/335/bql-none-should-be-considered-false-in

Stefano Zacchiroli:

The following query:
```
#!bql

SELECT ANY_META('trip') AS trip, SUM(position) AS amount
WHERE account ~ '^Assets:Reimbursable'
AND ANY_META('trip')
GROUP BY trip
```
when 'trip' might be missing from some transaction fails as follows:
```
#!python

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'
```
the string in question is probably "None", i.e., the string representation of Python None values.

It would be nice if missing metadata could be treated as False in boolean context by BQL.

[shreedha...@gmail.com]

Did you already try : 

STR(ANY_META('trip')) != null ??

I think null is use by BQL to represent Python's None. 

As a sidenote, != null is not "sql"-like since in sql, IS NULL/ IS NOT NULL expressions are used instead. These are special since the = operator is strict i.e returns NULL if any of its operand is NULL. That means in a WHERE clause, the NULL results work like 'false'.

[Stefano Zacchiroli]

On Sat, Sep 29, 2018 at 06:33:48AM -0700, shreedha...@gmail.com wrote:
> Did you already try :
> STR(ANY_META('trip')) != null ??

Oh, I didn't know about null in BQL. So the following works (which is
probably what you meant above):

ANY_META('trip') != null

and is indeed quite nice.

Interpreting null values as False in boolean context would still be a
nice to have, but it's definitely not more important than that.

Thanks for the tip!

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 »

[Martin Blais]

This whole SQL prototype needs to get rewritten at some point.

It really is just a quickly put together, poorly tested prototype.

(I've started to fiddle a tiny litlte bit with Project Ibis (https://github.com/ibis-project/ibis) and maybe implementing a SQL FE to this and a simple in-memory BE could leverage its type system and provide a more robust SQL-like implementation.)

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20180929135225.v26knfs3lty5zxnv%40upsilon.cc.
For more options, visit https://groups.google.com/d/optout.

[Daniele Nicolodi]

I haven't thought much about this, but wouldn't RDF and SPARQL also be
goo matches for this task? I believe rdflib provides all required
building blocks. I'm not sure if SPARQL is a suitable query language,
though.

On 29/09/2018 10:58, Martin Blais wrote:
> This whole SQL prototype needs to get rewritten at some point.
> It really is just a quickly put together, poorly tested prototype.
> (I've started to fiddle a tiny litlte bit with Project Ibis
> (https://github.com/ibis-project/ibis) and maybe implementing a SQL FE
> to this and a simple in-memory BE could leverage its type system and
> provide a more robust SQL-like implementation.)
>
>
> On Sat, Sep 29, 2018 at 9:52 AM Stefano Zacchiroli <za...@upsilon.cc> wrote:
>
> On Sat, Sep 29, 2018 at 06:33:48AM -0700,
> shreedha...@gmail.com <mailto:shreedha...@gmail.com> wrote:
> > Did you already try :
> > STR(ANY_META('trip')) != null ??
>
> Oh, I didn't know about null in BQL. So the following works (which is
> probably what you meant above):
>
>   ANY_META('trip') != null
>
> and is indeed quite nice.
>
> Interpreting null values as False in boolean context would still be a
> nice to have, but it's definitely not more important than that.
>
> Thanks for the tip!
>
> Cheers
> --
> Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack

> <http://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 »
>
> --
> You received this message because you are subscribed to the Google
> Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to beancount+...@googlegroups.com

> <mailto:beancount%2Bunsu...@googlegroups.com>.

> To post to this group, send email to bean...@googlegroups.com

> <mailto:bean...@googlegroups.com>.

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/beancount/20180929135225.v26knfs3lty5zxnv%40upsilon.cc.
> For more options, visit https://groups.google.com/d/optout.
>

> --
> You received this message because you are subscribed to the Google
> Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to beancount+...@googlegroups.com

> <mailto:beancount+...@googlegroups.com>.

> To post to this group, send email to bean...@googlegroups.com

> <mailto:bean...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/beancount/CAK21%2BhMsbLf3ZxDiSdCasgZG4j8kf3ohSYHpZ7-_QbS3_PtxLw%40mail.gmail.com
> <https://groups.google.com/d/msgid/beancount/CAK21%2BhMsbLf3ZxDiSdCasgZG4j8kf3ohSYHpZ7-_QbS3_PtxLw%40mail.gmail.com?utm_medium=email&utm_source=footer>.

[Stefano Zacchiroli]

On Sat, Sep 29, 2018 at 12:58:43PM -0400, Martin Blais wrote:
> This whole SQL prototype needs to get rewritten at some point.

Maybe. But, for what is worth, BQL is really pleasant to use.

Once you grok the data model and the main hack you did on standard SQL
(i.e., FROM is for transactions, WHERE for postings) writing queries is
intuitive and hence quick. I never felt the same ease of use with
ledger-cli query functionalities in spite of having used it for much
longer than bean-query.

If you rewrite it, this is a strong +1 for keeping a similar language /
data model :-)

--
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 .

[Martin Blais]

I don't feel that the kinds of complex joins/conditions that Prolog provides are really useful for the kinds of info we need to export out of Beancount, but I'm definitely not very familiar with these systems (my Prolog experiment extends to have read just about halfway through the Reasoned Schemer and a few papers here and there). AFAIK we mostly really just need a single projection + filter / aggregation type of query.

Nothing should prevent you from exploring and filling up a database of rules or a tuple space with the contents of a Beancount ledger though, and experiment with that. 

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4e3eec41-29fd-e958-1c0d-f94104c54b5e%40grinta.net.

[Shreedhar Hardikar]

On Sat, Sep 29, 2018 at 7:12 PM Martin Blais <bl...@furius.ca> wrote:

I don't feel that the kinds of complex joins/conditions that Prolog provides are really useful for the kinds of info we need to export out of Beancount, but I'm definitely not very familiar with these systems (my Prolog experiment extends to have read just about halfway through the Reasoned Schemer and a few papers here and there). AFAIK we mostly really just need a single projection + filter / aggregation type of query.

And subqueries and windows sometimes. At the very least scalar subqueries. Although, non-scalar subqueries would be awesome (at which point they're really joins).

 

Nothing should prevent you from exploring and filling up a database of rules or a tuple space with the contents of a Beancount ledger though, and experiment with that. 

Right, I have a script (using ideas from the sqlite export code) that loads beancount entries into a postgres database on which I can issue as complex a query as I want. But for that, I had to write a load script, define a bunch of composite types & python UDFs that can handle the datatypes in beancount. This is rather incomplete at the moment though, but fairly functional (although maybe not performant).  This can be extended further using postgres external tables / FDW. Anyway, even though this gives me access to the full power of postgres' SQL compliance, the queries are complex (involving views with joins just like in sqlite) and they lose the nuanced difference of "entry" vs "position" in beancount.

I'm sure there's a middle ground solution lurking somewhere. I'm not really familiar with ibis to comment on that.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPgdVfprUr4kLHoeFML_MZOSbjsDp_nA42cKV14NvvQ1w%40mail.gmail.com.