Opimized Merge with a view as a source

Pe_Leroy

Hi,

I am facing an issueswhen tentatively writing an Optimized Merge query as described in 7.0 manual.
I follow the requirements as stated in the doc:
Conditions for an Optimized MERGE
HP Vertica prepares an optimized query plan when all of the following conditions are true:

• The target table's join column has a unique or primary key constraint
• UPDATE and INSERT clauses include every column in the target table
• UPDATE and INSERT clause column attributes are identical

But my source is a view.

And when I run my request, with or without EXPLAIN, I have following error :

[Vertica][VJDBC](3594) INTERNAL: Internal Optimizer Error (11) [Vertica][VJDBC]Detail: !isView(rte->relid)

Are views not supported as source of the merge?
Is there another workaround to make it work?

Tx in advance,
Br

Siddarth

Hi Pe, Views are supported as source in Merge. You have hit a bug. Can you give the exact scenario?

Pe_Leroy

The MERGE is working ok when I am not in the conditions of the optimized merge.
But as soon as I make additional changes to be under these conditions, the error occurs.


Here is my merge request, which is working fine:
MERGE INTO FCT_SESSIONS dest         
USING FCT_SESSIONS_VIEW src
ON (src.IMSI= dest.IMSI AND src.CHARGING_ID=dest.CHARGING_ID AND src.SESSION_ID=dest.SESSION_ID)
WHEN MATCHED THEN 
UPDATE SET 
...
TOTAL_VOLUME_DOWN=src.TOTAL_VOLUME_DOWN,
TOTAL_VOLUME_UP=src.TOTAL_VOLUME_UP
WHEN NOT MATCHED THEN
 INSERT VALUES (
src.IMSI,
src.CHARGING_ID,
src.SESSION_ID,
...
src.TOTAL_VOLUME_DOWN,
src.TOTAL_VOLUME_UP);



This one is rejected: 
MERGE INTO FCT_SESSIONS dest         
USING FCT_SESSIONS_VIEW src
ON (src.IMSI= dest.IMSI AND src.CHARGING_ID=dest.CHARGING_ID AND src.SESSION_ID=dest.SESSION_ID)
WHEN MATCHED THEN 
    UPDATE SET  
IMSI = src.IMSI,
CHARGING_ID = src.CHARGING_ID,
SESSION_ID = src.SESSION_ID,
.... (same as above)
TOTAL_VOLUME_DOWN=src.TOTAL_VOLUME_DOWN,
TOTAL_VOLUME_UP=src.TOTAL_VOLUME_UP
WHEN NOT MATCHED THEN
INSERT VALUES (
src.IMSI,
src.CHARGING_ID,
src.SESSION_ID,
....
src.TOTAL_VOLUME_DOWN,
src.TOTAL_VOLUME_UP);


My view:
CREATE OR REPLACE VIEW FCT_SESSIONS_VIEW AS SELECT SESSION_ID, CHARGING_ID, IMSI, TOTAL_VOLUME_DOWN,TOTAL_VOLUME_UP FROM
 (SELECT
row_number() OVER w_stop AS rank_0,
SESSION_ID,
    CHARGING_ID,
IMSI,
....
TOTAL_VOLUME_DOWN,
TOTAL_VOLUME_UP
FROM MBBQOE.S_SESSION 
  WHERE  IMSI <> 'UNKNOWN'
  WINDOW
w_stop AS (PARTITION BY SESSION_ID,CHARGING_ID,IMSI ORDER BY STOP_TIME DESC),
w_start AS (PARTITION BY SESSION_ID,CHARGING_ID,IMSI ORDER BY START_TIME) 
  ORDER BY SESSION_ID,CHARGING_ID,IMSI 
) ranked
WHERE rank_0 = 1;