Error: Custom SQL fields cannot contain sub-queries. When upgrading from 0.999 to 2.01. SQL in Filters

[xxcmp1977]

Hi!

I upgraded my superset from old 0.999dev to 2.01 and Got an error
Error: Custom SQL fields cannot contain sub-queries
on my charts
It's caused by Filter in most of my charts with SQL subquery in "Custom SQL" tab
Something like
Filters - Add - CUSTOM SQL
"WHERE"

ID not in (select id from someothertable where somefield='somevalue')

And everything works well when removing this filter

Is it a bug of new version?
And should I consider using other features to achieve same results ?

[xxcmp1977]

Generally, the problem is that I want to have a chart of sales by day from my database.
But I should not include sales to some group of clients (employes)
Scheme is like this:

table Orders:
orderId
clientId
orderdate
ordersum

table Clients:
clientId
clientName
clientGroupId

table ClientGroups
clientGroupId
clientGroupName

When I was on 0.999 version of superset I added table Orders as my Dataset
Visualization type: Table
Aggregate query mode with Dimensions on orderdate column and Sum of ordersum as Metrics
Then I added filter with custom sql value as

clientId not in (select clientid from clients where clientGroupId=1)

It worked perfect

I know I can use custom sql with joined tables as datasource now and filter on joined clientGroupId value
But I thought there is some another way achieving it
May be with new filters ?

[mpalmujoki]

We have encountered the same problem.

Has this been examined or did you find a solution to enable it?

[xxcmp1977]

No
I had to rewrite datasets 'cause of this

[fmannhardt]

Encountered the same problem. The issue seems to be an overly aggressive detection of sub queries in filters. For example trying to extract epoch from a PostgreSQL interval causes this. My guess it is the "FROM" keyword. For example in something as:

extract(epoch FROM (ts_end - ts_start)) * 1000 AS duration_ms

I logged this as an issue here:
#25072

[kperveen]

Getting the same error when I use JOINS

[LeftMonster]

+1 same issue here

[zero-stroke]

I was trying to just add some columns to a table when I got this error, without any kind of aggregation or anything. I was able to get around it by clicking on the arrow next to the column name, and going to "Custom SQL", then just putting some single quotes around the column name. Like 'column name'

[nathan-gilbert]

I am experiencing this too after upgrading to 4.1.1, but @zero-stroke solution fixes it. It does seem to be a bug though and that existing text in these columns wouldn't be flagged as SQL just because it has () or % in it.

That fixes it, but we didn't have to do that on every column before 4.1.1

[nathan-gilbert]

Really, need double quotes and not single quotes to get back to where we were before 4.1.1, fwiw