#1568 String truncation in derived view column
Please see the attached DB with a text table and a CSV with some large values for certain columns (>32K).
When you do select * from v_view, you get 'data exception: string data, right truncation'. It seems there is an intermediate VARCHAR of length 32K being used in the view calculation which gets exceeded (the same happens if you just do the view definition query on its own).
Is this perhaps related to, as you state in the guide, "a VARCHAR column declaration that has no size, is given a 32K size"? [but there isn't any way of hinting to the view that the derived column will be >32K]
The table column definitions were LONGVARCHAR in the table definition, but I've tried various combinations, and also doing a e.g LEFT(xxx, 1024) for each column as part of the view definition, but nothing seems to work.
There is no problem with the column definitions as LONGVARCHAR.
The error relates to the last column of the view. The VARCHAR(32K) comes from evaluation of NVL(...) functions. As the result of concatenation exceeds 32K, the errror is thrown. This behaviour is not ideal and may change in the next version.
You could eliminate NULL from your table columns by declaring them NOT NULL DEFAULT '' and avoid using NVL(), which is translated internally to the equivalent IFNULL().
Last edit: Fred Toussi 2020-02-20
Thanks a lot, I'll try that.
Doesn't work with text tables unfortunately, it rejects loading the CSV file because of empty columns:
NOT NULL check constraint; SYS_CT_10093 table: T_TABLE column: PASSENGERCONDITION
I have fixed the NVL issue and will also add a text table property to use the column default when an empty (NULL) field is encountered in the text table source (CVS) file. Will probably commit the changes today and add a comment here.
Enhancement committed.
Use the new property null_def, together with DEFAULT '' NOT NULL for all table columns that can be empty strings in the CSV file.
SET TABLE PUBLIC.T_TABLE SOURCE 'Demo.csv;fs=\semi;null_def=true'