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]

Reply via email to