aws / aws/amazon-documentdb-jdbc-driver
[BUG]Inconsistent WEEK behavior in the documentdb driver compared to other data sources
- Dominant language
- Java
- Stars
- 17
- Forks
- 16
- PR merge metrics
- No merged PRs in 30d
Description
## Environment
- DocumentDB JDBC driver version: 1.4.4
- DocumentDB server version: MongoDB v6.0.3
- OS: MacOS Ventura 13.1
- BI Tool or client name: DBeaver
- BI Tool or client version: 22.3.5
## Problem Description
1. Steps to reproduce: As is noted in the [SQL limitation document](https://github.com/normanj-bitquill/amazon-documentdb-jdbc-driver/blob/0d9ddcbd6fee4456460a7bc7e4244f752a90d792/src/markdown/sql/sql-limitations.md), EXTRACT for WEEK or WEEK() return an integer between 0 and 53. This is in line with the MongoDB interpretation of week numbers but other databases more commonly return a value between 1 and 53. While this follows the MongoDB behavior, it would be ideal to also have the option to use [isoWeek](https://www.mongodb.com/docs/v5.2/reference/operator/aggregation/isoWeek/) which Mongo also supports get access to the week number in ISO 8601 format.
Same goes for FLOOR(timestamp TO timeunit), basically things that have to do with week numbering.
2. Expected behaviour: Week number in ISO 8601 format also being available
3. Actual behaviour: Currently only supports 0-53 in WEEK
4. Error message/stack trace: N/A
5. Any other details that can be helpful: This would be desirable since while this does follow the Mongo behavior, the inconsistency with other databases leads to some difficulties in the development of database connectors using this driver, which can be easily resolved with the ISO format being an option. Thanks.
Contributor guide
Research direction
Start with src/markdown/sql/sql-limitations.md and review the documented behavior for EXTRACT, WEEK(), and FLOOR(timestamp TO timeunit). Trace the SQL handling for those expressions and compare it with MongoDB's isoWeek behavior. Done means an ISO 8601 week-number option is available and documented.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, mongodb, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100