CASE returning unexpected value for timestamp IS NULL comparison

[javier]

To reproduce

with t AS (
      (
            select
                  null :: timestamp ts,
                  1 :: int as i
            UNION
            select
                  now() ts,
                  1 :: int as i
      )
)
select
      case
            when ts is null then i
            else ts
      end,
      case
            when ts is null then i
            else ts :: string
      end,
      case
            when ts is null then i
            else now()
      end,
      coalesce(ts, i),
      *
from
      t;

The three CASE statements above should return the i column, as the value for ts is null. However, when the else contains a timestamp (both for ts itself or for now()) we get the incorrect value of 1970-01-01T00:00:00.000001Z. If the else has a different type, like a string, then things work as expected.

Please note that COALESCE is also showing this behaviour, so it might be related. Filling a different issue for COALESCE.

A user reports on slack that this was working before 7.4.2 for this query and it is breaking now

  select
      period_start_time,
      cal_timestamp_time nas_timestamp,
      'SIP' as feed_table,
      device_name,
      application_protocol_type_code,
      application_name,
      application_group,
      response_code,
      min(controlplane_response_time_usec) min_response_time_usec,
      max(controlplane_response_time_usec) max_response_time_usec,
      sum(controlplane_response_time_usec) total_response_time_usec,
      count(controlplane_response_time_usec) count_response_time,
      count() events
  from (
    select * from (
    select
      case
        when controlplane_transaction_start_time is null
        then cal_timestamp_time
        else controlplane_transaction_start_time
      end as period_start_time,
      *
    from nAS_ControlPlane_SIP
    where
      cal_timestamp_time > dateadd('m',-10,now())
      and cal_timestamp_time < dateadd('m',-5,now())

    order by 1 asc

    )
    timestamp(period_start_time)
  ) sample by 5m align to calendar;

QuestDB version:

7.4.3 (from source, also reported on 7.4.2)

OS, in case of Docker specify Docker and the Host OS:

OSX

File System, in case of Docker specify Host File System:

ext4

Full Name:

javier ramirez

Affiliation:

questdb

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response

[mrosi]

Hi,
the problem is probably somewhere else /or it's another problem/.
I've removed case and ensured timestamp is never null, so that query is:

  select
      period_start_time,
      cal_timestamp_time nas_timestamp,
      'SIP' as feed_table,
      device_name,
      application_protocol_type_code,
      application_name,
      application_group,
      response_code,
      min(controlplane_response_time_usec) min_response_time_usec,
      max(controlplane_response_time_usec) max_response_time_usec,
      sum(controlplane_response_time_usec) total_response_time_usec,
      count(controlplane_response_time_usec) count_response_time,
      count() events
  from (
    select * from (
    select
      controlplane_transaction_start_time as period_start_time,
      *
    from nAS_ControlPlane_SIP
    where
      cal_timestamp_time > dateadd('m',-10,now())
      and cal_timestamp_time < dateadd('m',-5,now())
      and not controlplane_transaction_start_time is null
    order by 1 asc

    )
    timestamp(period_start_time)
  ) sample by 5m align to calendar
  ;

it works without sample but not with sample