inDateRange with only one date not working as expected

[danidal]

Describe the bug As stated in the documentation (operator inDateRange) "If only one date specified the filter would be set exactly to this date". Instead, it seems to consider the only date provided as a lower bound.

To Reproduce

{
  member: "Posts.time",
  operator: "inDateRange",
  values: ['2015-01-01']
}

Expected behavior Same as

{
  member: "Posts.time",
  operator: "inDateRange",
  values: ['2015-01-01', '2015-01-01']
}

or

{
  member: "Posts.time",
  operator: "equals",
  values: ['2015-01-01']
}

Version: 0.26.77

[vasilev-alex]

@danidal is it still an issue in 0.28?

[danidal]

HI @vasilev-alex, sorry for the delay. I tested using these dependencies and the result is the same as before:

"dependencies": {
    "@cubejs-backend/cubestore-driver": "^0.28.8",
    "@cubejs-backend/postgres-driver": "^0.28.8",
    "@cubejs-backend/redshift-driver": "^0.28.8",
    "@cubejs-backend/server": "^0.28.8",
    "@cubejs-backend/serverless": "^0.28.8",
    "@cubejs-backend/serverless-aws": "^0.28.8",
    "serverless": "^2.33.0",
    "serverless-express": "^2.0.11"
}

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[igorlukanin]

I was able to reproduce this on the latest Cube (v0.36.2).

Data model:

cubes:
  - name: in_date_range
    sql: >
      SELECT 10 AS value, '2024-01-01'::TIMESTAMP AS time UNION ALL
      SELECT 20 AS value, '2024-01-01'::TIMESTAMP AS time UNION ALL
      SELECT 30 AS value, '2024-01-02'::TIMESTAMP AS time UNION ALL
      SELECT 40 AS value, '2024-01-02'::TIMESTAMP AS time UNION ALL
      SELECT 50 AS value, '2024-01-03'::TIMESTAMP AS time UNION ALL
      SELECT 60 AS value, '2024-01-03'::TIMESTAMP AS time UNION ALL
      SELECT 70 AS value, '2024-01-03'::TIMESTAMP AS time

    dimensions:
      - name: time
        sql: time
        type: time

    measures:
      - name: sum
        sql: value
        type: sum

Query via the REST API without filters:

{
  "measures":["in_date_range.sum"],
  "dimensions":["in_date_range.time"]
}

Result:

[
  {
    "in_date_range.time": "2024-01-03T00:00:00.000",
    "in_date_range.sum": "180"
  },
  {
    "in_date_range.time": "2024-01-02T00:00:00.000",
    "in_date_range.sum": "70"
  },
  {
    "in_date_range.time": "2024-01-01T00:00:00.000",
    "in_date_range.sum": "30"
  }
]

Query via the REST API with a two-value inDateRange filter:

{
  "measures":["in_date_range.sum"],
  "dimensions":["in_date_range.time"],
  "filters":[
    {
      "member":"in_date_range.time",
      "operator":"inDateRange",
      "values":["2024-01-02","2024-01-02"]
    }
  ]
}

Result:

[
  {
    "in_date_range.time": "2024-01-02T00:00:00.000",
    "in_date_range.sum": "70"
  }
]

Query via the REST API with a single-value inDateRange filter:

{
  "measures":["in_date_range.sum"],
  "dimensions":["in_date_range.time"],
  "filters":[
    {
      "member":"in_date_range.time",
      "operator":"inDateRange",
      "values":["2024-01-02"]
    }
  ]
}

Result:

[
  {
    "in_date_range.time": "2024-01-03T00:00:00.000",
    "in_date_range.sum": "180"
  },
  {
    "in_date_range.time": "2024-01-02T00:00:00.000",
    "in_date_range.sum": "70"
  }
]

So, this confirms that "it seems to consider the only date provided as a lower bound."

---

In my opinion, this whole feature looks like an unnecessary complication: "If only one date specified the filter would be set exactly to this date." I'm not sure if this solves any problem or helps any use case.

My suggestion would be to remove this feature from Cube altogether. I understand this is formally a breaking change but it has never worked properly anyway, at least since v0.26.77.