metabase-clickhouse-driver
metabase-clickhouse-driver copied to clipboard
Filtering on Custom Field Broken
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

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 []}}