arjitmalviya89 opened a new issue, #42019:
URL: https://github.com/apache/superset/issues/42019
### Bug description
### Bug Description
The **"Format SQL"** button in SQL Lab changes the SQL dialect/syntax
instead of only reformatting (indenting/prettifying) the query.
PostgreSQL-specific operators and casts are being converted to generic SQL
functions, which breaks the query.
This worked correctly in **Superset 5.0** but is broken in **6.0**.
### How to Reproduce
1. Open SQL Lab connected to a **PostgreSQL** database
2. Write the following valid PostgreSQL query:
```sql
SELECT DISTINCT
data->>'name' AS name,
((data->>'price')::NUMERIC) AS price
FROM products,
jsonb_array_elements(metadata->'categories'->'items') AS data
WHERE id = COALESCE(NULLIF('{{ url_param("id", "") }}', ''),
(SELECT (MAX(NULLIF(id, ''))::NUMERIC)::TEXT FROM products))
AND status = 'active'
AND metadata ->> 'type' = 'electronics'
```
3. Click **"Format SQL"** button
### Expected Results
The query should be **indented/prettified** while preserving the original
PostgreSQL syntax:
- `->>` operator should remain as `->>`
- `->` operator should remain as `->`
- `::NUMERIC` cast should remain as `::NUMERIC`
- `::TEXT` cast should remain as `::TEXT`
### Actual Results
The formatter **transpiles** the query into a different SQL dialect:
| Original PostgreSQL Syntax | After Format SQL (Broken) |
|---|---|
| `data->>'name'` | `JSON_EXTRACT_SCALAR(data, '$.name')` |
| `(data->>'price')::NUMERIC` | `CAST(JSON_EXTRACT_SCALAR(data, '$.price')
AS DECIMAL)` |
| `metadata->'categories'->'items'` | `JSON_EXTRACT('categories',
'$.items')` |
| `metadata ->> 'type'` | `JSON_EXTRACT_SCALAR(metadata, '$.type')` |
The formatted query fails because `JSON_EXTRACT_SCALAR` and `JSON_EXTRACT`
are not valid PostgreSQL.
### Root Cause
In `superset/sql/parse.py`, `SQLStatement.format()` calls
`Dialect.generate()` which normalizes PostgreSQL-specific operators to generic
SQL equivalents instead of preserving them.
### Additional Context
- Worked correctly in Superset 5.0
- Query executes perfectly before formatting
- Any query using PostgreSQL JSON operators or :: casts is affected
- Installation: Docker (apache/superset:latest)
### Screenshots/recordings
before using formate sql
<img width="1134" height="354" alt="Image"
src="https://github.com/user-attachments/assets/eb59bae0-e0b0-4565-ab0e-06295ed12fae";
/>
after using formate sql
<img width="1043" height="342" alt="Image"
src="https://github.com/user-attachments/assets/e9cee534-fd81-4d91-ac1f-96efbaba0b83";
/>
**error
<img width="1175" height="291" alt="Image"
src="https://github.com/user-attachments/assets/2d709fae-8062-4fa1-b2a6-284f8f0e20a9";
/>**
### Superset version
6.0.0
### Python version
I don't know
### Node version
I don't know
### Browser
Chrome
### Additional context
- This worked correctly in Superset 5.0 — the formatter only
indented/prettified without changing operators
- The query executes perfectly before formatting
- After formatting, the query fails because JSON_EXTRACT_SCALAR and
JSON_EXTRACT are not valid PostgreSQL functions
- Any query using PostgreSQL JSON operators (->, ->>) or :: type casts is
affected
- Installation: Docker (apache/superset:latest)
### Checklist
- [ ] I have searched Superset docs and Slack and didn't find a solution to
my problem.
- [ ] I have searched the GitHub issue tracker and didn't find a similar bug
report.
- [ ] 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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
