Yang, Yongfeng created TRAFODION-3202:
-----------------------------------------
Summary: Case when used in the sorting sequence of paging
functions. execution error
Key: TRAFODION-3202
URL: https://issues.apache.org/jira/browse/TRAFODION-3202
Project: Apache Trafodion
Issue Type: Bug
Reporter: Yang, Yongfeng
Assignee: Yang, Yongfeng
SQL statement:
SELECT * FROM (select row_number() over (ORDER BY t.num DESC) row_num,* from (
select DATEDIFF(minute,TO_TIMESTAMP (C_START_TIME),TO_TIMESTAMP (CASE WHEN
C_END_TIME IS NULL THEN NOW() ELSE C_END_TIME END))
as num,* from T1 )
t ) t2 where t2.row_num between 1 and 20;
error information：
*** ERROR[2235] Compiler Internal Error: Pass one skipped, but cannot produce a
plan in pass two, originated from file ../optimizer/opt.cpp at line 7147.
*** ERROR[8822] The statement was not prepared.
After remove the case when in the sql statement, it can be executed normally:
SELECT * FROM (select row_number() over (ORDER BY t.num DESC) row_num,* from (
select DATEDIFF(minute,TO_TIMESTAMP (C_START_TIME),TO_TIMESTAMP (C_END_TIME))
as num,* from T1 )
t ) t2 where t2.row_num between 1 and 20;
Steps to reproduce.
1) create table t1 (
C_ID LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 1734251 INCREMENT BY 1 MAXVALUE 9223372036854775806
MINVALUE 1 CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C_CENTER_ID VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_AREA_ID VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_PLAZA_ID VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_CLIMATIC_ID VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_ALARM_TYPE INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C_ALARM_LEVEL INT DEFAULT NULL NOT SERIALIZED
, C_POSITION_CODE VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_START_TIME TIMESTAMP(0) NO DEFAULT NOT NULL NOT
DROPPABLE NOT SERIALIZED
, C_END_TIME TIMESTAMP(0) DEFAULT NULL NOT SERIALIZED
, C_STATE INT DEFAULT NULL NOT SERIALIZED
, C_REMARK VARCHAR(200 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_CREATE_TIME TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
, C_UPDATE_TIME TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
, C_USER VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_POST_CODE VARCHAR(50 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C_FLOOR_TYPE INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C_DATA NUMERIC(18, 4) DEFAULT NULL NOT SERIALIZED
, C_UP_LIMIT NUMERIC(18, 4) DEFAULT NULL NOT SERIALIZED
, C_DOWN_LIMIT NUMERIC(18, 4) DEFAULT NULL NOT SERIALIZED
, C_CHECK INT DEFAULT NULL NOT SERIALIZED
, C_HIGH_OR_LOW INT DEFAULT NULL NOT SERIALIZED
, C_DURATIONLEVEL INT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (C_START_TIME DESC, C_ALARM_TYPE ASC, C_FLOOR_TYPE ASC,
C_CENTER_ID ASC, C_AREA_ID ASC, C_CLIMATIC_ID ASC, C_PLAZA_ID ASC,
C_POSITION_CODE ASC)
)
SALT USING 5 PARTITIONS
ON (C_PLAZA_ID, C_POSITION_CODE);
2) SELECT * FROM
(select row_number() over (ORDER BY t.num) row_num,
*
from
(
select DATE_part('day',(CASE WHEN C_END_TIME IS NULL THEN now() ELSE
C_END_TIME END)) as num,
C_END_TIME
from t1 ) t
) t2
where t2.row_num between 1 and 20;
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
