malloydata / malloy

Malloy is an experimental language for describing data relationships and transformations.
http://www.malloydata.dev
MIT License
2k stars 76 forks source link

Add error for aggregates in `group_by` #346

Open anikaks opened 2 years ago

anikaks commented 2 years ago

This query currently runs:

query:  order_items_base -> {
    group_by: user_id
    group_by: brand_count is count(distinct ii.product_brand)
  } 

and produces this SQL

SELECT 
   order_items_base.user_id as user_id,
   count(distinct ii_0.product_brand) as brand_count
FROM `malloy-data.ecomm.order_items` as order_items_base
LEFT JOIN `malloy-data.ecomm.inventory_items` AS ii_0
  ON order_items_base.inventory_item_id = ii_0.id
GROUP BY 1
ORDER BY 2 desc

We should probably not allow this and throw a reasonable error about disallowed aggregates in a group_by, rather than quietly not putting the field in the SQL's GROUP BY.

mtoy-googly-moogly commented 2 years ago

yeah and probably the inverse as well, i wrote code for this but never tested it