aws / aws/amazon-documentdb-jdbc-driver

[BUG]Inconsistent WEEK behavior in the documentdb driver compared to other data sources

Open
#509 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.