You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "Habeeb556 (via GitHub)" <gi...@apache.org> on 2024/11/27 15:34:13 UTC

[I] Unable to parse SQL (Dialects.TSQL) [superset]

Habeeb556 opened a new issue, #31183:
URL: https://github.com/apache/superset/issues/31183

   ### Bug description
   
   After upgrade from` 4.0.1` to` 4.1.1` I encountered an issue with permission 
   
   ```
   DB engine Error
   You may have an error in your SQL statement. Unable to parse script
   ```
   
   This error occurred for users to whom I assigned specific permissions on the `dbo` schema only. When I changed the permissions to full access for the entire database, the issue was resolved. I noticed that this problem only happens with the `mssql.py` engine, while the others (MySQL, PostgreSQL, and Oracle) are working fine.
   
   Log:
   
   `WARNING:superset.sql_parse:Unable to parse SQL (Dialects.TSQL): Template(body=[Output(nodes=[TemplateData(data='SELECT ...')])])`
   
   ### Screenshots/recordings
   
   ![image](https://github.com/user-attachments/assets/0a63a19b-9df5-4b23-b82d-646f9f7cdfa0)
   
   
   ### Superset version
   
   master / latest-dev
   
   ### Python version
   
   3.11
   
   ### Node version
   
   I don't know
   
   ### Browser
   
   Not applicable
   
   ### Additional context
   
   _No response_
   
   ### Checklist
   
   - [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
   - [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
   - [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2504279330

   @dosu How Can I handle this as before upgrade?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2506549391

   I have workaround this, using the old `sql_parse.py` script.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2513056209

   Assigned it to you temporarily, @Habeeb556, since we need the query to investigate this more.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2524202098

   Fix is out, as a workaround you can enable the `ENABLE_TEMPLATE_PROCESSING` feature flag.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 closed issue #31183: Unable to parse SQL (Dialects.TSQL)
URL: https://github.com/apache/superset/issues/31183


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "geido (via GitHub)" <gi...@apache.org>.
geido commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2512225946

   @betodealmeida this might also use your help! 🙏


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2524311578

   Awesome! I tried the workaround by adding `ENABLE_TEMPLATE_PROCESSING = True` in the `superset_config.py`, but it didn't work. However, after applying the fix you suggested in `sql_parse.py`, it's working perfectly. I really appreciate your help!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2517607837

   Hello @betodealmeida, actually this issue isn't limited to a specific query. If you attempt to connect any MSSQL database with a dummy table and select from it while granting permissions on a specific schema (set to the default `dbo`), you will encounter this error.
   
   ![image](https://github.com/user-attachments/assets/2182afcd-f8cc-4408-aacc-b94f5754bf91)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2504282989

   <!-- Answer -->
   To handle the issue as before the upgrade, you can try the following steps:
   
   1. **Review SQL Syntax**: Ensure that the SQL queries being executed are fully compatible with the TSQL dialect as interpreted by the `sqlglot` library. Simplify the queries if possible to identify any specific syntax causing the issue.
   
   2. **Check Permissions**: Since the issue seems to be related to permissions on the `dbo` schema, review the permission settings. You might need to adjust the permissions to match the previous configuration before the upgrade. If full access resolves the issue, consider incrementally adjusting permissions to identify the minimum required settings.
   
   3. **Superset Logs**: Examine the Superset logs for any additional error messages or warnings that could provide more context on the parsing failure. This might help identify specific SQL statements or permission settings causing the issue.
   
   4. **Rollback**: If the above steps do not resolve the issue, consider rolling back to version 4.0.1 temporarily while further investigating the changes between versions 4.0.1 and 4.1.1 that might have affected SQL parsing or permission handling.
   
   These steps should help you manage the issue while maintaining the necessary permissions and SQL functionality.
   
   
   <!-- Dosu Comment Footer -->
   *To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2525557400

   @betodealmeida, I have another inquiry also regarding this. Previously `4.0.1` when I select for example `select * from database_name.schema_name.table_name` it was working fine.
   
   I applied the fix but still cannot select with database_name access denied. I required to grant full access `id:` to do that which wasnt before.
   
   ![image](https://github.com/user-attachments/assets/e23f89f7-2e25-41ff-b030-5badeb6c0a47)
   
   ```
   Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,802:DEBUG:superset.sql_parse:Parsing with sqlparse statement: select * from abcm.dbo.temp ;
   Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,816:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) SqlLabRestApi.execute_sql_query.error#033[0m
   Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,816:DEBUG:superset.stats_logger:#033[36m[stats_logger] (timing) SqlLabRestApi.execute_sql_query.time | 122.99154791980982 #033[0m
   Dec  8 11:26:11 ef-extsuperset superset[3696616]: 2024-12-08 11:26:11,842:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) log#033[0m
   ```
   but I am able to select with dbo only:
   
   ![image](https://github.com/user-attachments/assets/41bafd0a-1208-4bc1-b15f-231c1f89c818)
   
   This needed for databases I have in the same instance and calling each other.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2523981664

   OK, I was able to repro this, working on a fix.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2518260378

   @Habeeb556 even if it's not specific, can you share a query where that happens?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2518741139

   Okey, here is a sample with query error debug from superset log:
   
   ```
   Dec  5 01:02:40 ef-extsuperset superset[2876150]: 2024-12-05 01:02:40,146:DEBUG:superset.sql_parse:Parsing with sqlparse statement: Template(body=[Output(nodes=[TemplateData(data='SELECT\n  *\nFROM dbo.[Status]')])])
   Dec  5 01:02:40 ef-extsuperset superset[2876150]: 2024-12-05 01:02:40,149:WARNING:superset.sql_parse:Unable to parse SQL (Dialects.TSQL): Template(body=[Output(nodes=[TemplateData(data='SELECT\n  *\nFROM dbo.[Status]')])])
   Dec  5 01:02:40 ef-extsuperset superset[2876150]: 2024-12-05 01:02:40,154:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) SqlLabRestApi.execute_sql_query.error#033[0m
   Dec  5 01:02:40 ef-extsuperset superset[2876150]: 2024-12-05 01:02:40,154:DEBUG:superset.stats_logger:#033[36m[stats_logger] (timing) SqlLabRestApi.execute_sql_query.time | 142.49966898933053 #033[0m
   Dec  5 01:02:41 ef-extsuperset superset[2876150]: 2024-12-05 01:02:41,173:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) log#033[0m
   ```
   
   This when I am just reviewing table data:
   
   ![image](https://github.com/user-attachments/assets/6946275a-e843-4079-9e50-2e0ffb47fe91)
   
   If I try to write a code also:
   
   ![image](https://github.com/user-attachments/assets/1ffd8983-6d51-49f5-9c8c-029078bd8875)
   
   attached log:
   
   ```
   Dec  5 01:05:43 ef-extsuperset superset[2876153]: 2024-12-05 01:05:43,811:DEBUG:superset.sql_parse:Parsing with sqlparse statement: Template(body=[Output(nodes=[TemplateData(data='select * from Status ;')])])
   Dec  5 01:05:43 ef-extsuperset superset[2876153]: 2024-12-05 01:05:43,814:WARNING:superset.sql_parse:Unable to parse SQL (Dialects.TSQL): Template(body=[Output(nodes=[TemplateData(data='select * from Status ;')])])
   Dec  5 01:05:43 ef-extsuperset superset[2876153]: 2024-12-05 01:05:43,823:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) SqlLabRestApi.execute_sql_query.error#033[0m
   Dec  5 01:05:43 ef-extsuperset superset[2876153]: 2024-12-05 01:05:43,823:DEBUG:superset.stats_logger:#033[36m[stats_logger] (timing) SqlLabRestApi.execute_sql_query.time | 129.73859580233693 #033[0m
   Dec  5 01:05:44 ef-extsuperset superset[2876154]: 2024-12-05 01:05:44,841:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) log#033[0m
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2518761681

   Thanks! I'll take a look!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2524359698

   Awesome! I tried the workaround by adding `ENABLE_TEMPLATE_PROCESSING = True` in the `superset_config.py`, but it didn't work. However, after applying the fix you suggested in `sql_parse.py`, it's working perfectly. I really appreciate your help!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2513054364

   > This error occurred for users to whom I assigned specific permissions on the `dbo` schema only. When I changed the permissions to full access for the entire database `id:`, the issue was resolved. I noticed that this problem only happens with the `mssql.py` engine, while the others (MySQL, PostgreSQL, and Oracle) are working fine.
   
   Right, we need to parse the query in order to determine which catalogs/schemas/tables are being accessed when the user doesn't have full access to the database. If the user has full access we just don't parse the query.
   
   In the past this was done using `sqlparse`, which is very lenient, so it was much harder to get an error when parsing SQL. The problem was that it was much easier to trick the parser, and the parsing was not dialect-specific, so we had many security holes.
   
   We're currently in the process of switching the parsing to `sqlglot`, which has dialect-specific parsing. Unfortunately some engines don't have corresponding `sqlglot` dialects, and we have to fallback to a generic parser.
   
   For MSSQL we use the T-SQL dialect, which AFAIK is the correct one. If you can share the query that's causing problems (anonymized is fine) we can take a look, and file a ticket upstream with `sqlglot`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "Habeeb556 (via GitHub)" <gi...@apache.org>.
Habeeb556 closed issue #31183: Unable to parse SQL (Dialects.TSQL)
URL: https://github.com/apache/superset/issues/31183


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org


Re: [I] Unable to parse SQL (Dialects.TSQL) [superset]

Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #31183:
URL: https://github.com/apache/superset/issues/31183#issuecomment-2504183442

   <!-- Answer -->
   The issue you're encountering seems to be related to SQL parsing for the TSQL dialect in the `mssql.py` engine after upgrading from Superset version 4.0.1 to 4.1.1. Superset uses the `sqlglot` library to handle SQL parsing for different database dialects, including TSQL, by mapping the "mssql" engine to the `sqlglot` dialect `Dialects.TSQL` <sup>[[1]](https://github.com/apache/superset/blob/master/superset/sql/parse.py)</sup>.
   
   The error message suggests that there might be a problem with how SQL statements are being parsed for users with specific permissions on the `dbo` schema. When you grant full access to the entire database, the issue is resolved, indicating that it might be related to permission handling or SQL parsing specific to the `dbo` schema.
   
   To address this issue, you might want to:
   
   1. Verify that the SQL statements being executed are compatible with the TSQL dialect as interpreted by `sqlglot`.
   2. Check if there are any changes in the permission handling logic for the `mssql.py` engine between versions 4.0.1 and 4.1.1 that could affect schema-specific permissions.
   3. Review the Superset logs for any additional error messages or warnings that might provide more context on the parsing failure.
   
   If the problem persists, consider testing with a simplified SQL query to isolate whether the issue is with specific SQL syntax or permission settings.
   
   
   <!-- Dosu Comment Footer -->
   *To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org