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

Field filter fails if it references a column from an aliased table #3324

Open
maliayas opened this issue Sep 7, 2016 · 18 comments

Comments

Projects
None yet
@maliayas
Copy link
Contributor

commented Sep 7, 2016

I'm using a filter variable in an SQL query. The query is similar to this:

select bla_bla
from my_table as mt
left join bla_bla
where {{some_date_field}}

some_date_field is a variable with type "Field filter" and it's a column from my_table. However this filter fails in the dashboard. After some investigation, I found that Metabase generates filter's SQL code as my_table.some_column and not mt.some_column which causes a MySQL error like "Unknown column 'my_table.some_column' in 'field list'". Apparently MySQL requires using alias if an alias is defined for that table.

It seems, in the menu where we define field filters, metabase should ask user how to refer to the table & column rather than what table and column to pick. Or this may be integrated into the syntax. E.g.

select bla_bla
from my_table as mt
left join bla_bla
where {{some_date_field:mt.some_column}}

Bug Report Info

  • I'm using this browser:
  • Chrome
  • Firefox
  • Safari
  • Internet Explorer / Edge
  • Other: ____________________
  • My computer's OS is:
  • OS X
  • Windows
  • Linux
  • Other: ____________________
  • I'm running Metabase:
  • Locally from the JAR
  • Locally with the Mac App
  • On AWS (via Docker)
  • On Heroku
  • Other: Local Docker
  • Internally, Metabase is using this database: (This is the database Metabase stores internal information like accounts and dashboards in, not one you're running queries against. If you haven't done anything special to configure this, Metabase defaults to using H2 when running locally; if you're running Metabase on AWS or Heroku using the provided scripts/instructions, you're most likely using Postgres.):
    • H2
    • Postgres
    • MySQL
  • My data is in a database of this type:
  • Postgres
  • MySQL
  • Redshift
  • BigQuery
  • MongoDB
  • SQLite
  • Druid
  • SQL Server
  • Crate
  • Oracle
  • Other: ____________________
  • I'm using Metabase version:
  • 0.18.0
  • 0.18.1
  • 0.19.0
  • 0.19.1
  • 0.19.2
  • 0.19.3
  • Other: ____________________
@salsakran

This comment has been minimized.

Copy link
Contributor

commented Sep 7, 2016

Yes, we don't really support aliased table names in this version of filter tags. Definitely seems like a useful addition.

I like the idea of optionally being able to specify the columns aliased name, as this would solve a number of other use cases we've found where the current approach doesn't work 100% of the time.

@maliayas

This comment has been minimized.

Copy link
Contributor Author

commented Sep 7, 2016

Thanks for your comments. Btw I meant {{some_date_field:mt.some_column}} instead of {{some_date_field:mt.my_table}}. (edited top post)

@nickcarenza

This comment has been minimized.

Copy link

commented Oct 24, 2017

What kind of dev effort would this feature take? Is it something a newcomer to the project could tackle?

@igisho

This comment has been minimized.

Copy link

commented Jan 17, 2018

Dos this issue has any workaround?

@marcoruggine

This comment has been minimized.

Copy link

commented Jan 17, 2018

I'm stuck here as well and it is extremely annoying. Any plan to fix this issue?

@Tim-The-Third

This comment has been minimized.

Copy link

commented Jan 26, 2018

@igisho @marcoruggine workaround is to use the full table name,
so rather than
Select AB.name From Alphabeta as ab

select Alphabeta.name from Alphabeta

