Feedback: Grammar composability with Postgres

[ankoh]

Hey all 👋 , thanks for this amazing project, I love it.
I really want to see a non-toy-grammar that cleanly bridges Grammar of Graphics with SQL, this would be fantastic.

I started to implement your grammar on top of the Postgres bison rules and must say that I ran into a few walls.
Please don't see this as an angry list of complaints, but just as feedback from an enthusiastic database engine developer:

1. The first thing that I noticed was your colon namespace in the examples. I would recommend to just use dot.qualified names here since there's no colon separators in identifier names in the major databases.
2. The VIA keyword confused me. If you develop ambitions to extend any of the major database grammars, something like an integer transform just becomes a normal expression before the alias clause, e.g. log10(foo) as x or cast(foo as integer) as x.
3. If you want to use arbitrary sql expressions in clauses like FILTER without breaking the world, you need to think about how your visualize statement can work without declaring all of your new terms as reserved sql keywords. This is seriously harmful. Right now, you cannot really just throw in a a_expr of postgres (https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y#L15837) in your filter clause because it will collide with all of your vis keywords when unreserved.
4. MAPPING and REMAPPING feel a bit odd, they are very close to SELECT, you just chose a different name. REMAPPING has the additional twist that it's redundant if you decide to merge VIA into MAPPING. If you had full-fledged sql result target in your mapping clause (select), remapping is just an alias after applying the cast/function call.
5. SETTING is very close to what several databases name USING. Plus it would imo not harm to say USING always opens paranthesis (like done in databases), to just have a cleanly separated option-value list.

I ended up implementing a slightly different version of your grammar in the end which is composing beautifully with Postgres (and does not need reserved keywords):

VISUALISE (SELECT ... FROM sales)
    DRAW <geom> [AS (SELECT ... [WHERE ...] [PARTITION BY ...] [ORDER BY ...])] [USING (...)]
    PLACE <geom> [USING (...)]
    SCALE [<type>] <aesthetic> [FROM [...] TO [...]] [USING (...)]
    FACET <cols> [BY <cols>] [USING (...)]
    PROJECT [<aesthetics>] TO <type> [USING (...)]
    LABEL USING (...)

Would love to hear your thoughts.
Also feel free to end this issue with a harsh comment if this left too many "let me change everything" vibes (not my intention).

[ankoh]

Examples:

      VISUALISE (SELECT * FROM sales)
        DRAW point AS (SELECT date AS x, revenue AS y)
        PLACE text USING (label => 'top seller')
        SCALE continuous y FROM [0] TO [1000]
        FACET region BY fiscal_year
        PROJECT TO cartesian
        LABEL USING (title => 'Sales');

https://github.com/ankoh/dashql/blob/093df8ba05e71c6680fe1cd268086d0e6d4e83b4/snapshots/parser/vis_visualise.yaml#L1885

      VISUALISE (SELECT * FROM sales)
        DRAW line AS (SELECT date AS x, revenue AS y)
        DRAW point AS (SELECT date AS x, revenue AS y) USING (size => 4);

https://github.com/ankoh/dashql/blob/093df8ba05e71c6680fe1cd268086d0e6d4e83b4/snapshots/parser/vis_visualise.yaml#L1120

[teunbrand]

This is in no way a full response to your post, but some initial thoughts and a humble request for clarification.

The VIA keyword confused me. If you develop ambitions to extend any of the major database grammars, something like an integer transform just becomes a normal expression before the alias clause, e.g. log10(foo) as x or cast(foo as integer) as x.

I think I'm confused by what part of VIA has confused you? The VIA keyword is a part of a scale and announces a named transformation. It is not (just) there for casting, it is also there to signal sensible strategies for axis-breaks. For example, the integer transform will ensure axis labels are 1, 2, 3 etc instead of 0.0, 2.5, 5.0 etc. The log transform will prefer 10, 100, 1000 etc.

REMAPPING has the additional twist that it's redundant if you decide to merge VIA into MAPPING

I don't understand what you mean by 'merging VIA into MAPPING', could you expand on this point? Are you thinking about MAPPING log10(foo) as x? If so, see the next bit.

MAPPING and REMAPPING feel a bit odd, they are very close to SELECT, you just chose a different name.

Mapping does not take expressions in front of AS, it only takes identifiers. Aside from this technical difference, it also has a semantic difference that MAPPING is not just an aliassing operation, but pairs a column to a scale as well.

[ankoh]

I think I'm confused by what part of VIA has confused you? The VIA keyword is a part of a scale and announces a named transformation. It is not (just) there for casting, it is also there to signal sensible strategies for axis-breaks. For example, the integer transform will ensure axis labels are 1, 2, 3 etc instead of 0.0, 2.5, 5.0 etc. The log transform will prefer 10, 100, 1000 etc.

Excellent, thanks for clarifying. Yes, I was indeed not understanding fully what VIA is actually supposed to do.
The problem that I have with VIA is that it feels close enough to a proper SQL expression that it's a shame it isn't one.

I guess my version has no answer for breaks and projecting scale values...

[ankoh]

Mapping does not take expressions in front of AS, it only takes identifiers. Aside from this technical difference, it also has a semantic difference that MAPPING is not just an aliassing operation, but pairs a column to a scale as well.

In your parser, true. But this feels like an artificial limitation.
Correct me if I misunderstood but:

DRAW <layer-type>
  MAPPING <mapping>, ... FROM <data-source>

Here mapping is just a DRAW-specific view on the data since you want to be able to conveniently declare other variables as x and y etc, no?
And wouldn't that be way more intuitive if we'd just treat this as a proper SQL select clause as in:

      VISUALISE (SELECT * FROM sales)
        DRAW line AS (SELECT date AS x, revenue AS y)
        DRAW point AS (SELECT date AS x, revenue AS y) USING (size => 4);

Then you wouldn't really need to explain SQL-affine people (your ai) what the difference between MAPPING, REMAPPING and SELECT is, it's all just projections... expressions and aliases

[ankoh]

        SCALE continuous x FROM [0, 100] TO [0, 1000]
          TRANSFORM log10
          USING (breaks => 5)
          FORMAT (lambda v: case v when .... end)

I guess this could work.
Anyway, I realized that I'll have to drill into this deeper than the parser to not jump to conclusions too quickly.

I just wanted to emphasize that being not only a dedicated transpiler but a SQL grammar extensions compatible to grammars like Postgres would be very desirable.