[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-378137689 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. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377975772 @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`. https://user-images.githubusercontent.com/33317356/38208202-f4308c16-36b8-11e8-9d74-04d8f085a44c.png;> This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377975772 @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`. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377975772 @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 query in question on the current version of `sqlparse`. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377895246 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. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions
(CTE), a.k.a. "with" statements
URL:
https://github.com/apache/incubator-superset/issues/3998#issuecomment-377607727
@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:
```sql
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
```
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions
(CTE), a.k.a. "with" statements
URL:
https://github.com/apache/incubator-superset/issues/3998#issuecomment-377607727
@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:
```sql
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')
group by
modality,
hour
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'
group by
modality,
hour
)
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
```
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions
(CTE), a.k.a. "with" statements
URL:
https://github.com/apache/incubator-superset/issues/3998#issuecomment-377607727
@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:
```sql
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
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
```
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions
(CTE), a.k.a. "with" statements
URL:
https://github.com/apache/incubator-superset/issues/3998#issuecomment-377607727
@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:
```sql
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
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
```
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377230350 @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? This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377224979 Thanks @sangramdhal102 , I was able to reproduce the error. Will investigate. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-377212261 @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? This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-375807662 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. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements
villebro commented on issue #3998: [SQLLab] Allow common table expressions (CTE), a.k.a. "with" statements URL: https://github.com/apache/incubator-superset/issues/3998#issuecomment-375807662 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. This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
