ysinghc opened a new pull request, #35848:
URL: https://github.com/apache/superset/pull/35848
fix(db2): use DATE() function for DAY time grain to fix sqlglot parsing error
### SUMMARY
Fixes the DB2 DAY time grain expression to use the standard `DATE()`
function instead of complex timestamp arithmetic that could not be parsed by
sqlglot. This resolves chart creation failures when applying daily time grains
to timestamp columns in DB2 databases.
**Problem:**
The previous implementation used an invalid SQL expression for the DAY time
grain:
```sql
CAST({col} as TIMESTAMP) - HOUR({col}) HOURS - MINUTE({col}) MINUTES -
SECOND({col}) SECONDS - MICROSECOND({col}) MICROSECONDS
```
This syntax could not be parsed by sqlglot, causing `ParseError` exceptions
and preventing users from creating charts with daily aggregations on DB2
databases.
**Solution:**
Changed the `TimeGrain.DAY` expression to use the simpler and more standard:
```sql
DATE({col})
```
This approach:
- Is valid DB2 SQL syntax that sqlglot can parse correctly
- Is more efficient and cleaner than the previous implementation
- Follows the same pattern used by MySQL's engine spec
- Maintains compatibility with all DB2 versions
### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
N/A - Backend fix for SQL generation
### TESTING INSTRUCTIONS
1. Connect Superset to a DB2 database with a table containing timestamp
columns
2. Create a chart using a timestamp column
3. Apply a "Day" time grain to the timestamp column
4. Verify the chart loads successfully without ParseError
5. Run unit tests: `pytest test_db2.py -v`
6. Verify all 14 tests pass, including the new
`test_time_grain_day_parseable` test
### ADDITIONAL INFORMATION
- [x] Has associated issue: Fixes #35792
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Migration is atomic, supports rollback & is backwards-compatible
- [ ] Confirm DB migration upgrade and downgrade tested
- [ ] Runtime estimates and downtime expectations provided
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
**Changes:**
- Modified db2.py - Updated `TimeGrain.DAY` expression
- Enhanced test_db2.py - Added comprehensive time grain tests including
sqlglot parsing validation
**Test Coverage:**
- Added `test_time_grain_expressions` - Validates all time grain SQL
expressions
- Added `test_time_grain_day_parseable` - Specifically tests that DAY grain
produces parseable SQL
- All existing DB2 tests continue to pass
--
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]
