Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Filtering on Custom Field Broken #56

Open
enqueue opened this issue Jul 1, 2020 · 0 comments
Open

Filtering on Custom Field Broken #56

enqueue opened this issue Jul 1, 2020 · 0 comments
Labels
bug Something isn't working

Comments

@enqueue
Copy link
Contributor

enqueue commented Jul 1, 2020

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 []}}
@slvrtrn slvrtrn removed the prio-low label Feb 6, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants