QuakeWang opened a new issue, #11215:
URL: https://github.com/apache/paimon/issues/11215
### Search before asking
- [x] I searched the Paimon issues and pull requests for an existing
`DATE_TRUNC` proposal for Ossie semantic views.
### Motivation
#11132 added support for querying single-table Ossie models as native Spark
4.2 Metric Views. A model should be able to declare `DATE_TRUNC('month',
order_date)` once and let users group `MEASURE(revenue)` by that dimension. The
pinned Ossie expression spec lists `DATE_TRUNC` as required. [Ossie expression
spec](https://github.com/apache/ossie/blob/2692b1cc4708fdcf6a91e597ca928a9bc9d0da60/core-spec/expression_language.md#date-truncation-required)
#### Minimal reproduction
Use Spark 4.2.1 with `PaimonSparkSessionExtensions` enabled and a Paimon
REST catalog named `paimon` that supports semantic view management.
```sql
SET spark.sql.session.timeZone=UTC;
CREATE DATABASE IF NOT EXISTS paimon.sales;
CREATE TABLE paimon.sales.date_trunc_orders (
order_date DATE,
amount BIGINT
) TBLPROPERTIES ('bucket' = '-1');
INSERT INTO paimon.sales.date_trunc_orders VALUES
(DATE '2026-05-10', 10),
(DATE '2026-05-31', 20),
(DATE '2026-06-01', 7),
(NULL, 3);
```
Register this document as `sales.order_metrics` with format `ossie-yaml`
through the REST Catalog's semantic view management API.
```yaml
version: 0.3.0.dev0
name: order_metrics
datasets:
- name: orders
source: date_trunc_orders
fields:
- name: order_month
expression:
sql: DATE_TRUNC('month', order_date)
metrics:
- name: revenue
expression:
sql: SUM(source.amount)
```
```sql
SELECT order_month, MEASURE(revenue) AS revenue
FROM paimon.sales.order_metrics
GROUP BY order_month;
```
#### Current result
Reproduced on Spark 4.2.1 with Paimon at `1f01a70625` using the REST Catalog
test fixture in `OssieSemanticViewTest`. Query analysis fails with the
following error.
```text
java.lang.IllegalArgumentException: requirement failed: Unsupported
OSSIE_SQL_2026 function: DATE_TRUNC
```
The same model and query succeed when the dimension expression is
`order_date`.
#### Expected result
| order_month | revenue |
| --- | ---: |
| `2026-05-01 00:00:00` | 30 |
| `2026-06-01 00:00:00` | 7 |
| `NULL` | 3 |
A direct Spark SQL query with `GROUP BY DATE_TRUNC('month', order_date)` and
`SUM(amount)` returned these values from the same source table in UTC and had a
timestamp output column. A separate native query confirmed the Monday boundary
for `week`.
### Solution
Support `DATE_TRUNC(part, date_expr)` in the Spark 4.2 Ossie expression
translator and delegate evaluation to Spark's native `date_trunc`. [Spark 4.2.1
function
reference](https://spark.apache.org/docs/4.2.1/api/sql/datetime-functions/#date_trunc)
- Accept the eight Ossie units as case-insensitive string literals (`year`,
`quarter`, `month`, `week`, `day`, `hour`, `minute`, and `second`). Reject
unsupported units during model analysis so a misspelling cannot silently
produce a `NULL` dimension.
- For `week`, truncate to Monday at midnight. In UTC, Sunday `2026-05-31`
truncates to `2026-05-25 00:00:00` and Monday `2026-06-01` truncates to
`2026-06-01 00:00:00`.
- A `NULL` date or timestamp produces a `NULL` dimension value and
contributes to the `NULL` group in the example query.
- Use Spark's native return type, `TimestampType` (`TIMESTAMP_LTZ`),
including for `DATE` input. Truncation follows Spark's session time zone.
[Spark
implementation](https://github.com/apache/spark/blob/v4.2.1/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L2463-L2484)
Extend `OssieSemanticViewTest` with the grouped measure query and coverage
for the week boundary, the `NULL` group, the output schema, and rejection of
unsupported units.
--
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]