Bug 6464

Summary: Python loader / COPY LOADER INTO overrules defaults with NUL
Product: SQL Reporter: Stefan Manegold <Stefan.Manegold>
Component: allAssignee: SQL devs <bugs-sql>
Status: REOPENED ---    
Severity: enhancement CC: niels
Priority: Normal    
Version: -- development   
Hardware: All   
OS: All   

Description Stefan Manegold cwiconfidential 2017-11-11 23:50:06 CET
When loading data via COPY LOADER INFO using a PYTHON LOADER function, but omitting columns in the emit() statements, the implementation of the emit() function replaces these columns by NULL values, rather than skipping these columns using the columns' default values --- I'm not sure which part of the code does / is supposed to take care of using default values for omitted columns --- thus effectively overruling the default, incl. triggering unexpected errors in case an omitted columns has a default other than NULL and is (explicitly or implicitly) declared NOT NULL.

As an example, for this code:

CREATE LOADER myloader(x integer, y string) LANGUAGE PYTHON {
        z={}
        i=0
        for j in ('a','b','c','d','e','f','g'):
                i += 1    
                if j in y:
                        z[j] = x - i
        _emit.emit(z)
};
create table t (
        a integer auto_increment primary key,
        b integer generated always as identity (start with 2) unique,  
        c integer generated always as identity (start with 3) not null,
        d integer generated always as identity (start with 4),
        e integer default 5 not null,
        f integer default 6,
        g integer
);
COPY LOADER INTO t FROM myloader(-10,'abcdefg');
COPY LOADER INTO t FROM myloader(-20,'abcdef');
COPY LOADER INTO t FROM myloader(-30,'abcdeg');
COPY LOADER INTO t FROM myloader(-40,'abcdfg');
COPY LOADER INTO t FROM myloader(-50,'abcefg');
COPY LOADER INTO t FROM myloader(-60,'abdefg');
COPY LOADER INTO t FROM myloader(-70,'acdefg');
COPY LOADER INTO t FROM myloader(-80,'bcdefg');
select * from t;
drop table t;
drop loader myloader;

We get this results:

operation successful
operation successful
1 affected row
1 affected row
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.e
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.c
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.a
+------+------+------+------+------+------+------+
| a    | b    | c    | d    | e    | f    | g    |
+======+======+======+======+======+======+======+
|  -11 |  -12 |  -13 |  -14 |  -15 |  -16 |  -17 |
|  -21 |  -22 |  -23 |  -24 |  -25 |  -26 | null |
|  -31 |  -32 |  -33 |  -34 |  -35 | null |  -37 |
|  -51 |  -52 |  -53 | null |  -55 |  -56 |  -57 |
|  -71 | null |  -73 |  -74 |  -75 |  -76 |  -77 |
+------+------+------+------+------+------+------+
5 tuples
operation successful
operation successful

While we would expect this results:

operation successful
1 affected row
1 affected row
1 affected row
1 affected row
1 affected row, last generated key: 4
1 affected row, last generated key: 3
1 affected row, last generated key: 2
1 affected row, last generated key: 1
+------+------+------+------+------+------+------+
| a    | b    | c    | d    | e    | f    | g    |
+======+======+======+======+======+======+======+
|  -11 |  -12 |  -13 |  -14 |  -15 |  -16 |  -17 |
|  -21 |  -22 |  -23 |  -24 |  -25 |  -26 | null |
|  -31 |  -32 |  -33 |  -34 |  -35 |    6 |  -37 |
|  -41 |  -42 |  -43 |  -44 |    5 |  -46 |  -47 |
|  -51 |  -52 |  -53 |    4 |  -55 |  -56 |  -57 |
|  -61 |  -62 |    3 |  -64 |  -65 |  -66 |  -67 |
|  -71 |    2 |  -73 |  -74 |  -75 |  -76 |  -77 |
|    1 |  -82 |  -83 |  -84 |  -85 |  -86 |  -87 |
+------+------+------+------+------+------+------+
8 tuples
operation successful
Comment 1 MonetDB Mercurial Repository cwiconfidential 2017-11-11 23:53:27 CET
Changeset b6e8be70fea0, made by Stefan Manegold <Stefan.Manegold@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b6e8be70fea0

Changeset description:

	added test for bug 6464
Comment 2 Niels Nes cwiconfidential 2017-11-26 10:16:03 CET
the test has been approved with the correct result (as far as I can see).
Comment 3 MonetDB Mercurial Repository cwiconfidential 2017-11-26 13:02:52 CET
Changeset 0f5827e686f3, made by Stefan Manegold <Stefan.Manegold@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0f5827e686f3

Changeset description:

	test for Bug-6464: undo output approval of changeset b3bc5cb7c32e on default branch, only;

	while the behaviour in the Jul2017 release branch will not change,
	i.e., the Python loader does not support default values,
	I consider bug 6464 (still/now) a feature request for the default branch
	and have the (failing) test (with "desired" output) remind us of it.
Comment 4 Stefan Manegold cwiconfidential 2017-11-26 13:05:41 CET
While the bug is "fixed" in the Jul2017 branch by accepting that the Python loader does not support default values, and making it throw an error message,
I consider this now a feature request for the default branch and have the (failing) test (with "desired" output) remind us of it.
Comment 5 MonetDB Mercurial Repository cwiconfidential 2017-11-26 18:32:46 CET
Changeset dc98e14ee24a, made by Stefan Manegold <Stefan.Manegold@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dc98e14ee24a

Changeset description:

	test for Bug-6464: undo output approval of changeset b3bc5cb7c32e on default branch, only;

	while the behaviour in the Jul2017 release branch will not change,
	i.e., the Python loader does not support default values,
	I consider bug 6464 (still/now) a feature request for the default branch
	and have the (failing) test (with "desired" output) remind us of it.