(in summary if you plan to use a column as a field filter just don't alias it)

@kimroen

This comment has been minimized.

Copy link
Contributor

commented Feb 28, 2018

Could an alternative be to add behavior where you can output the ID instead of the full part of the query? That way, you could alias all you want.

What are the different behaviors we provide by emitting the full clause at the moment? Could those be handled some other way, maybe?

@kimroen

This comment has been minimized.

Copy link
Contributor

commented Feb 28, 2018

I worked around the issue by using a number as the variable instead of a field filter and providing a link to a different Question to look up the right ID by name.

@probaner

This comment has been minimized.

Copy link

commented Apr 10, 2018

Facing same issue when querying SQL server with column for aliased table. Trying to use it as field filter.

@Woodz

This comment has been minimized.

Copy link

commented May 25, 2018

I would suggest that we review the design of field filters. It doesn't seem practical to have a dropdown of every database, table and then field that you want to filter on when the majority of those are not present in the query. Identifying tables and fields used in the current query (including aliases) seems like a good way to both improve the usability of field filters as well as to address this issue. Thoughts?

@calfzhou

This comment has been minimized.

Copy link

commented Aug 16, 2018

it would be better not automatically include field name when using field filter.
say, i prefer where my_field in {{filter_value}} instead of where {{filter_value}}.

For backward compatibility, add an option to field filter such as "ignore field name?". Seems not very hard to implement.

@maliayas

This comment has been minimized.

Copy link
Contributor Author

commented Aug 17, 2018

@calfzhou: it would be better not automatically include field name when using field filter.

You can aldreay achive that by not selecting field filter as variable type. You have "text", "number" and "date" options which work exactly like your example. "Field filter" type on the other hand needs to know how to refer to the column because the final SQL syntax will change based on what the user selects via the filter box.

@Woodz Thoughts?

I would not suggest parsing the query in order to identify possible tables and aliases. Asking this to the user via a textbox would be a more proper way.

@Woodz I would suggest that we review the design of field filters.

It would be great. So here is what's on my mind.


As far as I see, field filter variable type is useful only for 2 cases:

  1. You want a rich date selector. "Date" type allows you only to pick a single date, where "field filter" and then "date filter" as widget type allows you choose from many date selector types.
  2. You want autocomplete and/or multiselection while filtering on a category column. Metabase achieves this via SQL constructs like some_column in ("name 1", "name 2")

Now I'll try to reimagine the UX to achieve above requirements as well as addressing this GitHub issue.

Case # 1 only needs to know "how to refer to the column". A textbox in the variable details UI would be enough for this. You can enter my_table.some_column or mt.some_column if you're using an alias. Further details are irrelevant to Metabase, such as which specific table&column is that.

Case # 2 on the other hand needs to know both "how to refer to the column" and "which specific table&column is that". This is because Metabase will retrieve values from that column in order to provide autocomplete. So this UX can be achieveable by a textbox just like above and a db/table/column picker which we already have in the current variable details UI.

So I propose to both:

  1. Add a fifth option to the variable type selectbox: Text, Number, Date, Rich Date, Field Filter. New Rich Date type will solve Case # 1 above.
  2. Remove "Date Filter" widget type from "Field Filter" variable type. Because it's now Rich Date, no longer a field filter.

Furthermore, Date and Rich Date can even be combined into one item, since the latter is the superset of the former.

This is my 2 cents. I hope the issue gets fixed in the most simple and consistent way.

@leorochael

This comment has been minimized.

Copy link

commented Aug 24, 2018

Being able to refer to aliases on a Field Fielter is a requirement when doing a query with an auto-join.

For example, imagine a Persons table with columns for Biological Father and Biological Mother that are foreign keys into the same Persons table.

In a query where I'm trying to filter on columns of the father or mother, I'll have to use one or more aliiases to refer to the same table.

I think it's a must to be able to to specify the alias, either using a field on the variable form, as suggested by others here, or to have some form of syntax on the variable definition inside the SQL.

The syntax option could be something like {{table_alias.variable_name}}, as in:

SELECT
   alias1.column1 AS alias1_column1,
   alias2.column1 AS alias2_column1
FROM sometable AS alias1
INNER JOIN sometable AS alias2 ON (alias1.join_id = alias2.id)
[[ WHERE {{alias1.my_variable}} ]]
@lwqcz

This comment has been minimized.

Copy link

commented Aug 27, 2018

Guys, any plans for this feature/repair? To have no feature to use aliases in complex SQLs and widgets is really a pain. 🤕 Thanks.

EDIT: I have a fuly working workaround guys 🎉

Common approach width {{dateFieldWidget}} pointed to t2.received_at:

SELECT * FROM 
   table1 t1 
JOIN table2 t2 ON t2.t1_id = t1.id
WHERE {{dateFieldWidget}}

Would lead to psql erros like:

org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "table2" Hint: Perhaps you meant to reference the table alias "t2".

But another approach using filtering in subqueries like:

SELECT * FROM table1 t1
JOIN table2 t2 ON t2.t1_id = t1.id
WHERE t2.received_at >= (SELECT DATE_TRUNC('day', received_at) FROM table2 WHERE {{dateFieldFilter}} ORDER BY received_at ASC LIMIT 1) AND
    t2.received_at <= (SELECT DATE_TRUNC('day', received_at) FROM table2 WHERE {{dateFieldFilter}} ORDER BY received_at DESC LIMIT 1)

Will give you and working Question 💛

EDIT: It is again just a workaround but - if you ditch all aliases then everything works like it should.

Example - instead of this:

SELECT * FROM 
   table1 t1 
JOIN table2 t2 ON t2.t1_id = t1.id
WHERE {{dateFieldWidget}}

Just use this form:

SELECT * FROM 
   table1 
JOIN table2 ON table2.t1_id = table1.id
WHERE {{dateFieldWidget}}
@Nufan1121

This comment has been minimized.

Copy link

commented Jul 11, 2019

Apologies if this has been chase elsewhere, this is the only issue i was able to find that matches my exact and current problem. Is there any traction on this Open issue please? It seems communication has halted since '27 Aug 2018' - this would be an extremely useful feature for us.

I can confirm the filters work perfectly as an intended in my tests sans the table alias. It would be extremely unfortunate to have to correct the duplicate column names in the structure of my tables in order to use these brilliant filter selection features.

@flamber

This comment has been minimized.

Copy link
Contributor

commented Jul 11, 2019

Hi @Nufan1121 Not sure what you mean by traction, but this is still an open issue. Don't understand the problem with duplicate columns, since this should only be an issue for table aliases.
If you need help troubleshooting, then please use the forum: https://discourse.metabase.com/

@Nufan1121

This comment has been minimized.

Copy link

commented Jul 11, 2019

Hi @Nufan1121 Not sure what you mean by traction, but this is still an open issue. Don't understand the problem with duplicate columns, since this should only be an issue for table aliases.
If you need help troubleshooting, then please use the forum: https://discourse.metabase.com/

Hi @flamber , thank you for the quick reply. Apologies for being vague, i believe my problem is the same as this one as we have two tables with identical column names (which is what i meant by duplicate column names). This is why we must use aliases or restructure to the table entirely in order to be able to use the field type filter.

I appreciate this i still open, i was hoping to identify if there had been any progress made on this since 27 Aug 2018? I will of course review the link for forums and see if there has been any mention/progress of this issue on there too.

@flamber

This comment has been minimized.

Copy link
Contributor

commented Jul 11, 2019

@Nufan1121
Any progress about issues is tracked here. The forum is for questions/troubleshooting.
But you shouldn't have any issues with aliased columns, it's an issue with aliased tables.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.