Compiling a query results a Generator error

[QF Chen]

compiling this query runs into an error in generator.
This error usually means that something is not set up correctly
especially related to value ids that are available in that operator.
Can we look into it?

Trafodion Conversational Interface 0.8.4
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>obey qry21;
>>prepare s from
+>SELECT /*+ PARALLEL(4) */
+> /* OSr <email address hidden> - adhoc */
+> UNIQUE backlog_item.so_no, backlog_item.big_deal_id,
+> backlog_ship.shipment_no, backlog_item.material_no,
+> TO_CHAR (backlog_delv.eshp_actual, 'YYYY-MM-DD'),
+> backlog_hdr.purch_agree,
+> (CASE WHEN backlog_hdr.gtm = 'D' THEN 'direct' WHEN backlog_hdr.gtm = 'I' THEN 'indirect' ELSE '' END),
+> detail_techinfo.serial_number, backlog_item.invoice_no,
+> backlog_ids.ship_to_l2, ship_ncrf_l2.name,
+> backlog_item.product_line, backlog_item.product_descr,
+> (backlog_delv.ship_to_addr_1 || backlog_delv.ship_to_addr_2 || backlog_delv.ship_to_addr_3 || backlog_delv.ship_to_addr_4 || backlog_delv.ship_to_addr_5),
+> backlog_hdr.customer_name, end_customer_address_details.name1,
+> backlog_hdr.purchase_order_no, backlog_hdr.purch_order_type,
+> (CASE WHEN backlog_hdr.gtm = 'D' THEN 'direct' WHEN backlog_hdr.gtm = 'I' THEN 'indirect' ELSE '' END),
+> TO_CHAR (net_dlr_invoice_price, '999999999999.99'),
+> backlog_hdr.dlr_tot_net_price, measure.global_tat,
+> ( ( (TO_NUMBER (TRUNC (backlog_delv.eshp_actual, 'D') - TRUNC (backlog_delv.hp_receive_date + 6, 'D'))) / 7 * 5) + MOD (7 - TO_NUMBER (TO_CHAR (backlog_delv.hp_receive_date, 'D')), 6) + LEAST (TO_NUMBER (TO_CHAR (backlog_delv.eshp_actual, 'D')) - 2, 5)),
+> ( ( (TO_NUMBER (TRUNC (SYSDATE, 'D') - TRUNC (backlog_hdr.compl_landed_date + 6, 'D'))) / 7 * 5) + MOD (7 - TO_NUMBER (TO_CHAR (backlog_hdr.compl_landed_date, 'D')), 6) + LEAST (TO_NUMBER (TO_CHAR (SYSDATE, 'D')) - 2, 5)),
+> ( ( (TO_NUMBER (TRUNC (SYSDATE, 'D') - TRUNC (backlog_delv.pgi_actual + 6, 'D'))) / 7 * 5) + MOD (7 - TO_NUMBER (TO_CHAR (backlog_delv.pgi_actual, 'D')), 6) + LEAST (TO_NUMBER (TO_CHAR (SYSDATE, 'D')) - 2, 5)),
+> ( ( (TO_NUMBER (TRUNC (SYSDATE, 'D') - TRUNC (backlog_hdr.hp_receive_date + 6, 'D'))) / 7 * 5) + MOD (7 - TO_NUMBER (TO_CHAR (backlog_hdr.hp_receive_date, 'D')), 6) + LEAST (TO_NUMBER (TO_CHAR (SYSDATE, 'D')) - 2, 5)),
+> (TRUNC (SYSDATE - backlog_item.ercv_actual)),
+> TO_CHAR (backlog_delv.fda_actual, 'YYYY-MM-DD'),
+> backlog_ship.delvtocountry, v_country.country_name,
+> backlog_hdr.opportunity_id,
+> TO_CHAR (backlog_hdr.op_receive_date, 'YYYY-MM-DD')
+> FROM reptemp rp,
+> backlog_hdr
+> LEFT OUTER JOIN
+> end_customer_address_details
+> ON (backlog_hdr.end_cust_addr_no =
+> end_customer_address_details.reference_no)
+> LEFT OUTER JOIN
+> ossdba.v_country
+> ON (v_country.hp_code = backlog_hdr.sold_to_wcc),
+> backlog_item
+> LEFT OUTER JOIN
+> detail_techinfo
+> ON ( ( detail_techinfo.delivery_group =
+> backlog_item.delivery_group
+> AND detail_techinfo.item_subitem = backlog_item.item_subitem))
+> LEFT OUTER JOIN
+> pl_dim
+> ON (backlog_item.product_line = pl_dim.line),
+> backlog_ids
+> LEFT OUTER JOIN
+> ship_ncrf_l2
+> ON (ship_ncrf_l2.id = backlog_ids.ship_to_l2),
+> backlog_ship,
+> country,
+> backlog_delv
+> LEFT OUTER JOIN
+> measure
+> ON ( ( measure.mlevel = 'delv'
+> AND measure.id = backlog_delv.delivery_group))
+>WHERE rp.legacy_order_no = backlog_hdr.legacy_order_no
+> AND ( backlog_hdr.legacy_order_no = backlog_ids.legacy_order_no
+> AND backlog_ship.legacy_order_no = backlog_ids.legacy_order_no
+> AND backlog_delv.legacy_order_no = backlog_ids.legacy_order_no
+> AND backlog_item.legacy_order_no = backlog_ids.legacy_order_no
+> AND backlog_hdr.legacy_order_no = backlog_ship.legacy_order_no
+> AND backlog_ship.shipment_group = backlog_delv.shipment_group
+> AND backlog_hdr.legacy_order_no = backlog_delv.legacy_order_no
+> AND backlog_delv.delivery_group = backlog_item.delivery_group
+> AND backlog_hdr.legacy_order_no = backlog_item.legacy_order_no
+> AND country.hp_code = backlog_ship.ship_to_wcc
+> AND (backlog_hdr.is_valid IS NULL OR backlog_hdr.is_valid = 'Y')
+> AND (backlog_ship.is_valid IS NULL OR backlog_ship.is_valid = 'Y')
+> AND (backlog_delv.is_valid IS NULL OR backlog_delv.is_valid = 'Y')
+> AND (backlog_item.is_valid IS NULL OR backlog_item.is_valid = 'Y')
+> AND ( pl_dim.group_name = 'IMAGING AND PRINTING GROUP'
+> AND country.region = 'Asia Pacific'
+> AND backlog_item.product_line = '2D'
+> AND (backlog_delv.eshp_actual >= '2014-09-01 00:00:00')));

