Fatal error wile executing query

[devysh1907]

WITH data AS (
SELECT
COALESCE(bur.region_id, 'Unknown') AS region,
br.rm_email,
br.enquiry_number,
br.status,
br.created_on
FROM dl_buyer_rfq br
LEFT JOIN buyer_account ba
ON UPPER(ba.crm_buyer_id) = UPPER(br.crm_buyer_id)
LEFT JOIN business_user_region bur
ON UPPER(bur.user_id) = UPPER(br.rm_id)
WHERE br.source = 'ENQUIRY'
AND br.status != 'REQUEST_RECEIVED'
AND br.rm_name != 'Sidhanta Agarwal'
AND br.created_on >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '4 months'
)
SELECT
data.region,
COUNT(DISTINCT CASE WHEN date(created_on) = date(CURRENT_DATE) THEN rm_email END) AS "Active RMs Today",
COUNT(DISTINCT CASE WHEN date(created_on) = date(CURRENT_DATE) THEN enquiry_number END) AS "Enquiry Today",
COUNT(CASE WHEN date(created_on) = date(CURRENT_DATE) AND status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Today",
COUNT(DISTINCT CASE WHEN date(created_on) = date(CURRENT_DATE - INTERVAL '1 day') THEN rm_email END) AS "Active RMs Yesterday",
COUNT(DISTINCT CASE WHEN date(created_on) = date(CURRENT_DATE - INTERVAL '1 day') THEN enquiry_number END) AS "Enquiry Yesterday",
COUNT(CASE WHEN date(created_on) = date(CURRENT_DATE - INTERVAL '1 day') AND status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Yesterday",
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('week', CURRENT_DATE)) THEN rm_email END) AS "Active RMs This Week",
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('week', CURRENT_DATE)) THEN enquiry_number END) AS "Enquiry This Week",
COUNT(CASE WHEN date(created_on) >= DATE_TRUNC('week', CURRENT_DATE) AND status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders This Week",
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) THEN rm_email END) AS "Active RMs This Month",
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) THEN enquiry_number END) AS "Enquiry This Month",
COUNT(CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) AND status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders This Month",
-- Last Month
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) THEN rm_email END) AS "Active RMs Last Month",
COUNT(DISTINCT CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) THEN enquiry_number END) AS "Enquiry Last Month",
COUNT(CASE WHEN date(created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) AND status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Last Month"
FROM data
GROUP BY data.region
ORDER BY data.region

While I run this query and save dataset it say fatal error

[dosubot[bot]]

A similar issue was reported in a closed thread, but no solution was found in the comments ^[1].

Common causes of fatal errors in Apache Superset when executing SQL queries include syntax errors, database connection issues, non-existent database objects, timeouts, security and permissions issues, invalid queries, results backend issues, and payload or template issues ^[2].

To troubleshoot, you might want to check for any syntax errors in your SQL query, ensure that all database objects (tables, columns) exist, and verify that you have the necessary permissions. Additionally, consider checking the Superset logs for more detailed error messages that could provide further insights into the issue.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[devysh1907]

@dosu
500 Internal Server Error was coming

[dosubot[bot]]

A 500 Internal Server Error in Apache Superset can be caused by various issues, including database connection errors, SQL syntax errors, or server configuration problems. To diagnose the issue, you can check the Superset logs for more detailed error messages. These logs might provide specific information about the error, such as whether it's related to a database connection issue or a problem with the SQL query itself. Additionally, ensure that your database connection settings are correct and that the SQL query is valid and does not exceed any execution limits or constraints set by Superset ^[1][2].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[devysh1907]

@dosu
2025-02-14 12:14:11,626:ERROR:flask_appbuilder.api:Only SELECT statements are allowed
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/api/init.py", line 111, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 119, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1364, in time_function
response = func(*args, **kwargs)
File "/app/superset/utils/log.py", line 303, in wrapper
value = f(*args, **kwargs)
File "/app/superset/views/base_api.py", line 91, in wraps
return f(self, *args, **kwargs)
File "/app/superset/datasets/api.py", line 337, in post
new_model = CreateDatasetCommand(item).run()
File "/app/superset/utils/decorators.py", line 266, in wrapped
return on_error(ex)
File "/app/superset/utils/decorators.py", line 236, in on_error
raise ex
File "/app/superset/utils/decorators.py", line 259, in wrapped
result = func(*args, **kwargs)
File "/app/superset/commands/dataset/create.py", line 51, in run
dataset.fetch_metadata()
File "/app/superset/connectors/sqla/models.py", line 1814, in fetch_metadata
new_columns = self.external_metadata()
File "/app/superset/connectors/sqla/models.py", line 1377, in external_metadata
return get_virtual_table_metadata(dataset=self)
File "/app/superset/connectors/sqla/utils.py", line 110, in get_virtual_table_metadata
raise SupersetSecurityException(
superset.exceptions.SupersetSecurityException: Only SELECT statements are allowed

[dosubot[bot]]

The error "Only SELECT statements are allowed" indicates that your SQL query contains operations other than SELECT, which are not permitted when creating a dataset in Apache Superset. This restriction is enforced to prevent any data mutations or changes to the database during dataset creation ^[1].

To resolve this issue, ensure that your SQL query only includes SELECT statements. Review the query to check for any unintended operations like INSERT, UPDATE, DELETE, or any other non-SELECT SQL commands. If your query is already a SELECT statement, double-check for any syntax or structural issues that might be misinterpreted as non-SELECT operations.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[devysh1907]

@dosu
SELECT
COALESCE(bur.region_id, 'Unknown') AS region,
COUNT(DISTINCT CASE WHEN date(br.created_on) = date(CURRENT_DATE) THEN br.rm_email END) AS "Active RMs Today",
COUNT(DISTINCT CASE WHEN date(br.created_on) = date(CURRENT_DATE) THEN br.enquiry_number END) AS "Enquiry Today",
COUNT(CASE WHEN date(br.created_on) = date(CURRENT_DATE) AND br.status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Today",
COUNT(DISTINCT CASE WHEN date(br.created_on) = date(CURRENT_DATE - INTERVAL '1 day') THEN br.rm_email END) AS "Active RMs Yesterday",
COUNT(DISTINCT CASE WHEN date(br.created_on) = date(CURRENT_DATE - INTERVAL '1 day') THEN br.enquiry_number END) AS "Enquiry Yesterday",
COUNT(CASE WHEN date(br.created_on) = date(CURRENT_DATE - INTERVAL '1 day') AND br.status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Yesterday",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('week', CURRENT_DATE)) THEN br.rm_email END) AS "Active RMs This Week",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('week', CURRENT_DATE)) THEN br.enquiry_number END) AS "Enquiry This Week",
COUNT(CASE WHEN date(br.created_on) >= DATE_TRUNC('week', CURRENT_DATE) AND br.status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders This Week",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) THEN br.rm_email END) AS "Active RMs This Month",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) THEN br.enquiry_number END) AS "Enquiry This Month",
COUNT(CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) AND br.status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders This Month",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(br.created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) THEN br.rm_email END) AS "Active RMs Last Month",
COUNT(DISTINCT CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(br.created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) THEN br.enquiry_number END) AS "Enquiry Last Month",
COUNT(CASE WHEN date(br.created_on) >= date(DATE_TRUNC('month', CURRENT_DATE)) - INTERVAL '1 month'
AND date(br.created_on) < date(DATE_TRUNC('month', CURRENT_DATE)) AND br.status = 'CONVERTED_TO_ORDER' THEN 1 END) AS "Orders Last Month"
FROM dl_buyer_rfq br
LEFT JOIN buyer_account ba
ON UPPER(ba.crm_buyer_id) = UPPER(br.crm_buyer_id)
LEFT JOIN business_user_region bur
ON UPPER(bur.user_id) = UPPER(br.rm_id)
WHERE br.source = 'ENQUIRY'
AND br.status != 'REQUEST_RECEIVED'
AND br.rm_name != 'Sidhanta Agarwal'
AND br.created_on >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '4 months'
GROUP BY bur.region_id
ORDER BY bur.region_id;

chart is giving
Custom SQL fields cannot contain sub-queries.

[dosubot[bot]]

The error "Custom SQL fields cannot contain sub-queries" suggests that Apache Superset is interpreting part of your query as a sub-query, which is not allowed in custom SQL fields. However, your query does not explicitly contain any sub-queries. This might be a misinterpretation by Superset's SQL validation logic.

To address this, ensure that your query structure is as straightforward as possible. Here are a few suggestions:

1. Simplify the Query: Break down complex expressions or conditions that might be misinterpreted as sub-queries. For example, ensure that all CASE statements are straightforward and do not involve nested logic that could be seen as a sub-query.

2. Check for Misinterpretations: Sometimes, certain SQL constructs or functions might be misinterpreted as sub-queries. Ensure that all functions and expressions are supported and correctly used.

3. Review SQL Syntax: Double-check the SQL syntax to ensure there are no errors or unconventional uses of SQL that might confuse the parser.

If the issue persists, consider testing the query in smaller parts to identify which specific part might be causing the misinterpretation.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}