is_between in a filter for datetime gives incorrect results

[daBlesr]

Checks

• I have checked that this issue has not already been reported.
• I have confirmed this bug exists on the latest version of Polars.

Reproducible example

There is a problem where the is_between filter does not work well. In the below code you see that I have applied exactly same is_between expression in a filter and in a with_column statement. This dataframe returns false values for this column, which should not be possible. See example below.

let tz = Some("Europe/Amsterdam".to_string());

let df = DataFrame::from_rows(&[
    Row(vec![
        // 11th
        AnyValue::Datetime(1718085600000, TimeUnit::Milliseconds, &tz),
        AnyValue::Datetime(1718114400000, TimeUnit::Milliseconds, &tz),
    ]),
    Row(vec![
        // 12th
        AnyValue::Datetime(1718172000000, TimeUnit::Milliseconds, &tz),
        AnyValue::Datetime(1718200800000, TimeUnit::Milliseconds, &tz),
    ]),
    Row(vec![
        // 19th
        AnyValue::Datetime(1718776800000, TimeUnit::Milliseconds, &tz),
        AnyValue::Datetime(1718805600000, TimeUnit::Milliseconds, &tz),
    ]),
]).unwrap()
    .lazy()
    .with_column(col("column_0").alias("start_datetime"))
    .with_column(col("column_1").alias("end_datetime"))
    .drop(["column_0", "column_1"])
    .sort(["start_datetime"], SortMultipleOptions {
        descending: vec![false],
        ..Default::default()
    });

let out_df = df
    .clone()
    .cross_join(df.clone())
    .filter(
        col("end_datetime_right").is_between(
            col("start_datetime"),
            col("start_datetime").dt().offset_by(lit("132h")),
            ClosedInterval::Both
        )
    )
    .with_column(
        col("end_datetime_right").is_between(
            col("start_datetime"),
            col("start_datetime").dt().offset_by(lit("132h")),
            ClosedInterval::Both
        ).alias("is_between")
    )
    .filter(col("start_datetime").dt().date().str().contains(lit("2024-06-11"), true))
    .select([col("start_datetime"), col("end_datetime_right"), col("is_between")]);

This returns:

┌────────────────────────────────┬────────────────────────────────┬────────────┐
│ start_datetime                 ┆ end_datetime_right             ┆ is_between │
│ ---                            ┆ ---                            ┆ ---        │
│ datetime[ms, Europe/Amsterdam] ┆ datetime[ms, Europe/Amsterdam] ┆ bool       │
╞════════════════════════════════╪════════════════════════════════╪════════════╡
│ 2024-06-11 08:00:00 CEST       ┆ 2024-06-11 16:00:00 CEST       ┆ true       │
│ 2024-06-11 08:00:00 CEST       ┆ 2024-06-12 16:00:00 CEST       ┆ true       │
│ 2024-06-11 08:00:00 CEST       ┆ 2024-06-19 16:00:00 CEST       ┆ false      │
└────────────────────────────────┴────────────────────────────────┴────────────┘

Log output

No response

Issue description

filter method does not work for some reason in this specific case. The is the smallest reproducible example I could do (the original query was bigger).

Expected behavior

The last row in the example where is_between column reports false should not be present.

Installed versions

chrono = "0.4.38"
polars = { version = "0.40.0", features = ["lazy", "strings", "temporal", "timezones", "date_offset", "dtype-date", "cross_join", "is_between"] }

[cmdlineluser]

It seems to be an optimizer issue.

>>> out.collect()
# shape: (3, 3)
# ┌────────────────────────────────┬────────────────────────────────┬────────────┐
# │ start_datetime                 ┆ end_datetime_right             ┆ is_between │
# │ ---                            ┆ ---                            ┆ ---        │
# │ datetime[ms, Europe/Amsterdam] ┆ datetime[ms, Europe/Amsterdam] ┆ bool       │
# ╞════════════════════════════════╪════════════════════════════════╪════════════╡
# │ 2024-06-11 08:00:00 CEST       ┆ 2024-06-11 16:00:00 CEST       ┆ true       │
# │ 2024-06-11 08:00:00 CEST       ┆ 2024-06-12 16:00:00 CEST       ┆ true       │
# │ 2024-06-11 08:00:00 CEST       ┆ 2024-06-19 16:00:00 CEST       ┆ false      │
# └────────────────────────────────┴────────────────────────────────┴────────────┘

Turning off predicate_pushdown gives the correct result.

>>> out.collect(predicate_pushdown=False)
# shape: (2, 3)
# ┌────────────────────────────────┬────────────────────────────────┬────────────┐
# │ start_datetime                 ┆ end_datetime_right             ┆ is_between │
# │ ---                            ┆ ---                            ┆ ---        │
# │ datetime[ms, Europe/Amsterdam] ┆ datetime[ms, Europe/Amsterdam] ┆ bool       │
# ╞════════════════════════════════╪════════════════════════════════╪════════════╡
# │ 2024-06-11 08:00:00 CEST       ┆ 2024-06-11 16:00:00 CEST       ┆ true       │
# │ 2024-06-11 08:00:00 CEST       ┆ 2024-06-12 16:00:00 CEST       ┆ true       │
# └────────────────────────────────┴────────────────────────────────┴────────────┘

Python repro:

import polars as pl

lf = pl.LazyFrame(
    [
        [1718085600000, 1718172000000, 1718776800000],
        [1718114400000, 1718200800000, 1718805600000]
    ], 
    schema=["start_datetime", "end_datetime"]
).cast(pl.Datetime("ms", "Europe/Amsterdam"))

out = (
    lf.join(lf, how="cross")
      .filter(
          pl.col.end_datetime_right.is_between(
              pl.col.start_datetime,
              pl.col.start_datetime.dt.offset_by("132h")
          )
      )
      .with_columns(
          pl.col.end_datetime_right.is_between(
              pl.col.start_datetime,
              pl.col.start_datetime.dt.offset_by("132h")
          )
          .alias("is_between")
      ) 
      .filter(pl.col.start_datetime.dt.date().str.contains("2024-06-11"))
      .select("start_datetime", "end_datetime_right", "is_between")
)

The correct rows are there if the final .select() is removed.

This is probably one for @ritchie46

[ritchie46]

Slightly more minimal:

import polars as pl

lf = pl.LazyFrame(
    [
        [1718085600000, 1718172000000, 1718776800000],
        [1718114400000, 1718200800000, 1718805600000]
    ], 
    schema=["start_datetime", "end_datetime"]
).cast(pl.Datetime("ms", "Europe/Amsterdam"))

out = (
    lf.join(lf, how="cross")
      .filter(
          pl.col.end_datetime_right.is_between(
              pl.col.start_datetime,
              pl.col.start_datetime.dt.offset_by("132h")
          )
      )
      .select("start_datetime", "end_datetime_right")
).collect(predicate_pushdown=True)