*** WARNING[6008] Statistics for column (PURCH_ORDER_TYPE) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (HP_RECEIVE_DATE) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (PURCH_AGREE) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (OP_RECEIVE_DATE) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (COMPL_LANDED_DATE) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (GTM) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (SOLD_TO_WCC) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (OPPORTUNITY_ID) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (ERCV_ACTUAL) from table TRAFODION.OSSDBA.BACKLOG_ITEM were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (ITEM_SUBITEM) from table TRAFODION.OSSDBA.DETAIL_TECHINFO were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (SHIP_TO_WCC) from table TRAFODION.OSSDBA.BACKLOG_SHIP were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (IS_VALID) from table TRAFODION.OSSDBA.BACKLOG_SHIP were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (DELVTOCOUNTRY) from table TRAFODION.OSSDBA.BACKLOG_SHIP were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (HP_RECEIVE_DATE) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (PGI_ACTUAL) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (FDA_ACTUAL) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (NET_DLR_INVOICE_PRICE) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (REFERENCE_NO, NAME1, NAME2, NAME3, NAME4, STREET, CITY, POSTAL_CODE, COUNTRY, REGION, TELEPHONE, EMAIL_ADDRESS) from table TRAFODION.OSSDBA.ADDRESS_DETAILS were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (DELIVERY_GROUP, LEGACY_ORDER_NO) from table TRAFODION.OSSDBA.BACKLOG_ITEM were not available. The columns were being used by Join operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (LEGACY_ORDER_NO, DELIVERY_GROUP) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. The columns were being used by Join operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (LEGACY_ORDER_NO, SHIPMENT_GROUP) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. The columns were being used by Join operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (DELIVERY_GROUP, ITEM_SUBITEM) from table TRAFODION.OSSDBA.DETAIL_TECHINFO were not available. The columns were being used by Join operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (DELIVERY_GROUP, ITEM_SUBITEM) from table TRAFODION.OSSDBA.BACKLOG_ITEM were not available. The columns were being used by Join operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (CUSTOMER_NAME, PURCH_ORDER_TYPE, DLR_TOT_NET_PRICE, HP_RECEIVE_DATE, PURCHASE_ORDER_NO, PURCH_AGREE, OP_RECEIVE_DATE, COMPL_LANDED_DATE, OPPORTUNITY_ID) from table TRAFODION.OSSDBA.BACKLOG_HDR were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (SO_NO, MATERIAL_NO, PRODUCT_DESCR, INVOICE_NO, PRODUCT_LINE, BIG_DEAL_ID) from table TRAFODION.OSSDBA.BACKLOG_ITEM were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (SHIPMENT_NO, DELVTOCOUNTRY) from table TRAFODION.OSSDBA.BACKLOG_SHIP were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (HP_RECEIVE_DATE, PGI_ACTUAL, ESHP_ACTUAL, FDA_ACTUAL) from table TRAFODION.OSSDBA.BACKLOG_DELV were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

*** ERROR[7000] An internal error occurred in the code generator in file ../generator/Generator.cpp at line 1670:
ValueId 3517 (TRAFODION.OSSDBA.BACKLOG_DELV._SALT_...) not found in MapTable 0x7fff1cdbbf20.

*** ERROR[2235] Compiler Internal Error: An unknown error, originated from file ../generator/Generator.cpp at line 2035.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>

The DDLs can be found at /opt/trafodion/qifan/bugs.

[Suresh Subbiah (suresh-subbiah)]

cqd hbase_hash2_partitioning 'off' can be used to workaround this issue.

[QF Chen (qifan-chen)]

Could not see the problem on workstation. Need the new osim to reproduce.