BUG: Unable to convert timestamp_ns to date in iceberg table

[cyberchen98]

Describe the bug

When executing a SQL query that involves a date comparison with an interval subtraction on an Iceberg table, the query fails with the following error

Error message/log

dev=> SELECT id, price, date FROM price WHERE date <= DATE '2025-02-10' - interval '5' day;
ERROR:  Failed to run the query

Caused by these errors (recent errors listed first):
  1: Scheduler error
  2: DataInvalid => Can't convert datum from timestamp_ns type to date type.

To Reproduce

general table

dev=> CREATE TABLE price                  
    (
      id            BIGINT,
      price         NUMERIC,    
      date          DATE
    ); 
CREATE_TABLE
dev=> INSERT INTO price (id, price, date) VALUES                        
(1, 100.50, '2025-02-01'),
(2, 200.75, '2025-02-05'),
(3, 150.25, '2025-02-10');
INSERT 0 3
dev=> SELECT id, price, date FROM price WHERE date <= DATE '2025-02-10' - interval '5' day;
 id | price  |    date    
----+--------+------------
  1 | 100.50 | 2025-02-01
  2 | 200.75 | 2025-02-05
(2 rows)

iceberg table

dev=> CREATE TABLE price
    (
      id            BIGINT,
      price         NUMERIC,    
      date          DATE
    ) ENGINE = ICEBERG; 
CREATE_TABLE
dev=> INSERT INTO price (id, price, date) VALUES
(1, 100.50, '2025-02-01'),
(2, 200.75, '2025-02-05'),
(3, 150.25, '2025-02-10');
INSERT 0 3
dev=> SELECT id, price, date FROM price WHERE date <= DATE '2025-02-10' - interval '5' day;
ERROR:  Failed to run the query

Caused by these errors (recent errors listed first):
  1: Scheduler error
  2: DataInvalid => Can't convert datum from timestamp_ns type to date type.

Expected behavior

No response

How did you deploy RisingWave?

RisingWave Cloud

The version of RisingWave

dev=> select version();
                                       version                                        
--------------------------------------------------------------------------------------
 PostgreSQL 13.14.0-RisingWave-2.3.0-alpha (f9e420f8743215eff528f81bdd8478bb8467abf5)
(1 row)

Additional context

No response

[xxhZs]

The type of date - interval is timestamp. And iceberg does not support timestamp to date. In theory, not considered a bug? cc @chenzl25

[chenzl25]

I think it is a bug, because for our query perspective, our iceberg table should support every datatype supported in a normal risingwave table. I think we converted the date type to timestamp_ns before. We can convert it to a supported datatype.

[chenzl25]

It seems a error from iceberg predicate pushdown

dev=> explain SELECT id, price, date FROM price2 WHERE date <= DATE '2025-02-10' - interval '5' day;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 BatchExchange { order: [], dist: Single }
 └─BatchIcebergScan { source: __iceberg_source_price2, columns: [id, price, date], iceberg_scan_type: DataScan, predicate: date <= 2025-02-05 00:00:00 }
(2 rows)