Data preview for iceberg partitioned tables(using trino) does not work.

[amir-bashir]

A clear and concise description of what the bug is.

In SQL LAB, previewing iceberg partitioned tables using trino connector is failing.
1 - Superset is reading partition data to show it below the table list.
2 - Then it is showing all columns and data types.
3 - In the last step it is executing trino query to fetch 100 rows for preview.

But this step is failing in my case. It is appending record_count, file_count, total_size and data fields from partition file and appending these four columns as where clause in trino query. Since these fields are not part of the table, trino throws error as shown in the picture below.

How to reproduce the bug

1. Create an iceberg partitioned table in trino
2. Open SQL Lab
3. Select catalog, schema and table from the drop downs.
4. You will see an error "trino error: line 5:7: Column 'record_count' cannot be resolved"

Expected results

The preview should run properly and display the data preview

Actual results

The preview fails with following error.

Environment

(please complete the following information):

• superset version: 3.0.0
• python version: 3.9
• trino version: 428

Checklist

Superset logs are:

Triggering query_id: 44
2024-01-10 13:13:55,636:INFO:superset.sqllab.commands.execute:Triggering query_id: 44
Query 44: Executing 1 statement(s)
2024-01-10 13:13:55,669:INFO:superset.sql_lab:Query 44: Executing 1 statement(s)
Query 44: Set query to 'running'
2024-01-10 13:13:55,669:INFO:superset.sql_lab:Query 44: Set query to 'running'
2024-01-10 13:13:55,752:DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): trino-dev2.digixt.ae:443
2024-01-10 13:13:55,939:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "POST /v1/statement HTTP/1.1" 200 328
2024-01-10 13:13:55,948:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/queued/20240110_131355_04768_yj9fy/y61c87885b3716c3aeb602e582689e4bda331199b/1 HTTP/1.1" 200 328
2024-01-10 13:13:55,956:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/queued/20240110_131355_04768_yj9fy/y845f4804062c99e367543efad25e818547c46f3b/2 HTTP/1.1" 200 337
2024-01-10 13:13:55,964:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/executing/20240110_131355_04768_yj9fy/ya39456ccf05d76175d3ad158e116c2dc82d675ab/0 HTTP/1.1" 200 535
2024-01-10 13:13:56,042:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/executing/20240110_131355_04768_yj9fy/ybca09ab00285e284f9ac19efa111537387f9f501/1 HTTP/1.1" 200 447
Query 44: Running statement 1 out of 1
2024-01-10 13:13:56,045:INFO:superset.sql_lab:Query 44: Running statement 1 out of 1
2024-01-10 13:13:56,165:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "POST /v1/statement HTTP/1.1" 200 327
2024-01-10 13:13:56,243:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/queued/20240110_131356_04769_yj9fy/y18debc4fc194e74fbff9c77d6936c3c0ef40b10f/1 HTTP/1.1" 200 327
2024-01-10 13:13:56,263:DEBUG:urllib3.connectionpool:https://trino-dev2.digixt.ae:443 "GET /v1/statement/queued/20240110_131356_04769_yj9fy/ybd14d8bd60657510e8663226e660c6f4aa3223b7/2 HTTP/1.1" 200 1242
SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 127, in wraps
raise ex
File "/app/superset/views/base_api.py", line 121, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1526, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 93, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 255, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 310, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 121, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 103, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message="trino error: line 5:7: Column 'record_count' cannot be resolved", error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Trino', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
2024-01-10 13:13:56,778:WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 127, in wraps
raise ex
File "/app/superset/views/base_api.py", line 121, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1526, in time_function
response = func(*args, **kwargs)
File "/app/superset/views/base_api.py", line 93, in wraps
return f(self, *args, **kwargs)
File "/app/superset/utils/log.py", line 255, in wrapper
value = f(*args, **kwargs)
File "/app/superset/sqllab/api.py", line 310, in execute_sql_query
command_result: CommandResult = command.run()
File "/app/superset/sqllab/commands/execute.py", line 121, in run
raise ex
File "/app/superset/sqllab/commands/execute.py", line 103, in run
status = self._run_sql_json_exec_from_scratch()
File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
raise ex
File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
return self._sql_json_executor.execute(
File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message="trino error: line 5:7: Column 'record_count' cannot be resolved", error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Trino', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

Additional context

On left side under table name i.e. schools, superset is showing latest partition data. Then it is using this information to create a select query which I have copied from copy button and pasted in the query pad.

[jkleinkauff]

Same here. Seems the same as #25307
I see both errors happening, "partition cannot be resolved" and "column record_count cannot be resolved"

[anandnalya]

I was able to get this working with the following patch which disables partitioning support for Iceberg:

--- a/site-packages/superset/db_engine_specs/trino.py
--- b/site-packages/superset/db_engine_specs/trino.py
@@ -445,6 +445,13 @@
         :returns: The indexes
         """
         try:
-            return super().get_indexes(database, inspector, table_name, schema)
+            indexes = super().get_indexes(database, inspector, table_name, schema)
+            # Handle iceberg tables. Even for non-partitioned tables, it returns a value
+            iceberg_cols_ignore = {"record_count", "file_count", "total_size", "data"}
+            if len(indexes) == 1 and indexes[0].get(
+                "name") == "partition" and iceberg_cols_ignore.issubset(
+                set(indexes[0].get("column_names", []))):
+                return []
+            return indexes
         except NoSuchTableError:
             return []

[rusackas]

Closing this in favor of #25307... but if you think the above diff fixes the issue, maybe it can be generalized a bit to (safely) address the issue on Iceberg and/or other data sources having similar issues?