create index fails because no sort operator is added to plan

[khaled Bouaziz]

This issue was faced with HammerDB POC

Here are the steps to reproduce. tyhe plan shoud have a sort node in this case (which it does not)

>>cqd hide_indexes 'all';

--- SQL operation complete.

>>set parserflags 1;

--- SQL operation complete.

>>explain options 'f' load transform into table(index_table ORDERS_i2) select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS ;
..

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

3 . 4 root 1.00E+002
1 2 3 tuple_flow 1.00E+002
. . 2 trafodion_load_prepa ORDERS_I2 1.00E+000
. . 1 trafodion_scan ORDERS 1.00E+002

--- SQL operation complete.
>>explain load transform into table(index_table ORDERS_i2) select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS ;
..

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212280765588587912
ROWS_OUT ............... 100
EST_TOTAL_COST ........... 0.01
STATEMENT ................ load transform into table(index_table ORDERS_i2)
                             select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS;

------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality 100
  total_overflow_size .... 0.00 KB
  xn_access_mode ......... read_only
  xn_autoabort_interval 0
  auto_query_retry ....... enabled
  plan_version ....... 2,600
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  ATTEMPT_ESP_PARALLELISM OFF
  SCHEMA ................. HAMMERDB
  COMP_BOOL_226 .......... ON
  SHOWCONTROL_SHOW_ALL ... OFF
  HIDE_INDEXES ........... ALL
  GENERATE_EXPLAIN ....... ON

TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  join_type .............. inner
  join_method ............ in-order nested

TRAFODION_LOAD_PREPARATION ================ SEQ_NO 2 NO CHILDREN
TABLE_NAME ............... TRAFODION.HAMMERDB.ORDERS_I2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  iud_type ............... index_trafodion_load_preparation
                             TRAFODION.HAMMERDB.ORDERS_I2
  new_rec_expr ........... ("O_W_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_W_ID),
                             ("O_D_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_D_ID
                             ), ("O_C_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_C
                             _ID), ("O_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_
                             ID)

TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... ORDERS
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.HAMMERDB.ORDERS
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........... 100
  probes ................. 1
  rows_accessed ........ 100
  key_columns ............ O_W_ID, O_D_ID, O_ID

--- SQL operation complete.

>>showddl orders;

CREATE TABLE TRAFODION.HAMMERDB.ORDERS
  (
    O_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
      DROPPABLE
  , O_W_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
      DROPPABLE
  , O_D_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
      DROPPABLE
  , O_C_ID NUMERIC(9, 0) DEFAULT NULL
  , O_CARRIER_ID NUMERIC(9, 0) DEFAULT NULL
  , O_OL_CNT NUMERIC(9, 0) DEFAULT NULL
  , O_ALL_LOCAL NUMERIC(9, 0) DEFAULT NULL
  , O_ENTRY_D TIMESTAMP(6) DEFAULT NULL
  , PRIMARY KEY (O_W_ID ASC, O_D_ID ASC, O_ID ASC)
  )
;

CREATE UNIQUE INDEX ORDERS_I2 ON TRAFODION.HAMMERDB.ORDERS
  (
    O_W_ID ASC
  , O_D_ID ASC
  , O_C_ID ASC
  , O_ID ASC
  )
;

[Suresh Subbiah (suresh-subbiah)]

Khaled and I also found with the display tool that the sort node was considered by the optimizer and produced a valid plan. It lost out on the basis of cost. Using a CQS that had sort explicitly specified, Khaled was able to get the required plan.

A possible fix may be such that we do not consider any plan where the source sort order is not identical to the target keys. Either such plans need not be considered or we could somehow mark them as not satisfying all required properties.

[QF Chen (qifan-chen)]

I was able to trace the problem to plan1 of NJ. This plan optimizes the outer child first and then passes the sort order to the right. Since only the leading key column is checked against that of the target table, a perfect match is found for this particular query. Plan1 (without the sort) wins.

The fix is to require a full sort order matches for fast sort prepare.

[QF Chen (qifan-chen)]

The final fix requires the sorting key to cover the clustering key of the base table.