[IGNITE-17453] Calcite Engine: ORDER BY Optimization

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 2.13
• Fix Version/s: 2.14
• Component/s: sql
• Release Note: Change resolution to duplicate
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: YuJue Li
• Created: 01/Aug/22 13:36
• Updated: 04/Oct/22 09:45
• Resolved: 25/Aug/22 09:22

Description

1.start a node;

2.create table:
CREATE TABLE PI_COM_DAY
(COM_ID VARCHAR(30) NOT NULL ,
ITEM_ID VARCHAR(30) NOT NULL ,
DATE1 VARCHAR(8) NOT NULL ,
KIND VARCHAR(1),
QTY_IOD DECIMAL(18, 6) ,
AMT_IOD DECIMAL(18, 6) ,
PRIMARYKEY (COM_ID,ITEM_ID,DATE1)) WITH"template=partitioned,CACHE_NAME=PI_COM_DAY";

CREATE INDEX IDX_PI_COM_DAY_ITEM_DATE ON PI_COM_DAY(ITEM_ID);
 
3.insert some data, for example, 1m;
4.execute sql:
select * from pi_com_day order by item_id desc limit 10;
normal.
select /+ QUERY_ENGINE('calcite') */  from pi_com_day order by item_id desc limit 10;
then OOM.

Issue Links

• is duplicated by: IGNITE-16013 Calcite engine. Introduce sort-with-limit physical relational operator