MAPITEMS limit ?

bhervy

Hi,

We are using Vertica 9.

I get an error using MAPITEMS function :
[Vertica]VJDBC ERROR: Error calling processPartition() in User Function MapItems at [src/Dict.cpp:994], error code: 0, message: Exception while processing partition row: [1] on map items retrieval: [Tried to retrieve Flex Table data from a cell not containing an Flex Table map.]

The message that throw the error is a well formed JSON, the issue seems to be about his size.

With MAPKEYSINFO function, I obtain for the node that cause the issue :
keys: G_PRODUIT_SERVICE
length: 206460
type_oid: 199
row_num: 1
field_num: 14

MAPITEMS on this specific keys cause the error (no error on the others keys at the same level of the JSON).

On Vertica Documentation, I saw a note for MAPJSONEXTRACTOR function :
Note: The function fails if the output size of the function is greater than 65000.

But nothing is documented for a same limit with MAPITEMS.

Regards,
Benoît

Jim_Knicely

Hi,

I opened a support ticket per you issue. I will keep you updated on its progress to resolution.

Thanks for reporting this!

Jim_Knicely

It appears its not a size errror...

-- Example: 65K cap 
create flex table flex1(); 

-- Load value with length > 65K 
\! echo `printf '{"key" : "'; printf 'a%.0s' {1..65001}; printf '"}'` | vsql -c "copy flex1 from stdin parser fjsonparser();" 

-- Check length per MapItems 
select length(values) from (select mapitems(__raw__) over(partition best) from flex1) t; 
 length 
-------- 
  65000 
(1 row) 

-- Check actual virtual column length 
select length(key) from flex1; 
 length 
-------- 
  65001 
(1 row) 

-- Validate VMap. Should be 1 or -1 if invalid 
select mapversion(__raw__) from flex1; 
 mapversion 
------------ 
          1 
(1 row) 

bhervy

Hi,

Thank you for your quick answer.

mapversion(raw) return 1, so on the whole message but mapversion on the specific node that cause the MAPITEMS error return -1

   select mapversion(v)
   from (
         select values as v
         from (
                      select mapitems(__raw__) OVER (PARTITION BY "__identity__")
                      FROM cq_data_aa.f_r5_offre
                      where f_r5_offre."__identity__" = 3250034
                ) a
         where keys = 'G_PRODUIT_SERVICE'
         --where keys = 'G_CRITERE'
         )b

select length(values) from (select mapitems(raw) over(partition best) from cq_data_aa.f_r5_offre where f_r5_offre."identity" = 3250034) t where keys = 'G_PRODUIT_SERVICE';

64800

What is the difference between length from MAPKEYSINFO and length from LENGTH functions ?

If it's not a size issue, what could cause this error ?

Regards,
Benoît

Jim_Knicely

@bhervy - Can you post the actual query that is causing the error along with the error message?