evizhnay95 opened a new issue, #70415: URL: https://github.com/apache/doris/issues/70415
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description ## Description Since 4.1.5 (#68786), external `timestamptz` columns (e.g. Iceberg `timestamptz`) always map to `TIMESTAMPTZ(6)`, and `enable.mapping.timestamp_tz = false` no longer restores the previous `DATETIME` mapping. Over the MySQL protocol, `TIMESTAMPTZ` values are sent as strings with an offset (e.g. `2026-10-31 00:00:00.000000-05:00`) and the column is advertised as a string type (#63292). For MySQL-protocol BI clients this is a breaking change: - Power BI (MySQL Connector/NET 8.0.28 and MySQL ODBC 5.3) receives the column as text, so every report needs a text-to-datetime conversion. - In Power BI DirectQuery, setting the column type to Date makes Power BI generate `CAST(col AS DATE)`, which fails with `cannot cast TIMESTAMPTZ(6) to DATEV2`. `DATE(col)` and `TO_DATE(col)` work. - `CAST(col AS DATETIME)` produces `DATETIME(0)` and rounds: `23:59:59.9` becomes the next day. We understand why #67960 was not accepted (the BE still serializes the offset, and advertising `TIMESTAMP` breaks Connector/J). We are asking for an opt-in path instead, with the current behavior as the default. ## Proposal Any of these would solve it for us: 1. A session/global variable (for example `serialize_timestamptz_as_datetime`, default `false`) that, for MySQL-protocol result sets only, renders `TIMESTAMPTZ` as `DATETIME` in the session time zone (no offset) and advertises it as `MYSQL_TYPE_DATETIME`. 2. Keep honoring `enable.mapping.timestamp_tz = false` on external catalogs as a deprecated compatibility switch. 3. Independently of 1 and 2: support `CAST(timestamptz AS DATE)` using the session time zone, consistent with `DATE()` and `TO_DATE()`. ## How to reproduce ```sql SET time_zone = 'America/Guayaquil'; SELECT CAST('2026-11-09 23:59:59.900000-05:00' AS TIMESTAMPTZ(6)) AS ts; SELECT CAST(CAST('2026-11-09 23:59:59.900000-05:00' AS TIMESTAMPTZ(6)) AS DATE) AS d; SELECT CAST(CAST('2026-11-09 23:59:59.900000-05:00' AS TIMESTAMPTZ(6)) AS DATETIME) AS dt0; SELECT TO_DATE(CAST('2026-11-09 23:59:59.900000-05:00' AS TIMESTAMPTZ(6))) AS d_ok; ``` Output on 4.1.4 (`mysql --column-type-info`, session time zone `America/Guayaquil`): ``` Field 1: `ts` Type: STRING Collation: utf8mb3_general_ci (33) Length: 32 Decimals: 0 +----------------------------------+ | ts | +----------------------------------+ | 2026-11-09 23:59:59.900000-05:00 | +----------------------------------+ ERROR 1105 (HY000) at line 1: errCode = 2, detailMessage = cannot cast TIMESTAMPTZ(6) to DATEV2 ``` Same input on 4.1.5: | Expression | Result | |---|---| | `CAST(x AS DATE)` | `ERROR: cannot cast TIMESTAMPTZ(6) to DATEV2` | | `CAST(x AS DATETIME)` | `2026-11-10 00:00:00` (rounded to the next day) | | `CAST(x AS DATETIME(6))` | `2026-11-09 23:59:59.900000` | | `DATE(x)`, `TO_DATE(x)` | `2026-11-09` | On an Iceberg REST catalog created with `'enable.mapping.timestamp_tz' = 'false'`, `DESCRIBE` shows `datetime(6)` on 4.1.4 and `timestamptz(6)` on 4.1.5. ## Environment - Doris 4.1.4 and 4.1.5 (apache/doris images, doris-operator 26.0.1 on Kubernetes), 1 FE + 1 BE - Iceberg REST catalog - Power BI Desktop 2.157 (Aug 2026) with MySQL ODBC 5.3.13 and MySQL Connector/NET 8.0.28 - JasperReports with Connector/J 8.0.33 ### Use case ## Use case Doris is our BI access layer over Iceberg (REST catalog). The tables are written by Spark as `timestamptz`. The consumers are Power BI Import and DirectQuery (ODBC custom connector), JasperReports (Connector/J) and Superset. All users are in a single time zone, set as the global `time_zone`. To keep native datetimes we pinned 4.1.4 with `'enable.mapping.timestamp_tz' = 'false'` on the catalog. It works, but it blocks every upgrade. We verified it both ways: on 4.1.4 the column is `datetime(6)`; after upgrading the same cluster to 4.1.5 with the same catalog property, it becomes `timestamptz(6)`. ### Related issues - #68786: 4.1.5 change that always maps external timestamptz to TIMESTAMPTZ - #67960: closed proposal to report TIMESTAMPTZ as MYSQL_TYPE_TIMESTAMP - #63292: TIMESTAMPTZ reported as STRING over the MySQL protocol ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
