metabase-clickhouse-driver icon indicating copy to clipboard operation
metabase-clickhouse-driver copied to clipboard

Filtering on Custom Field Broken

Open enqueue opened this issue 5 years ago • 0 comments

When filtering on a custom field (via expression), the driver uses the human-readable field name instead of the correct alias.

SELECT 
    source.warennummerKN8 AS warennummerKN8, 
    source.`sum-where` AS `sum-where`, 
    source.`sum-where_2` AS `sum-where_2`
FROM 
(
    SELECT 
        rsianalytics.customs_atlasaes.warennummerKN8 AS warennummerKN8, 
        sum(multiIf((rsianalytics.customs_atlasaes.statistik_datum >= parseDateTimeBestEffort('2018-12-31 23:00:00')) AND (rsianalytics.customs_atlasaes.statistik_datum <= parseDateTimeBestEffort('2019-06-29 22:00:00')), rsianalytics.customs_atlasaes.anzahlPositionen, 0.)) AS `sum-where`, 
        sum(multiIf((rsianalytics.customs_atlasaes.statistik_datum >= parseDateTimeBestEffort('2019-12-31 23:00:00')) AND (rsianalytics.customs_atlasaes.statistik_datum <= parseDateTimeBestEffort('2020-06-29 22:00:00')), rsianalytics.customs_atlasaes.anzahlPositionen, 0.)) AS `sum-where_2`
    FROM rsianalytics.customs_atlasaes
    GROUP BY rsianalytics.customs_atlasaes.warennummerKN8
    ORDER BY rsianalytics.customs_atlasaes.warennummerKN8 ASC
) AS source
WHERE source.`Positionen 2019` > 0
LIMIT 2000
FORMAT TabSeparatedWithNamesAndTypes

Received exception from server (version 20.4.5):
Code: 47. DB::Exception: Received from cdb1.dev.riege.de:9000. DB::Exception: There's no column 'source.Positionen 2019' in table 'source'.

See how it's using source.Positionen 2019 instead of sum_where (or sum_where_2).

This is how I reproduce this on our development database, trying to follow the steps in the Metabase blog post on Time Series Comparison

metabase_error_query_alias

The MBQL query from the log:

:query
  {:source-query
   {:source-table 521,
    :aggregation
    [[:aggregation-options
      [:sum-where
       [:field-id 18361]
       [:between
        [:field-id 38231]
        [:absolute-datetime (t/zoned-date-time "2020-01-01T00:00+01:00[Europe/Berlin]") :default]
        [:absolute-datetime (t/zoned-date-time "2020-02-01T00:00+01:00[Europe/Berlin]") :default]]]
      {:display-name "Positionen 2019", :name "sum-where"}]
     [:aggregation-options
      [:sum-where
       [:field-id 18361]
       [:between
        [:field-id 38231]
        [:absolute-datetime (t/zoned-date-time "2020-02-01T00:00+01:00[Europe/Berlin]") :default]
        [:absolute-datetime (t/zoned-date-time "2020-03-01T00:00+01:00[Europe/Berlin]") :default]]]
      {:display-name "Positionen März", :name "sum-where_2"}]],
    :breakout [[:field-id 18791]],
    :order-by [[:asc [:field-id 18791]]]},
   :filter [:> [:field-literal "Positionen 2019" :type/Float] [:value 0 nil]],
   :source-metadata
   [{:name "angemeldetesVerfahren",
     :id 18791,
     :table_id 521,
     :display_name "Angemeldetes Verfahren",
     :base_type :type/Text,
     :special_type :type/Category,
     :fingerprint {:global {:distinct-count 5, :nil% 0.0}, :type {:type/Text {:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :average-length 2.0}}},
     :settings nil}
    {:name "sum-where", :display_name "Positionen 2019", :base_type :type/Integer, :special_type :type/Number, :settings nil}
    {:name "sum-where_2", :display_name "Positionen März", :base_type :type/Integer, :special_type :type/Number, :settings nil}],
   :fields [[:field-literal "angemeldetesVerfahren" :type/Text] [:field-literal "sum-where" :type/Integer] [:field-literal "sum-where_2" :type/Integer]],
   :limit 2000},
  :type :query,
  :middleware {:add-default-userland-constraints? true},
  :info
  {:executed-by 11,
   :context :ad-hoc,
   :nested? false,
   :query-hash [102, 39, 72, -66, -50, -78, -70, -9, 63, -37, 106, 123, 79, 6, -61, 75, -118, 90, 2, 117, 33, 78, -79, -20, -51, -3, -116, 83, -106, 72, -116, 111]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :data {:rows [], :cols []}}

enqueue avatar Jul 01 '20 06:07 enqueue