Bug? If table has "SET USING" column, COPYs with too many FILLER specs get ERROR 7943.

KKirkpatrick

Spotted in Vertica version 9.2
If a COPY command is loading a table and that table has a SET USING (or DEFAULT USING) column in ordinal position N, and the COPY command contains N or more "FILLER" column specifications (e.g. "TMP FILLER VARCHAR") , the COPY fails with:
ERROR 7943: Cannot modify column with SET USING expression ""

Quick demo script:

DROP SCHEMA IF EXISTS BUG_DEMO CASCADE;
CREATE SCHEMA BUG_DEMO;
SET SEARCH_PATH TO BUG_DEMO;

CREATE TABLE BUG_DEMO1 (C1 VARCHAR SET USING (SELECT 'x' FROM DUAL), C2 VARCHAR, C3 VARCHAR); -- C1 is SET USING
CREATE TABLE BUG_DEMO2 (C1 VARCHAR, C2 VARCHAR SET USING (SELECT 'x' FROM DUAL), C3 VARCHAR); -- C2 is SET USING
CREATE TABLE BUG_DEMO3 (C1 VARCHAR, C2 VARCHAR, C3 VARCHAR SET USING (SELECT 'x' FROM DUAL)); -- C3 is SET USING

--0 filler columns: no issue for SET USING in c1, c2, or c3:
COPY BUG_DEMO1 (C2,C3) FROM '/dev/null'; 
COPY BUG_DEMO2 (C1,C3) FROM '/dev/null'; 
COPY BUG_DEMO3 (C1,C2) FROM '/dev/null'; 

--1 filler column: ERROR 7943 for C1 only... C2 and C3 okay
COPY BUG_DEMO1 (F1 FILLER VARCHAR, C2,C3) FROM '/dev/null'; 
COPY BUG_DEMO2 (F1 FILLER VARCHAR, C1,C3) FROM '/dev/null'; 
COPY BUG_DEMO3 (F1 FILLER VARCHAR, C1,C2) FROM '/dev/null'; 

--2 filler columns: ERROR 7943 for C1 and C2... C3 okay
COPY BUG_DEMO1 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, C2,C3) FROM '/dev/null'; 
COPY BUG_DEMO2 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, C1,C3) FROM '/dev/null'; 
COPY BUG_DEMO3 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, C1,C2) FROM '/dev/null'; 


--3 filler columns: ERROR 7943 for all three (C1, C2, and C3)
COPY BUG_DEMO1 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, F3 FILLER VARCHAR, C2,C3) FROM '/dev/null'; 
COPY BUG_DEMO2 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, F3 FILLER VARCHAR, C1,C3) FROM '/dev/null'; 
COPY BUG_DEMO3 (F1 FILLER VARCHAR, F2 FILLER VARCHAR, F3 FILLER VARCHAR, C1,C2) FROM '/dev/null'; 

In working to pin this down, I tried many other variations of COPY syntax and table structure, but the only two factors that seemed to matter are those noted here:

• the column position of the "SET USING" column
• the total number of FILLER specifications in the COPY command.
  (and whether the latter is greater than or equal to the former).

mosheg

Very likely VER-66902 - Flatten table Can't be exported using Export functionality.
Fixed in version 9.3