Query failed : Exception: expressions in ELSE clause must be of the same or compatible types


#1

While MAPD doesn’t claim to support overlaps, this results in an expected error, suggesting re-written query failing.
select 1 from tdt where ( date ‘1999-12-01’ , tdt.cdt ) overlaps ( date ‘2001-01-01’ , date ‘2001-11-01’ ) and rnum=0

Error: Query failed : Exception: expressions in ELSE clause must be of the same or compatible types as those in the THEN clauses.
SQLState: null
ErrorCode: 0

create table if not exists TDT ( RNUM integer not null , CDT date );
insert into TDT(RNUM, CDT) values ( 0, NULL);
insert into TDT(RNUM, CDT) values ( 1, DATE ‘1996-01-01’);
insert into TDT(RNUM, CDT) values ( 2, DATE ‘2000-01-01’);
insert into TDT(RNUM, CDT) values ( 3, DATE ‘2000-12-31’);


#2

Hi,

Calcite does a rewrite on this query

mapdql> explain calcite select 1 from tdt where ( date '1999-12-01' , tdt.cdt ) overlaps ( date '2001-01-01' , date '2001-11-01' ) and rnum=0 ;
Explanation
LogicalProject(EXPR$0=[1])
  LogicalFilter(condition=[AND(>=(CASE(<=(1999-12-01, $1), $1, 1999-12-01), CASE(<=(2001-01-01, 2001-11-01), 2001-01-01, 2001-11-01)), >=(CASE(<=(2001-01-01, 2001-11-01), 2001-11-01, 2001-01-01), CASE(<=(1999-12-01, $1), 1999-12-01, $1)), =($0, 0))])
    EnumerableTableScan(table=[[mapd, TDT]])

Unfortunately we do not seem to be handling the case inputs correctly currently

Will raise an issue

regards


#3

issue created https://github.com/mapd/mapd-core/issues/172