(superset) 01/01: fix(sqllab): Set explicit Content-Type headers to prevent HTTP 406 errors

[ma...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

maximebeauchemin pushed a commit to branch issue-36072-sql-lab-where
in repository https://gitbox.apache.org/repos/asf/superset.git

commit 59ddd52789157eda60d5d5efe628a303d14adfdb
Author: Maxime Beauchemin <ma...@gmail.com>
AuthorDate: Thu Nov 13 10:47:44 2025 -0800

    fix(sqllab): Set explicit Content-Type headers to prevent HTTP 406 errors
    
    Fixes #36072 where SQL Lab queries with WHERE clauses failed with
    "Database error: Not acceptable" in Superset v4.1+.
    
    Root cause: Flask 2.3+ (upgraded in v4.1.0) has stricter content
    negotiation that could return HTTP 406 when Content-Type headers
    aren't explicitly set, particularly with ENABLE_PROXY_FIX or certain
    Accept header configurations.
    
    Changes:
    - Add explicit Content-Type headers to /api/v1/sqllab/execute/ and
      /api/v1/sqllab/results/ endpoints
    - Improve error handling with try-except blocks for result fetching
      and JSON serialization
    - Add targeted integration test for WHERE clause queries
    
    The fix ensures Flask 2.3+ doesn't attempt content negotiation that
    could fail, while maintaining backward compatibility.
    
    🤖 Generated with [Claude Code](https://claude.com/claude-code)
    
    Co-Authored-By: Claude <no...@anthropic.com>
---
 superset/sqllab/api.py                  | 37 +++++++++++++++++++++++++--------
 tests/integration_tests/sqllab_tests.py | 35 +++++++++++++++++++++++++++++++
 2 files changed, 63 insertions(+), 9 deletions(-)

diff --git a/superset/sqllab/api.py b/superset/sqllab/api.py
index 906dd72bca..f4ccce7c41 100644
--- a/superset/sqllab/api.py
+++ b/superset/sqllab/api.py
@@ -337,16 +337,32 @@ class SqlLabRestApi(BaseSupersetApi):
         params = kwargs["rison"]
         key = params.get("key")
         rows = params.get("rows")
-        result = SqlExecutionResultsCommand(key=key, rows=rows).run()
+
+        try:
+            result = SqlExecutionResultsCommand(key=key, rows=rows).run()
+        except Exception as ex:
+            logger.exception("Error fetching query results for key=%s", key)
+            return self.response_500(message=str(ex))
 
         # Using pessimistic json serialization since some database drivers can return
         # unserializeable types at times
-        payload = json.dumps(
-            result,
-            default=json.pessimistic_json_iso_dttm_ser,
-            ignore_nan=True,
-        )
-        return json_success(payload, 200)
+        try:
+            payload = json.dumps(
+                result,
+                default=json.pessimistic_json_iso_dttm_ser,
+                ignore_nan=True,
+            )
+        except Exception as ex:
+            logger.exception("Error serializing query results for key=%s", key)
+            return self.response_500(message="Unable to serialize query results")
+
+        # Use json_success with explicit Content-Type to ensure Flask 2.3+ correctly
+        # handles the response and doesn't trigger HTTP 406 errors due to content
+        # negotiation issues with Accept headers or proxy configurations
+        response = json_success(payload, 200)
+        # Explicitly set Content-Type as a safeguard against content negotiation issues
+        response.headers["Content-Type"] = "application/json; charset=utf-8"
+        return response
 
     @expose("/execute/", methods=("POST",))
     @protect()
@@ -410,8 +426,11 @@ class SqlLabRestApi(BaseSupersetApi):
                 if command_result["status"] == SqlJsonExecutionStatus.QUERY_IS_RUNNING
                 else 200
             )
-            # return the execution result without special encoding
-            return json_success(command_result["payload"], response_status)
+            # Return the execution result without special encoding
+            # Set explicit Content-Type to prevent Flask 2.3+ content negotiation issues
+            response = json_success(command_result["payload"], response_status)
+            response.headers["Content-Type"] = "application/json; charset=utf-8"
+            return response
         except SqlLabException as ex:
             payload = {"errors": [ex.to_dict()]}
 
diff --git a/tests/integration_tests/sqllab_tests.py b/tests/integration_tests/sqllab_tests.py
index 99c347d95f..1a1c65e2e0 100644
--- a/tests/integration_tests/sqllab_tests.py
+++ b/tests/integration_tests/sqllab_tests.py
@@ -126,6 +126,41 @@ class TestSqlLab(SupersetTestCase):
                 "engine_name": engine_name,
             }
 
+    @pytest.mark.usefixtures("load_birth_names_dashboard_with_slices")
+    def test_sql_json_where_clause_content_type(self):
+        """
+        Test that queries with WHERE clauses return proper Content-Type headers.
+
+        This test addresses issue #36072 where Flask 2.3+ content negotiation
+        could cause HTTP 406 errors for queries with WHERE clauses, particularly
+        when using ENABLE_PROXY_FIX or certain Accept header configurations.
+        """
+        self.login(ADMIN_USERNAME)
+
+        # Test query with WHERE clause
+        resp = self.client.post(
+            "/api/v1/sqllab/execute/",
+            json={
+                "database_id": self.get_database_by_name("examples").id,
+                "sql": "SELECT * FROM birth_names WHERE name = 'John' LIMIT 5",
+                "client_id": "test_where_1",
+            },
+        )
+
+        # Verify response is successful
+        assert resp.status_code in (200, 202), f"Expected 200/202, got {resp.status_code}"
+
+        # Verify Content-Type header is explicitly set to prevent 406 errors
+        assert "application/json" in resp.headers.get("Content-Type", "")
+
+        # Verify response body is valid JSON
+        data = resp.json
+        assert isinstance(data, dict)
+
+        # If query ran synchronously (200), verify it has data
+        if resp.status_code == 200:
+            assert "data" in data or "query_id" in data
+
     @pytest.mark.usefixtures("load_birth_names_dashboard_with_slices")
     def test_sql_json_dml_disallowed(self):
         self.login(ADMIN_USERNAME)