[SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements #3998

I am unable to reproduce this bug. As we're currently using sqlparse==0.2.4, and CTEs were added in 0.2.0 (see https://github.com/andialbrecht/sqlparse/issues/227 and https://github.com/andialbrecht/sqlparse/commit/8f39d3367e8878e5f9a09b0166e3f0f73626e67d), this works in the current version of Superset. Please let me know if I am missing something, otherwise I propose closing this issue.

I am also facing a similar issue.I am using CTE.I get the same response as above.

@sangramdhal102 Can you post your (anonymized) query which fails? Better yet, can you write a CTE which references one of the tables in the Superset datamodel?

with total_tasks_per_project AS ((select extract('hour' from soapgatewayservice_received ) AS hour,modality,count(ingestion_status) as Total,count(case lower(ingestion_status) when 'completed' then 1 else null end) as completed,count(case lower(ingestion_status) when 'failed' then 1 else null end) as failed,count(case lower(ingestion_status) when 'inprocess' then 1 else null end) as inprocess, avg(ingestioncheckpointservice_completed-soapgatewayservice_received) AS avg_ingestion_time from provenance.provenancedata where (soapgatewayservice_received >= 'startdate’ and soapgatewayservice_received <= 'enddate' ) and modality in('CV','DC','MR','ICAP')group by modality, hour order by modality) UNION (select extract('hour' from restingestiongatewayservice_received ) AS hour,modality,count(ingestion_status) as Total,count(case lower(ingestion_status) when 'completed' then 1 else null end) as completed ,count(case lower(ingestion_status) when 'failed' then 1 else null end) as failed,count(case lower(ingestion_status) when 'inprocess' then 1 else null end) as inprocess, avg(ingestioncheckpointservice_completed-restingestiongatewayservice_received) AS avg_ingestion_time from provenance.provenancedata where (restingestiongatewayservice_received >='startdate' and restingestiongatewayservice_received <=’enddate’) and modality ='CT'group by modality, hour))select hour,modality,total,completed,failed,inprocess,avg_ingestion_time from total_tasks_per_project order by hour))select hour,modality,total,completed,failed,inprocess,avg_ingestion_time from total_tasks_per_project order by hour;

This is pulling data from one table only

Thanks @sangramdhal102 , I was able to reproduce the error. Will investigate.

@sangramdhal102 The query you posted does not validate using standard sql validator. What dialect is this? Furthermore, does it work when run in a native terminal?

Yes ..it does work .i am able to run the query in pgadmin.

@sangramdhal102 I'm surprised the query runs, as you seem to have mixed ' and ’. Example: soapgatewayservice_received >= 'startdate’. I believe that's what is confusing sqlparse. I'm not an expert in Postgres, but I would perhaps refactor the query to make it slightly easier to read. Something along these lines:

with total_tasks_per_project as (
select 
  extract('hour' from soapgatewayservice_received) as hour,
  modality,
  case lower(ingestion_status) when 'completed' then 1 else null end as completed,
  case lower(ingestion_status) when 'failed' then 1 else null end as failed,
  case lower(ingestion_status) when 'inprocess' then 1 else null end as inprocess,
  ingestioncheckpointservice_completed - soapgatewayservice_received as ingestion_time
from
  provenance.provenancedata 
where
  soapgatewayservice_received >= 'startdate'
  and soapgatewayservice_received <= 'enddate'
  and modality in ('CV','DC','MR','ICAP')
union
select
  extract('hour' from restingestiongatewayservice_received) as hour,
  modality,
  case lower(ingestion_status) when 'completed' then 1 else null end as completed,
  case lower(ingestion_status) when 'failed' then 1 else null end as failed,
  case lower(ingestion_status) when 'inprocess' then 1 else null end as inprocess,
  ingestioncheckpointservice_completed - restingestiongatewayservice_received as ingestion_time
from
  provenance.provenancedata
where 
  restingestiongatewayservice_received >= 'startdate'
  and restingestiongatewayservice_received <= 'enddate'
  and modality = 'CT'
)
select 
  hour,
  modality,
  count(*) as total,
  count(completed) as completed,
  count(failed) as failed,
  count(inprocess) as inprocess,
  avg(ingestion_time) as avg_ingestion_time
from 
  total_tasks_per_project
group by
  hour,
  modality
order by
  hour

The query failed to execute.

As I don't have access to your database, this is as far as I can help you. At any rate this didn't seem to be an issue with Superset, but rather the underlying query.

Currently we use Python's sqlparse to figure out the statement type, when it's a CTE, it says UNKNOWN.

Here's where the current logic is: https://github.com/apache/incubator-superset/blob/master/superset/sql_parse.py#L34

It may be ok to look whether the query starts with a WITH token and confirm that no DELETE or UPDATE tokens are present in the query (as CTEs can be used in DML contexts).

@mistercrunch Did you see my reply above (10 days ago)? Running the query in the initial bug report does not produce an error on the master branch, as sqlparse.parse(query)[0].get_type(), which is essentially what is_select() is calling, returns SELECT for the CTE query in question on the current version of sqlparse.

<img width="943" alt="screen shot 2018-04-02 at 20 59 33" src="https://user-images.githubusercontent.com/33317356/38208202-f4308c16-36b8-11e8-9d74-04d8f085a44c.png">

Oh I ran a quick test with a very basic CTE against MySQL and get_type() returned UNKNOWN

Actually I take this back, I was facing competing issues with my local MySQL not supporting CTE to test this...

This shouldn't be database type specific, as sqlparse is used prior to query execution. So if this has a database dependency, then the problem is probably somewhere else in the code.

@rororofff has funded $5.00 to this issue.

• Submit pull request via IssueHunt to receive this reward.
• Want to contribute? Chip in to this issue via IssueHunt.
• Checkout the IssueHunt Issue Explorer to see more funded issues.
• Need help from developers? Add your repository on IssueHunt to raise funds.

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.