cube

cube copied to clipboard

Describe the bug We are experiencing an odd behavior when a timestamp column has a value equals '9999-12-31 00:00:00'. This is error in cube server:

thread 'tokio-runtime-worker' panicked at 'invalid or out-of-range datetime', /github/home/.cargo/registry/src/github.com-1ecc6299db9ec823/chrono-0.4.19/src/naive/datetime.rs:117:18

To Reproduce Steps to reproduce the behavior:

1. Run SELECT TIMESTAMP '1999-12-31 00:00:00' and see that it works
2. Run SELECT TIMESTAMP '9999-12-31 00:00:00' and see the error

Expected behavior Return the value '9999-12-31 00:00:00' when I run the query SELECT TIMESTAMP '9999-12-31 00:00:00'

Version: 0.30.47

@Larissa-Rocha, does it works for you with previous versions?

Hi @Larissa-Rocha , Could you please share exact steps to reproduce that? We need more info to be able to help here.

@Larissa-Rocha, does it works for you with previous versions?

@buntarb I haven't used any previous versions, just 0.30.47

Hi @Larissa-Rocha , Could you please share exact steps to reproduce that? We need more info to be able to help here.

Hi @ivan-vdovin, I think I forgot to mention that I'm using the Cube SQL API, so to reproduce that you just need run query number 2, in my case I have used Apache Superset as BI tool

@Larissa-Rocha, is it a query over the pre-aggregations, or are you querying the data source?

@buntarb I'm querying the data source, there's a 'end date column' where the value is '9999-12-31 00:00:00' by default when there's no end date

This bug is related to the SQL API bug, not to the Cube (js) part, I will prepare a fix for that.

Expected behavior Return the value '9999-12-31 00:00:00' when I run the query SELECT TIMESTAMP '9999-12-31 00:00:00'

Instead of silent panic, it will return an error to the client.

I've prepared the PR https://github.com/cube-js/cube.js/pull/5122 which will handle panics as errors back to the client instead of panicking and closing the connection.

The same issue will be with Cube Store.

Related to this error. It cannot be stored and I there are no plans to implement it, because there are limitions in:

1. Apache Arrow stores Timestamp as i64
2. Rust's Chrono library uses i64.

Why it panics:

    #[inline]
    pub fn timestamp_nanos(&self) -> i64 {
        let as_ns = self.timestamp() * 1_000_000_000;
        as_ns + i64::from(self.timestamp_subsec_nanos())
    }

As another way it can be possible to store such a big timestamp with Second units. Right now, we use Nanosecond units. But I don't think that we will use Second units because it can be another limitation.

end date column' where the value is '9999-12-31 00:00:00' by default

As workaround you can:

1. Use custom SQL in your Cube with CASE my_date WHEN 9999-12-31 00:00:00 THEN NULL (another, smaller date) ELSE my_date inside sql field.
2. https://cube.dev/docs/schema/reference/dimensions#parameters-case (I am not sure that it will work)

WDYT about this workaround? Will it work for you?

Thanks

@Larissa-Rocha Could you please share the place where superset introduces this date? Which version of superset do you use?

Hi @ovr, thank you for your quick reply, that workaround did work for me!

@Larissa-Rocha Could you please share the place where superset introduces this date? Which version of superset do you use?

Hi @paveltiunov, I'm sorry if I wasn't clear, Superset does not introduce this date, this date is stored in my database, I'm using Superset just to run the query in SQL Lab.

I am going to close this issue because it looks solved by the workaround.

Thanks