Borderless Queries

[mtoy-googly-moogly]

Queries Should Not Look Like Sources

A “source” is a set of definitions. It is never intended to be run, and so the syntax for a source looks like a bag of properties. This makes sense.

We currently use the same syntax that we developed for writing sources, to write queries. Mapping everything that happens in a query into something which looks like a source declaration. It might be more natural for queries to be written as sentences, not as bags of properties.

Step One, No {}

We change nothing about the language except that query “statements” are not contained in {} but simply strung out like SELECT, JOIN, FROM, GROUP BY are in SQL. Set the query free, let it out of bracket jail!

Notice how this short SQL query reads as “Select these two things from this data set with this grouping”, the scan across the sentence is uninterrupted and smooth.

One Liner Example

select carrier, count() as flight_count from flights group by 2

Now let’s write that sentence in Malloy. Notice how your ability to simply read left to right and collect information about the sentence is interrupted, you read this as flights … pause … piped into something which as you parse it … groups and counts … oh yeah groups and counts flights

flights -> { group_by: carrier aggregate: flight_count is count() }

Now in this new hybrid syntax it again scans smoothly “flights grouped by carrier and aggregated”

flights -> group_by: carrier aggregate: flight_count is count()

Other Examples

Let’s look at some small, but more normal queries where you are not reading the whole query, by scanning it for parts “what data is used, what is filtered, what is the grouping, what is computed, how is it sorted” In SQL you need to read carefully to answer those questions.

select
  prop1,
  prop2,
  prop2,
  avg(some_expr) as avg_thing,
  sum(some_expr) as total_thing,
  count() as thing_count
from somewhere
where SomeCondition
group_by 1,2,3
order_by 5

Malloy does this so much better, though the } means it takes one more line to do so

flights -> {
  where: someCondition
  group_by:
    prop1
    prop2
    prop3
  aggregate:
    avg_thing is avg(some_expr)
    total_thing is sum(some_expr)
    thing_count is count
  order_by: thing_count
}

And this query in the {}-free version, it is just slightly better, it has the barely perceptible speed bump of the { edited out of the experience. We carry “flights” with us into the query because we weren’t told by the { that this is new context. And just for laughs, this is the same number of LOC as the SQL query.

flights ->
  where: someCondition
  group_by:
    prop1
    prop2
    prop3
  aggregate:
    avg_thing is avg(some_expr)
    total_thing is sum(some_expr)
    thing_count is count
  order_by: thing_count

Here’s some sample code which is sometimes used to evaluate syntax in a more complex setting. Note that {} can still bracket a query for clarity, or here, when it is required to get the nested queries in genre_matrix, but you are only incurring the extra burden of parsing the containment when the query is complicated enough to need it.

source: _mv is duckdb('data/titles.parquet') extend { primary_key: tconst }
source: movies is _mv extend {
  measure:
    title_count is count(distinct tconst)
    total_ratings is sum(ratings.numVotes/1000.0)
    percent_of_titles is title_count/all(title_count)* 100

  query: genre_matrix is ->
    extend: {
      join_many: x is (_mv -> group_by tconst, genres.value)
                 on tconst = x.tconst
    }
    where: genres.value != x.value
    group_by: genre1 is genres.value
    aggregate: title_count
    nest: genre is -> {
      group_by: genre1 is genres.value, genre2 is x.value
      nest: by_title_list is -> (
        group_by: primaryTitle, ratings.numVotes
        order_by: 2 desc
        limit: 10
      )
    }

}

run: movies -> genre_matrix
run: movies -> genre_matrix refine where: year ? @2010

This feature depends on the extend and refine keywords being required, so it needs to wait for that to have shipped. This feature could then be rolled out, or if we are uncertain of its value, beta-tested, and enabled by users who want to experiment with it. (There is a lurking proposal to use part of the document tag-space for language directives)

###! x-queriesWithoutBorders // Enable experimental parsing of queries without {}

[mtoy-googly-moogly]

Step Two, kill the :

Now that the {} is optional, maybe it makes sense to NOT have the query phrases start a keyword:.

I am less sure about this, because in glancing at a string of words, it is kind of nice to know which ones of those words have meaning to the computer. In an IDE the keywords would be colored, and maybe that is enough.

For example, in this sentence, maybe it doesn't read well because the words carrier aggregate are next to each other and your eyes NEED some punctuation

flights -> group_by carrier aggregate flight_count is count()

We could use the statement seperator

flights -> group_by carrier; aggregate flight_count is count()

But in the case of the 1 liner, I think I still prefer

flights -> group_by: carrier aggregate: flight_count is count()

In the medium example where there is one statement per line, this reads pretty well, very SQL-like

flights ->
  where someCondition
  group_by
    prop1
    prop2
    prop3
  aggregate
    avg_thing is avg(some_expr)
    total_thing is sum(some_expr)
    thing_count is count
  order_by thing_count

Here's a snapshot of code like this, with IDE highlighting ...

Not sure what I would suggest here

1. Drop :
2. Keep :
3. Make : somehow optional

[christopherswenson]

One thing I don't love about this is that when you have a nested query with two stages, it's a really unintuitive transformation to add a second nest after it:

Before:

  query: by_origin is -> 
    group_by: origin
    nest: by_state is -> group_by state aggregate flight_count -> project *

After:

  query: by_origin is -> 
    group_by: origin
    nest: by_state is -> group_by state aggregate flight_count -> (project *)
    nest: by_state2 is -> group_by state aggregate flight_count -> project *

It almost makes me want to have a different "end of statement" gesture like ;;:

  query: by_origin is -> 
    group_by: origin
    nest: by_state is -> group_by state aggregate flight_count -> project * ;;
    nest: by_state2 is -> group_by state aggregate flight_count -> project *

Or maybe you just enclose it differently:

query: by_origin is -> 
    group_by: origin
    nest: 
      (by_state is -> group_by state aggregate flight_count -> project *)
      (by_state2 is -> group_by state aggregate flight_count -> project *)

[mtoy-googly-moogly]

I agree that saving {} for json-like property lists is a good thing.

If we drop the colon I would prefer () over an end token.

[mtoy-googly-moogly]

After experimenting with this feature, the difficulty of reading and writing nested queries was the eventual downfall of this idea.