Custom Expression not working properly depending on column name

Bug reports
#1

Hi,

I’m using Metabase v0.28.5 and I have a PostgreSQL database with a table X with a column called sum_profit. I’m trying to create a “Custom” question in which I use the option Custom Expression to calculate the value I should return in my query. This calculation is:

  • SUM(sum_profit * request_index)

However I receive the following error message Expected field name, number, or expression but found ‘sum’ and I’m not able to save this expression. When I replace sum_profit with another Number column (such as cost), the calculation seems to be correctly performed.

I noticed that this error appear whenever my column name starts with some SQL keyword, such as “count”, “sum”, “avg” and so on. Not sure if it’s an expected behavior of Metabase, but to me it’s really weird.

I also checked this behavior in the newest versions, v0.28.6 and v0.29.0-RC1

#2

Is this a metabase error or Postgresql? In MS SQL, I’d try [sum_profit] or “sum_profit”, no idea if that’s valud in Postgresql.
Sat on a train, so can’t check my test server.

#3

Hi @AndrewMBaines , it seems to be a Metabase error. I run a query in psql terminal using this expression SUM(t.sum_profit * t.request_index) and it worked correctly. I also tried to use Native Query option @ Metabase and it worked as well.

That’s why my hypothesis is that Metabase is doing some kind of “validation” in which it prevents calculation depending on column name

#4

What are the display names for those columns in Metabase? Do they show as Sum Profit and Request Index instead of sum_profit and request_index?

Perhaps you can try SUM("Sum Profit" * "Request Index") (or whatever the column display names are) as your custom expression.

#5

Hi, @william , I disabled the “humanized name” Metabase try to guess to column names. My solution for this was to change the column names in order to change de position of the SQL keyword. So, instead of name the column sum_profit, I called it profit_sum and it worked properly.

Anyways, maybe it’s a good point to review in Metabase custom expression creation. =)

1 Like
#6

I wonder if this is something to do with Metabase being written in Clojure. It has all sorts of functions for converting between ‘_’, ‘-’ and CamelCase. Perhaps somewhere the underscore is being interprested as minus.
No evidence, just conjecture.

Edit: Thinking further, that can’t be right or reversing the order wouldn’t work either.
Just rambiling…