INSERT WITH AUTO NAME should not fail on "Column 'z' not found"

[Breck_Carter]

INSERT x WITH AUTO NAME SELECT * FROM y should not fail on "Column 'z' not found" when y.z exists but not x.z.

Sometimes it is necessary to create tables that contain a subset or superset of the columns from another table. An example of a subset might be a temporary table containing only primary keys. An example of a superset might be an audit table that has a different, additional column as primary key, plus other columns for auditing purposes.

It is also sometimes convenient to make use of INSERT WITH AUTO NAME together with SELECT * when copying from one table to another.

This works fine when the target is a superset, but not when it is a subset. IMO columns in the SELECT that do not exist in the target table should be ignored... after all, that's what WITH AUTO NAME is for, and it already ignores the inverse: columns in the target table which are not specified in the SELECT.

CREATE TABLE x (
   a INTEGER,
   b INTEGER );

CREATE TABLE y (
   a INTEGER,
   b INTEGER,
   z INTEGER );

INSERT y VALUES ( 1, 2, 3 );
COMMIT;

-- This fails...

INSERT x WITH AUTO NAME SELECT * FROM y;

Could not execute statement.
Column 'z' not found
SQLCODE=-143, ODBC 3 State="42S22"
Line 1, column 1

-- This works...

INSERT x WITH AUTO NAME SELECT a, b FROM y;
SELECT * FROM x;

a,b
1,2

-- This also works...

INSERT x WITH AUTO NAME SELECT b FROM y;
SELECT * FROM x;

a,b
1,2
(NULL),2

[thomas_duemesnil]

I would see this a different approach to the existing one. If I prepare a select for use with insert AUTO NAME if often code

INSERT y 
WITH AUTO NAME 
SELECT FunctionResult() as a,
       C as b
FROM   x;

And I like the check that all values included in the select can be inserted in the destination table.

WITH ONLY EXISTING AUTO NAME 

could be a extension to the current behaviour an clearly indicate the different behaviour.

[VolkerBarth]

Good idea, and I would suggest to leave the current behaviour with the current syntax, and add something like an "WITH AUTO NAME ON MISSING [SKIP | ERROR]" clause. "ERROR" would then be the current behaviour.

[VolkerBarth]

I'm not sure I would want this enhancement.

As you state, the opposite (inserting into a superset) does work, but it relies on the fact that column z is implicitly declared as NULLable and as such, a NULL value is inserted. This corresponds to the AUTO NAME feature description (from the SA 12 docs):

Destination columns not defined in the query block are assigned their default value.

A simple sample based on your one:

INSERT x VALUES ( 2, 3 );
COMMIT;
INSERT y WITH AUTO NAME SELECT * FROM x; -- succeeds, inserts NULL into y.c

So, here the "SELECT *" result set will be used and for non-contained columns, their default is used - similar to an INSERT with a specified column-list.

But in your suggestion, the result set of the "SELECT *" would only be used partially. IMHO (and it's more of a feeling), that would contradict my understanding of the INSERT SELECT construct - specifying lesser columns then are contained in the target seems naturally, specifying more columns not.

Just my 2 cents, and as said, somewhat "more of a feeling" than real reasoning:)

[Breck_Carter]

The situation now is, if I want to copy 5 columns out of 50, I have to code the column names explicitly and cannot code SELECT *. This means I cannot take advantage of SELECT * which would be this: adding a 6th column to the target table means I have to add it to the SELECT rather than have the WITH AUTO NAME pick it up automatically. I someone doesn't like this behavior they are free to continue with the way they currently have to code it. Question: Do you use WITH AUTO NAME at all, currently?

[VolkerBarth]

@Breck (part I): I see your requirements, and I agree that AUTO NAME can help to adapt queries automatically to schema changes - I have used it for such cases, too. And obviously (and for good reasons) I'm in no position at all to prevent suggestions to get implemented:).

[VolkerBarth]

@Breck (part II): But imagine, your table x had a third column, and that were mistakenly named d instead of c - a case uncommon for such small tables but more common for tables with many columns. - So you would want AUTO NAME to skip that column without any warning, too, if it has a default value? IMHO, I would prefer to get prevented from such a use of AUTO NAME or to get a warning at least that there are missing columns in the target. But as said, just my 2 cents...