Currently, the VARCHAR columns are assigned the maximum size allowed (32K UNICODE) while identifying the Variable Length string in Iceberg data. This can cause an issue while trying to CONCATENATE two VARCHAR strings as we have used the Maximum Size. Starting in version 20.00.19, the VARCHAR size is determined from the OTF metadata. If the VARCHAR size cannot be determined from the metadata, then 32000 is used by default.
- A workaround is available to get the actual maximum length of the VARCHAR column and then cast it to the actual size and then concatenate it.
The example shown below:
SELECT (PICKUP_ZONE || ITINERARY) FROM ICEBERG_GLUE_1.glue_catalog.itinerary;
*** Failure 3798 A column or character expression is larger than the max size.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
# WORKAROUND
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select max(character_length (PICKUP_ZONE)), MAX(character_length (DROPOFF_ZONE)) from ICEBERG_GLUE_1.glue_catalog.itinerary;
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 2 seconds.
Maximum(Characters(pickup_zone)) Maximum(Characters(dropoff_zone))
-------------------------------- ---------------------------------
18 18
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
SELECT (cast (PICKUP_ZONE as char(18)) || cast(ITINERARY as char(18)) ) FROM ICEBERG_GLUE_1.glue_catalog.itinerary;;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 2 seconds.
Minimum(avg_dropoff_latitude)
-----------------------------
4.10000000000000E 001